另一個MySQL DBA: MySQL組複製

所以MySQL的組複製出來了MySQL 5.7。現在已經有一段時間了，人們開始更多地詢問它。

下面是一個如何設置它的例子和一些痛點的例子，因為我用它來探討。
我使用三個不同的服務器，

服務器CENTOSA


 mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 
 Query OK, 0 rows affected (0.02 sec) 
 
 vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=1 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.17:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> SET SQL_LOG_BIN=0; 
 mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword'; 
 mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; 
 mysql> FLUSH PRIVILEGES; 
 mysql> SET SQL_LOG_BIN=1; 
 
 
 CHANGE MASTER TO 
 MASTER_USER='repl', 
 MASTER_PASSWORD='replpassword' 
 FOR CHANNEL 'group_replication_recovery'; 
 
 
 mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
 Query OK, 0 rows affected (0.00 sec) 
 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.11 sec) 
 
 
 mysql> SET GLOBAL group_replication_bootstrap_group=OFF; 
 Query OK, 0 rows affected (0.00 sec) 
 
 
 mysql> SELECT * FROM performance_schema.replication_group_members \G 
 
 *************************** 1. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 
 MEMBER_HOST: centosa 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: PRIMARY 
 MEMBER_VERSION: 8.0.15

所以現在我們可以添加更多服務器。
服務器CENTOSB

https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=2 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.89:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> CHANGE MASTER TO 
 MASTER_USER='repl', 
 MASTER_PASSWORD='replpassword' 
 FOR CHANNEL 'group_replication_recovery'; 
 Query OK, 0 rows affected, 2 warnings (0.02 sec) 
 
 mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; 
 Query OK, 0 rows affected (0.02 sec) 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (4.03 sec) 
 
 mysql> SELECT * FROM performance_schema.replication_group_members; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 2 rows in set (0.00 sec)

服務器CENTOSC

https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=3 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.124:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> CHANGE MASTER TO 
 -> MASTER_USER='repl', 
 -> MASTER_PASSWORD='replpassword' 
 -> FOR CHANNEL 'group_replication_recovery'; 
 Query OK, 0 rows affected, 2 warnings (0.02 sec) 
 
 mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; 
 Query OK, 0 rows affected (0.02 sec) 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.58 sec) 
 mysql> SELECT * FROM performance_schema.replication_group_members \G 
 *************************** 1. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 
 MEMBER_HOST: centosa 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: PRIMARY 
 MEMBER_VERSION: 8.0.15 
 
 *************************** 2. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1 
 MEMBER_HOST: centosb 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: SECONDARY 
 MEMBER_VERSION: 8.0.15 
 
 *************************** 3. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6 
 MEMBER_HOST: centosc 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: SECONDARY 
 MEMBER_VERSION: 8.0.15 
 3 rows in set (0.00 sec)

所以這一切都很棒，但並不總是意味著他們上網，他們通常可以坐在恢復模式。
到目前為止，我已經看到MySQL崩潰失敗，所以需要確保它穩定。

mysql>
 create database testcentosb;<br> ERROR 1290 (HY000): The MySQL 
server is running with the --super-read-only option so it cannot execute
 this statement<br>

附註解決其中一些因素 -

mysql> START GROUP_REPLICATION; 
 ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start. 
 
 mysql> reset slave all; 
 Query OK, 0 rows affected (0.03 sec)

- 然後從Change master命令重新開始

mysql> START GROUP_REPLICATION; 

 ERROR 3092 (HY000): The server is not configured properly to be an 
active member of the group. Please see more details on error log. 
 

 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] 
Error on opening a connection to 192.168.111.17:33061 on local port: 
33061.' 
 [ERROR] [MY-011526] [Repl] Plugin group_replication 
reported: 'This member has more executed transactions than those present
 in the group. Local transactions: 
c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 > 
 [ERROR] [MY-011522] 
[Repl] Plugin group_replication reported: 'The member contains 
transactions not present in the group. The member will now exit the 
group.'

https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/

[ERROR]
 [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error 
during the recovery process of Group Replication. The server will leave 
the group.' 
 [ERROR] [MY-013173] [Repl] Plugin group_replication 
reported: 'The plugin encountered a critical error and will abort: Fatal
 error during execution of Group Replication' 
 
 SELECT * FROM performance_schema.replication_connection_status\G

我的想法...
請記住，可以在單主模式或多節點中設置組複製

mysql> select @@group_replication_single_primary_mode\G 
 *************************** 1. row *************************** 
 @@group_replication_single_primary_mode: 1 
 
 mysql> create database testcentosb; 
 ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

如果您寫入無主節點，您當然會收到錯誤。

group-replication-single-primary-mode = off < - 添加到cnf文件中。

mysql> SELECT * FROM performance_schema.replication_group_members;

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | 主 | 8.0.15 |

| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | 線上 | 主 | 8.0.15 |

| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | RECOVERING | 主 | 8.0.15 |

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

3組（0.00秒）

現在，如果您使用Keepalived，MySQL路由器，ProxySQL等來處理您的流量，以便在發生故障轉移時自動翻轉。當我停止主要時，我們可以從下面看到它立即失敗了。

mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec) 
 
 [root@centosa]# systemctl stop mysqld 
 
 mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 2 rows in set (0.00 sec) 
 
 [root@centosa]# systemctl start mysqld 
 [root@centosa]# mysql 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.34 sec) 
 
 mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec)

現在復蘇仍然是一個問題，因為它不會簡單地加入。不得不再次審查所有帳戶和步驟，但我最終確實得到了它。

mysql> SELECT * FROM performance_schema.replication_group_members; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec)