うまいぼうぶろぐ

linuxとhttpdとperlのメモ

mysqldump の option 復習

mysql 5.5 GA が年内にreleaseされるとかいう噂を聞いていて、ふとdumpについて知りたくなったので調べた。主にlock関連で。

--opt

defaultで有効。
"--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset" をまとめて設定。

--quick

defaultで有効。
結果をmemoryにbufferしないで、1行ずつSQLを吐き出す。sizeの大きいdatabase/table に対してこのoptionを指定しないと(全部memoryに乗っけてからSQLを吐くので)、serverのmemoryを使いきってOOMになる可能性あり。

--lock-all-tables

全database の全table をlockする。 (default ではない)
binary log file 名とpositionも書き出す "--master-data" optionを有効にする自動的にこの"--lock-all-tables" optionも有効になる。(--single-transactionが指定されてなければ)

なぜ"--master-data"を付けると"--lock-all-tables"が有効になるかというと、binary log のposition を書き出す = slave serverがreplicationを追いかけるために必要な情報 なので、dump中のdataの整合性を保つために全tableをlockする。(と、理解している)

"--single-transaction"と"--lock-tables"とは排他なoption。

--lock-tables

defaultで有効。
dump対象のdatabase 内の全tableを lockする。lockはREAD LOCAL で行うので、条件が満たされていればMyISAM で同時挿入が可能(mysqldump中でselect していてもinsert可能)。databaseを跨って全tableをlockするわけではないので、

$ mysqldump --all-databases --lock-tables

などを実行した場合には、database Xとdatabase Y 内のtableは個別にlockされるので、dump開始時と終了時点で中身が違っている可能性がある。database間で整合性を保つ必要がないなら大丈夫。

次に書くけど、InnoDBのようにtransactionを使えるtableの場合は "--single-transaction" optionを使えば、lockなしに一貫性のあるdataをdumpできるのでそっちを使ったほうが良いとのこと。

--single-transaction

dumpする前にBEGIN (transaction開始) SQLを使ってdumpするので、 read lockをかけずにBEGIN 発行時点のdataをdumpできる。大きなtableの場合は、この"--single-transaction"と"--quick"を付けると良い。
もちろん、このoptionが意味があるのはtransactionを利用できる、InnoDBのようなtableのみ。
"--lock-all-tables","--lock-tables"と排他なoption。