本日もMySQLネタです。
データベースを運用していると、バックアップを取る必要が出てくることもあります。
例えば、毎週月曜日夜中3時にフルバックアップを取って、それ以外の日は差分バックアップを取るなどです。バックアップを定期的に行うことで、データベースが壊れてデータ復旧ができなくなったとしても被害を最小限に減らすことができます。 ※バックアップを取らずに、メイン以外のMySQLサーバーにレプリケーションでデータをバックアップする方法もありますが。
また、サーバー移行などでデータベースも移行させる場合もバックアップを取ります。 仕事でこの間までデータセンターに設置していたサーバーをAmazon Web Service (AWS)に移行させることもやっていましたし、このブログも一時的にさくらのVPSからAWSのEC2に載せ替えていますが、PHPなどのソースコードやデータベースも一緒に移行させました。バックアップしたファイルを移行先のサーバーで復元(リストア)することで、データベースを移行させることができます。
バックアップ(mysqldump)
MySQLのバックアップはmysqldumpコマンドを使います。このコマンドはLinuxだけでなく、Windowsも使えます。
$ mysqldump -uユーザ名 -pパスワード -hホスト名 [データベース名] [テーブル名] [オプション] > バックアップ先
テーブル名、オプションは入れなくても良いし、複数してもOKです。 データベース名、テーブル名を指定すると、データベース内の指定したテーブルをバックアップ(dump)できます。 バックアップ先を指定しないとdumpした内容が標準出力で表示されます。標準出力される結果をリダイレクトでファイルに出力しています。
オプション(一部)
- --add-drop-table
- dumpしたファイル内で、create table文の前にdrop table文が入る
- 既存のデータベースを上書きする場合に使う
- --no-create-info (-t)
- create table文を入れずにdumpする
- データだけを移行させる場合に使う
- --no-data (-d)
- データを含まず、create table文だけdumpする
- データ構造が変わった場合に、--add-drop-tableオプションと一緒に使うことが多い
- --all-databases (-A)
- すべてのデータベースをバックアップする場合にデータベース名を入力しないで使う
- --lock-all-tables (-x)
- データベースをバックアップ中にテーブルをロックする
- ロックすることによって、バックアップ中にデータが更新されるのを防ぎ、データの整合性を保つことができる ※このオプションを付けない場合、参照系(select文)は実行できますが、更新系(insert, update, deleteなど)はバックアップが終わるのを待ってから実行されるため、データの整合性は保つことはできますが、このオプションを付けた方が確実だと思います
- --default-character-set
- --ignore-table=データベース名.テーブル名
- dumpするテーブルを除外する
- データベース名とテーブル名の両方を指定する必要がある。複数テーブルを除外するにはこのオプションを複数回指定する
- --password=xxxxx (-pxxxx)
- xxxxxでパスワードを指定する
- --host=xxxxx (-hxxxx)
- xxxxxでホスト名を指定する
例として、昨日の記事で設定したデータベースをdumpしてみます。
$ mysqldump -uWP_USER -pWP_PW -hlocalhost WP_DB -–add-drop-table > ~/wordpress.sql
復元(リストア)
バックアップが取れたら、復元(リストア)してみましょう。
リストアはmysqlコマンドでできます。
$ mysql -uユーザ名 -pパスワード [データベース名] [テーブル名] < バックアップファイル名
テーブル名は特定のテーブルのみをリストアする場合に指定します。 --all-databases オプションで、すべてのデータベースをバックアップしている場合は、データベース名は指定しません。
例として、先ほどdumpしたファイル(wordpress.sql)を別のサーバーでリストアしてみます。 ここでは、リストア先のサーバーで、すでにデータベースとユーザー作成し、wordpress.sqlをリストア先サーバーの/tmpに移したとします。
$ mysql -uWP_USER -pWP_PW -hlocalhost < /tmp/wordpress.sql
最後に
MySQLでデータベースのバックアップと復元はコマンドで簡単にできることがわかりました。 仕事でデータベースを移行したときに、サーバーの文字コードがUTF-8で、データベースの文字コードがcp932だったため、文字化けが起こったのでそれは別の記事で書ければと思います。
