前回は、オプションをずらーっと並べて紹介しました。
mysqldumpの使い方 オプション編
mysqldumpのオプションが多すぎて、どこをどうすればいいのかわかりません。
ということで、今回はもうすこし実用的な形で進めたいと思います。
今回は、テーブルやデータベースをバックアップ＆リストアをする、基本的な方法をまとめてみました！
テーブル
まずは、テーブルをバックアップ＆リストアする方法です。1.テーブルのダンプ
mysqldump -uroot -p -hlocalhost database_name table_name > filename
テーブルは、1つだけでなく複数指定することもできます。
mysqldump -uroot -p -hlocalhost database_name table_name1 [table_name2...] > filename
mysqldump -uroot -p -hlocalhost -d database_name table_name > filename
mysqldump -uroot -p -hlocalhost -t database_name table_name > filename
mysqldump -uroot -p -hlocalhost -w="id = 1" database_name table_name > filename
mysqldump -uroot -p -hlocalhost -w="id >= 100 AND flag = 1" database_name table_name > filename
指定した日付以降のデータを取得する場合はこの通り。
Linux
mysqldump -uroot -p -hlocalhost -w="date_column >= DATE('${ymd}')" database_name table_name > filename
mysqldump -uroot -p -hlocalhost -w="date_column >= DATE('%ymd%')" database_name table_name > filename
whereオプションの条件は、全てのテーブルを対象にしたり複数テーブル指定したりした場合、対象テーブル全てに適用されます。
全てに共通したカラムがある場合は利用できるかもしれません。
ちなみに、このwhereオプション、単純にSELECT文のWHERE句、WHEREの後ろに文字列を結合しているだけらしく、EXISTSを使ったサブクエリやORDER BY句、はたまたGROUP BY句まで記述できます。
※EXISTSでサブクエリを使った場合、「サブクエリのテーブルがLOCKされていない」となってエラーになりますが、そもそもロックしないようにskip-lock-tablesオプションを付けてダンプするとエラーが発生しません。single-transactionオプションと組み合わせれば、利用できそうです。
2.テーブルのリストア
mysql -uroot -p -hlocalhost target_database < filename
先程とったダンプからデーブルをリストアします。
リストアではmysqlコマンドを使用します。
mysqldumpで作成したファイルには、SQL文が出力されていますので、mysqlで実行できるのです。
MySQLにログインして実行することもできます。
私は、手動でリストアする場合、手順やデータベース内容を確認しながら対応できるので、以下のようにやっています。
mysql -uroot -p -hlocalhost
USE target_database
SOURCE filename
データベース
次に、データベースのバックアップ＆リストアです。1.データベースのダンプ
mysqldump -uroot -p -hlocalhost -B database_name1 [database_name2 ...] > filename
データベース内の全てのテーブルをダンプします。
全データベースをダンプしたいときは、all-databases(A)オプションを使います。
mysqldump -uroot -p -hlocalhost -A > filename
データベース定義とテーブル定義のみダンプしたい場合は、no-data(d)オプションを使います。
mysqldump -uroot -p -hlocalhost -A -d > filename
mysqldump -uroot -p -hlocalhost -A -d -n > filename
mysqldump -uroot -p -hlocalhost -A -d -t > filename
mysqldump -uroot -p -hlocalhost -A -n > filename
mysqldump -uroot -p -hlocalhost -A -n -t > filename
2.データベースのリストア
mysql -uroot -p -hlocalhost < filename
注意したいのは、既に同じ名前のデータベースがある場合は、CREATE DATABASE文がスキップされ、テーブルをDROP&CREATEしてしまいます。
便利なオプション
1.トランザクション
mysqldump -uroot -p -hlocalhost --single-transaction database_name > filename
トランザクションを開始することで、テーブルをロックせずにデータを取得でき、他のトランザクションからのコミットでファジーリード・ファントムリードが発生するのを防ぎます。(MySQLはデフォルトでトランザクションレベルがREPEATABLE-READですが、ファントムリードも防いでくれます)
トランザクションが有効なInnoDBをダンプするのに便利です。
注意する点は、MyISAMやMEMORYなどトランザクションが無効なデータベースには効果がないこと、ダンプ中にALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE文などのDDLが他のトランザクションから実行されると読み取り一貫性が崩れてしまうことです。
lock-tables(l)オプションとは互いに排他で、自動的にオフにします。
2.コメントの出力をやめる
mysqldump -uroot -p -hlocalhost --skip-comments database_name > filename
skip-commentsオプションを使用すると、これらコメントが出力されなくなります。
データバックアップだけでリストアする時も中身を見ないからコメントはいらない場合に使いましょう。
さらに「バージョンやDB名、テーブル名は残しておきたいけど、出力日時はいらない！」というわがままなあなたにはこちら。
mysqldump -uroot -p -hlocalhost --skip-dump-date database_name > filename
これで、ファイルの差分を取ったときに出力日時で引っかからずにすみます。
出力日時はファイル名やファイルの作成日時でわかればいい。むしろ、どこが変わったかファイルの差分を掛けて知りたい。という人におすすめです。
3.特定のテーブルのバックアップをしない
mysqldump -uroot -p -hlocalhost database_name --ignore-table=database_name.table_name > filename
テーブルの指定を行う際、データベース名から記述しなければいけないので、気をつけましょう。
このオプションは何回も使えるので、ダンプしないテーブルを複数指定できます。
mysqldump -uroot -p -hlocalhost database_name --ignore-table=database_name.table_name1 --ignore-table=database_name.table_name2 > filename
4.MySQL独自のCREATE TABLEオプションを削除する
mysqldump -uroot -p -hlocalhost database_name --skip-create-options > filename
他のDBMSに移行する際には、このオプションによってエラーが発生するため、邪魔になってしまいます。
テーブルオプションを削除したい場合は、skip-create-optionsオプションを使って出力しないようにできます。
なお、オプションを無効化するskip～には、オプションの省略形は使えません。
5.データの文字コードを変更する
mysqldump -uroot -p -hlocalhost database_name --default-character-set utf8 > filename
注意したいのは、データの文字コードは変わりますが、テーブル定義は変わらないことです。
いくらデータを文字コード変換して出力しても、テーブルの文字コードに変換されてリストアされます。
データベースで保持するデータの文字コードを変換するには、先んじてのテーブル定義の変更など、工夫が必要になります。
6.出力先
mysqldump -uroot -p -hlocalhost database_name -r filename
result-file(r)オプションで、ファイルの出力先を指定できます。
今までは、標準出力に出力されたデータを直接ファイル出力していました。
Windowsでは、標準出力をコマンドプロンプトでファイルへ出力すると、改行が\n(newline)から\r\n(carriage returnとnewline)に変換されてしまいます。
それを防ぐためにこちらのオプションが使えます。
ルート配下のbackupディレクトリに出力するならこんな感じになります。
Linux
mysqldump -uroot -p -hlocalhost database_name -r /backup/filename.sql
mysqldump -uroot -p -hlocalhost database_name -r C:\backup\filename.sql
mysqldump -uroot -p -hlocalhost database_name -v -r filename
最後に
今回は、バックアップとリストアの基本的な方法をまとめてみました。役に立ちそうなオプションの使い方は見つかりましたでしょうか？
オプションを組み合わせて、適したバックアップをみつけてみましょう。
次回は、ダンプ時にデータベースとテーブルの作成のDDLとデータを分割して出力するオプションの使い方をまとめたいと思います。
