LoginSignup
230

More than 5 years have passed since last update.

よく使うmysqldumpのオプションと使用例

Last updated at Posted at 2015-06-10

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を有効にする

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
230