MySQLのデータをWindowsからLinuxへ移行する手順
Windowsで作成したMySQLのスキーマとデータをごっそりLinuxへ移すことにしたのだが、例によって文字コードなどの問題があり、素直には行かなかった。メモとして残しておこう。mysqldumpを用いてWindowsのMySQLで作成したデータベースをバックアップし、それをLinuxのMySQLにmysqlを使ってリストアする手順なのだが、2点ほど注意が必要だった。
それぞれのMySQLは以下のような構成になっている。create database test; とするとtestというdatabaseが作成される。DBの実体はLinux側では/var/lib/mysql/testとなる。同じ構成でWindowsのMySQLにもtestというdatabaseがあるとする。
■Windows側
+-- C:/Program Files/MySQL/MySQL Server 5.x/
| +-- my.ini
| +-- bin
| +-- mysql
| +-- mysqldump
+-- C:/ProgramData/MySQL/MySQL Server 5.x/data/
+-- mysql
+-- test # このdatabaseをmysqldumpして■Linux側
+-- /etc/
| +-- my.cnf
+-- /var/lib/mysql/
| +-- mysql
| +-- test # ここにrestoreしてみる
+-- /usr/bin/
+-- mysql
+-- mysqldump
問題1: テーブル名が小文字になってしまう。
WindowsのMySQLではテーブル名に大文字／小文字の区別がないが、LinuxのMySQLでは区別されており、しかもWindowsで大文字で定義したテーブル名はmysqldumpを経由してLinux側にリストアするとテーブル名が小文字になってしまう。このままでは大文字でテーブル名を指定しているSQLが実行時にエラーになる。Linux側のmy.cnfにlower_case_table_names=1を指定しておけばいいことが分かった。この変数がデフォルトでUnixでは0、Windowsでは1、MacOSでは2になっているらしい。1を設定すると、テーブル名に大文字／小文字の区別がなくなり、内部的にはすべて小文字で表現されることになる。ちなみにカラム名はWindowsでもLinuxでも大文字／小文字の区別はされない。
[mysqld]
lower_case_table_names=1
問題2: 文字コードをWindowsではSJISにしているがLinuxではUTF8にしたい。
Linux側ではSJISは扱いにくい。Windows側でUTF8にしておけばよかったのかもしれないが、既にSJISで作成してしまっている。どうするか。mysqldumpでダンプする際にUTF8への変換を指定できるようだ。あるいはリストア時にUTF8に変換して登録できるようだ。
Windows側のtestデータベースの文字コードは以下のようにSJISになっている。
Linux側のmy.cnfには以下の感じでしつこくUTF8を明示しておいた。省略してもデフォルトがUTF8になるようだ。
# mysql -uroot -p test
mysql> show variables like 'char%';
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | sjis |
| character_set_filesystem | binary |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
+----------------------------------------+
これによってLinux側のtestデータベースの文字コードが以下のようにUTF8になっていることを確認しておく。testデータベースが既存でUTF8でないなら一旦drop databaseして、my.cnfを変更した後に改めてcreate databaseすればいい。
[mysqld]
lower_case_table_names=1
default-character-set=utf8
[mysql]
default-character-set=utf8
[client]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[mysqldump]
default-character-set=utf8
上記の条件の下でmysqldumpを行ってみる。ポイントは引数で--default-character-set=utf8を指定してやればいい。これを省略するとSJISになる。
# mysql -uroot -p
mysql> create database test;
mysql> use test;
mysql> show variables like 'char%';
+--------------------------+-------------+
| Variable_name | Value |
+--------------------------+-------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+----------------------------------------+
Windows側で作成したdump-test.bakをLinux側に渡し、mysqlでリストアする。testというdatabaseは事前に作成しておく必要があるが、各テーブルは作成しておく必要はない。テーブルがなければcreate tableを行ってからinsertしてくれるようだ。
# mysqldump -uroot -p --default-character-set=utf8 test > dump-test.bak
Windows側で作成したdump-test.bakがUTF-8で作成されていることをnkfで確認し、mysqlで登録されたテーブルとデータが文字化けもなく参照できることが確認できた。
# nkf -g dump-test.bak
UTF-8 (CR)
# mysql -uroot -p test < dump-test.bak
# mysql -uroot -p test
mysql> show tables;
mysql> select * from test.xxx;
[2010/12/04 追記]
Linux環境でMySQLを定期的にバックアップするようにしたので追記しておく。ネット情報を参考に以下のようなシェルmysql-backup.shを作成して、cronに登録した。一応うまく動作しているようなのでメモしておく。2週間分が保存されるようになっている。
シェルに実行権を与えて、cronに毎朝4:00にバックアップされるように登録しておく。
#!/bin/sh
day=14
dst=/var/lib/mysql/backup-test
bak=test.bak
d1=`date +%Y%m%d`
d2=`date "-d${day} days ago" +%Y%m%d`
new=test-${d1}.tar.gz
old=test-${d2}.tar.gzcd ${dst}
/usr/bin/mysqldump -uroot -pxxx test > ${bak}
tar zcvf ${new} ${bak} > /dev/null 2>&1
if [ $? != 0 -o ! -e ${new} ]; then
echo backup failed ${new}
exit 1
fi
if [ -e ${old} ]; then
rm -f ${old}
fiecho ----------- ${dst}
ls ${dst}
# vi mysql-backup.sh
# chmod 777 mysql-backup.sh
# ./mysql-backup.sh
# echo "0 4 * * * root mysql-backup.sh" > /etc/cron.d/mysql-backup
[2010/12/07 追記]
tarコマンドの引数の順序を間違えてましたので、修正しておきました。
