MySQLのダンプとリストア

MySQLはそれ自体では非常に高速で非常に便利なのですが、CSVとか吐き出したり読み込んだりできる!なんて思って、CSVでバックアップ取ろうとすることにおのずとなるわけですが、あれ、正直いってダメですね。文字コードがなんちゃらでとかダブルクォートがなんかおかしくなったとか何とか。
ダンプとリストアは、コマンドラインでどかっとやらないとダメです。PHPMyAdminなんてものができてから、これらのコマンドがあんまり使われなくなっているのかな...と思っていたら、PHPMyAdminにも同じことがWEBの画面からできましたので、使ってください。

SSHが使えれば、ダンプとリストアはこれが一番いいです。

mysqldump ダンプの方法

$ mysqldump -u [username] -p [dbname] [tablename] > table_backup.sql
localhostではこれでOKです。SSHでログインしているホームディレクトリにSQL文の書かれたファイルがダンプされます。mysqlにログインしなくてもできるので、かなりいいです。
テーブル名は省略可で省略するとデータベースすべてがダンプされます。実際は以下のような感じで実行します。
# mysqldump -u root -p DB1 > DB1.sql
passwd: ******

MySQLのホストがlocalhost以外にある場合にはホスト名明示的に書かないと接続できません。
$ mysqldump -h [hostname] -u [username] -p [dbname] [tablename] > table_backup.sql

時々文字コードの設定などでダンプファイルが丸ごと文字化けしていることがあります。そういう場合は--default-character-set=binaryを追加して実行します。
$ mysqldump -h [hostname] -u [username] -p --default-character-set=binary [dbname] [tablename] > table_backup.sql

mysql リストアの方法

今度はその逆でダンプしたものをテーブルに差し戻して元に戻します。データをぐちゃぐちゃにしても元に戻るので安心です。
$ mysql -u [username] -p [dbname] < table_backup.sql
ダンプと違うのは、先頭のmysqlのところと、テーブル名が指定されていないところです。バックアップしたsql文の中にテーブル名が明記されているので、データベースだけ指定してやればそれでOK。元に戻ってます。

PHPMyAdminでやる場合は、エクスポート、インポートのフォーマットをsql文にしてやるといいです。ダンプとリストアなのでヘタにSHIFT_JISとかにしない方がいいでです。どうせバックアップで、MySQLに入れてなんぼという話なので。

mysqldump ダンプの応用

大量のデータベーステーブルがある場合、それらのテーブルをすべてダンプ&リストアしたくない場合があります。例えばテーブルデータの中には設定情報など環境に依存した情報などが入っている場合です。こういった場合、いくつかのテーブルダンプを除外してそのダンプデータをマージしつつリストアという感じになります。こういう場合は--ignoreオプションを使います。
--ignore-table=db_name.table_name
やたらといろんなサイトに書いてあることなので今更でもあるのですが、データベース名.テーブル名という並びになっています。実際にmysqldumpでは以下のように利用します。
mysqldump --hex-blob -u [USERNAME] -p[PASSWAORD] --ignore-table=[DBNAME].[TABLENAME1] --ignore-table=[DBNAME].[TABLENAME2] [DBNAME] > db.sql
複数のテーブルを除外する場合は--ignoreを複数指定するだけでOKです。