mysqldump の--dump-slaveオプションを使用する時の注意点

このオプションを使用すると、mysqldump はダンプ前にスレーブ SQL スレッドを終了し、ダンプ後に再起動します。

MySQL5.5 から追加された --dump-slaveオプションについて落とし穴にハマったのでメモ。
上記のマニュアルの通り、このオプションを使用すると、dump取得中はレプリケーションが停止する。
そのため、以下の点に注意する必要がある。

・ 内部的に STOP SLAVE SQL_THREAD / START SLAVE を実行するため、mysqldump実行用ユーザを別途作成する場合はSUPER権限が必要となる

・ dump取得中はレプリケーションが停止しているが、スレーブのテーブルにアクセスすることは可能となるため、mysqldump実行中はアプリケーションからアクセスが来ないよう、LBなどから切り離す必要がある

※ ちなみに --dump-slave オプション自体はMySQL5.5から導入されたものだが、
　 実行元が5.5以上であれば、dumpの取得元であるMySQLは5.1でも問題はない模様

なお、GTIDが有効になっている環境であれば、そもそも--dump-slaveオプションを使用する必要はありません（むしろ非推奨のようです）

Warning
This option should not be used if the server where the dump is going to be applied uses gtid_mode=ON and MASTER_AUTOPOSITION=1.

■ tpccスキーマのitemテーブルのdumpファイルを取得した時の一般クエリログ

実行コマンド：
mysqldump -u dump_user -ppassword --dump-slave=2 -h 192.168.11.33 --single-transaction --routines --events --triggers --default-character-set=sjis --hex-blob --databases tpcc --tables item > test_dump.sql

170111 10:32:17    17 Connect   dump_user@192.168.11.88 on 
                   17 Query     /*!40100 SET @@SQL_MODE='' */
                   17 Query     /*!40103 SET TIME_ZONE='+00:00' */
                   17 Query     SHOW SLAVE STATUS
                   17 Query     STOP SLAVE SQL_THREAD
                   17 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                   17 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                   17 Query     SHOW VARIABLES LIKE 'gtid\_mode'
                   17 Query     SHOW SLAVE STATUS
                   17 Query     UNLOCK TABLES
                   17 Query     SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='tpcc' AND TABLE_NAME IN ('item'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME
                   17 Query     SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='tpcc' AND TABLE_NAME IN ('item')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
                   17 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
                   17 Init DB   tpcc
                   17 Query     SHOW TABLES LIKE 'item'
                   17 Query     show table status like 'item'
                   17 Query     SET SQL_QUOTE_SHOW_CREATE=1
                   17 Query     SET SESSION character_set_results = 'binary'
                   17 Query     show create table `item`
                   17 Query     SET SESSION character_set_results = 'sjis'
                   17 Query     show fields from `item`
                   17 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `item`
                   17 Query     SET SESSION character_set_results = 'binary'
                   17 Query     use `tpcc`
                   17 Query     select @@collation_database
                   17 Query     SHOW TRIGGERS LIKE 'item'
                   17 Query     SET SESSION character_set_results = 'sjis'
                   17 Query     show events
                   17 Query     use `tpcc`
                   17 Query     select @@collation_database
                   17 Query     SET SESSION character_set_results = 'binary'
                   17 Query     SHOW FUNCTION STATUS WHERE Db = 'tpcc'
                   17 Query     SHOW PROCEDURE STATUS WHERE Db = 'tpcc'
                   17 Query     SET SESSION character_set_results = 'sjis'
                   17 Query     SHOW SLAVE STATUS
                   17 Query     START SLAVE
                   17 Quit