mysqldumpのオプションはすぐ忘れてしまうので、よく使うものをここに書いておきます。
大抵の場合ここに書いたものだけで十分事足りると思います。
よく使うmysqldumpコマンドの例
細かい解説は後回しにして、まずは使用例をどうぞ。
InnoDBテーブルしかないDBを1つダンプする場合
mysqldump --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 <database> > dump.sql
MyISAMテーブルを含むDBを1つダンプする場合
mysqldump --quote-names --flush-logs <database> > dump.sql
ある瞬間の全DBのスナップショットが欲しい場合
mysqldump --quote-names --flush-logs --master-data=2 --all-databases > dump.sql
よく使うオプション一覧
| オプション | 説明 |
|---|---|
| --opt |
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charsetと同じ。デフォルトでこのオプションは有効。 |
| --add-drop-table | DROP TABLE文を含める |
| --add-locks | 各テーブルへのINSERT文の前後にLOCK_TABLES文とUNLOCK TABLES文を含める。インポートの速度が向上する。 |
| --create-options | CREATE TABLE文にMySQL特有のオプションを含める |
| --disable-keys | 各テーブルについて、全てのレコードのインポートが完了するまでインデックスを作らないようにする。インポートの速度向上が期待できるが、MyISAMテーブルの(UNIQUEではない)通常のインデックスにしか効果が無い。 |
| --extended_insert | INSERT文をコンパクトな書式でダンプする。ダンプファイルのサイズが小さくなる。インポートの時間も短縮する。 |
| --lock-tables | ダンプの前にDBの全テーブルをロックする。ただし、ロックはDBごとに行われるのでDB間でのデータ整合性は保証されない。InnoDBでは--single-transactionを使った方が速い。 |
| --skip-lock-tables | --lock-tablesオプションを無効にする。--optが--lock-tablesを有効にするので、それを打ち消す為に使用する。 |
| --quick | ダンプ時にテーブルの全レコードをメモリに一旦バッファする代わりに、1行ずつ読み込んでダンプする。データ量の大きなテーブルのダンプ時にメモリを圧迫しなくて済む。 |
| --set-charset | SET NAMES文を出力する。 |
| --quote-names | DB名、テーブル名、カラム名などの識別子をバックティック文字で囲む。これらの識別子にMySQLの予約後が含まれていても問題なく動作するようになる |
| --single-transaction | ダンプ処理をトランザクションで囲む。データの整合性を保つのに有効だが、MyISAMテーブルが含まれるDBでは意味が無いので、代わりに--lock-tablesか--lock-all-tablesを使う。 |
| --flush-logs | バイナリログをフラッシュして、新しいファイルを作る。フラッシュ |
| --lock-all-tables | ダンプの開始から完了まで、全データベースの全テーブルをロックする。これを使うと自動的に--single-transactionと--lock-tablesオプションはオフになる。 |
| --master-data[=2] |
CHANGE MASTER TO句を含める。これによってレプリケーションのスレーブサーバがマスターサーバのバイナリログの読み取り開始ポイントを知ることが出来る。このオプションは--lock-all-tablesを オンにする。ただし、--single-transactionがオンの場合はそうはならず、代わりにダンプ開始時に一瞬だけグローバルリードロックされる。=2を付けた場合はバイナリログの読み出し開始位置をコメントとして出力する。これは人間が参考のために見たいときに使う。 |
| --password | MySQLサーバに接続する時のパスワード |
解説
InnoDBテーブルしかないDBを1つダンプする場合
MyISAMを使う機会は滅多になくなったので、殆どの場合これを使います。
mysqldump --quote-names --skip-lock-tables --single-transaction --flush-logs --master-data=2 <database> > dump.sql
主にやっていることは以下になります。
- 暗黙の
--optが適用する--lock-tablesを--skip-lock-tablesで打ち消すことでテーブルロックを防ぐ -
--single-transactionでダンプを1トランザクション内で行うことで、テーブルをロックせずとも整合性を保つ -
--flush-logsでバイナリログをフラッシュして新しいファイルを作る -
--master-data=2で新しいバイナリログの開始点をコメントとして出力する -
--quote-namesで識別子をバックティックで囲む
MyISAMテーブルを含むDBを1つダンプする場合
MyISAMではトランザクションが使えないので、データの整合性を保つためにテーブルのロックが必要になります。
mysqldump --quote-names --flush-logs <database> > dump.sql
上のInnoDBで使ったコマンドとの主な違いは以下になります。
- 暗黙の
--optによって--lock-tablesが有効となり、ダンプ中は全てのテーブルがリードロックされる -
--master-dataを--single-transaction抜きで使うと全DBの全テーブルがリードロックされるので使わない - 新しいバイナリログの開始点がダンプに出力されないので、自分で調べてどこかにメモする
ある瞬間の全DBのスナップショットが欲しい場合
複数のDBが相互に依存してる場合などは整合性を保つためにある瞬間の全DBのスナップショットが必要になります。
それを作るには全DBの全テーブルをリードロックする必要があります。
mysqldump --quote-names --flush-logs --master-data=2 --all-databases > dump.sql
1つのDBをダンプする場合との主な違いは以下になります。
-
--all-databasesで全DBを対象にする -
--master-data=2により、暗黙の--lock-all-tablesを有効にする