MTSを使ってクエリをロールバックする。
この記事はMySQL Casual Advent Calendar 2018の16日目です。
元ネタはlefredさんのこちらのスライド FOSDEM MySQL & Friends Devroom, February 2018 MySQL Point-in-Time Recovery like a Rockstar
ざっくりいうと、 マルチスレッドスレーブ(以下MTS)を使ってbinlogを読み込ませてPITRを早くするというものです。 検証環境はCentOS7、MySQL8.0.13。
例えばこんな感じなテーブル。
fuki1 [d1]> SELECT * FROM t1 limit 10; +----+------------+ | id | value | +----+------------+ | 1 | C8vAt5qSyl | | 2 | 15mtFfSGuz | | 3 | 96ZFZ0451W | | 4 | ZHM6qMQRXI | | 5 | xMkqCVFgOT | | 6 | FWBCUry1FW | | 7 | Uoa5Y1sOPK | | 8 | DoCkXtvZSj | | 9 | JdiF94D1t2 | | 10 | dwqV6ykt54 | +----+------------+ 10 rows in set (0.01 sec) fuki1 [d1]> SELECT COUNT(1) FROM t1; +----------+ | COUNT(1) | +----------+ | 1010000 | +----------+ 1 row in set (0.15 sec)
このテーブルにこんな更新を全件にかけてしまったとします。
fuki1 [d1]> UPDATE t1 SET value = 'lhfukamachi'; Query OK, 1010000 rows affected (14.48 sec) Rows matched: 1010000 Changed: 1010000 Warnings: 0 fuki1 [d1]> SELECT * FROM t1 limit 10; +----+-------------+ | id | value | +----+-------------+ | 1 | lhfukamachi | | 2 | lhfukamachi | | 3 | lhfukamachi | | 4 | lhfukamachi | | 5 | lhfukamachi | | 6 | lhfukamachi | | 7 | lhfukamachi | | 8 | lhfukamachi | | 9 | lhfukamachi | | 10 | lhfukamachi | +----+-------------+ 10 rows in set (0.03 sec)
UPDATE t1 SET value = 'lhfukamachi'によってvalueカラムが全て'lhfukamachi'に書き換わってしまいました。
その後もデータが来る想定でこのテーブルに1000件ほどデータを追加します（ついでに他のテーブルにも更新します。
fuki1 [d1]> select count(1) FROM t1; +----------+ | count(1) | +----------+ | 1011000 | +----------+ 1 row in set (0.13 sec)
通常PITRをする時は、クエリが実行される以前のフルバックアップ + バイナリログを用いてmysqlbinlogコマンドを使って特定のクエリのポジションを飛ばしてデータを再構築するやり方が一般的かと思います。
MTS（というか、レプリケーション）を使ったやり方の場合はbinlogをあたかもrelaylogとして読ませてやるやり方らしいです。
（下記記事の「レプリケーション機能を使用してリカバリする」を参考） 第46回 特定テーブルをロールバックする方法：MySQL道普請便り｜gihyo.jp … 技術評論社
とりあえず現状のgtid_executedを確認してみます。
fuki1 [(none)]> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 11025939
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919
1 row in set (0.03 sec)
次に、問題のクエリをbinlogから探し出してGTIDを調べます。
$ /usr/local/mysql8012/bin/mysqlbinlog -vv mysql-bin.000002 | grep -A10 -B20 'lhfukamachi' (省略) SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123919'/*!*/; # at 9448700 #181216 13:28:00 server id 19355 end_log_pos 9448789 CRC32 0xd73ed147 Query thread_id=30269 exec_time=15 error_code=0 SET TIMESTAMP=1544934480/*!*/; BEGIN /*!*/; # at 9448789 #181216 13:28:00 server id 19355 end_log_pos 9448908 CRC32 0x71786abb Query thread_id=30269 exec_time=15 error_code=0 SET TIMESTAMP=1544934480/*!*/; UPDATE t1 SET value = 'lhfukamachi' /*!*/; # at 9448908 #181216 13:28:00 server id 19355 end_log_pos 9448939 CRC32 0x4314fe86 Xid = 91082 COMMIT/*!*/; # at 9448939 #181216 13:28:54 server id 19355 end_log_pos 9449014 CRC32 0x3e0275c5 GTID last_committed=30003 sequence_number=30004 rbr_only=no original_committed_timestamp=1544934534697601 immediate_commit_timestamp=1544934534697601 transaction_length=317 # original_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST) # immediate_commit_timestamp=1544934534697601 (2018-12-16 13:28:54.697601 JST) /*!80001 SET @@session.original_commit_timestamp=1544934534697601*//*!*/; SET @@SESSION.GTID_NEXT= '7d2772a5-8987-11e8-a6a6-0201858272ab:123920'/*!*/;
問題のクエリのGTIDはこれっぽい。
7d2772a5-8987-11e8-a6a6-0201858272ab:123919
ここで一旦MySQLを止めて、binlogを退避後、過去のコールドバックアップをもってきます。
$ MYSQL_PWD='********' /usr/local/mysql8013/bin/mysqladmin --defaults-file=/data/mydatabase/my.cnf -uroot shutdown $ cp -p /data/mydatabase/mysql-bin.index /data/tmp/relaylog_backup/mysql-relay.index $ cp -p /data/mydatabase/mysql-bin.000001 /data/tmp/relaylog_backup/mysql-relay.000001 $ cp -p /data/mydatabase/mysql-bin.000002 /data/tmp/relaylog_backup/mysql-relay.000002 $ vim mysql-relay.index ~ `mysql-bin.*` を`mysql-relay*`に書きなおしましょう。 ~ // 過去のコールドバックアップの解凍 # zstd -dc -p2 mydatabase.20182214.tar.zst | tar xv // 現在のdatadirを名前を変えて退避 # mv /data/mydatabase /data/_mydatabase // datadirに過去のコールドバックアップを置き直す。 # mv mydatabase /data/mydatabase
続いて過去のbinlogをrelaylogとして設置します
$ cp -p /data/tmp/relaylog_backup/mysql-relay.* /data/mydatabase/
あとはMySQLを起動後、RESET MASTERしてから除外するクエリのGTIDをそえて、GTID_PURGEDを設定します。
fuki1 [(none)]> SELECT @@gtid_executed; +----------------------------------------------+ | @@gtid_executed | +----------------------------------------------+ | 7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916 | +----------------------------------------------+ 1 row in set (0.00 sec) fuki1 [(none)]> RESET MASTER; Query OK, 0 rows affected (0.06 sec) fuki1 [d1]> SET GLOBAL GTID_PURGED='7d2772a5-8987-11e8-a6a6-0201858272ab:1-93916,7d2772a5-8987-11e8-a6a6-0201858272ab:123919'; Query OK, 0 rows affected (0.08 sec) fuki1 [mysql]> SET GLOBAL server_id = 1; Query OK, 0 rows affected (0.00 sec) fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_TYPE = 'LOGICAL_CLOCK'; Query OK, 0 rows affected (0.00 sec) fuki1 [mysql]> SET GLOBAL SLAVE_PARALLEL_WORKERS = 3; Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER構文とSTART SLAVE sql_threadを実施してreloylog（もともとbinlogだったやつ）の読み込み開始
fuki1 [(none)]> CHANGE MASTER TO master_host='dummy',
-> relay_log_file = 'mysql-relay.000001',
-> relay_log_pos= 4;
Query OK, 0 rows affected (0.04 sec)
fuki1 [(none)]> START SLAVE sql_thread;
Query OK, 0 rows affected (0.10 sec)
おぉ。innotopのトランザクションをみてると確かに3 threadで実行されてる！！
History Versions Undo Dirty Buf Used Bufs Txns MaxTxnTime LStrcts
51 2.67% 3.78% 8 00:00
ID User Host Txn Status Time Undo Query Text
12 system use ACTIVE (PR 00:00 2
13 system use ACTIVE (PR 00:00 2
14 system use ACTIVE (PR 00:00 2
完了後データを確認してみるとたしかにgtid_purgedで設定したクエリはとばされて読み込みが完了してます。
fuki1 [d1]> SELECT * FROM t1 limit 10; +----+------------+ | id | value | +----+------------+ | 1 | C8vAt5qSyl | | 2 | 15mtFfSGuz | | 3 | 96ZFZ0451W | | 4 | ZHM6qMQRXI | | 5 | xMkqCVFgOT | | 6 | FWBCUry1FW | | 7 | Uoa5Y1sOPK | | 8 | DoCkXtvZSj | | 9 | JdiF94D1t2 | | 10 | dwqV6ykt54 | +----+------------+ 10 rows in set (0.01 sec) fuki1 [d1]> SELECT @@gtid_executed; +-----------------------------------------------+ | @@gtid_executed | +-----------------------------------------------+ | 7d2772a5-8987-11e8-a6a6-0201858272ab:1-128919 | +-----------------------------------------------+ 1 row in set (0.00 sec) fuki1 [d1]> select count(1) FROM t1; +----------+ | count(1) | +----------+ | 1011000 | +----------+ 1 row in set (0.13 sec)
こんな感じでMTSを利用してPITRができるようです。すごい！ 本来は速度検証してどれくらい早くなるかまでやりたかったけど更新データ量が少なくてそこまで至らなかった感じです。。。:bow: 多分自分が本番で実際することはないですが、こういうやり方もあるよーって感じで勉強になりました。
以上です。
