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

廣告

MySQL 5.1.x & unicode

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/