たこはちの「へのかっぱ」日記

MySQLのデータをWindowsからLinuxへ移行する手順

MySQL

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になっている。

# 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側のmy.cnfには以下の感じでしつこくUTF8を明示しておいた。省略してもデフォルトがUTF8になるようだ。

[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

これによってLinux側のtestデータベースの文字コードが以下のようにUTF8になっていることを確認しておく。testデータベースが既存でUTF8でないなら一旦drop databaseして、my.cnfを変更した後に改めてcreate databaseすればいい。

# 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        |
　+----------------------------------------+

上記の条件の下でmysqldumpを行ってみる。ポイントは引数で--default-character-set=utf8を指定してやればいい。これを省略するとSJISになる。

# mysqldump -uroot -p --default-character-set=utf8 test > dump-test.bak

Windows側で作成したdump-test.bakをLinux側に渡し、mysqlでリストアする。testというdatabaseは事前に作成しておく必要があるが、各テーブルは作成しておく必要はない。テーブルがなければcreate tableを行ってからinsertしてくれるようだ。

# 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;

Windows側で作成したdump-test.bakがUTF-8で作成されていることをnkfで確認し、mysqlで登録されたテーブルとデータが文字化けもなく参照できることが確認できた。

[2010/12/04 追記]

Linux環境でMySQLを定期的にバックアップするようにしたので追記しておく。ネット情報を参考に以下のようなシェルmysql-backup.shを作成して、cronに登録した。一応うまく動作しているようなのでメモしておく。2週間分が保存されるようになっている。

#!/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.gz

cd ${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}
fi

echo ----------- ${dst}
ls   ${dst}

シェルに実行権を与えて、cronに毎朝4:00にバックアップされるように登録しておく。

# 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コマンドの引数の順序を間違えてましたので、修正しておきました。

Permalink | コメント(0) | トラックバック(0)