MySQL5.7からslave_parallel_workersを調整してスレーブの遅延が対応出来る事は、
色々な資料やブログ等にも書かれているので詳細はそちらを確認してみて下さい。
Oracle MySQL Cloud Service(OC3 = 2vCPU)の環境でSQLSLAPで負荷をかけてみて、
マスターとスレーブで遅延がどれだけ解消できるか?若しくはどこまで調整すれば良いか確認してみました。
slave_parallel_workersを1,2,4,8,16,32と変更して確認した中では、slave_parallel_workers=8が安定していました。
但し、slave_parallel_workersが多いからと言ってCPUが少ないインスタンスより上がる訳では無く、全体的なシステムのバランスが重要なようです。
スレーブのCPUや実行されているQuery等を確認して、適宜最適な値を調整出来ると良いですね。

環境
Oracle Cloud
MySQL Service
Instance: OC3 (2vCPU)
Monitoring: MySQL Enterprise Monitor

slave_parallel_workers=1

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'SLAVE_PARALLEL_WORKERS';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> 

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.711 seconds
        Minimum number of seconds to run all queries: 38.711 seconds
        Maximum number of seconds to run all queries: 38.711 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

slave_parallel_workers=8

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL SLAVE_PARALLEL_WORKERS=8;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'SLAVE_PARALLEL_WORKERS';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 8     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql> 
[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 38.466 seconds
        Minimum number of seconds to run all queries: 38.466 seconds
        Maximum number of seconds to run all queries: 38.466 seconds
        Number of clients running queries: 10
        Average number of queries per client: 10000

[oracle@study01-mysql-1 ~]$ 

以下、MySQL Enterprise Monitorで確認したグラフです。スレーブの遅延の状況が分かり易いですね。

slave_parallel_workers=2とslave_parallel_workers=8の差です。
1) Master -> 2) slave_parallel_workers=8 -> slave_parallel_workers=2の順にデータベース内での処理が終わっています。

slave_parallel_workers=2とslave_parallel_workers=32の差です。
この検証では、マスターとスレーブがほぼ同時に終了しています。

数回CPUの負荷を取得してみましたが、今回の環境ではThreadが少ない方がCPU負荷が上がっていました。何処に差があるかはまた別途確認してみますが、Threadが多いからと言って必ずしも少ないインスタンスよりCPU負荷が上がる訳では無さそうです。

[oracle@study02-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.97      0.08      0.18      0.80      0.07     97.90
01:20:01 PM     all      4.08      0.32      0.31      4.32      0.08     90.89
01:30:01 PM     all      0.78      2.31      7.91      8.56      0.12     80.33
01:40:01 PM     all      0.57      2.46      7.94     10.96      0.13     77.93
01:50:01 PM     all      0.61      0.14      0.09      0.66      0.06     98.44
02:00:01 PM     all      0.74      0.14      0.09      0.63      0.07     98.33
Average:        all      0.10      0.10      0.22      0.69      0.06     98.83


[oracle@study03-mysql-1 ~]$ 
01:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:10:01 PM     all      0.79      0.15      0.09      0.55      0.06     98.35
01:20:01 PM     all      0.68      0.34      0.16      3.82      0.09     94.91
01:30:01 PM     all      0.56      1.75      0.70      6.41      0.10     90.48
01:40:01 PM     all      0.58      1.75      0.75      8.11      0.12     88.69
01:50:01 PM     all      0.52      0.13      0.09      0.43      0.08     98.75
02:00:01 PM     all      0.55      0.13      0.09      0.86      0.07     98.30
Average:        all      0.23      0.09      0.08      0.61      0.07     98.93

大目に負荷をかけて、もう少し長いスパンで影響を確認してみました

[oracle@study01-mysql-1 ~]$ ./mysqlslap.sh 
Benchmark
        Running for engine InnoDB
        Average number of seconds to run all queries: 185.845 seconds
        Minimum number of seconds to run all queries: 185.845 seconds
        Maximum number of seconds to run all queries: 185.845 seconds
        Number of clients running queries: 10
        Average number of queries per client: 50000

[oracle@study01-mysql-1 ~]$

以下の、グラフを確認してみるとslave_parallel_workersを適切に調節すれば、レプリケーションの遅延、余計なCPU負荷の削減なども出来る事が確認出来ます。

補足: SHOW SLAVE STATUSによるマスターからの遅延状況の確認

slave_parallel_workersが8と16では殆ど変りが無い事が分かります。
サーバースペック、ネットワーク、インスタンスの負荷等によっても変わってくるので適宜調整下さい。


MySQL5.7から実装された、マルチソースレプリケーションに関しての質問も少しずつ増えてきたので、改めて基本的な挙動をこちらに纏めました。
基本的には、通常のレプリケーションの挙動と変わりませんが、CHANNELに分けて1台のスレーブが複数マスターからのログを受け取るので、運用上、気を付けないといけない部分が増えてくるので、リカバリー含むPOCはしておいた方が安心です。

■ マスター側の設定(複数マスターサーバー全体でサーバーID以外は同じ)
※ マスター側は通常のレプリケーションの設定をしています。(サーバーID, バイナリーログ等)
※ ログポジションベース、GTIDモードどちらも可能ですが、今回はGTIDで設定しています。
※ Masterに予めスレーブ用のアカウントを作成済み。

Optionファイルに設定した方が良いですが検証なので、動的に設定しています。
MySQL8.0ではここら辺の動的設定も永続化するオプションが追加されています。

以下の構成での説明になります

Master1(Port:63301) --------------------->
                                            SLAVE(Port:63303)
Master2(Port:63302) --------------------->

■ Slaveにて、各マスターへの接続設定を行います。


mysql> STOP SLAVE;SET GLOBAL master_info_repository = 'TABLE';SET GLOBAL relay_log_info_repository = 'TABLE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='multi_source', MASTER_PORT=63301, MASTER_PASSWORD='password', \
    -> MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-1';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='multi_source', MASTER_PORT=63302, MASTER_PASSWORD='password', \
    -> MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master-2';
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql>

mysql> START SLAVE FOR CHANNEL 'master-1';START SLAVE FOR CHANNEL 'master-2';
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

■ レプリケーションステータスの確認
(CHANNELを指定しなければ、全てのCHANNELの接続情報が表示されます)


mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-1' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63301
                Connect_Retry: 60
              Master_Log_File: Fabric02-bin.000001
          Read_Master_Log_Pos: 885
               Relay_Log_File: Fabric02-relay-bin-master@002d1.000002
                Relay_Log_Pos: 423
        Relay_Master_Log_File: Fabric02-bin.000001
             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: 885
              Relay_Log_Space: 686
              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: a0c35acf-839f-11e6-89f4-080027d65c57
             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: 
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-3,
a65948cd-839f-11e6-8cb5-080027d65c57:1-3,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'master-2' \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63302
                Connect_Retry: 60
              Master_Log_File: Fabric02-bin.000001
          Read_Master_Log_Pos: 885
               Relay_Log_File: Fabric02-relay-bin-master@002d2.000002
                Relay_Log_Pos: 1104
        Relay_Master_Log_File: Fabric02-bin.000001
             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: 885
              Relay_Log_Space: 1327
              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: 2
                  Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
             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: a65948cd-839f-11e6-8cb5-080027d65c57:1-3
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-3,
a65948cd-839f-11e6-8cb5-080027d65c57:1-3,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63301
                Connect_Retry: 60
              Master_Log_File: Fabric02-bin.000001
          Read_Master_Log_Pos: 2102
               Relay_Log_File: Fabric02-relay-bin-master@002d1.000002
                Relay_Log_Pos: 1640
        Relay_Master_Log_File: Fabric02-bin.000001
             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: 2102
              Relay_Log_Space: 1903
              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: a0c35acf-839f-11e6-89f4-080027d65c57
             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: a0c35acf-839f-11e6-89f4-080027d65c57:4-8
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8,
a65948cd-839f-11e6-8cb5-080027d65c57:1-6,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63302
                Connect_Retry: 60
              Master_Log_File: Fabric02-bin.000001
          Read_Master_Log_Pos: 1623
               Relay_Log_File: Fabric02-relay-bin-master@002d2.000002
                Relay_Log_Pos: 1842
        Relay_Master_Log_File: Fabric02-bin.000001
             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: 1623
              Relay_Log_Space: 2065
              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: 2
                  Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
             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: a65948cd-839f-11e6-8cb5-080027d65c57:1-6
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8,
a65948cd-839f-11e6-8cb5-080027d65c57:1-6,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

mysql> 

■ Performance_Schemaのコネクションテーブルからも接続を確認可能です。


mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-1'\G
*************************** 1. row ***************************
             CHANNEL_NAME: master-1
               GROUP_NAME: 
              SOURCE_UUID: a0c35acf-839f-11e6-89f4-080027d65c57
                THREAD_ID: 36
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 38
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:43:04
 RECEIVED_TRANSACTION_SET: a0c35acf-839f-11e6-89f4-080027d65c57:4-8
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G
*************************** 1. row ***************************
             CHANNEL_NAME: master-2
               GROUP_NAME: 
              SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
                THREAD_ID: 38
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 39
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:43:01
 RECEIVED_TRANSACTION_SET: a65948cd-839f-11e6-8cb5-080027d65c57:1-6
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> 

全てのマスターからスレーブに配布するスキーマが異なっていれば、運用的な敷居は低いかと思います。
こちらの例では、マスター毎にスキーマ(データベース)が別です。

multi-master

マスターとスレーブで同じスキーマ、同じテーブルを利用する場合は、データに矛盾が発生しないようにしないといけません。
予めIDが被らないようにするなどの設計と実装が必要です。
同じスキーマを対象にしている場合のオブジェクト変更は、特定のマスターのみで変更して他のマスターではBINLOGをOFFにして変更する等の運用が必要です。

(以下の例では同じオブジェクトに別々のIDでデータをINSERTしています)
multi-source_same_schema_table

こちらは、サーバーPORTにプラスで付けてます。何でも被らなければOKです。
multi-source_same_schema_table_appli

リレーログは、各マスターサーバー毎に作成されます。
こちらの例では、relay-bin-master@002d1とrelay-bin-master@002d2でログがサーバー毎に分かれています。

-bash-4.2$ ls -l /home/mysql/fabric/mysql3/*relay*
-rw-r-----. 1 mysql mysql  223  9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.000001
-rw-r-----. 1 mysql mysql 1411  9月 27 11:16 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.000002
-rw-r-----. 1 mysql mysql   82  9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d1.index
-rw-r-----. 1 mysql mysql  223  9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.000001
-rw-r-----. 1 mysql mysql 1411  9月 27 11:17 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.000002
-rw-r-----. 1 mysql mysql   82  9月 27 11:02 /home/mysql/fabric/mysql3/Fabric02-relay-bin-master@002d2.index
-bash-4.2$ /usr/local/mysql57/bin/mysqlbinlog -v Fabric02-relay-bin-master@002d1.000002 | grep CREATE
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
CREATE TABLE MASTER01 (
-bash-4.2$ /usr/local/mysql57/bin/mysqlbinlog -v Fabric02-relay-bin-master@002d2.000002 | grep CREATE
GRANT SELECT, INSERT, DELETE, CREATE, DROP ON `test`.* TO 'app'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*5BCB3E6AC345B435C7C2E6B7949A04CE6F6563D3'
CREATE TABLE MASTER02 (
-bash-4.2$ 

■ RESET (特定CHANNELのみRESETしてますが、CHANNELオプション無しで全てRESETも可能です。)


mysql> STOP SLAVE FOR CHANNEL 'master-1';
Query OK, 0 rows affected (0.00 sec)

mysql> RESET SLAVE FOR CHANNEL 'master-1';
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-1'\G
*************************** 1. row ***************************
             CHANNEL_NAME: master-1
               GROUP_NAME: 
              SOURCE_UUID: a0c35acf-839f-11e6-89f4-080027d65c57
                THREAD_ID: NULL
            SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 49
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:48:34
 RECEIVED_TRANSACTION_SET: 
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G
*************************** 1. row ***************************
             CHANNEL_NAME: master-2
               GROUP_NAME: 
              SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
                THREAD_ID: 38
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 53
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:50:01
 RECEIVED_TRANSACTION_SET: a65948cd-839f-11e6-8cb5-080027d65c57:1-6
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

mysql>

mysql> STOP SLAVE FOR CHANNEL 'master-2';
Query OK, 0 rows affected (0.00 sec)

mysql> RESET SLAVE FOR CHANNEL 'master-2';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM performance_schema.replication_connection_status WHERE channel_name='master-2'\G
*************************** 1. row ***************************
             CHANNEL_NAME: master-2
               GROUP_NAME: 
              SOURCE_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
                THREAD_ID: NULL
            SERVICE_STATE: OFF
COUNT_RECEIVED_HEARTBEATS: 55
 LAST_HEARTBEAT_TIMESTAMP: 2016-09-26 13:51:01
 RECEIVED_TRANSACTION_SET: 
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63301
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: Fabric02-relay-bin-master@002d1.000002
                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: 1903
              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: a0c35acf-839f-11e6-89f4-080027d65c57
             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: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8,
a65948cd-839f-11e6-8cb5-080027d65c57:1-6,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: localhost
                  Master_User: multi_source
                  Master_Port: 63302
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: Fabric02-relay-bin-master@002d2.000002
                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: 2065
              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: 2
                  Master_UUID: a65948cd-839f-11e6-8cb5-080027d65c57
             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: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: a0c35acf-839f-11e6-89f4-080027d65c57:1-8,
a65948cd-839f-11e6-8cb5-080027d65c57:1-6,
abf190f1-839f-11e6-8dbb-080027d65c57:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

mysql> 
 

参考: 
https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-overview.html
http://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-tutorials.html


MySQL5.7.14のGroup Replication DMR版が先月リリースされていたので、基本的な動作確認をしました。
まだ、DMR版なので前回の5.7.10とまた微妙に異なっていて、
group_replication_peer_addressesがgroup_replication_group_seedsに代わっていたり、
group_replication_recovery_userやgroup_replication_recovery_passwordが無くなっていたりします。

MySQL Group Replication: A Quick Start Guide
http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

MySQL5.7.10版での検証: MYSQL GROUP REPLICATION
http://variable.jp/2016/07/18/mysql-group-replication/

検証環境のオプションファイル設定
基本的にはserver_idをノード間で変更しているのみ。

# Binary logging and Replication
server_id                      = 1
log_bin                        = mysql-bin
binlog_cache_size              = 1M
binlog_stmt_cache_size         = 1M                                  # Since 5.5
max_binlog_size                = 64M                                 # Make bigger for high traffic to reduce number of files
sync_binlog                    = 0                                   # Set to 1 or higher to reduce potential loss of binary-log data
expire_logs_days               = 30                                  # We will survive easter holidays
#binlog_format                 = MIXED                               # Use MIXED if you experience some troubles
binlog_format                  = ROW
binlog_row_image               = MINIMAL                             # Since 5.6
binlog_rows_query_log_events   = 1                                   # Log Statement in ROW Base Binlog
# auto_increment_increment       = 1                                 # For Master/Master set-ups use 1 and 2
# auto_increment_offset          = 2                                 # For Master/Master set-ups use 2 for both nodes
log_timestamps = SYSTEM


# Add for GTID Mode
gtid-mode                      = on
enforce-gtid-consistency       = on
log-slave-updates

master_info_repository        = TABLE
relay_log_info_repository     = TABLE

# Group replication
# plugin-load = group_replication.so
group_replication_start_on_boot = ON
group_replication_bootstrap_group = OFF
# transaction-write-set-extraction=MURMUR32
transaction-write-set-extraction=XXHASH64
binlog-checksum=NONE
group_replication = FORCE_PLUS_PERMANENT
transaction-write-set-extraction = XXHASH64
group_replication_group_name= "00000000-1111-2222-3333-123456789ABC"
#group_replication_recovery_user='rpl_user'                             #commented out at upgrading to 5.7.14
#group_replication_recovery_password='rpl_pass'                         #commented out at upgrading to 5.7.14
group_replication_recovery_retry_count= 2
group_replication_recovery_reconnect_interval= 120
group_replication_local_address="192.168.56.101:6606"
#group_replication_peer_addresses= "192.168.56.101:18620,192.168.56.102:18620" #commented out at upgrading to 5.7.14
group_replication_group_seeds =  "192.168.56.101:6606,192.168.56.102:6606"

# 5.7.14
relay-log-recovery = ON
slave-parallel-type = LOGICAL_CLOCK
slave-preserve-commit-order = ON
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave-type-conversions = ALL_NON_LOSSY
sync-master-info = 1000
sync-relay-log = 1000
slave-parallel-workers = 0

■Boot Strap Nodeの起動

– It will not try and participate in any group communication when starting but will instead configure the group as consisting only of itself.
– Any subsequent member that attempts to join the group will sync itself up with the state of this instance.

We need to pick one member and declare that it is the bootstrap node by setting group_replication_bootstrap_group=ON
Just remember to turn group_replication_bootstrap_group=OFF again after the first member is up.

root@localhost [GR_TEST]> select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc01     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | OFF   |
+-----------------------------------+-------+
1 row in set (0.03 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> show variables like 'group_replication_bootstrap_group';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| group_replication_bootstrap_group | ON    |
+-----------------------------------+-------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.07 sec)

root@localhost [GR_TEST]> SET GLOBAL group_replication_bootstrap_group= 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:1
                         MEMBER_ID: 29ea17bc-3848-11e6-9900-0800279ca844
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■2番目のノード追加
Specify valid MySQL credentials that this node will use when requesting GTIDs from existing members of the
group (a seed or donor) necessary to perform an automated recovery (such as when joining the group, which we’ll do next):
CHANGE MASTER TO MASTER_USER=’rpl_user’, MASTER_PASSWORD=’rpl_pass’ FOR CHANNEL ‘group_replication_recovery’;
Have the new node join to become a member with: STOP GROUP_REPLICATION; START GROUP_REPLICATION; (STOP is necessary because we have start_on_boot enabled).

root@localhost [GR_TEST]>  select @@version,@@hostname;
+-----------------------+------------+
| @@version             | @@hostname |
+-----------------------+------------+
| 5.7.14-labs-gr080-log | misc02     |
+-----------------------+------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | OFFLINE      |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> STOP GROUP_REPLICATION;START GROUP_REPLICATION;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (7.04 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from performance_schema.replication_group_member_stats\G
*************************** 1. row ***************************
                      CHANNEL_NAME: group_replication_applier
                           VIEW_ID: 14732520384730018:2
                         MEMBER_ID: 5b07d5d8-4057-11e6-a315-0800279cea3c
       COUNT_TRANSACTIONS_IN_QUEUE: 0
        COUNT_TRANSACTIONS_CHECKED: 0
          COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 
    LAST_CONFLICT_FREE_TRANSACTION: 
1 row in set (0.00 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 追加の確認と設定(NODE2を追加後にBootstrapノードで実行)

You should also now execute step 3 (the CHANGE MASTER TO) on the node we used to bootstrap the group, if you haven’t previously done so.

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

root@localhost [GR_TEST]> select user_name, user_password from mysql.slave_master_info where channel_name = 'group_replication_recovery';
+-----------+---------------+
| user_name | user_password |
+-----------+---------------+
| rpl_user  | rpl_pass      |
+-----------+---------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

以下、基本動作確認

■マルチマスターの基本動作確認 (NODE1とNODE2からINSERT)

NODE1)

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.01 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(1,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2)

root@localhost [GR_TEST]> select * from T01;
Empty set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
+----+--------+---------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> insert into T01(ID,MEMO) values(2,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]>

blog2

■トランザクションの確認
NODE1)

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(3,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) 上記を実行中に同じテーブルに対してINSERTは問題ない。

root@localhost [GR_TEST]> start transaction;insert into T01(ID,MEMO) values(4,@@hostname);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
3 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

■ 同じ行を更新してみる(where ID=4)
NODE1)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'misc02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2) NODE1のトランザクション中に実行。
※トランザクション発行の順番では無く、先にcommitした方が優先される。

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC01 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | MISC02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC01' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
ERROR 1180 (HY000): Got error 149 during COMMIT
root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-09-07 22:05:15 |
|  2 | misc02 | 2016-09-07 22:05:47 |
|  3 | misc01 | 2016-09-07 22:09:10 |
|  4 | misc02 | 2016-09-07 22:09:13 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

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


現状、まだLab版ですが、MySQL5.7の追加のプラグインとして、マルチマスタまたはアクティブ/アクティブレプリケーションをサポートする
同期レプリケーション型のグループレプリケーションが準備されています。まだ、Lab版という事もあり、機能追加やバグ対応などがまだまだ必要な段階ですが、LAB版→DR版→RC版→GA版と段々と安定してくると思いますので、次のLab版がリリースされたら是非検証環境で試してみて頂ければと思います。
マスターサーバーのHA対応やスレーブが多い環境で、マスターサーバーのレプリケーション負荷分散等に活用出来そうです。

Group Replication関連参考ブログを見て頂けると、基本的なインストール方法が書かれていますので試される場合は、此方を参考にして下さい。

http://mysqlhighavailability.com/getting-started-with-mysql-group-replication/

NODE1にてグループレプリケーション開始
※オプションファイルに書いておくことで、SETコマンドの実行は不要です。
※XCOMで通信する為のポートは、通常のMySQL PORT3306とは別にしてください。


root@localhost [mysql]> SET GLOBAL group_replication_group_name= "00000000-1111-2222-3333-123456789ABC";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_bootstrap_group= 1;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_local_address="192.168.56.101:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_peer_addresses= "192.168.56.101:13001,192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_user='rpl_user';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_password='rpl_pass';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.59 sec)

root@localhost [mysql]> SET GLOBAL group_replication_bootstrap_group= 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [mysql]> 


NODE2をGRに参加してみます。


root@localhost [mysql]> SET GLOBAL group_replication_group_name= "00000000-1111-2222-3333-123456789ABC";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_local_address="192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_peer_addresses= "192.168.56.101:13001,192.168.56.102:13001";
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_user='rpl_user';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SET GLOBAL group_replication_recovery_password='rpl_pass';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.04 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 29ea17bc-3848-11e6-9900-0800279ca844
 MEMBER_HOST: misc01
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 5b07d5d8-4057-11e6-a315-0800279cea3c
 MEMBER_HOST: misc02
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
2 rows in set (0.01 sec)

root@localhost [mysql]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [mysql]> 



group_members

MEMBER_STATEが共にONLINEになっているので、DDL、DMLを処理して同期されているか確認して見ます
先ずは、NODE1でデータベース、テーブルを作成してからデータを1件入れてみます。
NODE1で作成したオブジェクトやデータはNODE2でも確認出来ます。
また、同様にNODE2で入れたデータは、NODE1で確認する事が出来ます。


root@localhost [mysql]> CREATE DATABASE GR_TEST;
Query OK, 1 row affected (0.03 sec)

root@localhost [mysql]> use GR_TEST;
Database changed
root@localhost [GR_TEST]> CREATE TABLE GR_TEST.T01 (
    -> ID INT NOT NULL PRIMARY KEY,
    -> MEMO varchar(30) COLLATE utf8_bin NOT NULL DEFAULT ''
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.07 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (1,@@hostname);
Query OK, 1 row affected (0.07 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
+----+--------+
1 row in set (0.01 sec)

root@localhost [GR_TEST]> 

NODE2でデータを確認してみます。


root@localhost [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| GR_TEST            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> use GR_TEST
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 [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
+----+--------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (2,@@hostname);
Query OK, 1 row affected (0.04 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
+----+--------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2で入れたデータはNODE1でも確認出来ました。
これで、双方向にレプリケーションが張られている事が確認出来ました。


root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
+----+--------+
2 rows in set (0.00 sec)


root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

GTIDの状態を確認
自分で更新したデータに関しては、RECEIVED_TRANSACTION_SETには反映されないので、@@GLOBAL.GTID_EXECUTEDで何処まで適用されているか確認。


root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7:9
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-10 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE1とNODE2の間で、トランザクションの競合が発生した場合
(同時に同じデータを更新しようとした場合)

NODE1で先ずは、トランザクションを張って処理を実行してみます。そして、Commitを行う前に、NODE2で同じデータを更新処理してみます。
最初に処理を開始した、NODE1は問題無く処理出来てますが、NODE2のCommit処理はエラーで終了しています。

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc02 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = @@hostname where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
Query OK, 0 rows affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc01 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2は、ERROR 1180 (HY000): Got error 149 during COMMITでエラーになっています。

root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc02 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> start transaction;update T01 set MEMO = 'MISC02' where ID = 4;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost [GR_TEST]> commit;
ERROR 1180 (HY000): Got error 149 during COMMIT
root@localhost [GR_TEST]> select * from T01;
+----+--------+
| ID | MEMO   |
+----+--------+
|  1 | misc01 |
|  2 | misc02 |
|  3 | misc01 |
|  4 | misc01 |
+----+--------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

group_replication_tran_conf

ONLINE DDLでALTERを実行した場合
結果としては、問題無くDDLも実行可能で伝搬されます。但し、現状のGRの仕様としては、オンラインスキーマ変更は推奨されていないので、
1)BINLOGをOFFにしてDDL実行 2) 他のノードも同様にBINLOGをOFFにしてDDL実行 3) 最後にアプリケーションを変更し変更を反映させるのが良さそうです。

NODE1にてDDLを実行して列を追加してみます。

root@localhost [GR_TEST]> ALTER TABLE T01 add column created_time datetime DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.03 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (5,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
+----+--------+---------------------+
5 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2でNODE1で実行されたDDLの結果を確認して、NODE2からデータを追加してみます。

root@localhost [GR_TEST]> desc T01;
+--------------+-------------+------+-----+-------------------+-------+
| Field        | Type        | Null | Key | Default           | Extra |
+--------------+-------------+------+-----+-------------------+-------+
| ID           | int(11)     | NO   | PRI | NULL              |       |
| MEMO         | varchar(30) | NO   |     |                   |       |
| created_time | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
+--------------+-------------+------+-----+-------------------+-------+
3 rows in set (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
+----+--------+---------------------+
5 rows in set (0.00 sec)

root@localhost [GR_TEST]> INSERT INTO GR_TEST.T01(ID,MEMO) VALUES (6,@@hostname);
Query OK, 1 row affected (0.01 sec)

root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
|  6 | misc02 | 2016-07-17 15:44:03 |
+----+--------+---------------------+
6 rows in set (0.00 sec)


root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-15 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:4-7:9:11:13-14
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

NODE2でデータを入れたあとにNODE1の状況を確認してみます。
特に問題無く、データが反映されている事が確認出来ます。
レプリケーションもRAWベースなので特に時間関連の関数なども気にしなくて良いです。


root@localhost [GR_TEST]> select * from T01;
+----+--------+---------------------+
| ID | MEMO   | created_time        |
+----+--------+---------------------+
|  1 | misc01 | 2016-07-17 15:40:29 |
|  2 | misc02 | 2016-07-17 15:40:29 |
|  3 | misc01 | 2016-07-17 15:40:29 |
|  4 | misc02 | 2016-07-17 15:40:29 |
|  5 | misc01 | 2016-07-17 15:42:33 |
|  6 | misc02 | 2016-07-17 15:44:03 |
+----+--------+---------------------+
6 rows in set (0.01 sec)

root@localhost [GR_TEST]> SELECT @@GLOBAL.GTID_EXECUTED;
+-------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                    |
+-------------------------------------------+
| 00000000-1111-2222-3333-123456789abc:1-15 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_connection_status\G
*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
               GROUP_NAME: 00000000-1111-2222-3333-123456789ABC
              SOURCE_UUID: 00000000-1111-2222-3333-123456789ABC
                THREAD_ID: NULL
            SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
 RECEIVED_TRANSACTION_SET: 00000000-1111-2222-3333-123456789abc:1-4:8:10:12:15
        LAST_ERROR_NUMBER: 0
       LAST_ERROR_MESSAGE: 
     LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.00 sec)

root@localhost [GR_TEST]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 29ea17bc-3848-11e6-9900-0800279ca844 | misc01      |        3306 | ONLINE       |
| group_replication_applier | 5b07d5d8-4057-11e6-a315-0800279cea3c | misc02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

root@localhost [GR_TEST]> 

余談: Group Replicationは全てマスターなので、Show Slave Statusは不要ですね。


/*** NODE1 ***/
root@localhost [GR_TEST]> show slave status\G
Empty set (0.00 sec)

root@localhost [GR_TEST]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 |     4391 |              |                  | 00000000-1111-2222-3333-123456789abc:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 


/*** NODE2 ***/
root@localhost [GR_TEST]> show slave status\G
Empty set (0.02 sec)

root@localhost [GR_TEST]> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000001 |     4391 |              |                  | 00000000-1111-2222-3333-123456789abc:1-15 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GR_TEST]> 

参考パラメータ


root@localhost [GR_TEST]> show global variables like '%group_repli%';
+---------------------------------------------------+-------------------------------------------+
| Variable_name                                     | Value                                     |
+---------------------------------------------------+-------------------------------------------+
| group_replication_allow_local_lower_version_join  | OFF                                       |
| group_replication_auto_increment_increment        | 7                                         |
| group_replication_bootstrap_group                 | OFF                                       |
| group_replication_components_stop_timeout         | 31536000                                  |
| group_replication_gcs_engine                      | xcom                                      |
| group_replication_group_name                      | 00000000-1111-2222-3333-123456789ABC      |
| group_replication_local_address                   | 192.168.56.101:13001                      |
| group_replication_peer_addresses                  | 192.168.56.101:13001,192.168.56.102:13001 |
| group_replication_pipeline_type_var               | STANDARD                                  |
| group_replication_recovery_complete_at            | TRANSACTIONS_CERTIFIED                    |
| group_replication_recovery_password               |                                           |
| group_replication_recovery_reconnect_interval     | 120                                       |
| group_replication_recovery_retry_count            | 2                                         |
| group_replication_recovery_ssl_ca                 |                                           |
| group_replication_recovery_ssl_capath             |                                           |
| group_replication_recovery_ssl_cert               |                                           |
| group_replication_recovery_ssl_cipher             |                                           |
| group_replication_recovery_ssl_crl                |                                           |
| group_replication_recovery_ssl_crlpath            |                                           |
| group_replication_recovery_ssl_key                |                                           |
| group_replication_recovery_ssl_verify_server_cert | OFF                                       |
| group_replication_recovery_use_ssl                | OFF                                       |
| group_replication_recovery_user                   | rpl_user                                  |
| group_replication_start_on_boot                   | OFF                                       |
+---------------------------------------------------+-------------------------------------------+
24 rows in set (0.01 sec)

root@localhost [GR_TEST]> 

【参考】

LABサイト
http://labs.mysql.com/

Group Replication関連参考ブログ
http://mysqlhighavailability.com/getting-started-with-mysql-group-replication/

MySQLセミナー資料
http://downloads.mysql.com/presentations/20160510_06_MySQL_57_ReplicationEnhancements.pdf

Auto Incrementの値
http://mysqlhighavailability.com/mysql-group-replication-auto-increment-configuration-handling/


特定のSQL処理で、GROUP BYなどの集合関数を利用していて、
“Using temporary”,”Using filesort”などが出て処理時間がかかり過ぎたり、
サブクエリーによる結果をJOINしてindexが利用出来無かったりと、
困難な場面に遭遇する事があるかと思います。

基本的には、物理的に変更しても良くて数倍だと思いますので、
アプリケーションやクエリーを工数かけて書き換えて対応するのが良いと思いますが、
なかなか出来ない場合は、可能な範囲でサーバーパラメータを変更したり、
クエリーを若干変更してメモリーテーブルやTEMPORARY TABLEなどでワークテーブルを作成し、
サブクエリーなどの結果を随時集計しIndexを使えるように処理する方法もあるかと思います。
もちろんハードで短期、中期的に解決する為に、力技で対応した場合もありましたが。。。

過去にもMEMORY STORAGE ENGINEについて、書きましたが改めて再確認してみました。
MEMORY ストレージエンジン

その他ソリューション:
Entperprise Edition:Query Rewrite Plugins
検証:MySQL Query Rewrite Plugins

MEMORYストレージエンジンによるワークテーブル
以下、メモリーストレージエンジンを利用した一時テーブル例 (インデックス利用可)


root@localhost [test]> CREATE TABLE T_Work_mem01 (
    ->   id int(11) DEFAULT NULL,
    ->   text varchar(100) DEFAULT NULL,
    ->   KEY idx_T_MEM01_id (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_mem01;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_mem01 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_mem01 where id = 8;
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_mem01 | ref  | idx_T_MEM01_id | idx_T_MEM01_id | 5       | const |    2 | NULL  |
+----+-------------+--------------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 


root@localhost [test]> CREATE TABLE T_Work_mem02 (
    -> id int(11) DEFAULT NULL,
    -> text varchar(100) DEFAULT NULL,
    -> INDEX USING HASH (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_mem02;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

root@localhost [test]> insert into T_Work_mem02 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_mem02 where id = 8;
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_mem02 | ref  | id            | id   | 5       | const |    2 | NULL  |
+----+-------------+--------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> 

memory

メモリーテーブルなので、再起動したらデータは無くなりますが、
ワークテーブルとして一時的に利用するのであれば問題無いかと。


[root@GA01 admin]# /etc/init.d/mysql.server restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@GA01 admin]# exit
exit
[admin@GA01 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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 test
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 [test]> select count(*) from T_Work_mem01;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> show tables like 'T_Work%';
+--------------------------+
| Tables_in_test (T_Work%) |
+--------------------------+
| T_Work_mem01             |
| T_Work_mem02             |
+--------------------------+
2 rows in set (0.00 sec)

root@localhost [test]>

※ データは空ですが、テーブル定義は残ります。

memory_table_after_reboot

参考 (レプリケーション時の注意点等):
8.3.8 Comparison of B-Tree and Hash Indexes

17.4.1.21 Replication and MEMORY Tables

15.3 The MEMORY Storage Engine

[留意点] MEMORY Tables and Replication
A server’s MEMORY tables become empty when it is shut down and restarted.
If the server is a replication master, its slaves are not aware that these
tables have become empty, so you see out-of-date content if you select data
from the tables on the slaves. To synchronize master and slave MEMORY tables,
when a MEMORY table is used on a master for the first time since it was started,
a DELETE statement is written to the master’s binary log, to empty the table on the slaves also.
The slave still has outdated data in the table during the interval
between the master’s restart and its first use of the table.
To avoid this interval when a direct query to the slave could return stale data,
use the –init-file option to populate the MEMORY table on the master at startup.

今回のデモ環境では,SLAVEにて除外してあります。

ignor

TEMPORARYテーブルの利用例(インデックス利用可)


root@localhost [test]> CREATE temporary TABLE T_Work_temp01 (
    ->  id int(11) DEFAULT NULL,
    ->  text varchar(100) DEFAULT NULL,
    ->  KEY idx_T_MEM01_id (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> desc T_Work_temp01;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_temp01 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_temp01 where id = 8;
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_temp01 | ref  | idx_T_MEM01_id | idx_T_MEM01_id | 5       | const |    2 | NULL  |
+----+-------------+---------------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

root@localhost [test]> CREATE temporary TABLE T_Work_temp02 (
    ->  id int(11) DEFAULT NULL,
    ->  text varchar(100) DEFAULT NULL,
    ->  INDEX USING HASH (id)
    -> ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> desc T_Work_temp02;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  | MUL | NULL    |       |
| text  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@localhost [test]> insert into T_Work_temp02 select * from  T_ONLINE_DDL;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

root@localhost [test]> explain select * from T_Work_temp02 where id = 8;
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | T_Work_temp02 | ref  | id            | id   | 5       | const |    2 | NULL  |
+----+-------------+---------------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

TEMPORARYテーブルは、Show Tablesをしても表示されません。
作成したセッションでは、SELECTする事は可能です。


root@localhost [test]> show tables;
+--------------------+
| Tables_in_test     |
+--------------------+
| FW_DEMO            |
| T_MEM              |
| T_MEM01            |
| T_ONLINE_DDL       |
| T_ONLY_FULL        |
| T_UNION01          |
| T_UNION02          |
| T_Work_mem01       |
| T_Work_mem02       |
| card_info          |
| employees          |
| tbl_partition      |
| tbl_partition2     |
| tbl_partition_year |
| users              |
+--------------------+
15 rows in set (0.00 sec)

root@localhost [test]> select count(*) from T_Work_temp01;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> select count(*) from T_Work_temp02;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

root@localhost [test]> 

オラクル等では、Global Session Temp Tableなどを利用して
他のセッションからもTEMPORARY TABLEにアクセス出来ますが、
こちらは、他のセッションからアクセスする事は出来ません。


[admin@GA02 ~]$ mysql -h 192.168.56.201 -u admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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.

admin@192.168.56.201 [(none)]> use test
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
admin@192.168.56.201 [test]> select count(*) from T_Work_temp01;
ERROR 1146 (42S02): Table 'test.T_Work_temp01' doesn't exist
admin@192.168.56.201 [test]>

temporary_table

ログオフ、ログインし直しても、当然無くなっています。
作成した、セッションのみで有効


root@localhost [test]> exit
Bye
[admin@GA01 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.24-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, 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 test
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 [test]> select count(*) from T_Work_temp01;
ERROR 1146 (42S02): Table 'test.T_Work_temp01' doesn't exist
root@localhost [test]> 

その他、参考情報
Materialized Views with MySQL
MySQLにおけるJOINのチューニングの定石


先日、ご紹介させて頂いた、MySQL Enterprise Firewallを利用する事により、
White ListベースのDBアクセス制御(ステートメントベース)をUserアカウントとSQLステートメントの
組み合わせで実装する事が出来ますが、Publicクラウドを含む環境でWebサイトを運用されている場合は、Replication機能と組み合わせてご利用される場合もあるかと思います。

MySQL Enterprise Firewallには、全部で4つのテーブルがありますが、
(Information_Schemaに2つMySQLに2つテーブルが準備されています)
information_schemaにあるテーブルに関しては、レプリケーション対象外なので実際にSlaveに同期されるのは以下の2つのテーブルという事になります。

firewall-diag

root@localhost [information_schema]> show tables from mysql like '%fire%';
+--------------------------+
| Tables_in_mysql (%fire%) |
+--------------------------+
| firewall_users           |
| firewall_whitelist       |
+--------------------------+

また、MySQL Enterprise FirewallのWhite Listはメモリー上にCacheされているので、
スレーブに同期された段階では直ぐにスレーブ側では有効にならないので、
以下のマニュアルに記載されているように、コマンドでメモリーに反映する必要があります。

6.3.15.4 MySQL Enterprise Firewall Reference

実際のMySQL Enterprise FirewallとReplicationの動作をこちらでご紹介します。

既存、White Listに定義を追加してSlave側で確認してみます。

MASTER


root@localhost [mysql]> select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
+-------------------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','RECORDING');
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0  Imported rules: 0       |
+-----------------------------------------------+
1 row in set (0.00 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

root@localhost [mysql]> CALL sp_set_firewall_mode('fw_user@localhost','PROTECTING');
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.01 sec)

Query OK, 3 rows affected (0.01 sec)

root@localhost [mysql]> select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
| fw_user@localhost | SELECT * FROM `card_info` WHERE `id` = ?                                     |
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                       |
+-------------------+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> 

White List追加前の状態
master_slave

White List対象ユーザーでMasterにアクセスしてWhite ListにSQLステートメントを追加
※ マスター側でRECORDING状態の段階で実行

fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

SLAVE
White ListはReplicationの機能を使い、同期されているがまだメモリーに反映されていない為、
Master側で追加したSQLステートメントはブロックされる。


fw_user@localhost [test]> SELECT id,fname,lname,cnumber FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> 

レプリケーションによるデータ同期
finish

White List Tableには反映されているが、Slave側ではメモリーに無い為まだBlockされる。
master

スレーブ側にて以下のコマンドを実施して、最新のWhite Listをメモリーに反映させる。
※ 権限のある、管理ユーザーで実行してください。
※ ここの処理は、マスターのWhite Listを追加する段階でバッチ処理として追加しておいた方が漏れが無くて良さそうです。


root@localhost [mysql]>  select * from firewall_whitelist;
+-------------------+------------------------------------------------------------------------------+
| USERHOST          | RULE                                                                         |
+-------------------+------------------------------------------------------------------------------+
| fw_user@localhost | SELECT SYSTEM_USER ( )                                                       |
| fw_user@localhost | SELECT `id` , `fname` , `lname` , `cnumber` FROM `card_info` WHERE `id` = ?  |
| fw_user@localhost | SELECT * FROM `card_info` WHERE `id` = ?                                     |
+-------------------+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> SELECT read_firewall_whitelist('fw_user@localhost', 'RECORDING') FROM mysql.firewall_whitelist;
+-----------------------------------------------------------+
| read_firewall_whitelist('fw_user@localhost', 'RECORDING') |
+-----------------------------------------------------------+
| Imported users: 0   Imported rules: 1                     |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT read_firewall_users('fw_user@localhost', 'RECORDING') FROM mysql.firewall_users;
+-------------------------------------------------------+
| read_firewall_users('fw_user@localhost', 'RECORDING') |
+-------------------------------------------------------+
| Imported users: 0   Updated users:  1                 |
+-------------------------------------------------------+
1 row in set (0.01 sec)

root@localhost [mysql]>

メモ:以下のいずれかの方法でもMemoryに反映されます。
※ fw_user@localhostは検証用アカウントです、ご利用時は適宜書き換えて下さい。
■ Pattern1
CALL sp_set_firewall_mode(‘fw_user@localhost’,’RECORDING’);
CALL sp_set_firewall_mode(‘fw_user@localhost’,’PROTECTING’);

■ Pattern2
SELECT read_firewall_users(userhost, mode) from mysql.firewall_users;
SELECT read_firewall_whitelist(userhost, rule) from mysql.firewall_whitelist;

■ Pattern3
SELECT read_firewall_users(‘fw_user@localhost’, ‘RECORDING’) FROM mysql.firewall_users;
SELECT read_firewall_whitelist(‘fw_user@localhost’, ‘RECORDING’) FROM mysql.firewall_whitelist;

上記コマンドでSLAVE側に同期れた定義をメモリーにロードする事でSLAVE側でも実行する事が可能になりました。
※再起動でも反映されますが、殆どのケースで、オンラインで実施するのが現実的かと思います。


fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
ERROR 1045 (28000): Statement was blocked by Firewall
fw_user@localhost [test]> SELECT * FROM card_info WHERE id = 1;
+----+--------+-----------+---------------------+
| id | fname  | lname     | cnumber             |
+----+--------+-----------+---------------------+
|  1 | 佐藤   | さとう    | 1234-5678-9999-0123 |
+----+--------+-----------+---------------------+
1 row in set (0.00 sec)

fw_user@localhost [test]> 

master1

30日間 トライアル
MySQL Enterprise Edition トライアル
※ Publicクラウド環境などを使われている場合は,IDSやIPSを導入する事が難しく、
WAFを導入したりしている方もいらっしゃると思いますが、MySQLをご利用の場合は、
こちらのEnterprise Firewallを実装する事で、SQL Injectionの対策などがコストを抑えて対応する事が可能になります。

参照:
6.3.15.4 MySQL Enterprise Firewall Reference
MySQL Enterprise Firewall


MySQL5.7にてレプリケーションに新たなオプションが加わったので、
ここで軽くご紹介させて頂きます。

今回は、以下の2点のみ検証してみました。
1. マルチスレッドスレーブ(同一スキーマ)
– 全体的なパフォーマンス次第ですが、トランザクションを並列して実行可能。
レプリケーションのパフォーマンスが向上します。
2. 動的フィルタリング
– オンライン処理増えると、メンテンナンス時間の制限が減らせてますね。

検証、MySQL Version


root@localhost [REPLI]> select @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+

以下は、また時間見てご紹介させて頂きます。

■ SEMI-SYNC(準同期)に関しては、Loss-Lessが選択できるようになりました。
AFTER_SYNCかAFTER_COMMITを選択
SET rpl_semi_sync_master_wait_point= [AFTER_SYNC|AFTER_COMMIT]
MySQL5.7.2で準同期が改良され、データ同期時にマスター障害が発生しても
データ損失が発生しない、 Loss-less 準同期レプリケーションに改良されました。
[AFTER_SYNC = Loss Less]

■ ACK待ちするスレーブの数(Default = 1)の変更
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count= N [Default = 1]

■ GTIDのローリングアップグレード
MySQL 5.7.6でGTIDのローリング有効化ができるようになったので、システム全体を一度にシャットダウンしなくてもOK
GTIDのローリングアップグレードに関しては、こちらのサイトが参考になります。
http://yoku0825.blogspot.jp/2015/03/mysql-576gtidok.html

1) My.cnfの変更(マスター&スレーブ)
こちらは、従来通りserver_id,log_bin,gtid-mode, enforce-gtid-consistency,
log-slave-updates,master_info_repository,relay_log_info_repository,relay_log_recoveryなどを設定しています。

2) アカウント作成

root@localhost [(none)]> CREATE USER 'repl_user'@'192.168.56.109' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.56.109';
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> 

3)バックアップとコピー
今回は、検証用のREPLI DBのみ対象なのでREPLIのみダンプしてコピー&リストアしてます。

[root@misc01 admin]# /usr/local/mysql/bin/mysqldump -uroot -p --databases REPLI --single-transaction --triggers --routines --events > GTID_REPLI.sql
Enter password: 
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
[root@misc01 admin]# scp GTID_REPLI.sql admin@192.168.56.109:/home/admin/
The authenticity of host '192.168.56.109 (192.168.56.109)' can't be established.
ECDSA key fingerprint is 34:a2:3f:a8:a5:16:6a:35:1c:d6:9c:5f:30:1e:c8:79.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.109' (ECDSA) to the list of known hosts.
admin@192.168.56.109's password: 
GTID_REPLI.sql                                    100% 2705     2.6KB/s   00:00    
[root@misc01 admin]#

4)スレーブにてデータリストア

5)スレーブの設定
※スレーブをスタートする前に、
ここでは、REPLICATION FILTER,SLAVE_PARALLEL_TYPE,SLAVE_PARALLEL_WORKERS
などを設定変更してスレーブをスタートしています。

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

root@localhost [REPLI]> CHANGE REPLICATION FILTER REPLICATE_DO_DB=(REPLI);
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)

root@localhost [REPLI]> SET GLOBAL SLAVE_PARALLEL_WORKERS=5;
Query OK, 0 rows affected (0.01 sec)

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

root@localhost [REPLI]>

replication_filter

フィルターの設定とマルチスレッドスレーブが反映されているか確認

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.000018
          Read_Master_Log_Pos: 822
               Relay_Log_File: misc02-relay-bin.000002
                Relay_Log_Pos: 1035
        Relay_Master_Log_File: mysql-bin.000018
             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: 822
              Relay_Log_Space: 1243
              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:1-3
            Executed_Gtid_Set: 2dde009f-d4dc-11e4-b437-0800279cea3c:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
1 row in set (0.01 sec)

root@localhost [REPLI]> show variables like '%slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| init_slave                   |                       |
| log_slave_updates            | ON                    |
| log_slow_slave_statements    | OFF                   |
| pseudo_slave_mode            | OFF                   |
| rpl_stop_slave_timeout       | 31536000              |
| slave_allow_batching         | OFF                   |
| slave_checkpoint_group       | 512                   |
| slave_checkpoint_period      | 300                   |
| slave_compressed_protocol    | OFF                   |
| slave_exec_mode              | STRICT                |
| slave_load_tmpdir            | /tmp                  |
| slave_max_allowed_packet     | 1073741824            |
| slave_net_timeout            | 60                    |
| slave_parallel_type          | LOGICAL_CLOCK         |
| slave_parallel_workers       | 5                     |
| slave_pending_jobs_size_max  | 16777216              |
| slave_preserve_commit_order  | OFF                   |
| slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN |
| slave_skip_errors            | OFF                   |
| slave_sql_verify_checksum    | ON                    |
| slave_transaction_retries    | 10                    |
| slave_type_conversions       |                       |
| sql_slave_skip_counter       | 0                     |
+------------------------------+-----------------------+
23 rows in set (0.01 sec)

root@localhost [REPLI]> 

※ 動的に設定した、replicate-do-db, slave_parallel_type, slave_parallel_workersは,
Instanceの再起動で消えてしまうので必要に応じてオプションファイルに追加設定する必要があります。

root@localhost [REPLI]> system cat /etc/my.cnf | grep replicate-do-db
replicate-do-db=REPLI
root@localhost [REPLI]> 

マルチスレッドスレーブ
slave_para
フィルター
do_db

■INSERTの確認
マスター

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

root@localhost [REPLI]>  select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
| 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 |
+-------+-------------------------+----------------------------+
5 rows in set (0.01 sec)

root@localhost [REPLI]> INSERT INTO T_REPLI01(n_time,s_time) values(now(3),sysdate(6));
Query OK, 1 row affected (0.00 sec)

root@localhost [REPLI]>  select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 |
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 

Slave

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

root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
| 70010 | 2015-04-27 13:42:41.794 | 2015-04-27 13:42:41.794726 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> select * from T_REPLI01 order by ID desc limit 0,5;
+-------+-------------------------+----------------------------+
| id    | n_time                  | s_time                     |
+-------+-------------------------+----------------------------+
| 70015 | 2015-04-27 13:47:42.973 | 2015-04-27 13:47:42.973535 |
| 70014 | 2015-04-27 13:42:41.800 | 2015-04-27 13:42:41.800603 |
| 70013 | 2015-04-27 13:42:41.799 | 2015-04-27 13:42:41.799552 |
| 70012 | 2015-04-27 13:42:41.797 | 2015-04-27 13:42:41.797451 |
| 70011 | 2015-04-27 13:42:41.795 | 2015-04-27 13:42:41.795360 |
+-------+-------------------------+----------------------------+
5 rows in set (0.00 sec)

root@localhost [REPLI]> 

master_Slave

■Processlistでスレッドの確認

root@localhost [REPLI]> show processlist;
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db    | Command | Time | State                                                  | Info             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
|  3 | root        | localhost | REPLI | Query   |    0 | starting                                               | show processlist |
|  9 | system user |           | NULL  | Connect | 3775 | Waiting for master to send event                       | NULL             |
| 10 | system user |           | NULL  | Connect |  115 | Slave has read all relay log; waiting for more updates | NULL             |
| 11 | system user |           | NULL  | Connect |  115 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL  | Connect |  416 | Waiting for an event from Coordinator                  | NULL             |
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
8 rows in set (0.00 sec)

root@localhost [REPLI]> select thd_id,conn_id,user,db,command,current_statement from sys.processlist where user = 'sql/slave_worker';
+--------+---------+------------------+------+---------+-------------------+
| thd_id | conn_id | user             | db   | command | current_statement |
+--------+---------+------------------+------+---------+-------------------+
|     34 |      12 | sql/slave_worker | NULL | Connect | NULL              |
|     35 |      13 | sql/slave_worker | NULL | Connect | NULL              |
|     36 |      14 | sql/slave_worker | NULL | Connect | NULL              |
|     37 |      15 | sql/slave_worker | NULL | Connect | NULL              |
|     33 |      11 | sql/slave_worker | NULL | Connect | NULL              |
+--------+---------+------------------+------+---------+-------------------+
5 rows in set (0.09 sec)

root@localhost [REPLI]> 

Show_full_Process_List

参考:
13.4.2.2 CHANGE REPLICATION FILTER Syntax

MySQLのセミナーなどで、デモも含めてご紹介させて頂いているので参加して頂くのも良いかもしれません。
セミナーサイト
http://events.oracle.com/search/search?start=1&pageHitCount=10&group=Events&keyword=japan=


MYSQLを利用してsemi-synchronous-replicationしていても人的ミスでデータが無くなってしまう事もあるので、
バックアップは必ず定期的に計画的な運用の一環として行っておく。

レプリケーションしている場合は、負荷の高いマスターDBでダンプを取得しないでも運用・バックアップ専用スレーブで
バックアップ取得すれば利用者にも迷惑かけずにデータ保護する事が出来る。

——————————————
1:スレーブDBにてダンプ取得 2:マスターに取得したダンプをコピー 3:マスターにて意図的にテーブルTruncate
4:スレーブのデータも消えている事を確認 5:マスターDBにデータをリストア 6:スレーブにてDBが復旧している事を確認。
——————————————

スレーブで取得したデータをマスターでもリストアするので一応”master-data=2”を付けてバックアップ
master-data=2オプションはバックアップ時点のバイナリーログの位置情報をダンプファイル
に記録するオプションで、バイナリーログによるロールフォワードに利用されます。

[root@HOME002 mysql]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > MYSQL_dump20130111.sql -u root -p
Enter password:
[root@HOME002 mysql]#

こちらが”master-data=1”でダンプした場合。” CHANGE MASTER TO MASTER_LOG_FILE=, MASTER_LOG_POS=”がコメントアウトされて無い。

[root@HOME002 mysql]# mysqldump --single-transaction --flush-logs --master-data=1 --databases test > MYSQL_dump20130111.sql -u root -p
Enter password:
[root@HOME002 mysql]#

diff

ダンプデータをスレーブからマスターに転送

[root@HOME002 mysql]# scp MYSQL_dump20130111.sql root@home001:/home/mysql/
root@home001's password:
MYSQL_dump20130111.sql           100% 3211     3.1KB/s   00:
[root@HOME002 mysql]# 

マスターDBにてデータを削除(仮ミスオペ)し、リストア


mysql> truncate table TABLE001;
Query OK, 0 rows affected (0.40 sec)

mysql> select * from TABLE001;
Empty set (0.00 sec)

mysql> exit
Bye
[root@HOME001 mysql]# mysql -u root -p test < MYSQL_dump20130111.sql; Enter password: [root@HOME001 mysql]# [/SHELL] マスターとスレーブにてデータが復旧されている事を確認
restore


MYSQL Semi-Sync Replicationについて

Semi-Synchronous Replicationは、マスターでコミットされた処理がスレーブにも送信されて
スレーブにデータが送信されて受け取った事を確認してからAckをマスターに返して初めて、
処理が確定されます。なので以下の1の処理が完了しているので、マスターDBが破損してもデータの
損失を最小限に留める事が出来ます。
——————————————
 1. I/Oスレッド(スレーブバイナリーログ受信)
 2. SQLスレッド(スレーブにてバイナリーログを読み込んで実行)
——————————————
但し、Ackが発生するのはマスターとスレーブのログに書き込んでからなので
当然CPU、メモリー、ディスク、ネットワークが遅いとユーザーに返すレスポンスは極端に遅くなります。
特にFusion-IOやSSDを用いてディスクの遅延が無い環境ではネットワーク遅延が原因で、
DB処理が遅くなりユーザーへのレスポンスも極端に遅くなるので実装する時は十分な確認が必要です。

以前、オラクルを稼動させた外部ディスクにてのディスクのSync機能を利用して
遠隔地とDark Fiberを利用してディスクのMirrorをした時は、
オラクル処理の遅延を懸念して、データ、REDOログのディスクはA-SYNCにして非同期で同期して、
Archiveログで利用しているディスクだけSync設定にしてデータ保護していました。
障害発生時のシュミレーションではテーブルスペースに前日同期したテーブルスペースに、
同期してあるArchiveログを適用したら問題無く起動する事が出来ました。
RedoやControlファイルが壊れている場合は再作成で対応。

マスターDBにプラグインをインストール

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.35 sec)

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql>

スレーブにプラグインのインストール

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
1 row in set (0.00 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

install_plugin

設定を有効にしてみる。
オンラインで実行可能なので、SETコマンドで有効にした後に動作確認し、
次回再起動時に有効にする為にmy.cnfに設定を追加。

スレーブ側で念の為、既存のレプリケーションを停止しておいた。
既存のレプリケーション設定があったので念の為。

mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql>

マスター側でsemi_syncの設定、タイムアウト値を変更して有効にする。
合わせてmy.cnf側も変更しておく。(次の再起動に備えて)

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_master_timeout=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 5     |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

mysql>

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000029 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

マスター側で次回回帰同時の為に、semi_syncの設定を入れておく。

[root@HOME001 ~]# vi /etc/my.cnf
[root@HOME001 ~]# cat /etc/my.cnf | grep rpl_semi
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=5
[root@HOME001 ~]#

スレーブ側でSETコマンドで設定を有効にしてmy.cnfにも追加しておく。

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>  show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> set GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql>

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000029
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-bin.000035
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           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: 107
              Relay_Log_Space: 549
              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
1 row in set (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>

次回再起動時の為にスレーブのmy.cnfを変更

[root@HOME002 ~]# vi /etc/my.cnf
[root@HOME002 ~]# cat /etc/my.cnf | grep rpl
rpl_semi_sync_slave_enabled=1
[root@HOME002 ~]#

基本動作確認(データ同期)
同期は問題無いが、シンプルなInsertに若干時間がかかっているような気がする。
マスター、スレーブのI/OスレッドAck待ちの影響?
semi-sync

レプリケーションポジション確認
master

マスター書き込みとRelayログの更新時間確認
マスターDBにinsertと同時にスレーブ側のログ時間も更新されている事を確認。
log_time

レプリケーション障害発生時の留意点:
もしレプリケーションを構築していて、スレーブが破損してしまい
マスターからデータを持ってきてレプリケーションを再構築する場合には以下留意。
①マスターからDBコピー
②スレーブのmy.cnfにて(rpl_semi_sync_slave_enabled)をコメントアウト。
③①で展開したデータフォルダーを利用してMYSQLの再開
④以下のコマンドでスレーブ用に設定を変更する。

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.12 sec)
mysql> uninstall PLUGIN rpl_semi_sync_master;
Query OK, 0 rows affected (0.02 sec)

⑤CHANGE MASTER TOコマンドでスレーブを設定。
⑥②でコメントアウトした箇所のコメントを外す。
⑦MYSQLを再起動してshow slave status \Gで確認。

その他参考:遅延レプリケーション(MASTER_DELAY)
5.6からはMASTER_DELAYでレプリケーションを意図的に遅延させる事が出来るそうなので、
オペミスしたら直ぐに最新のデータを戻す為の用途などに利用出来そうです。

遅延が無い場合は、以下の遅延状況は0秒になっていて良いのですがもしオペミスでデータ破損してしまった場合は、
即時ミスも反映されてしまうので、バックアップ専用スレーブDBなどには遅延を入れて運用すると良いかもしれません。

通常

[root@HOME002 ~]# mysql -u root -ppassword -e "show slave status\G" | grep "Seconds_Behind_Master"
        Seconds_Behind_Master: 0
[root@HOME002 ~]#

設定方法

CHANGE MASTER TO MASTER_DELAY = N;

運用ケース

To protect against user mistakes on the master. A DBA can roll back a delayed slave to the time just before the disaster.

To test how the system behaves when there is a lag. For example, in an application, a lag might be caused by a heavy load on the slave. However, it can be difficult to generate this load level. Delayed replication can simulate the lag without having to simulate the load. It can also be used to debug conditions related to a lagging slave.

To inspect what the database looked like long ago, without having to reload a backup. For example, if the delay is one week and the DBA needs to see what the database looked like before the last few days’ worth of development, the delayed slave can be inspected.

16.3.9. Delayed Replication
MySQL 5.6.0-m4登場

参考

Semi-Synchronous Replication用プラグインをインストールする。
最強のMySQL HA化手法 – Semi-Synchronous Replication
MySQL5.5 Semisynchronous Replicationを試してみる ~構築編
MySQL 5.5 GA版が出たのでSemisynchronous Replicationを使ってみた
MySQL 準同期レプリケーション(Semisynchronous Replication)設定メモ
MySQLにおけるレプリケーション遅延の傾向と対策
http://www.day32.com/MySQL/
UNINSTALL PLUGIN 構文