💽

mysqldumpの--single-transactionでデータの整合性を保つ

yukana2022/12/24に公開

先日、データに不整合があり、調査するとダンプの仕方がよくなかったことがわかりました。
色々調べてわかったことを備忘録的にまとめます。

--single-transactionを使う

mysqldump db_name > backup-file.sql

上記が基本的なmysqldumpの使い方です。
しかし、デフォルトの状態でダンプすると、整合性のないダンプファイルが作成される可能性があります。
不整合が起こるのはテーブルをロックせずに、オンラインでダンプをするケースです。

例えば、テーブルA->テーブルB->テーブルCの順にダンプするとします。
テーブルBのダンプ中にテーブルAとテーブルCにデータが追加された場合、テーブルCに追加されたデータはダンプされるけど、テーブルAに追加されたデータはダンプされないといった不整合が起こります。

データの不整合が問題となる場合は、--single-transaction を使いましょう!

mysqldump db_name --single-transaction > backup-file.sql

--single-transaction はトランザクションを開始してからダンプを行うためのオプションで、トランザクション開始時点のデータをダンプすることができます。

注意しないといけないのはInnoDBの場合のみ有効なオプションだということです。
InnoDBが何かはよくわかってないですが😅

https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html#option_mysqldump_single-transaction

--skip-lock-tablesも使う必要があるのか?

mysqldump db_name --single-transaction --skip-lock-tables > backup-file.sql

テーブルをロックしないために --skip-lock-tables を使っているのをよく見かけます。

ただ、ドキュメントには --lock-tables--single-transaction は相互に排他的で、--single-transaction を使うと、--lock-tables はオフになると書かれています。
実際、ソースコードを見ると、--single-transaction を指定していると lock_tables = false になっています。
なので、--single-transaction を使う場合は、--skip-lock-tables は不要ですね!

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly. So this option automatically turns off --lock-tables

https://mariadb.com/kb/en/mariadb-dumpmysqldump/

https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html#option_mysqldump_single-transaction

(おまけ)DBeaverで--single-transactionを使う

Export configuration->Execution Methodを Online backup in single transaction にします。

参考

https://dba.stackexchange.com/questions/300259/do-i-need-skip-lock-tables-with-single-transaction
https://qiita.com/song_ss/items/38e514b05e9dabae3bdb#repeatable-read

BABY JOB テックブログ

「すべての人が子育てを楽しいと思える社会」の実現を目指すスタートアップ BABY JOB のテックブログです。

Discussion

ログインするとコメントできます