mysqldump

Mysql で レプリケーション をするときに最適なオプションを調べました。

色々なサイト様でよく見かけるのは
MyNA(日本MySQLユーザ会)さま
http://www.mysql.gr.jp/frame/modules/bwiki/index.php?FAQ#m60940d3

mysqldump --single-transaction --master-data --skip-opt --create-options --set-charset --default-character-set=binary

これが多く、また --hex-blob がついたものもある。
私はこっちのほうがいいと思うのですが、いかがなもんでしょう

mysqldump --opt --master-data --hex-blob --allow-keywords --default-character-set=binary

オプションの説明

--single-transaction

mysqldump を トランザクション 内で処理するというもの
データベースは止まらない

--master-data

--first-slave + CHANGE MASTER TO コマンドを出力
--first-slave(4.1.8以降 --lock-all-tables)
すべてのテーブルをロック

--hex-blob

BINARY, VARBINARY, BLOB の バイナリ文字列を16進数ダンプする

--opt

--quick --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --set-charset と同じ

--skip-opt

--opt を 無効にする。デフォルトでは --opt 有効

--quick

ダンプ結果をメモリにロードせず直に標準出力に出力する

--add-drop-table

ダンプ結果の先頭にDROP TABLEを挿入する

--add-locks

ダンプ結果を insert する前に、LOCK TABLES `table_name` WRITE する
高速に insert できる

--create-options

CREATE TABLE に MySQLのテーブルオプションを追加する
ENGINE=InnoDB DEFAULT CHARSET=utf8 みたいなの

--disable-keys

高速に insert するために インデックスを無効にして insert する

--extended-insert

insert に高速な insert ステートメントを使用する

--lock-tables

ダンプ中のデータベース内のテーブルを全てREAD LOCALでロックする
データベース個別のロックなので、データベース間で
データが矛盾しないことが保障されるわけではないことに注意

--set-charset

SET NAMES default_charset がダンプしたファイルに挿入される
--default-character-set=default_charset で指定したものが使用され、
指定しない場合 version 4.1.2 以降は utf8 、それ以外は latin1 が使用される
--default-character-set
ダンプする際の charset を指定する。binary 指定すると16進数データで書き出す
ダンプされるデータからロードしたデータベース、テーブルは charset がダンプ元と同じため、
binary 指定しておくと変換が行われず文字化けしない。

--quote-names

‘`’文字でデータベース、テーブル、そしてカラム名をクオートします。デフォルトで有効

--allow-keywords

予約語を使用できるようにするため、table_name.column_name という形で出力する
(と、書いてあったけどやってみたら効果が無かった)

さて、つらつらと書きましたが、上を元にMyNAさまの mysqldump オプションを見てみると、
--single-transaction と --master-data が同時に使われています。
http://dev.mysql.com/doc/refman/5.1/ja/mysqldump.html
データベースがInnoDBだけで構成されている場合、有効なやり方だとは思いますが、
MyISAM等の非トランザクションテーブルが含まれている場合、
それらのテーブルは状態が変化してしまう可能性があるらしいです。
--master-data を使用したときに --lock-tablesがOFFになります。
このとき--single-transaction がONにしてある場合は --lock-all-tables がOFFにされず(有効化されず)、

--single-transaction がOFFの場合にのみ --lock-all-tables がONにされます。
ということはMyISAM等のテーブルは READ LOCK すらされないのかもしれません(そんなことはないとは思いますが)
なのでInnoDB、MyISAM混在の場合は --single-transaction は使用しない方がいいと判断しました。
# ただし、InnoDBのみの構成の場合サーバが停止しないため --single-transaction は非常に有効です。
また、--skip-opt を使用したことによって、高速化に有効な様々なオプションが無効になってしまっているのも気になりました。
ちなみに
--flush-logs が必要かと思われましたが、
--master-data をONにすると行われるみたいです。
ということで

mysqldump --opt --master-data --hex-blob --allow-keywords --default-character-set=binary

です。
勉強不足ゆえご指摘ありましたらお投げくださいませ。