2010/05/28

MySQL 5.x Repair Cheat Sheet

If you upgrade MySQL or move the database around, you are most likely need to do those operations:

Backup

 mysqldump -u root -p --default-character-set=utf8 databasename > data.sql

Restore

mysqldump -u root -p --default-character-set=utf8 databasename < data.sql
if database is larger then 100MB, this works better:



mysql -u root -p

#mysql> use the_database

#mysql> source data.sql

Check, Repair & Optimize All Tables in All Databases

If you need database server up, use mysqlcheck:



mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Or you can make it offline and do a better check:



service mysqld stop

myisamchk /var/lib/mysql/the_database/*.MYI

service mysqld start

Repair single table

 mysql -u root -p

#mysql> use the_database

#mysql> repair table the_table

Reference:
http://www.felipecruz.com/repair-mysql-database.php

2010/05/24

In our recent upgrade from 5.0 to 5.1.47, the new version behaves differently and it screw up character set when user submit info from our web app powered by .NET.

After digging around for a afternoon, this is how to fix it:



vi /etc/my.cnf

And add those lines to the config:



init_connect='SET collation_connection = utf8_general_ci'

init_connect='SET NAMES utf8'

default-character-set=utf8

character-set-server=utf8

collation-server=utf8_general_ci

skip-character-set-client-handshake

There are other options for collation besides utf8_general_ci such as utf8_unicode_ci. See this article:
http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

Reference: http://rhyous.com/2009/11/05/how-to-create-a-utf-8-unicode-database-on-mysql/

MINGSTER @ BLOGGER