Zimbra Mysql Crash Recovery

In the event of database corruption it may be necessary to manually perform database recovery. See Bug 15797 for an example of an issue with mysql that will require database recovery. In that example, a warning message like the following appeared in the mysql error log:

InnoDB: Serious error! InnoDB is trying to free page 716
InnoDB: though it is already marked as free in the tablespace!
InnoDB: The tablespace free space info is corrupt.
InnoDB: You may need to dump your InnoDB tables and recreate the whole
InnoDB: database!

Before beginning a full database recovery, check to see if the corruption may be limited to a single mboxgroup or a single user within an mboxgroup. This type of corruption frequently lets the server run normally for extended periods of time, with crashes occurring only when an affected user attempts to access certain mailbox items. If this is the case, it may be possible to dump, drop and recover only the affected entries without disrupting the database as a whole. Please see the instructions in the Mysql Crash Recovery (alternate method) article.

Overview of Recovery Process

1. Configure mysql to start in recovery mode
2. Generate SQL dumps of all relevant databases
3. Remove all existing (and possibly corrupt) databases
4. Re-create all databases
5. Repopulate the databases with the data from the SQL dumps
6. Test databases and start all ZCS services

Details of Recovery Process

1. Configure mysql to start in recovery mode

1. Edit the file /opt/zimbra/conf/my.cnf and add a line like innodb_force_recovery = 1 under the [mysqld] section (Note that it may be necessary to increase the recovery level depending on the extent of the database corruption, as shown at the end of the database dump step)
2. Save the file and re-start mysqld

mysql.server start

2. Generate SQL dumps of all databases

1. Load some mysql configuration into shell variables (i.e. $mysql_socket and $mysql_root_password; note that you will use these again in step 3)
2. Make a list of the existing databases
3. Create a directory to hold the SQL dumps
4. Generate the SQL dumps from the database list

source ~/bin/zmshutil ; zmsetvars

mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra > /tmp/mysql.db.list

Note: If you are using ZCS v8.8.x with Chat/Talk enabled then you should take Chat database dump as well

mysql --batch --skip-column-names -e "show databases" | grep -e mbox -e zimbra -e chat > /tmp/mysql.db.list

mkdir /tmp/mysql.sql 

for db in `cat /tmp/mysql.db.list`; do
     mysqldump $db -S $mysql_socket -u root --password=$mysql_root_password > /tmp/mysql.sql/$db.sql
     echo "Dumped $db"
     sleep 10
 done

Note: If you encounter any mysql errors while dumping the databases, start over by re-editing /opt/zimbra/conf/my.cnf, incrementing the value for innodb_force_recovery by one, and restarting mysqld. It is critical to update this incrementally - 1, 2, 3, and only if needed 4. 4 and above can cause DB corruption. Please see MySQL's Forcing InnoDB Recovery guide for more information.

Note: Starting 8.7 , path of mysqldump has been changed from /opt/zimbra/mysql/bin/mysqldump to /opt/zimbra/common/bin/mysqldump . Please update the command accordingly if you are doing this for a system >= ZCS 8.7.x

Note: An error of "bash: /tmp/mysql.sql/$db.sql: ambiguous redirect" probably indicates your using an apostrophe or single quote ' rather than a tick ` -- which is one the same key as the tilde ~ .

Note: Do not reboot the machine, as some Operating Systems will remove all contents in the /tmp directory during the reboot sequence, i.e. your /tmp/mysql.sql will be removed.

HINT Did the dump work or not, try grep -L "Dump completed" /tmp/mysql.sql/*.sql [those that didn't] and grep "Dump completed" /tmp/mysql.sql/*.sql [those that did].

3. Remove all existing (and possibly corrupt) databases

Note: Take a copy of /opt/zimbra/db/data before dropping the databases. This will ensure a copy of old database.

Note that we drop the zimbra database last because the mboxgroup* databases depend on it

for db in `cat /tmp/mysql.db.list |grep mbox`
do
    mysql -u root --password=$mysql_root_password -e "drop database $db"
    echo -e "Dropped $db"
done

mysql -u root --password=$mysql_root_password -e "drop database zimbra"

Remove existing InnoDB tablespace and log files

rm -rf /opt/zimbra/db/data/ib*

Note: First, use with caution - this shouldn't need to be used often. Issue came about because of some rsync issues. Can't dump db's because of 'connection' issues at this point? One could move the /opt/zimbra/db/data directory - mv /opt/zimbra/db/data /opt/zimbra/db/data-old and then make the db - mkdir /opt/zimbra/db/data w/ ownership of zimbra:zimbra . Remove the innodb_force_recovery line from /opt/zimbra/conf/my.cnf . Then recreate a default mysql db by running /opt/zimbra/libexec/zmmyinit --sql_root_pw $mysql_root_password and then attempt this steps over again to confirm you can drop them. Also note that you may have to reset the zimbra password manually in mysql, then set it again in Zimbra with the instructions from this page: http://wiki.zimbra.com/wiki/Resetting_LDAP_%26_MySQL_Passwords

4. Re-create all databases

1. Run mysql in non-recovery mode
   1. Remove the innodb_force_recovery line from /opt/zimbra/conf/my.cnf
   2. Save the file and restart mysqld
2. Re-create the databases from the database list

mysql.server restart

for db in `cat /tmp/mysql.db.list`
do
    mysql -e "create database $db character set utf8"
    echo "Created $db"
done

5. Repopulate the databases with the data from the SQL dumps

Import the data from the SQL dumps. Note that we import the zimbra database first because the mboxgroup databases depend on it

mysql zimbra < /tmp/mysql.sql/zimbra.sql

for sql in /tmp/mysql.sql/mbox*
do
    mysql `basename $sql .sql` < $sql
    echo -e "Updated `basename $sql .sql` \n"
done

Note : If you are using ZCS v8.8.x with Chat/Talk enabled then you should import chat db as well.

mysql chat < /tmp/mysql.sql/chat.sql

6. Test databases and start all ZCS services

Note that this is an example query. If you know of any particular databases that were corrupt, you may want to construct other queries to verify normal access to the data.

mysql zimbra -e "select * from mailbox order by id desc limit 1"

Once you are satisfied that the databases are restored intact, start the rest of the zimbra services.

zmcontrol start

Check /opt/zimbra/log/mysql_error.log and /opt/zimbra/log/mailbox.log for database errors.

Reference: https://wiki.zimbra.com/wiki/Mysql_Crash_Recovery

How to automatically backup mysql database using mysqldump?

It's a really good idea to use a least-privilege approach to most system administration tasks, and especially automated ones. This post describes using a "read only" MySQL user to handle backing up MySQL databases.

We use mysqldump to backup our databases on a regular basis, using scripts like this one:

#!/bin/sh

DIR=/backup/mysql/
DATESTAMP=$(date +%Y%m%d)
DB_USER=backup
DB_PASS='readonly'

# remove backups older than $DAYS_KEEP
DAYS_KEEP=30
find ${DIR}* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null

# create backups securely
umask 006

# list MySQL databases and dump each
DB_LIST=`mysql -u $DB_USER -p"$DB_PASS" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
  FILENAME=${DIR}${DB}-${DATESTAMP}.sql.gz
  mysqldump -u $DB_USER -p"$DB_PASS" --opt --flush-logs $DB | gzip > $FILENAME
done

You'll note that this script uses the user 'backup' to do the dumping. This is because our production servers grant potentially dangerous permissions (such as DROP TABLE) on a per-database basis. In order to run an automated backup, however, we need a single user that has just enough permissions to read from all the databases, but not enough to pose a risk to them.

The MySQL permissions required for the script above are SHOW DATABASES, SELECT, LOCK TABLES, and RELOAD. Grant them by entering the mysql command line and issuing these commands (choosing a better password than 'readonly' of course)

GRANT SHOW DATABASES, SHOW VIEW, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost
 IDENTIFIED BY 'readonly';
FLUSH PRIVILEGES;

You can now back up all your databases by way of a single MySQL account that has just enough access to do the job, and not enough to cause significant harm. Which is what least-privilege access is all about.

Credit to: Stevem

PHP 4 can not connect to mysql server with error "Client does not support authentication protocol"

MySQL 4.1+ uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older (pre-4.1) clients. MySQL extension for PHP4 was compiled with old (pre-4.1) client library.

When you connect to server. It will shows error

"Client does not support authentication protocol requested by server; consider upgrading MySQL client"

To solve this problem, you should use one of the following approaches:
1. reset password to old style password

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

For PHP mysql_connect command, you must specify parameters following

mysql_connect($server,$user,$pass,false,0);

2.Tell the server to use the older password hashing algorithm:
add this line to mysql configuration (my.ini/my.cnf)

[mysqld]
.
.
old-passwords = 1
.
.
[client]
.
.
old-passwords = 1
.
.

then restart service and reset password using command

SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('newpwd');

MySQL native driver for PHP can not connect to mysql. It shows error "mysqlnd cannot connect to MySQL 4.1+ using old authentication"

Symptom
MySQL native driver for PHP can not connect to mysql. It shows error "mysqlnd cannot connect to MySQL 4.1+ using old authentication"

Cause
New version of MySQL native driver for PHP uses an authentication protocol based on improved password hashing algorithm that is incompatible with an account that still has a pre-4.1-style password. 


Solution 
Reset the password to 4.1+ style for each user that needs to use the client program. 


SET SESSION OLD_PASSWORDS = FALSE; 
USE mysql;
UPDATE user SET password = PASSWORD('newpass') WHERE user='someuser'; 
FLUSH PRIVILEGES;

Clean up cache and log for magento

Create cleanup.php file in document root folder then put these code to it, edit database variables, save and run cleanup.php?clean=log or cleanup.php?clean=var


<?
$db['host'] = '';                    // Database hostname
$db['name'] = '';                    // Database name
$db['user'] = '';                    // Database username
$db['pass'] = '';                    // Database password
$db['pref'] = '';                    // Database tables' prefix

if($_GET['clean'] == 'log') clean_log_tables();
if($_GET['clean'] == 'var') clean_var_directory();

function clean_log_tables() {
    global $db;
 
    $tables = array(
        'log_url',
        'log_url_info',
        'log_visitor',
        'log_visitor_info'
    );
 
    mysql_connect($db['host'], $db['user'], $db['pass']) or die(mysql_error());
    mysql_select_db($db['name']) or die(mysql_error());
 
    foreach($tables as $v => $k) {
        mysql_query('TRUNCATE `'.$db['pref'].$k.'`') or die(mysql_error());
    }
}

function clean_var_directory() {
    $dirs = array(
        'var/cache/',
        'var/log/',
        'var/report/',
        'var/session/',
        'var/tmp/',
        'downloader/pearlib/cache/*',
        'downloader/pearlib/download/*'
    );
 
    foreach($dirs as $v => $k) {
        exec('rm -rf '.$k);
    }
}
?>


Reference: http://www.magentocommerce.com/boards/viewthread/36148/#t117754

How can I know what configure options were used to compile it?

You can see the configure options using the mysqlbug command-line utility.

In your shell type mysqlbug and you'll see a template e-mail for bug submission.
At the bottom end of that e-mail you can see the configure line with all the options your copy of MySQL was compiled with.

Reference: http://serverfault.com/questions/62212/configure-options-mysql-server-was-compiled-with

Client does not support authentication protocol

ใน MySQL 4.1 ขึ้นไปนั้น ได้ใช้ hashing algorithm ในการเก็บ password เพื่อใช้ในการ Authentication Protocol แบบใหม่ซึ่งทำให้ยังไม่สนับสนุนใน PHP หรือ software client ใน version เก่าๆ (หรือใหม่ๆ บางตัวก็ยังไม่สนับสนุน) ถ้าคุณ upgrade ไปเป็น MySQL 4.1 หรือสูงกว่า การติดต่อเพื่อเข้าไปใช้งานนั้น จะขึ้นข้อความว่า

Client does not support authentication protocol requested by server; consider upgrading MySQL client.

สาเหตุ มาจากการที่ MySQL ได้ทำการปรับเปลี่ยนการเข้ารหัส password ใหม่อย่างที่ได้กล่าวไปแล้วโดย ถ้าใน MySQL version ก่อน 4.1 นั้นใช้การเข้ารหัสที่มีความยาว 16 bytes แต่ใน version ตั้งแต่ 4.1 ขึ้นมาถึงปัจจุบันนี้จะใช้การเข้ารหัสที่มีความยาว 41 bytes ซึ่ง client บางตัวยังไม่สนับสนุน password hashing algorithm แบบนี้ครับ ซึ่งการเข้ารหัส password ที่มีความยาว 41bytes นี้จะทำให้การถอดรหัสเพื่อทำการ hack ข้อมูลนั้นทำได้ยากขึ้นมากเลยทีเดียวครับ

โดยถ้าเราเปรียบเทียบจากตัวก่อนหน้า version 4.1 จะได้

mysql> SELECT PASSWORD(’mypass’);
+——————–+
| PASSWORD(’mypass’) |
+——————–+
| 6f8c114b58f2ce9e |
+——————–+

แต่ถ้าเราใช้คำสั่งเดียวกันใน version หลังจาก 4.1 จะได้

mysql> SELECT PASSWORD(’mypass’);
+———————————————–+
| PASSWORD(’mypass’) |
+———————————————–+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+———————————————–+

จะเห็นความแตกต่างของการเข้ารหัส password ของ MySQL ครับ

การแก้ไขปัญหานี้ทำได้โดยการ

* ทำการ upgrade ตัว client ที่ติดต่อกับ MySQL ให้เป็น version ที่สนับสนุน hashing algorithm ที่เก็บ password ใหม่ใน MySQL 4.1 ขึ้นไป (client ที่ว่านี้หมายถึง PHP MySQL Module, MySQL Front, PHPMyAdmin และตัวจัดการข้อมูลต่างๆ ที่เชื่อมต่อกับ MySQL ถือเป็น client ทั้งหมดครับ)
* เมื่อทำการติดต่อกับ server โดยใช้ pre-4.1 client program ให้ใช้บัญชี username ที่ใช้ pre-4.1-style password แทนการใช้ username ที่ใช้ style password แบบเก่า
* ทำการ Reset password ไปเป็น pre-4.1 style โดยใช้คำสั่ง SET PASSWORD และ OLD_PASSWORD() function โดยทำใน MySQL Command Line Client ซึ่งใช้คำสั่งดังนี้

mysql> SET PASSWORD FOR

-> ‘some_user‘@’some_host‘ = OLD_PASSWORD(’newpwd‘);

* some_host ให้เปลี่ยนเป็น hostname ที่ใช้เช่น localhost, 127.0.0.1, 192.168.0.1 หรือที่เป็น hostname นั้นๆ
* some_user ให้เปลี่ยนเป็น username ที่ใช้เช่น root, admin หรือ username อื่นๆ ที่ต้องการ
* newpwd ให้เป็นเป็น password ที่ใช้เช่น 1234, abcde หรือที่ต้องการ

ตัวอย่าง :

mysql> SET PASSWORD FOR

-> ‘root‘@’localhost‘ = OLD_PASSWORD(’1234‘);

คือกำหนด password ใหม่ให้กับ root ที่ localhost โดยใช้ hashing algorithm password แบบเก่าโดยกำหนด password คือ 1234

แล้วทำการ UPDATE และ FLUSH PRIVILEGES โดยใช้สั่ง

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’newpwd‘)

-> WHERE Host = ‘some_host‘ AND User = ‘some_user‘;

mysql> FLUSH PRIVILEGES;

ตัวอย่าง :

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD(’1234‘)

-> WHERE Host = ‘localhost‘ AND User = ‘root‘;

mysql> FLUSH PRIVILEGES;

* ด้วยวิธีด้านบนนี้เหมาะสำหรับใช้ในกรณีที่มี user ที่อยู่ในฐานข้อมูลน้อยๆ เท่านั้น มิเช่นนั้นแล้วอาจจะต้องเปลี่ยนกันมือหงิก -_-"

* ถ้ามี user ในฐานข้อมูลมากๆ และการปรับเปลี่ยนวิธีด้านบนทั้งหมดเป็นการยากในการทำระบบฐานข้อมูลกลับมา ใช้งานได้ปกติในเวลาอันสั้น เราสามารถบอกให้ MySQL ใช้ password hashing algorithm แบบเก่าตอน start-up service ได้เลย แต่แนะนำให้ใช้งานชั่วคราวเท่านั้นครับ และเมื่อมีเวลาให้ทำการปรับเปลี่ยนไปใช้ระบบ password hashing algorithm แบบใหม่แทน

โดยเริ่มการทำงานของ mysqld ด้วย –old-passwords ที่เป็น option command

ตัวอย่าง :

c:\MySQL\bin\mysqld –old-passwords


ที่มา: http://www.thaicyberpoint.com/ford/blog/id/101/

ลืม password mysql จะสามารถกู้ข้อมูลได้อย่างไร

ขั้นตอนแรก
แก้ไขไฟล์คอนฟิก my.cnf หรือ my.ini โดยเพิ่มบรรทัดต่อไปนี้เข้าไปในส่วนของ [mysqld]
skip-grant-tables
แล้ว restart service mysql

ขั้นตอนที่สองให้พิมพ์คอมมานด์ต่อไปนี้
mysql -u root mysql


ขั้นตอนที่สามให้รัน statement ต่อไปนี้
update user set password=password('newpassword') where user='root';
flush privileges

ขั้นตอนที่สี่
แก้ไขไฟล์คอนฟิก my.cnf หรือ my.ini โดยเอาบรรทัดต่อไปนี้ออกจากส่วนของ [mysqld]
skip-grant-tables
แล้ว resert service mysql

เสร็จแล้วครับ
ลองทดสอบโดยคำสั่ง
mysql -u root -pnewpassword

เราจะสามารถทราบ root password ของ mysql ใน plesk ได้อย่างไร

ขึ้นอยู่กับชนิดของ database server ที่ใช้เก็บฐานข้อมูลของ plesk

============================================
กรณีที่เป็น Microsoft Access
============================================
Start >>Run ใส่ %plesk_dir%\admin\db\ แล้วกด Enter
เปิดไฟล์ psa.mdb ด้วยโปรแกรม Microsoft Access
============================================
กรณีที่เป็น Microsoft SQL Server
============================================
เปิดโปรแกรม Microsoft SQL Management Studio เพื่อเชื่อมต่อไปยัง Database Server แล้วเลือก database ที่ชื่อ psa
============================================
กรณีที่เป็น MySQL
============================================
ให้เปิด command prompt แล้วรันคอมมานด์
cd %plesk_dir%\Databases\MySQL\bin
mysql -P8306 -uadmin -p psa
============================================

จากนั้นให้เปิดดู table ที่ชื่อ DatabaseServers เพื่อดูข้อมูล admin ที่ใช้จัดการ database server

mysqldump ข้อมูลภาษาไทยเพี้ยน จะกู้กลับคืนมาได้อย่างไร

การใช้คำสั่ง mysqldump ข้อมูลภาษาไทยเพี้ยน จะ Import เข้าไปได้อย่างไร ในการแก้ปัญหาเรื่องภาษาไทยเพี้ยน จำเป็นต้องมีความรู้ความเข้าใจใน character set เป็นอย่างดีพอสมควร อ่านได้ที่ ความรู้เรื่อง mysql ตอน ปัญหาภาษาไทย กับ character set

ในบทความนี้ผมจะไม่กล่าวถึงเรื่องเกี่ยวกับ character set แต่จะกล่าวถึงวิธีการกู้ข้อมูลที่ผิดเพี้ยนไปนั้นกลับขึ้นมา

โดยก่อนอื่นให้ดูจากไฟล์ที่ dump ขึ้นมาว่ามีความผิดปกติในการเข้ารหัสข้อมูลภาษาไทยอย่างไรบ้าง

เช่น ถ้าตัวอักษรภาษาไทยกลายเป็นชุดอักษรสามตัวที่ขึ้นต้น ภหมายความว่ามีการเข้ารหัสด้วย utf8 แต่ถอดรหัสด้วย latin1

วิธีแก้ไข คือ
1. import ข้อมูลเดิมเข้าไปโดยไม่ต้องสนใจภาษาที่ผิดเพี้ยน
2. export อีกครั้งโดยเลือก default charset เป็น latin1 ด้วยคำสั่ง

mysqldump -u root -ppassword --default-character-set=latin1 --skip-set-charset -B dbname > dbname.sql

3. import ข้อมูลที่ export มาใหม่ โดยเลือก default charset เป็น utf8 ด้วยคำสั่ง

mysql -u root -ppassword --default-character-set=utf8 < dbname.sql

จะเห็นได้ว่า keyword ของการกู้ข้อมูลที่มีการเข้ารหัสผิดพลาด คือจะต้องทราบว่าข้อมูลผ่านการเข้ารหัสมาด้วย charset อะไรบ้าง นั่นเอง

เพื่อให้เข้าใจง่ายยิ่งขึ้น
ให้ นึกภาพข้อมูลที่รับส่งกันภายในคอมพิวเตอร์หรือระหว่างคอมพิวเตอร์ จะส่งกันเป็นข้อมูลไบนารี ดังนั้นเมื่อต้องการส่งข้อมูลที่เป็นตัวอักษรต่างๆ จะต้องมีการเข้ารหัสตัวอักษรให้เป็นตัวเลขฐานสองหรือข้อมูลไบนารีก่อน จากนั้นจึงส่งไปยังปลายทาง จากนั้นเมื่อผู้รับได้รับข้อมูลที่เป็นไบนารี จึงถอดรหัสข้อมูลไบนารีเป็นข้อมูลตัวอักษรอีกที

ดูตารางการเข้ารหัสสำหรับ UTF8
ดูตารางการเข้ารหัสสำหรับ TIS620
ดูตารางการเข้ารหัสสำหรับ LATIN1

จะ เห็นว่า ตัวอักษร ก เมื่อเข้ารหัสด้วย utf8 จะได้ข้อมูล 3 ไบต์ คือ 0xe0 0xb8 0x81 (เลขฐานสิบหก) จากนั้นเมื่อนำมาถอดรหัสด้วย latin1 จะได้ e0=à b8=¸ 81=ไม่มีค่า เป็นต้น

บทความที่เกี่ยวข้อง
mysqldump ข้อมูลภาษาไทยเพี้ยน จะกู้กลับคืนมาได้อย่างไร
ความรู้เรื่อง mysql ตอน ปัญหาภาษาไทย กับ character set
mysql: เมื่อภาษาไทยกลายเป็น ?????????

การ dump ข้อมูลใน MySQL ให้ถูกต้องตาม Character-set

สำหรับบันทึกนี้ขอกล่าวถึงเคล็ดลับในการแปลงฐานข้อมูลจากเดิมที่เป็น latin1 หรือ tis620 ให้เป็น utf8 นะครับ

มี เงื่อนไขเบื้องต้นว่า หาก character-set ของฐานข้อมูลเป็น tis620 หรือ latin1 (ความจริงเป็น tis620 ครับ เพราะ latin1 ไม่ใช่ character-set ของภาษาไทย) ต้องไม่กำหนดค่า

default-character-set=utf8

ใน my.cnf (สำหรับ Linux อยู่ที่ /etc/my.cnf หรือ /etc/mysql/my.cnf)

หลาย คนอาจจะชินกับการใช้ phpMyAdmin หากใช้แล้วไม่มีปัญหาก็ใช้ต่อไปครับ โดยปกติ เวลาจะ dump ข้อมูลโดยใช้ phpMyAdmin ก็ใช้วิธี export ออกมาเป็น SQL จะดีที่สุด วิธีที่จะดูว่า มีปัญหาหรือไม่ ก็คือ ลองเปิดไฟล์ที่ Export ออกมานั้นด้วย Text Editor ดู หรือใช้ command อื่นๆ เปิดดูก็ได้ครับ หากสามารถอ่านออกเป็นภาษาไทยได้ ก็แสดงว่า ไม่มีปัญหาครับ

ใน ที่นี้อยากแนะให้ใช้คำสั่ง mysqldump ซึ่งเป็น command line tool ที่นิยมใช้ใน Linux ครับ เข้าใจว่า ใน MS WIndows ก็มีเหมือนกัน วิธีใช้ก็ไม่น่าจะต่างกันมากครับ

ในการ dump ข้อมูล สิ่งที่ต้องทราบคือ character-set ของข้อมูลคือ อะไร ส่วนใหญ่จะไม่หนีจาก 3 ตัวนี้ครับ คือ latin1, tis620 และ utf8

หากไม่ทราบ ก็ลองด้วยการกำหนด character-set ไปเรื่อยๆ

วิธีการใช้คำสั่งก็ คือ

mysqldump --default-character-set=latin1 -h MySQLserver -u username -p DBName >DBName.sql

ตัว สีแดงคือ ค่าที่ต้องกำหนดให้ถูกต้องครับ หากเครื่องที่ทำการเรียกคำสั่ง mysqldump เป็นเครื่องเดียวกับ MySQL Server ก็ไม่ต้องมี "-h MySQLserver" ก็ได้ครับ

username คือ ชื่อ user ที่มีสิทธิ์ในการ access ฐานข้อมูล DBName

หาก โปรแกรมจัดการฐานข้อมูลที่ใช้อยู่แสดงผลเป็น character-set 8 bits (พวก tis-620, windows-874, iso-8859-11) การกำหนด --default-character-set=latin1 มักจะไม่มีปัญหาครับ ซึ่งหมายถึงอ่านออกเป็นภาษาที่ถูกต้องแน่นอนครับ

แต่สิ่งที่ต้องทราบต่อไปก็คือ ภาษาไทยที่แสดงนั้น มี character-set ที่แท้จริงเป็น tis620 ครับ ไม่ใช่ latin1 ครับ

หากเปิดไฟล์ DBName.sql ดู จะพบว่า ที่หัวไฟล์มีหลายบรรทัดเป็น

-- MySQL dump 10.9
--
-- Host: localhost Database: moodle
-- ------------------------------------------------------
-- Server version 4.1.20

ส่วน 5 บรรทัดข้างบนนี้ เป็น comment จะไม่สนใจก็ได้ครับ

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 *
/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

หลาย บรรทัดเหล่านี้ดูเหมือนจะเป็น comment แต่ไม่ใช่ครับ มันมีความหมายครับ เช่น 40101 หมายถึง MySQL รุ่น 4.1.1 และ 40014 หมายถึง MySQL รุ่น 4.0.14 และในแต่ละบรรทัดก็มีความหมายว่า หากเป็น MySQL รุ่นเท่ากับหรือสูงกว่าที่บอกไว้ต้นบรรทัด ก็จะ run คำสั่งที่ตามมา เช่น

/*!40101 SET NAMES latin1 */; ก็หมายถึง หากเรา import ข้อมูล DBName.sql เข้า MySQL Server รุ่นที่เท่ากับหรือสูงกว่า 4.1.1 ก็จะมีการสั่งให้ SET NAMES latin1 ซึ่งหมายถึงการตั้งค่าการสื่อสารข้อมูลด้วย character-set lantin1 แต่ถ้าเป็นการ import ข้อมูลเข้า MySQL Server รุ่นที่ต่ำกว่า 4.1.1 บรรทัดนั้นก็จะถูกข้ามไปครับ

สิ่งที่สำคัญในที่นี้ก็คือ การกำหนดให้เป็น latin1 ในที่นี้ อาจจะทำให้ import ข้อมูลผิดพลาด หากไม่แน่ใจก็ให้ลบบรรทัดนี้ทิ้งไป

ส่วนที่จะเป็นปัญหาต่อมา คือ ในส่วนของไฟล์ที่เกี่ยวกับการสร้างตาราง เช่น

CREATE TABLE `mdl_assignment` (
`id` int(10) unsigned NOT NULL auto_increment,
`course` int(10) unsigned NOT NULL default '0',
`description` text NOT NULL,
`format` tinyint(2) unsigned NOT NULL default '0',
`resubmit` tinyint(2) unsigned NOT NULL default '0',
`type` int(10) unsigned NOT NULL default '1',
`maxbytes` int(10) unsigned NOT NULL default '100000',
`timedue` int(10) unsigned NOT NULL default '0',
`grade` int(10) NOT NULL default '0',
`timemodified` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `course` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ให้สังเกตที่บรรทัดสุดท้ายครับ ตรงนี้ ให้ทำการลบ DEFAULT CHARSET=latin1 ออกให้หมด

ตรวจสอบไฟล์ให้แน่ใจว่า สามารถอ่านได้เป็นปกติ โดยที่เป็น tis-620

ขั้นตอนต่อมา คือ การ import ข้อมูลเข้า MySQL Server ที่กำหนด default-character-set=utf8 ใน my.cnf แล้ว

โดยปกติ ก็ใช้คำสั่ง

mysql --default-character-set=tis620 -h MySQLserver -u username -p DBName < DBName.sql

สังเกต ว่า การกำหนด --default-character-set=tis620 ในคำสั่งนี้ ต้องระบบ character-set ของไฟล์ให้ตรงกัน โดยไม่จำเป็นต้องแปลงไฟล์ให้เป็น utf8 ครับ เพราะ MySQL Server จะทำการแปลงให้เอง

สิ่งสำคัญต่อไป ก็คือ โปรแกรมที่ใช้จัดการฐานข้อมูลครับ ต้องใช้ character-set เหมือนกับของฐานข้อมูลเท่านั้นครับ และก็มีเคล็ดลับอีกเล็กน้อย ซึ่งจะได้กล่าวในบันทึกอื่นต่อไปครับ

ที่มา: http://share.psu.ac.th/blog/myopensource/5425