テーブルロックを発生させずにmysqldumpを実行する

MySQLのDB定義を更新する前に、念のためのmysqldumpを行うことになり、テーブルロックが発生しないようダンプする方法について調査していました。

正確に理解しきれていない部分もあるのですが、調べた結果を簡単にまとめます。

–single-transactionの使用

MySQLのストレージエンジンがInnoDBの場合、mysqldump時に--single-transactionというオプションをつけることで、ロックを発生させずにダンプができるようです。

–single-transactionとは

--single-transactionをつけて実行すると、ダンプ前にREPEATABLE READというモードが有効になります。

–single-transaction

This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

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

REPEATABLE READは分離レベルという設定の一つで、同一トランザクション内で一貫性を保つために、事前に作成されたスナップショットを読みにいきます。

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read.

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read

つまり--single-transactionをつけることで、スナップショットに対してダンプをとる形になり、テーブルロックがかからない(かける必要がない)仕組みです。

InnoDBについて

ついでにInnoDBについても調べました。

InnoDBはMySQLとMariaDBで採用されているストレージエンジン。

MySQLでは少なくとも5.6以降、標準のストレージエンジンとしてInnoDBが採用されています。

InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL’s default table type.

https://en.wikipedia.org/wiki/InnoDB

InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine.

https://dev.mysql.com/doc/refman/5.6/en/innodb-introduction.html

–skip-lock-tablesについて

さらに調べると--skip-lock-tablesというオプションがありました。

いかにもテーブルロックを回避できそうな名前のオプションなのですが、これは--optなど、--lock-tablesが暗黙的に有効になるケースにおいて、ロックを打ち消すためのオプションになります。

バージョンによっては、--optはデフォルトで有効になっているようなので、--skip-lock-tablesも合わせて使うと良さそうです。

Some options, such as --opt, automatically enable --lock-tables. If you want to override this, use --skip-lock-tables at the end of the option list.

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_lock-tables

The –opt option (and hence –quick) is enabled by default,

https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

MySQL < 5.5の場合

MySQLのバージョンが5.5以前の場合は、ストレートエンジンとしてMyISAMが使われているため、--single-transactionが効かないようです。

ですので、MySQL < 5.5を利用しているシステムでは別の方法でダンプする必要があります。

まとめ

mysqldump時のテーブルロックを回避する方法について調べ、まとめました。

MySQL 5.6以降はストレージエンジンとしてInnoDBが採用されているため、--single-transactionを使うことでテーブルロックを発生させずにダンプ可能です。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください