ログポジションベースのレプリケーションの場合は、レプリケーション障害発生時にスレーブでSKIP処理を容易に行う事ができましたが、GTIDモードの場合は以下のように空のトランザクションを実行して、エラー対応をする必要がありました。

mysqlslavetrxを利用しない場合の例

root@localhost [sakila]> stop slave;
Query OK, 0 rows affected (0.03 sec)

root@localhost [sakila]> SET GTID_NEXT = "3edaa0b8-3e39-11e4-9df1-080027f5bf08:54";
Query OK, 0 rows affected (0.00 sec)

root@localhost [sakila]> begin; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [sakila]> SET GTID_NEXT = "3edaa0b8-3e39-11e4-9df1-080027f5bf08:55";
Query OK, 0 rows affected (0.00 sec)

root@localhost [sakila]> begin; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [sakila]> SET GTID_NEXT = AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)

root@localhost [sakila]> start slave;
Query OK, 0 rows affected (0.02 sec)

多くのトランザクションをSKIPする場合は、何回もコミットを実行して空のトランザクションを作成しないといけなかったので、非常に面倒な操作でしたが、MySQL Utilitiesの1.6以降で準備されているmysqlslavetrxを利用すると、まとめて1回で複数のGTIDをスキップ出来るようになっています。これまで以上にGTIDでの運用がし易くなっているので、活用頂ければと思います。

[root@misc02 scripts]# ./mysqlslavetrx --help
MySQL Utilities mysqlslavetrx version 1.6.4 
License type: GPLv2

1) 先ずは、レプリケーションに問題が発生していない状況を確認

■ マスターの状況

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
+----+--------------------+------------+
3 rows in set (0.01 sec)

root@localhost [REPLI]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.002412 |     3028 |              |                  | 2dde009f-d4dc-11e4-b437-0800279cea3c:1-222553 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

root@localhost [REPLI]> 

■Slaveの状況

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc02     |
|  2 | mysqlslavetrx - 02 | misc02     |
|  3 | mysqlslavetrx - 03 | misc02     |
+----+--------------------+------------+
3 rows in set (0.00 sec)

root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002412
          Read_Master_Log_Pos: 3028
               Relay_Log_File: misc02-relay-bin.000133
                Relay_Log_Pos: 821
        Relay_Master_Log_File: mysql-bin.002412
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: REPLI
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3028
              Relay_Log_Space: 3749
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:216437-222553
            Executed_Gtid_Set: 2d0d3b32-4269-11e6-aca7-0800275fa837:1-7,
2dde009f-d4dc-11e4-b437-0800279cea3c:1-222553
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

root@localhost [REPLI]> 

■スレーブでデータ変更し、レプリケーションの不整合を発生させてみます。

スレーブでデータをINSERT

root@localhost [REPLI]> insert into T_Sample01(memo) values('Inserted @ Slave');
Query OK, 1 row affected (0.00 sec)

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc02     |
|  2 | mysqlslavetrx - 02 | misc02     |
|  3 | mysqlslavetrx - 03 | misc02     |
|  4 | Inserted @ Slave   | misc02     |
+----+--------------------+------------+
4 rows in set (0.00 sec)

root@localhost [REPLI]> 

マスターの状況

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
+----+--------------------+------------+
3 rows in set (0.00 sec)

root@localhost [REPLI]> 

■上記の状況でマスターでデータを変更

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
+----+--------------------+------------+
3 rows in set (0.00 sec)

root@localhost [REPLI]> insert into T_Sample01(memo) values('mysqlslavetrx - 04');
Query OK, 1 row affected (0.01 sec)

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
|  4 | mysqlslavetrx - 04 | misc01     |
+----+--------------------+------------+
4 rows in set (0.00 sec)

root@localhost [REPLI]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.002412 |     3395 |              |                  | 2dde009f-d4dc-11e4-b437-0800279cea3c:1-222554 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

root@localhost [REPLI]> 

SLAVEではReplicationエラーになっている。(Duplicate Key)

root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002412
          Read_Master_Log_Pos: 3395
               Relay_Log_File: misc02-relay-bin.000133
                Relay_Log_Pos: 821
        Relay_Master_Log_File: mysql-bin.002412
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: REPLI
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table REPLI.T_Sample01; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.002412, end_log_pos 3364
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3028
              Relay_Log_Space: 4116
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table REPLI.T_Sample01; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.002412, end_log_pos 3364
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 160808 10:31:59
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:216437-222554
            Executed_Gtid_Set: 2d0d3b32-4269-11e6-aca7-0800275fa837:1-8,
2dde009f-d4dc-11e4-b437-0800279cea3c:1-222553
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

root@localhost [REPLI]> 

マスターでデータをもう1件変更(不要ですが、GTIDのSKIPを分かり易くする為)

root@localhost [REPLI]> insert into T_Sample01(memo) values('mysqlslavetrx - 05');
Query OK, 1 row affected (0.00 sec)

root@localhost [REPLI]> show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.002412 |     3762 |              |                  | 2dde009f-d4dc-11e4-b437-0800279cea3c:1-222555 |
+------------------+----------+--------------+------------------+-----------------------------------------------+
1 row in set (0.00 sec)

root@localhost [REPLI]> 

■Slave側ではリレーログの受信まで出来て、SQL(Applier)スレッドがINSERTに失敗しているので、
Duplicate entry ‘4’ for key ‘PRIMARY’の処理だけSKIPしてみる。

【対象】
Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:216437-222555
Executed_Gtid_Set: 2d0d3b32-4269-11e6-aca7-0800275fa837:1-8,
2dde009f-d4dc-11e4-b437-0800279cea3c:1-222553

 対象: 2dde009f-d4dc-11e4-b437-0800279cea3c:222554

mysqlslavetrxで対象のGTID(トランザクション)をSKIP実行

[root@misc02 scripts]# ./mysqlslavetrx --gtid-set=2dde009f-d4dc-11e4-b437-0800279cea3c:222554 --slaves=admin:password@misc02
WARNING: Using a password on the command line interface can be insecure.
#
# GTID set to be skipped for each server:
# - misc02@3306: 2dde009f-d4dc-11e4-b437-0800279cea3c:222554
#
# Injecting empty transactions for 'misc02:3306'...
#
#...done.
#
[root@misc02 scripts]# 

補足:複数GTIDをスキップする場合は以下のようにハイフンでつなぎます。

# Skip the specified GTID set (three transaction: 10, 11, 12) on two slaves.
$ mysqlslavetrx –gtid-set=ee2655ae-2e88-11e4-b7a3-606720440b68:10-12 –slaves=rpl:pass@host2:3306,rpl:pass@host3:3306

SKIP後のレプリケーションの状況を確認(レプリケーションが停止している状況が解消されている)
※リトライカウントがオーバーしている場合は、STOP SLAVE, START SLAVEを実行してみて下さい。


root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002412
          Read_Master_Log_Pos: 3762
               Relay_Log_File: misc02-relay-bin.000135
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.002412
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: REPLI
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3762
              Relay_Log_Space: 962
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:216437-222555
            Executed_Gtid_Set: 2d0d3b32-4269-11e6-aca7-0800275fa837:1-8,
2dde009f-d4dc-11e4-b437-0800279cea3c:1-222555
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc02     |
|  2 | mysqlslavetrx - 02 | misc02     |
|  3 | mysqlslavetrx - 03 | misc02     |
|  4 | Inserted @ Slave   | misc02     |
|  5 | mysqlslavetrx - 05 | misc02     |
+----+--------------------+------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 


以下、レプリケーション再開後のデータ修正例

■データ修正@SLAVE

root@localhost [REPLI]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> update T_Sample01 set memo = 'mysqlslavetrx - 04' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [REPLI]> set sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc02     |
|  2 | mysqlslavetrx - 02 | misc02     |
|  3 | mysqlslavetrx - 03 | misc02     |
|  4 | mysqlslavetrx - 04 | misc02     |
|  5 | mysqlslavetrx - 05 | misc02     |
+----+--------------------+------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 

■データ修正後の動作確認@マスター

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
|  4 | mysqlslavetrx - 04 | misc01     |
|  5 | mysqlslavetrx - 05 | misc01     |
+----+--------------------+------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> insert into T_Sample01(memo) values('mysqlslavetrx - 06');
Query OK, 1 row affected (0.00 sec)

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc01     |
|  2 | mysqlslavetrx - 02 | misc01     |
|  3 | mysqlslavetrx - 03 | misc01     |
|  4 | mysqlslavetrx - 04 | misc01     |
|  5 | mysqlslavetrx - 05 | misc01     |
|  6 | mysqlslavetrx - 06 | misc01     |
+----+--------------------+------------+
6 rows in set (0.01 sec)

root@localhost [REPLI]> 

■SLAVE

root@localhost [REPLI]> select *,@@hostname from T_Sample01;
+----+--------------------+------------+
| id | memo               | @@hostname |
+----+--------------------+------------+
|  1 | mysqlslavetrx - 01 | misc02     |
|  2 | mysqlslavetrx - 02 | misc02     |
|  3 | mysqlslavetrx - 03 | misc02     |
|  4 | mysqlslavetrx - 04 | misc02     |
|  5 | mysqlslavetrx - 05 | misc02     |
|  6 | mysqlslavetrx - 06 | misc02     |
+----+--------------------+------------+
6 rows in set (0.01 sec)

root@localhost [REPLI]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002412
          Read_Master_Log_Pos: 4129
               Relay_Log_File: misc02-relay-bin.000135
                Relay_Log_Pos: 821
        Relay_Master_Log_File: mysql-bin.002412
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: REPLI
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4129
              Relay_Log_Space: 1329
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:216437-222556
            Executed_Gtid_Set: 2d0d3b32-4269-11e6-aca7-0800275fa837:1-8,
2dde009f-d4dc-11e4-b437-0800279cea3c:1-222556
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

root@localhost [REPLI]> 

詳細:
3.4.7 How do you fix errant transactions on the replication topology?

コメントを残す

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

Post Navigation