MySQLでInnoDBのデータベースをダンプする際、

mysqldump -uhogehoge -p -h database-host > dump.sql

シンプルに書くとこんな感じのコマンドになります。ですがこのままですとREAD LOCKがかかり、

点について解説します。検証したMySQLバージョンは5.7.26、データベースはInnoDBを使用。

mysqldump実行時、暗黙で--lock-tablesオプションが有効になっている

--lock-tablesオプションはdump中のテーブルにREAD LOCKをかける仕様になっています。LOCKには2種類あり、今回解説しているREAD LOCK以外にもWRITE LOCKがあります。

READ LOCKの挙動

READ LOCKを取得したセッション：

それ以外のセッション：

WRITE LOCKの挙動

WRITE LOCKを取得したセッション：

それ以外のセッション：

実際のmysqldump実行時は--lock-tablesではなく--optが暗黙で有効にされるのですが、この--optは複数のオプションを一括で有効にするオプションで、今回は詳細を割愛しますがその複数のオプションの中の1つに--lock-tablesがあるということです。

--lock-tablesが有効な場合に困ること

READ LOCKがかかると

ことになります。例えばWebサービス運営中にデータベースのバックアップを定期的に取りたいニーズがあり、mysqldumpを使いたいケースを考えてみましょう。Webサービスを介してMySQLで日々発行されるコマンドの大半は

• SELECT
• INSERT
• UPDATE
• DELETE

ですが、READ LOCKがかかるとSELECT以外の処理ができなくなり、LOCKが解除されるまで実行できなくなってしまいます。つまり一時的にWebサービスが止まる可能性があります。

余談ですが万が一WRITE LOCKがかかった場合、さらにSELECTもLOCKが解除されるまできなくなります。

何故--lock-tablesがデフォルトで指定されているのか

明確な見解を持ち合わせていないので推測になりますが、おそらくMySQLが初期から採用していたストレージエンジンがMyISAMだったからではないかと思われます。

MyISAMのストレージエンジンにはトランザクション機能が無く、ある瞬間のtable内情報をスナップショットのようにしてダンプさせるためにはLOCKさせるしか方法が無かったため、暗黙で--lock-tablesが適用される仕様になったのではないかと解釈しています。

MyISAMに対し、後発で生まれたInnoDBにはトランザクション機能があります。つまり

可能性があります。結論を先に申しますと不要にできます。以下で解説していきます。

--single-transactionオプションを活用する

mysqldump時に--single-transactionを指定するとREPEATABLE READが適用できるようになります。REPEATABLE READは分離レベルの1つで、簡単に言うと

ことができるようになります。つまり--single-transactionを指定することで、

ようになります。

仮にダンプ中にINSERT/UPDATE/DELETEされたデータもダンプしたデータに入れ込みたい場合はこの方法だと難しく、ダンプ後に何らかのバックフィルを実施してINSERT/UPDATE/DELETEされたデータを復元する必要があります。

LOCKしないようにするには--skip-lock-tablesオプションを使う

上記を踏まえた上で、今度はmysqldump中にREAD LOCKさせないようにする必要があります。方法は--skip-lock-tablesを指定するだけでOKです。

最終的なmysqldumpコマンドはこちら

# ASIS
mysqldump -uhogehoge -p -h database-host > dump.sql

# TOBE
mysqldump --single-transaction --skip-lock-tables -uhogehoge -p -h database-host > dump.sql

これでWebサービス稼働中でも、安心してmysqldump実行時点のデータを定期的にバックアップできるようになります。但しmysqldumpはCPUも食いますので、やるにしてもタイミングを夜中にする等ご検討ください。