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

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

尚、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へのデータ移行が完了しました。
もっと楽でいい方法があるよ、という方は教えて下さい:-)

追記) mysqlimportは LOAD DATA INFILE のインタフェースを提供するのでコマンドラインで実施できるみたいです。

mysqlimport [options] cgm cgm/a.txt cgm/b.txt cgm/c.txt

のようにすることで同等のことができます(奥野さんありがとうございます)

追記）4.1から5.5.8なら化けないというSH2さんからの指摘を受けました。検証環境でまず5.1.xx系にアップした場合に文字化けが起きたため私の勘違いかもしれません。その後、4.1→5.5.8に一気にこの方法でバージョンを上げたのですが問題ないみたいでした。また試す機会があった場合は訂正します。データとしてはテーブル別でujisやutf8が混在していました。
いずれにせよ、--tabによるバックアップと LOAD DATA INFILE によるリストアは高速なのでオススメなのと、character set に関しては付けておいても特に問題は発生しないものと思われます。記事のタイトルも悪かったかもしれません。
あと、コンパイルベースなんてどこにもないという指摘も頂きました。
それに関してはMySQLダウンロードページのSource Codeを選択すれば入手可能です。

関連エントリ

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