MySQL Group Replicationは、グループで一つのIDを持つ為、通常のシングルインスタンスと同じようにレプリケーションを組む事が出来ます。4月のInnoDB Clusterリリース以降、MySQLを利用されているお客様から、幾つか質問を受けていたので念の為に挙動を再確認。

環境
複数サーバーを準備出来なかったので,シングサーバーにポートを変更して、2グループ(6サーバー)で先ずはGROUP REPLICATIONを準備。

mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

グループレプリケーション間のレプリケーションの設定・開始
マスター側のGroup Replicationにスレーブ接続用のアカウントを作成し、スレーブにデータをコピーしたので、レプリケーションをスレーブ側のグループレプリケーションで開始。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=3310,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc01';
Query OK, 0 rows affected (3.11 sec)

MySQL Enterprise Monitorで確認

マスター側のGROUP REPLICATIONの状態

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | 199e19cb-5326-11e7-947d-080027d65c57 | replications |        3330 | ONLINE       |
| group_replication_applier | d9e318f0-5325-11e7-8762-080027d65c57 | replications |        3310 | ONLINE       |
| group_replication_applier | fab196ae-5325-11e7-8edb-080027d65c57 | replications |        3320 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> 

スレーブ側のGROUP REPLICATIONの状態


mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
| group_replication_applier | bc653b5a-3b8b-11e7-94cd-080027d65c57 | replications |       63301 | ONLINE       |
| group_replication_applier | c68819f0-3b8b-11e7-958b-080027d65c57 | replications |       63302 | ONLINE       |
| group_replication_applier | d0a3d2c8-3b8b-11e7-97ef-080027d65c57 | replications |       63303 | ONLINE       |
+---------------------------+--------------------------------------+--------------+-------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT LAST_CONFLICT_FREE_TRANSACTION  FROM performance_schema.replication_group_member_stats;
+-----------------------------------------+
| LAST_CONFLICT_FREE_TRANSACTION          |
+-----------------------------------------+
| 33cf36db-5326-11e7-8b16-080027d65c57:15 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS FOR CHANNEL 'remote-dc01'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl_user
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: replications-bin.000003
          Read_Master_Log_Pos: 15217
               Relay_Log_File: replications-relay-bin-remote@002ddc01.000003
                Relay_Log_Pos: 676
        Relay_Master_Log_File: replications-bin.000003
             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: 15217
              Relay_Log_Space: 16007
              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: 1097457189
                  Master_UUID: d9e318f0-5325-11e7-8762-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: 33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
            Executed_Gtid_Set: 00000000-1111-2222-3333-123456789abc:1-6,
33cf36db-5326-11e7-8b16-080027d65c57:1-15,
d9e318f0-5325-11e7-8762-080027d65c57:1-11
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: remote-dc01
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> 

マスター側でテーブルとデータを作成してレプリケーションの確認


mysql> CREATE TABLE `T_GR` (
    ->   `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `string1` char(1) DEFAULT NULL,
    ->   `string2` char(1) DEFAULT NULL,
    ->   `string1_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string1`))) VIRTUAL,
    ->   `string2_w_string` char(4) GENERATED ALWAYS AS (hex(weight_string(`string2`))) VIRTUAL,
    ->   `compare` char(1) GENERATED ALWAYS AS ((`string1` = `string2`)) VIRTUAL,
    ->   PRIMARY KEY (`pid`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into T_GR(string1,string2) values('A','a');
Query OK, 1 row affected (0.19 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.00 sec)

mysql> 

スレーブ側でデータの確認

mysql> show tables;
Empty set (0.00 sec)

mysql> show tables;
+-----------------------+
| Tables_in_GR_GR_REPLI |
+-----------------------+
| T_GR                  |
+-----------------------+
1 row in set (0.01 sec)

mysql> select * from T_GR;
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
1 row in set (0.30 sec)

mysql> 


スレーブ側の全てのグループメンバーでデータがレプリケートされている事を確認

-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql1/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql2/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ /usr/local/mysql/bin/mysql -uroot -proot  -S/home/mysql/gr2/mysql3/my.sock -e "select * from GR_GR_REPLI.T_GR"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----+---------+---------+------------------+------------------+---------+
| pid | string1 | string2 | string1_w_string | string2_w_string | compare |
+-----+---------+---------+------------------+------------------+---------+
|   1 | A       | a       | 0041             | 0041             | 1       |
+-----+---------+---------+------------------+------------------+---------+
-bash-4.2$ 

グループレプリケーション稼働中はRESET MASTER出来なかったり、パラメータ周りやレプリケーションの設定でいくつか考慮が必要なので、慣れるまでは十分に手順の確認と検証して下さい。
若しくは、ミッションクリティカルな環境では、設計の段階でサポートを受けると安心かと思います。 
サポート: https://www.mysql.com/jp/support/

参考までに、Defaultでシングルマスターモードでもauto_incrementの値が7になっているため、group_replication_auto_increment_increment=1等で設定を変更すると良いかと思います。
変更しない場合は、以下のように値がIncrementされていきます。

補足:
双方向にレプリケーションも可能ですが、やはり事前に確認が必要ですので事前のPOCをお願いします。
以下の様に、逆方向にもレプリケーションを張る事は可能です。但し、グループレプリケーションとグループレプリケーションの間は非同期でレプリケーションを張っています。その為、両方で同じデータを同時に変更しないようにする必要があります。またInnoDB Clusterの場合は、グループレプリケーションだけで構成した以外にも考慮が必要です。


mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=63301,
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='password',
    -> MASTER_AUTO_POSITION=1 FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> START SLAVE FOR CHANNEL 'remote-dc02';
Query OK, 0 rows affected (0.19 sec)



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


先日、ご紹介させて頂いた、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のレプリケーション構成において、スレーブの処理を並列にして処理を
高速化する事が可能。但し、5.6までは並列化の単位はスキーマ(データベース)
単位になります。5.7からは、同じスキーマ単位でも並列処理が可能になる予定だそうです。
OS側に余裕があれば、処理を高速化し時間を短縮化する事で他の業務も出来るようになるので、
MySQLのレプリケーションがスレッド不足で遅延している環境では、
有益な選択肢になるかと思います。もちろん、自分でツール開発やシェルを工夫して
対応する事も可能ですが、MySQL UtilityやMySQL Enterprise Monitorなどで管理していくと、
更に管理効率が上がり、工数削減とサービス開発等の業務に力を注ぐ事が出来るようになりますね。

slave_parallel_workers MySQL5.6.3~
Sets the number of slave worker threads for executing replication events (transactions) in parallel.
Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.

http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#sysvar_slave_parallel_workers

Default設定(slave_parallel_workers=0)

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

root@CentOS03 [(none)]> system cat /etc/my.cnf | grep slave_parallel_workers
# slave_parallel_workers       = 0 ~ 1024   

root@CentOS03 [(none)]> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
 
root@CentOS03 [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  421 | Waiting for master to send event                                            | NULL             |
|  2 | system user |           | NULL | Connect |  412 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  3 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

root@CentOS03 [(none)]>

設定変更(slave_parallel_workers=3)

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

root@CentOS02 [(none)]> system cat /etc/my.cnf | grep slave_parallel_workers
slave_parallel_workers          = 3                                  #Can be 0 ~ 1024

root@CentOS02 [(none)]> show variables like 'slave_parallel_workers';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 3     |
+------------------------+-------+
1 row in set (0.00 sec)

root@CentOS02 [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |  120 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  2 | system user |           | NULL | Connect |  120 | Waiting for master to send event                                            | NULL             |
|  3 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  4 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  5 | system user |           | NULL | Connect |  120 | Waiting for an event from Coordinator                                       | NULL             |
|  6 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
6 rows in set (0.00 sec)

root@CentOS02 [(none)]> 


マスター側へそれぞれのスレーブから接続に来ている事が確認出来る。
スレーブ側のスレッドは設定によって変化している事も確認出来る。

slave_parallel_workers

その他、5.6におけるReplicationに関しての資料
http://www.congresoeducacion.unach.mx/sistema_congeducacion/ponencias/replicacion.pdf


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

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

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

# master_info_repository        = TABLE
# relay_log_info_repository     = TABLE

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

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

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

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

root@localhost [test]> 

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

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

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

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

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

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

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

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

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

Database changed

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

root@localhost [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: CentOS03-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 151
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-204
                Auto_Position: 1
1 row in set (0.00 sec)

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

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

root@localhost [mysql]> 

Initial

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

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

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

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

root@localhost [test]>

既存Slave

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

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

root@localhost [test]> 

新規追加Slave

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

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

root@localhost [test]> 

マスターにてデータ変更

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

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

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

root@localhost [test]>

既存Slave

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

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

root@localhost [test]> 

新規追加Slave

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

root@localhost [test]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.101
                  Master_User: GTID_USER
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000256
          Read_Master_Log_Pos: 1536
               Relay_Log_File: CentOS03-relay-bin.000002
                Relay_Log_Pos: 1706
        Relay_Master_Log_File: mysql-bin.000256
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1536
              Relay_Log_Space: 1913
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 3edaa0b8-3e39-11e4-9df1-080027f5bf08
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:205-209
            Executed_Gtid_Set: 3edaa0b8-3e39-11e4-9df1-080027f5bf08:1-209
                Auto_Position: 1
1 row in set (0.00 sec)

root@localhost [test]> 

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

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

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


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

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

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

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

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


It takes long time since upgrade mysql last time,
so this time upgrade MySql from 5.6.14 to 5.6.16.

Changes in MySQL 5.6.16 (2014-01-31)
There is many InnoDB and Replication related bug fixes.

Download latest version of MySQL with wget.

 [root@HOME002 src]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
--11:58:56--  http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
dev.mysql.com をDNSに問いあわせています... 137.254.60.11
dev.mysql.com|137.254.60.11|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz [続く]
--11:58:57--  http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-i686.tar.gz
cdn.mysql.com をDNSに問いあわせています... 23.51.56.96
cdn.mysql.com|23.51.56.96|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 296884387 (283M) [application/x-tar-gz]
Saving to: `mysql-5.6.16-linux-glibc2.5-i686.tar.gz'
100%[=======================================================================>] 296,884,387 54.4K/s   in 96m 1s
13:35:00 (50.3 KB/s) - `mysql-5.6.16-linux-glibc2.5-i686.tar.gz' を保存しました [296884387/296884387]

[root@HOME002 src]#

Extract and move to self defined location.

[root@HOME002 src]# tar zxvf mysql-5.6.16-linux-glibc2.5-i686.tar.gz
mysql-5.6.16-linux-glibc2.5-i686/bin/innochecksum
mysql-5.6.16-linux-glibc2.5-i686/bin/myisam_ftdump
mysql-5.6.16-linux-glibc2.5-i686/bin/mysql_waitpid
mysql-5.6.16-linux-glibc2.5-i686/bin/mysqld_safe
mysql-5.6.16-linux-glibc2.5-i686/bin/perror
mysql-5.6.16-linux-glibc2.5-i686/bin/resolveip
mysql-5.6.16-linux-glibc2.5-i686/bin/msql2mysql

省略...............

mysql-5.6.16-linux-glibc2.5-i686/support-files/my-default.cnf
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysql.server
mysql-5.6.16-linux-glibc2.5-i686/support-files/binary-configure
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysqld_multi.server
mysql-5.6.16-linux-glibc2.5-i686/support-files/magic
mysql-5.6.16-linux-glibc2.5-i686/support-files/mysql-log-rotate
[root@HOME002 src]#

[root@HOME002 src]# ls -l
合計 583004
-rw-r--r--  1 root root 299497357  9月 11 11:14 mysql-5.6.14-linux-glibc2.5-i686.tar.gz
drwxr-xr-x 13 root root      4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
-rw-r--r--  1 root root 296884387  1月 15 19:59 mysql-5.6.16-linux-glibc2.5-i686.tar.gz
[root@HOME002 src]# mv  mysql-5.6.16-linux-glibc2.5-i686 /usr/local/
[root@HOME002 src]# cd /usr/local/
[root@HOME002 local]# ls -l
合計 124
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33 11月  9 10:12 mysql -> mysql-5.6.14-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22  2013 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096  3月  1 13:40 src
[root@HOME002 local]#

Replace Symbolic link and data between old and new version.

[root@HOME002 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                     [  OK  ]
[root@HOME002 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME002 local]#

[root@HOME002 local]# ln -s mysql-5.6.16-linux-glibc2.5-i686/ mysql
[root@HOME002 local]# ls -l
合計 124
drwxr-xr-x  2 root  root  4096  1月  8  2013 bin
drwxr-xr-x  2 root  root  4096  3月 30  2007 etc
drwxr-xr-x  2 root  root  4096  3月 30  2007 games
drwxr-xr-x  2 root  root  4096  1月  7  2013 include
drwxr-xr-x  3 root  root  4096  1月  7  2013 lib
drwxr-xr-x  2 root  root  4096  3月 30  2007 libexec
drwxr-xr-x  3 root  root  4096  1月  7  2013 man
lrwxrwxrwx  1 root  root    33  3月  1 13:43 mysql -> mysql-5.6.16-linux-glibc2.5-i686/
drwxr-xr-x 13 mysql mysql 4096  4月 28  2013 mysql-5.6.11-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  6月 22  2013 mysql-5.6.12-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096  9月 14 07:48 mysql-5.6.13-linux-glibc2.5-i686
drwxr-xr-x 13 mysql mysql 4096 11月  9 10:06 mysql-5.6.14-linux-glibc2.5-i686
drwxr-xr-x 13 root  root  4096  3月  1 13:39 mysql-5.6.16-linux-glibc2.5-i686
drwxr-xr-x  2 root  root  4096  3月 30  2007 sbin
drwxr-xr-x  5 root  root  4096  1月  7  2013 share
drwxr-xr-x  2 root  root  4096  3月  1 13:40 src
[root@HOME002 local]#

[root@HOME002 mysql-5.6.14-linux-glibc2.5-i686]# cp -rp data/* /usr/local/mysql/data/
cp: `/usr/local/mysql/data/test/db.opt' を上書きしてもよろしいですか(yes/no)? y
[root@HOME002 mysql-5.6.14-linux-glibc2.5-i686]#

[root@HOME002 local]# chown -R mysql:mysql mysql-5.6.16-linux-glibc2.5-i686/
[root@HOME002 local]# chmod -R 755 mysql-5.6.16-linux-glibc2.5-i686/
[root@HOME002 local]#

[root@HOME002 local]# /etc/init.d/mysql start
Starting MySQL.......                                      [  OK  ]
[root@HOME002 local]#

[root@HOME002 local]#  /usr/local/mysql/bin/mysql --version
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.16, for linux-glibc2.5 (i686) using  EditLine wrapper
[root@HOME002 local]#

Update system DB in MySQL.


[root@HOME002 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
test.TABLE001                                      OK
test.timestamp_chk                                 OK
test.timestamp_chk2                                OK
OK
[root@HOME002 bin]#


[root@HOME002 bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.16-log MySQL Community Server (GPL)

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

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

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

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

mysql>

Since I didn`t use this environment for while, so I need to fix replication.
Before fix replication confirm current master log position.


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

Start to fix replication at slave side.

mysql> use test
Database changed
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.000034
          Read_Master_Log_Pos: 433
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000034
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              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: 433
              Relay_Log_Space: 120
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.16-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 140301 13:47:09
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

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

mysql> CHANGE MASTER TO MASTER_HOST = 'home001', master_port=3306, MASTER_LOG_FILE = 'mysql-bin.000035', MASTER_LOG_POS = 120;
Query OK, 0 rows affected (0.16 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000035
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000035
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 120
              Relay_Log_Space: 450
              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: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.16-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

Insert data in Master DB.

mysql> use test;
Database changed
mysql> insert into TABLE001(title,comment) values('after-upgrade','Master MYSQL is upgraded to 5.6.16.');
Query OK, 1 row affected (0.38 sec)

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 25 | after-upgrade | Master MYSQL is upgraded to 5.6.16. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Confirm data slave side and finish replication recovery.

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 24 | after-upgrade | Master MYSQL is upgraded to 5.6.14. |
+----+---------------+-------------------------------------+
1 row in set (0.08 sec)

------- Insert data at master DB.

mysql> select * from TABLE001 order by id desc limit 0,1;
+----+---------------+-------------------------------------+
| id | title         | comment                             |
+----+---------------+-------------------------------------+
| 25 | after-upgrade | Master MYSQL is upgraded to 5.6.16. |
+----+---------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>


先日、スレーブをアップグレードしてみて特に問題無さそうでしたので、
マスターDBをアップグレードしてみました。

アップグレード前の状況
既存では5.5.29をマスターDBで利用している。


[root@HOME001 local]# ls -l
合計 48
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

MYSQL5.6をダウンロードして展開。


[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 mysql mysql   27 12月 22 14:42 2012 mysql -> mysql-5.5.29-linux2.6-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 root  root  4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

既存DBを停止してアップグレード作業開始

1) STOP MYSQL
2) Remove Symbolic Link
3) Copy Data from Old MySQL to New SQL
4) Set Authentication
6) Recreate Symbolic Link with MySQL5.6


[root@HOME001 local]# /etc/init.d/mysql stop
Shutting down MySQL...                                    [  OK  ]
[root@HOME001 local]# 

[root@HOME001 local]# rm mysql
rm: remove シンボリックリンク `mysql'? y
[root@HOME001 local]#

[root@HOME001 local]# cp -rp /usr/local/mysql-5.5.29-linux2.6-i686/data/* /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/               
[root@HOME001 local]#

[root@HOME001 local]# chown -R mysql:mysql mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]# chmod -R 755 mysql-5.6.10-linux-glibc2.5-i686/
[root@HOME001 local]#


[root@HOME001 local]# ln -s mysql-5.6.10-linux-glibc2.5-i686/ mysql
[root@HOME001 local]# ls -l
合計 52
drwxr-xr-x.  2 root  root  4096  1月 31 14:18 2013 bin
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 etc
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 games
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 include
drwxr-xr-x.  3 root  root  4096  1月 31 14:12 2013 lib
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 libexec
drwxr-xr-x.  3 root  root  4096 12月 30 16:07 2012 man
lrwxrwxrwx.  1 root  root    33  3月 24 13:18 2013 mysql -> mysql-5.6.10-linux-glibc2.5-i686/
drwxrwxr-x. 13 mysql mysql 4096  1月 24 15:37 2013 mysql-5.5.29-linux2.6-i686
drwxr-xr-x. 13 mysql mysql 4096  3月 24 13:09 2013 mysql-5.6.10-linux-glibc2.5-i686
drwxr-xr-x.  4 mysql mysql 4096  1月 18 05:26 2013 percona-xtrabackup-2.0.5
drwxr-xr-x.  2 root  root  4096  9月 23 20:47 2011 sbin
drwxr-xr-x.  7 root  root  4096  1月  7 14:59 2013 share
drwxr-xr-x.  2 root  root  4096  3月 24 13:10 2013 src
drwxrwxrwt.  2 root  root    40  3月 24 12:22 2013 tmp
lrwxrwxrwx.  1 mysql mysql   24  1月 24 14:09 2013 xtrabackup -> percona-xtrabackup-2.0.5
[root@HOME001 local]#

準備が出来たので、MYSQLを起動してシステムDBをアップグレードします。
この段階では、旧バージョンで利用していたmy.confは編集していません。


[root@HOME001 bin]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]# ./mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
test.TABLE001                                      OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK
[root@HOME001 bin]#

アップグレードは終わりましたが、念の為再起動してログを確認。
以下のオプションだけ後日確認。

explicit_defaults_for_timestamp
innodb_additional_mem_pool_size
innodb_use_sys_malloc

[root@HOME001 bin]# /etc/init.d/mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]
[root@HOME001 bin]#




130324 13:21:15 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 13:21:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 13:21:15 5061 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 13:21:15 b789e8e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 13:21:15 5061 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 13:21:15 5061 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 13:21:15 5061 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 13:21:15 5061 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 13:21:15 5061 [Note] InnoDB: Using Linux native AIO
2013-03-24 13:21:15 5061 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 13:21:15 5061 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 13:21:15 5061 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 13:21:16 5061 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 13:21:16 5061 [Note] InnoDB: Waiting for purge to start
2013-03-24 13:21:16 5061 [Note] InnoDB: 1.2.10 started; log sequence number 1733578
2013-03-24 13:21:16 5061 [Note] Semi-sync replication initialized for transactions.
2013-03-24 13:21:16 5061 [Note] Semi-sync replication enabled on the master.
2013-03-24 13:21:16 5061 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 13:21:16 5061 [Note] IPv6 is available.
2013-03-24 13:21:16 5061 [Note]   - '::' resolves to '::';
2013-03-24 13:21:16 5061 [Note] Server socket created on IP: '::'.
2013-03-24 13:21:16 5061 [Note] Event Scheduler: Loaded 0 events
2013-03-24 13:21:16 5061 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
[root@HOME001 data]#


バージョンアップ結果確認


[root@HOME001 data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.10-log MySQL Community Server (GPL)

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

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

mysql>

MYSQL5610-master

その他レプリケーションの状態など。

マスターアップグレード前にSlaveを念の為、停止しておきました。
マスターアップグレード中なので書き込みも発生しないし、
スレーブは参照だけ出来ていれば良いかと。
但し、コマースサイトなどで書き込み処理も停止出来ない場合は、
他のスレーブをマスターにアップグレードする方法でも良いかと。
但し、スペックはますてーとして十分なシステムを選択する必要あり。


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

アップグレード後にスレーブ再開して問題無い事を確認済み。

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

mysql>


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: home001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 120
              Relay_Log_Space: 609
              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: 2516bf0a-943a-11e2-8417-00123fd6c9fa
             Master_Info_File: /usr/local/mysql-5.6.10-linux-glibc2.5-i686/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>
 

マスターでデータをINSERTして確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>


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

mysql>


mysql> insert into TABLE001(title,comment) values('after upgrade master','Upgrade to  5.6.10-log and Check Replication');
Query OK, 1 row affected (0.45 sec)

mysql>

スレーブ側にてデータ確認


mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

mysql>

mysql> select * from TABLE001;
+----+----------------------+----------------------------------------------+
| id | title                | comment                                      |
+----+----------------------+----------------------------------------------+
|  1 | Before upgrade       | MYSQL 5.6 - 0                                |
|  2 | Before upgrade       | MYSQL 5.6 - 1                                |
|  3 | Before upgrade       | MYSQL 5.6 - 2                                |
|  4 | After Slave upgrade  | MYSQL 5.6 - 0                                |
|  5 | After Slave upgrade  | MYSQL 5.6 - 1                                |
|  6 | reset replication    | Execute Change Master                        |
|  7 | reset replication    | Execute Change Master2                       |
|  8 | After Change Master  | Log is deleted by purge in my.conf           |
|  9 | after upgrade master | Upgrade to  5.6.10-log and Check Replication |
+----+----------------------+----------------------------------------------+
9 rows in set (0.23 sec)

mysql>

Slaveも再起動してみてログを確認


[root@HOME002 data]# /etc/init.d/mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL...                                          [  OK  ]
[root@HOME002 data]#


[root@HOME002 data]# cat HOME002.localdomain.err
130324 14:22:23 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
2013-03-24 14:22:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-03-24 14:22:24 13058 [Note] Plugin 'FEDERATED' is disabled.
2013-03-24 14:22:24 b7f596d0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-03-24 14:22:24 13058 [Note] InnoDB: The InnoDB memory heap is disabled
2013-03-24 14:22:24 13058 [Note] InnoDB: Mutexes and rw_locks use InnoDB's own implementation
2013-03-24 14:22:24 13058 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-03-24 14:22:24 13058 [Note] InnoDB: CPU does not support crc32 instructions
2013-03-24 14:22:24 13058 [Note] InnoDB: Using Linux native AIO
2013-03-24 14:22:24 13058 [Note] InnoDB: Initializing buffer pool, size = 32.0M
2013-03-24 14:22:24 13058 [Note] InnoDB: Completed initialization of buffer pool
2013-03-24 14:22:24 13058 [Note] InnoDB: Highest supported file format is Barracuda.
2013-03-24 14:22:25 13058 [Note] InnoDB: 128 rollback segment(s) are active.
2013-03-24 14:22:25 13058 [Note] InnoDB: Waiting for purge to start
2013-03-24 14:22:25 13058 [Note] InnoDB: 1.2.10 started; log sequence number 1734793
2013-03-24 14:22:26 13058 [Note] Server hostname (bind-address): '*'; port: 3306
2013-03-24 14:22:26 13058 [Note] IPv6 is available.
2013-03-24 14:22:26 13058 [Note]   - '::' resolves to '::';
2013-03-24 14:22:26 13058 [Note] Server socket created on IP: '::'.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: Start semi-sync replication to master 'slave_user@home001:3306' in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2013-03-24 14:22:26 13058 [Note] Slave I/O thread: connected to master 'slave_user@home001:3306',replication started in log 'mysql-bin.000020' at position 449
2013-03-24 14:22:26 13058 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2013-03-24 14:22:27 13058 [Note] Event Scheduler: Loaded 0 events
2013-03-24 14:22:27 13058 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.10-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2013-03-24 14:22:27 13058 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000020' at position 449, relay log '/usr/local/mysql/data/relay-bin.000007' position: 612
[root@HOME002 data]#

参考サイト
2.11.1.1. Upgrading from MySQL 5.5 to 5.6


MYSQLレプリケーションレビュー
 MYSQLでレプリケーション設定してある環境にてスレーブを追加する方法の再確認。

基本設定レビュー
MYSQL Replication Review
メンテナンス時の動作・挙動レビュー
MYSQL Replicationスレーブ停止と再開

環境
——————————
├─マスターDB(HOME001)
├─スレーブDB(HOME002)
└─スレーブDB (colinux) 新規追加

[root@HOME002 ~]# mysql -e "select @@version" -u root -p
Enter password:
+------------+
| @@version  |
+------------+
| 5.5.29-log |
+------------+
[root@HOME002 ~]#

スレーブDB(colinux)にインストールされていたMYSQLは古いバージョンだったので、
削除してHOME002からMYSQLフォルダー全部(MYSQLバイナリー、データ)を新しいスレーブにコピー。
my.cnfも既存スレーブからコピーしてserver-id だけ変更して利用。
コピー時は一時的にHOME002のMYSQLサービスを停止しましたが、STOP SLAVEしてバックアップ取得しても良い。

新規スレーブにコピーしたフォルダーの権限をMYSQLユーザーに所有権変更

[root@colinux mysql]# chown -R mysql:mysql mysql-5.5.29-linux2.6-i686/

既存スレーブのserver-id on my.cnf

[root@HOME002 ~]# hostname
HOME002.localdomain
[root@HOME002 ~]#  cat /etc/my.cnf | grep server-id | egrep -v ^#
server-id       = 2
[root@HOME002 ~]#

新規スレーブのserver-id on my.cnfを変更して既存スレーブと衝突しないよう調整

[root@colinux data]# hostname
colinux
[root@colinux data]# cat /etc/my.cnf | grep server-id | egrep -v ^#
server-id       = 3
[root@colinux data]#

マスターDBに新しいスレーブが接続出来るようにアカウントの追加

mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'colinux' IDENTIFIED BY 'パスワード';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

新規追加したスレーブにてMYSQLのサービスを起動してエラーが無いことを確認

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 1100
               Relay_Log_File: relay-bin.000019
                Relay_Log_Pos: 1246
        Relay_Master_Log_File: mysql-bin.000014
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 1100
              Relay_Log_Space: 1396
              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
1 row in set (0.00 sec)

mysql>

上記設定が完了したら、実際にマスターDBにデータを入れて基本動作を確認してみる。

マスターDBにてデータ追加
add slave

既存スレーブ
slave1

新規追加スレーブ
slave2

マスターと各スレーブが同期されエラーログが記録されて無い事を確認して終了。

「留意」
マスターはマルチスレッド処理だが、スレーブ側処理はシングルスレッドなのでマスターが
64bitやメモリー増設して処理が高速化するとスレーブ側は遅延する可能性がある。

MySQL/レプリケーション

スレーブへのレプリケーションのタイムラグを解消
innodb_flush_log_at_trx_commit=2