古いMySQLからMySQL5の最新版にアップグレードする際の文字化けを防ぐバックアップ＆リストア方法。

つい先日、とあるサーバのMySQLのバージョンが相当古く4.1ということだったので最新GAの5.5.8にバージョンアップしました。
大幅なパフォーマンス向上やクラッシュリカバリの高速化など5.5系にアップグレードのメリットは大きいですね (詳しくは奥野さんのブログを参照)

尚、rpmパッケージでのアップグレード方法ではなく、ソースのコンパイルベースのアップグレードなのでご注意ください。



その際に、データベースの移行をするため、mysqldump 高速にバックアップ＆リストアが出来て文字化けも一切なく作業が出来たので方法をご紹介です。

(注) 実際に作業する場合は、念のためMySQLのデータディレクトリをコピーして別途バックアップしておくことをお勧めします

バックアップ
cgm というデータベースをバックアップする例です。

$ mysqldump -a -uroot -p<password> --default-character-set=binary cgm --tab=./cgm

--default-character-set=binary を指定することで、文字コード変換なしの出力にします。
--tabでtab区切りのファイルを出力し、リストア時にSQL解釈なしで高速に挿入できるようオプション指定します。

以上で、cgm というディレクトリ以下に sql 文 (.sql) と tabで区切られたデータの (.txt) が出来ます。

上記はcgm というデータベース単一の例ですが、複数のデータベースを回す場合はスクリプトを書いてあげれば簡単に対応できます。
--tabと--all-databasesとの併用ができません。

リストア
古いバージョンのMySQLサーバをシャットダウンし、新しいバージョンをMySQLをインストールした後、MySQLデーモンを起動します。
mysql コマンドで流し込みますが、次のようにコマンドライン指定します。

ここでは、cgmというデータベース内にa,b,cという3つのテーブルがあったことを想定します。

$ mysql -uroot -p<password> -e "create database cgm if not exists"
// テーブル復元。cgm データベースに a, b, c というテーブルがあったことを想定
$ mysql -uroot -p<password> cgm < cgm/a.sql
$ mysql -uroot -p<password> cgm < cgm/b.sql
$ mysql -uroot -p<password> cgm < cgm/c.sql
// LOAD DATA INFILEでデータを読み込み (￥が全角注意)
$ mysql -uroot -p<password> cgm -e "set character_set_database=binary;LOAD DATA INFILE ￥"cgm/a.txt￥""
$ mysql -uroot -p<password> cgm -e "set character_set_database=binary;LOAD DATA INFILE ￥"cgm/b.txt￥""
$ mysql -uroot -p<password> cgm -e "set character_set_database=binary;LOAD DATA INFILE ￥"cgm/c.txt￥""

テーブルが多数ある場合で、1個1個コマンドラインでやるのは現実的でない場合、スクリプトを書くなどして対応します。
LOAD DATA INFILE の前の、 set character_set_database=binary によって、ファイルをバイナリとして扱うように指定しています。
LOAD DATA INFILE によって、SQLを解釈しないため、高速にデータ挿入することができます。

以上で、文字化けなしに最新バージョンのMySQLへのデータ移行が完了しました。
もっと楽でいい方法があるよ、という方は教えて下さい:-)

関連エントリ

• PHP開発者のMySQLの間違いトップ１０
• AjaxベースのMySQL管理ツール「MyWebsql」
• MySQLベースのデータ管理画面をWEB上で自動生成可能な「CRUD太郎」
• MySQLパフォーマンスに関する84のTIPS