ログポジションベースのレプリケーションの場合は、レプリケーション障害発生時にスレーブで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?


【再確認】GTIDを利用したMySQL Replicationにおけるスレーブの追加

新規Slaveのoption fileに以下の設定を追加

# Binary logging and Replication
gtid-mode                      = on
enforce-gtid-consistency       = on
server_id                      = 3
log_bin                        = mysql-bin
log-slave-updates

# master_info_repository        = TABLE
# relay_log_info_repository     = TABLE

#disable-gtid-unsafe-statements
#disable-gtid-unsafe-statementsは,GTIDと互換性のない一部のSQLの実行を無効にする。

GTIDを利用したSlave設定がONになっている事を確認@CentOS03

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS03   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
Empty set (0.00 sec)

root@localhost [test]> 

root@localhost [test]> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| enforce_gtid_consistency        | ON        |
| gtid_executed                   |           |
| gtid_mode                       | ON        |
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+
7 rows in set (0.00 sec)

Master側にてデータを取得してデータ作成(スレーブでもOK、また特定DBのみでも問題なければOK)

----------------
[admin@CentOS01 ~]$ /usr/local/mysql/bin/mysqldump -uroot -p --all-databases --single-transaction --triggers --routines --events > GTID_fulldump.sql
Enter password: 
[admin@CentOS01 ~]$ scp  GTID_fulldump.sql admin@192.168.56.112:/home/admin/
admin@192.168.56.112's password: 
GTID_fulldump.sql                                                                                                            100% 4456KB   4.4MB/s   00:00    
[admin@CentOS01 ~]$ 

SlaveにてデータのリストアとReplicationの開始

[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql 
Enter password: 
[admin@CentOS03 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

root@localhost [mysql]> change master to
    -> master_host = '192.168.56.101',
    -> master_port=3306,
    -> master_user='GTID_USER',
    -> master_password='password',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

root@localhost [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: CentOS03-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          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: 0
              Relay_Log_Space: 151
              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: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/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: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204
                Auto_Position: 1
1 row in set (0.00 sec)

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

root@localhost [mysql]> SELECT @@global.gtid_executed;
+--------------------------------------------+
| @@global.gtid_executed                     |
+--------------------------------------------+
| 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
+--------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

Initial

Masterでの変更が反映される確認(Table作成)

root@localhost [test]>select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS01   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]>CREATE TABLE `Repli_Demo` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `event` varchar(256) NOT NULL DEFAULT '',
    ->   `description` varchar(256) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.10 sec)

root@localhost [test]>show tables;
+----------------+
| Tables_in_test |
+----------------+
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
10 rows in set (0.00 sec)

root@localhost [test]>

既存Slave

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS02   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| BR_TEST        |
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
11 rows in set (0.00 sec)

root@localhost [test]> 

新規追加Slave

root@localhost [test]> select @@hostname;
+------------+
| @@hostname |
+------------+
| CentOS03   |
+------------+
1 row in set (0.00 sec)

root@localhost [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Customer_Data  |
| MyISAM_InnoDB  |
| Personal_Info  |
| Personal_Info0 |
| Personal_Info1 |
| Personal_Info2 |
| Repli_Demo     |
| employee_table |
| ex_timestamp   |
| performance    |
+----------------+
10 rows in set (0.00 sec)

root@localhost [test]> 

マスターにてデータ変更

root@localhost [test]>desc Repli_Demo;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(20)      | NO   | PRI | NULL    | auto_increment |
| event       | varchar(256) | NO   |     |         |                |
| description | varchar(256) | NO   |     |         |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

root@localhost [test]>insert into Repli_Demo(event,description) values('Seminor 20150116','Repli Seminor for beginner');
Query OK, 1 row affected (0.16 sec)

root@localhost [test]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000256 |     1536 |              |                  | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]>

既存Slave

root@localhost [test]> select * from Repli_Demo;
+----+------------------+----------------------------+
| id | event            | description                |
+----+------------------+----------------------------+
|  1 | Seminor 20150116 | Repli Seminor for beginner |
+----+------------------+----------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: GTID_SSL_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 1536
               Relay_Log_File: CentOS02-relay-bin.000111
                Relay_Log_Pos: 1746
        Relay_Master_Log_File: mysql-bin.000256
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 1536
              Relay_Log_Space: 2350
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /usr/local/mysql/ssl/sql-ssl-cert.pem
           Master_SSL_CA_Path: /usr/local/mysql/ssl/
              Master_SSL_Cert: /usr/local/mysql/ssl/sql-gtid-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /usr/local/mysql/ssl/sql-ssl-repl-key.pem
        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: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql-advanced-5.6.21-linux-glibc2.5-x86_64/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:122-209
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209,
cf80b01f-364f-11e4-aa59-0800270e2d1e:1-11
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [test]> 

新規追加Slave

root@localhost [test]> select * from Repli_Demo;
+----+------------------+----------------------------+
| id | event            | description                |
+----+------------------+----------------------------+
|  1 | Seminor 20150116 | Repli Seminor for beginner |
+----+------------------+----------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 1536
               Relay_Log_File: CentOS03-relay-bin.000002
                Relay_Log_Pos: 1706
        Relay_Master_Log_File: mysql-bin.000256
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 1536
              Relay_Log_Space: 1913
              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: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:205-209
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [test]> 

データ追加
final
GTIDの確認
final-2
Binary Logの確認
GTID
Binary Log確認時のGTID確認
GTID-Confirm

ここら辺は、Enterprise版のMySQL Enterprise Monitorでレプリケーションを統合監視しても良いかもしれません。

その他:メモ
—————————————————————————————————-
以前のReplicationの設定が残っていてエラーになる場合の対応  ERROR 1840 (HY000)
—————————————————————————————————-


[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql Enter password: ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. [admin@CentOS03 ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.6.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]> SELECT @@global.gtid_executed;
+——————————————–+
| @@global.gtid_executed |
+——————————————–+
| 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
+——————————————–+
1 row in set (0.00 sec)

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——————————————–+
| Variable_name | Value |
+———————————+——————————————–+
| enforce_gtid_consistency | ON |
| gtid_executed | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-203 |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——————————————–+
6 rows in set (0.00 sec)

root@localhost [(none)]> RESET MASTER;
Query OK, 0 rows affected (0.03 sec)

root@localhost [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%GTID%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+———————————+——-+
6 rows in set (0.00 sec)

root@localhost [(none)]> exit
Bye
[admin@CentOS03 ~]$ /usr/local/mysql/bin/mysql -u root -p < GTID_fulldump.sql Enter password: [/SQL] GTIDを利用するにあたり、読んでおいた方が良さそうなページ http://nippondanji.blogspot.jp/2014/12/mysqlgtid.html
http://yakst.com/ja/posts/896
http://www.percona.com/live/mysql-conference-2014/sites/default/files/slides/FacebookGTIDPerconaLive2014.pdf


mysqlfabricにてslaveを追加した場合に、新しいSlaveのバックグラウンド処理の確認。

Slave追加前

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0

スレーブ追加
mysqlfabric group add global 127.0.0.1:63304

スレーブ追加後

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode weight
------------------------------------ --------------- --------- ---------- ------
8cde5a0d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63301   PRIMARY READ_WRITE    1.0
8cfb7b39-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63302 SECONDARY  READ_ONLY    1.0
8da475e0-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63303 SECONDARY  READ_ONLY    1.0
8e43506d-8cfb-11e4-9f84-080027d65c57 127.0.0.1:63304 SECONDARY  READ_ONLY    1.0

新しいSlaveではリストア同期が行われ、アプリアカウント、テーブル、データのリストア同期が行われている。

-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 | egrep -v ^# | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
BINLOG '
jledVA8EAAAAdAAAAHgAAAABAAQANS42LjIyLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQbR
bZs=
'/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:1'/*!*/;
SET TIMESTAMP=1419597327/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
SET PASSWORD FOR 'root'@'localhost'='*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:2'/*!*/;
SET TIMESTAMP=1419597331/*!*/;
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED BY PASSWORD '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:3'/*!*/;
use `test`/*!*/;
SET TIMESTAMP=1419597503/*!*/;
DROP TABLE IF EXISTS `employees` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:4'/*!*/;
SET TIMESTAMP=1419597503/*!*/;
CREATE TABLE employees (   emp_no INT,    first_name CHAR(40),    last_name CHAR(40))
/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:5'/*!*/;
SET TIMESTAMP=1419597505/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597505/*!*/;
INSERT INTO employees VALUES (0, 'John:0', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:6'/*!*/;
SET TIMESTAMP=1419597506/*!*/;
BEGIN
/*!*/;
SET TIMESTAMP=1419597506/*!*/;
INSERT INTO employees VALUES (1, 'John:1', 'Doe')
/*!*/;
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:7'/*!*/;
--続ける--
-bash-4.2$ mysqlbinlog --no-defaults Fabric02-bin.000003 |  egrep -i GTID | tail -n 10
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:417'/*!*/;
#141226 21:42:16 server id 1  end_log_pos 118585 CRC32 0x55ca8db9       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:418'/*!*/;
#141226 21:42:17 server id 1  end_log_pos 118870 CRC32 0x19ecc662       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:419'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119155 CRC32 0x63c53321       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:420'/*!*/;
#141226 21:42:18 server id 1  end_log_pos 119440 CRC32 0xee427af3       GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8cde5a0d-8cfb-11e4-9f84-080027d65c57:421'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
-bash-4.2$ 

スレーブ追加時のデータ同期については、GTIDモードのレプリケーションに依存するので此方を参照下さい。
http://variable.jp/2015/02/26/mysqlfabric-group-add%E5%AE%9F%E8%A1%8C%E6%99%82%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E5%90%8C%E6%9C%9F/