2.12 lnmp架构_MySQL集群 GTID 半同步复制 延迟复制 并行复制 MySQL组复制

一主多从的弊端：
做主从的目的：主备

如果是一主多从，master挂了，会找slave1转换为master。之后的slave2 都要重连新的master
（需要重新配置主从）

而且，如果master挂了，要提升slave1，不能保证数据全一致
（成本高，人工干预多）

解决方法：GTID

1. GTID（全局识别ID）

G：Global（全局的，单调递增）

集群中的每个节点都有一个全局ID，这个ID呈单调递增的趋势

slave上的ID是gtid_next，只关心自己的下一跳

每个节点的ID不同，因为存在延迟

master切换会找和GTID值更接近的slave进行切换

补充：
IO和SQL线程不同步，IO线程已经将日志记录到中继日志中，但SQL并不一定就会执行完

转变为GTID模式

1. 修改MySQL配置文件，并重启MySQL

[root@lnmp1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=1
log-bin=mysql-bin

gtid_mode=ON					//启动GTID功能
enforce-gtid-consistency=ON		//强制打开

[root@lnmp1 data]# /etc/init.d/mysqld restart 

2. 生成对应的二进制文件

[root@lnmp1 data]# ls
auto.cnf         ib_buffer_pool  lnmp1.err         mysql-bin.000003    private_key.pem  westos
ca-key.pem       ibdata1         lnmp1.pid         mysql-bin.index     public_key.pem
ca.pem           ib_logfile0     mysql             mysql.sock          server-cert.pem
client-cert.pem  ib_logfile1     mysql-bin.000001  mysql.sock.lock     server-key.pem
client-key.pem   ibtmp1          mysql-bin.000002  performance_schema  sys

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3. slave1（server2）修改配置文件，并重启

[root@lnmp2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-bin=mysql-bin
log-slave-updates				//slave1是slave2的master

gtid_mode=ON
enforce-gtid-consistency=ON

[root@lnmp2 data]# /etc/init.d/mysqld restart 
Shutting down MySQL............ SUCCESS! 
Starting MySQL.. SUCCESS!

slave1主机指定master
（不再需要标注master二进制文件的名称和读的位置）

mysql> stop slave;
mysql> change master to master_host='172.25.21.1', master_user='REPL', master_password='westos', master_auto_position=1;
mysql> start slave;

4. slave（server3）重复上面同样的操作

[root@lnmp3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=3

gtid_mode=ON
enforce-gtid-consistency=ON

[root@lnmp3 data]# /etc/init.d/mysqld restart 
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 

slave2（server3）的master是slave（server2）

mysql> stop slave;
mysql> change master to master_host='172.25.21.2', master_user='REPL', master_password='westos', master_auto_position=1;
mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.2				//slave2的主机是slave1（server2）
                  Master_User: REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: lnmp3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-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: 154
              Relay_Log_Space: 574
              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: 86e432e0-e243-11eb-83d9-525400f6b245
             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 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: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

5. 测试

• master（server1）插入数据

mysql> INSERT  INTO  westos.user_tb VALUES('yao8',123);

• slave2（server3）同步

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
+----------+----------+
8 rows in set (0.00 sec)

• slave端的GTID数据显示

mysql> show slave status\G;
*************************** 1. row ***************************
......
           Retrieved_Gtid_Set: 167ce4c4-c2c8-11eb-85d9-525400f25677:1
            Executed_Gtid_Set: 167ce4c4-c2c8-11eb-85d9-525400f25677:1
                Auto_Position: 1
......
1 row in set (0.00 sec)

• master端的GTID数据

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 420
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 167ce4c4-c2c8-11eb-85d9-525400f25677:1

• 使用mysqlbinlog查看GITD记录，每个节点只关心自己的下一跳

[root@lnmp1 data]# mysqlbinlog mysql-bin.000003
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210717 16:24:46 server id 1  end_log_pos 123 CRC32 0xfc1232d0 	Start: binlog v 4, server v 5.7.31-log created 210717 16:24:46 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
zpPyYA8BAAAAdwAAAHsAAAABAAQANS43LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADOk/JgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdAyEvw=
'/*!*/;
# at 123
#210717 16:24:46 server id 1  end_log_pos 154 CRC32 0x8ea1146c 	Previous-GTIDs
# [empty]
# at 154
#210717 16:43:29 server id 1  end_log_pos 219 CRC32 0x5b744e23 	GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '167ce4c4-c2c8-11eb-85d9-525400f25677:1'/*!*/;
# at 219
#210717 16:43:29 server id 1  end_log_pos 287 CRC32 0x3d2a07a4 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1626511409/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 287
#210717 16:43:29 server id 1  end_log_pos 344 CRC32 0x7086c71f 	Table_map: `westos`.`user_tb` mapped to number 108
# at 344
#210717 16:43:29 server id 1  end_log_pos 389 CRC32 0xc88c19d9 	Write_rows: table id 108 flags: STMT_END_F

BINLOG '
MZjyYBMBAAAAOQAAAFgBAAAAAGwAAAAAAAEABndlc3RvcwAHdXNlcl90YgACDw8ESwCWAAAfx4Zw
MZjyYB4BAAAALQAAAIUBAAAAAGwAAAAAAAEAAgAC//wEeWFvOAMxMjPZGYzI
'/*!*/;
# at 389
#210717 16:43:29 server id 1  end_log_pos 420 CRC32 0x78ba5cce 	Xid = 37
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2. 半同步复制

回顾：主从复制的工作原理

先auth认证，由slave连接master（change的命令）（IO出现问题，可能是master火墙问题）
再，master开启binlog dump线程，当用户写数据时，其会将变更的二进制日志发送给slave
slave的IO线程进行接收，并写入到磁盘，持久化保存
再再，SQL线程重放操作，并将slave的操作记录到本地日志中

存在的问题：

如果默认工作方式是异步复制（最快）
（master发送完数据，就返回成功，不保证slave是否成功同步）

MySQL5.7做到了IO的无损复制

针对上述问题，需要设置以下的机制：
当IO线程已经存储了日志到relay log中后，给master发送一个ACK
这并不代表全部slave都同步了数据，只能保证其中一个slave已经同步数据，简介保证这个同步数据的slave可以作为备用的master，保证数据全平台一致 【半同步复制】

AFTER_COMMIT —— 【引擎提交是在ACK之前】
用户插入数据后，master变更binlog二进制日志
master插入PUSH日志到slave的IO线程，同时，它还会进行一个sync刷盘操作
之后，进行 引擎提交 。一旦提交，所有用户都可以在数据库中取数据
当master收到slave的ACK后，则master返回给用户ACK

存在的问题： 不能做到无损
比如：
插入数据1，user可以查看到，
如果此时master数据库宕掉，user无法查看到，
原因是IO在引擎提交之后才会返回ACK，master宕掉之后，slave提升为master，但是没有刚刚插入的数据

所以，在收到IO线程的ACK之前，不能做引擎提交（但可以刷盘）

改进的AFTER_COMMIT如下： 【【AFTER_SYNC】】
收到IO的ACK之后，才会引擎提交

实现半同步复制

master（server1）和 slave（server2）的主从配置

1. master（server1）安装master插件

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
//安装成功

2：
安装salve和master

2. slave（server2）安装slave插件和master插件

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

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

mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
//安装成功

3. master设置全局生效

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

4. slave（server2）设置全局生效

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

5. master（server1）查看参数
   表中的含义：

• 10s超时，开启半同步
  AFTER_SYNC模式
  如果超时，就从半同步复制，退回到异步复制

mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

6. slave（server2）查看参数

mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | OFF   |
+--------------------------------------------+-------+

这里的slave半同步模式没有开启，是因为，一直没有重启IO线程

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.11 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

再次查看slave的状态

mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

master（server2）和 slave（server3）的主从配置

1. master（server2）设定全局

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |	//开启
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

2. slave（server3）安装slave插件，设定全局，并重启IO线程

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

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

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.10 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+

3. master（server2）查看状态，观察到现在有一个slave

mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

4. 测试

• master（server1）插入数据

mysql> INSERT  INTO  westos.user_tb VALUES('yao9',123);

• slave（server3）查看

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
| yao9     | 123      |
+----------+----------+

• slave（server2）查看status表，
  Rpl_semi_sync_master_yes_tx：通过半同步成功的表

mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 1     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 553   |
| Rpl_semi_sync_master_tx_wait_time          | 553   |
| Rpl_semi_sync_master_tx_waits              | 1     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 1     |
| Rpl_semi_sync_slave_status                 | ON    |
+--------------------------------------------+-------+

模拟半同步失败

1. slave（server2）停止IO线程=半同步失败

mysql> STOP SLAVE IO_THREAD;

2. master（server1）插入数据
   （等待10s，收不到slave（server2）的ACK）

mysql> INSERT  INTO  westos.user_tb VALUES('yao10',123);
Query OK, 1 row affected (10.66 sec)

3. slave上都没有数据

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
| yao9     | 123      |
+----------+----------+

但是，master（server1） 等待10s之后，依旧是向用户返回OK成功信息
这是因为设置了10s等待时间
（生产环境的等待时间设置为无穷大）

mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+

恢复半同步

1. slave（server2）启动IO线程
   恢复之后，slave（server2和server3）立马就可以接受到master（server1）之前插入的数据

mysql> START SLAVE IO_THREAD;

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
| yao9     | 123      |
| yao10    | 123      |
+----------+----------+

【【【【【【【【数据库二进制：复习】】】】】】】】

3. 延迟复制

原理

实现

1. slave（server2）关闭SQL线程，设定延迟为30s
   延迟：slave的SQL回放延迟30s

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

mysql> change master to MASTER_DELAY=30;
Query OK, 0 rows affected (0.11 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.1
                  Master_User: REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 953
               Relay_Log_File: lnmp2-relay-bin.000004
                Relay_Log_Pos: 721
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......											//省略部分
                    SQL_Delay: 30				//SQL线程延迟30s
          SQL_Remaining_Delay: NULL
......
1 row in set (0.00 sec)

2. master（server1）插入数据

mysql> INSERT  INTO  westos.user_tb VALUES('yao11',123);

3. slave（server2）在30s之后成功同步数据

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.1
                  Master_User: REPL
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1220
               Relay_Log_File: lnmp2-relay-bin.000004
                Relay_Log_Pos: 721
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......											//省略部分
                    SQL_Delay: 30				//SQL线程延迟30s
          SQL_Remaining_Delay: 9				//剩下9s
......
1 row in set (0.00 sec)

查看30s后的结果

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
| yao9     | 123      |
| yao10    | 123      |
+----------+----------+
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| yao1     | 123      |
| yao2     | 123      |
| yao3     | 123      |
| yao4     | 123      |
| yao5     | 123      |
| yao6     | 123      |
| yao7     | 123      |
| yao8     | 123      |
| yao9     | 123      |
| yao10    | 123      |
| yao11    | 123      |
+----------+----------+

4. 并行复制（减少数据库延迟）

之前的SQL是单线程，现在要求多开几个SQL线程，实现并行复制

2：
查看slave开启的worker

1. slave（server2）查看可以开启的worker进程数

mysql> show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000003 |    4 | Format_desc    |         2 |         123 | Server ver: 5.7.31-log, Binlog ver: 4                             |
| mysql-bin.000003 |  123 | Previous_gtids |         2 |         154 |                                                                   |
| mysql-bin.000003 |  154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= '167ce4c4-c2c8-11eb-85d9-525400f25677:1' |
| mysql-bin.000003 |  219 | Query          |         1 |         282 | BEGIN                                                             |
| mysql-bin.000003 |  282 | Table_map      |         1 |         339 | table_id: 108 (westos.user_tb)                                    |
| mysql-bin.000003 |  339 | Write_rows     |         1 |         384 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 |  384 | Xid            |         1 |         415 | COMMIT /* xid=42 */                                               |
| mysql-bin.000003 |  415 | Gtid           |         1 |         480 | SET @@SESSION.GTID_NEXT= '167ce4c4-c2c8-11eb-85d9-525400f25677:2' |
| mysql-bin.000003 |  480 | Query          |         1 |         543 | BEGIN                                                             |
| mysql-bin.000003 |  543 | Table_map      |         1 |         600 | table_id: 108 (westos.user_tb)                                    |
| mysql-bin.000003 |  600 | Write_rows     |         1 |         645 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 |  645 | Xid            |         1 |         676 | COMMIT /* xid=72 */                                               |
| mysql-bin.000003 |  676 | Gtid           |         1 |         741 | SET @@SESSION.GTID_NEXT= '167ce4c4-c2c8-11eb-85d9-525400f25677:3' |
| mysql-bin.000003 |  741 | Query          |         1 |         804 | BEGIN                                                             |
| mysql-bin.000003 |  804 | Table_map      |         1 |         861 | table_id: 108 (westos.user_tb)                                    |
| mysql-bin.000003 |  861 | Write_rows     |         1 |         907 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 |  907 | Xid            |         1 |         938 | COMMIT /* xid=78 */                                               |
| mysql-bin.000003 |  938 | Gtid           |         1 |        1003 | SET @@SESSION.GTID_NEXT= '167ce4c4-c2c8-11eb-85d9-525400f25677:4' |
| mysql-bin.000003 | 1003 | Query          |         1 |        1066 | BEGIN                                                             |
| mysql-bin.000003 | 1066 | Table_map      |         1 |        1123 | table_id: 108 (westos.user_tb)                                    |
| mysql-bin.000003 | 1123 | Write_rows     |         1 |        1169 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000003 | 1169 | Xid            |         1 |        1200 | COMMIT /* xid=105 */                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

2. 修改slave（server2）的配置，设置开启的worker线程数

[root@lnmp2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-bin=mysql-bin
log-slave-updates

gtid_mode=ON
enforce-gtid-consistency=ON

rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE		//用文件或者TABLE的方式存储
relay_log_info_repository=TABLE		//同上
relay_log_recovery=ON				//启动relay-log自动恢复功能

3. slave（server2）查看参数
   这是之前的配置

mysql> show variables like '%info%';
+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | FILE           |
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | FILE           |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+

重启MySQL之后，查看参数

mysql> show variables like '%info%';
+--------------------------------+----------------+
| Variable_name                  | Value          |
+--------------------------------+----------------+
| master_info_repository         | TABLE          |
| relay_log_info_file            | relay-log.info |
| relay_log_info_repository      | TABLE          |
| session_track_transaction_info | OFF            |
| sync_master_info               | 10000          |
| sync_relay_log_info            | 10000          |
+--------------------------------+----------------+

查看worker线程

mysql> show processlist;
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User        | Host          | db   | Command          | Time | State                                                         | Info             |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+
|  1 | system user |               | NULL | Connect          |   58 | Waiting for master to send event                              | NULL             |
|  2 | system user |               | NULL | Connect          |   57 | Slave has read all relay log; waiting for more updates        | NULL             |
|  3 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  4 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  5 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  6 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  7 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  8 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
|  9 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 10 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 11 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 12 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 13 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 14 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 16 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 17 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 18 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 19 | system user |               | NULL | Connect          |   58 | Waiting for an event from Coordinator                         | NULL             |
| 20 | root        | localhost     | NULL | Query            |    0 | starting                                                      | show processlist |
| 21 | REPL        | server3:54718 | NULL | Binlog Dump GTID |    3 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-------------+---------------+------+------------------+------+---------------------------------------------------------------+------------------+

查看master信息

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 167ce4c4-c2c8-11eb-85d9-525400f25677:1-4

mysql> show variables like 'slave%';
+------------------------------+-----------------------+
| Variable_name                | Value                 |
+------------------------------+-----------------------+
| 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       | 16                    |
| 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       |                       |
+------------------------------+-----------------------+

5. MySQL组复制协议（分布式文件系统+MySQL主从）

【【【未来MySQL发展的趋势】】】

去中心化，不需要做读写分离

每个节点都可以读写

一旦写入，就开始写到其余的节点。只要有一个节点写入失败，其余节点都不能继续写入

单主模式

需要做读写分离

多主模式

不需要做读写分离

所有节点是master。更好的保证高可用

容错机制

3个节点，允许1个故障
7个节点，允许3个节点故障

实现

123：
停掉所有的节点
stop

1. 3个节点都关闭MySQL，重新配置

[root@lnmp1 data]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@lnmp2 data]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@lnmp3 data]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!

【【【复习！！！！数据库引擎：InnoDB】】】

• server1的操作，server1作为引导节点

• 清空配置文件，删除MySQL数据目录，进行初始化

[root@lnmp1 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

记住临时密码

[root@lnmp1 data]# rm -fr *
[root@lnmp1 data]# mysqld --initialize --user=mysql
2021-07-17T10:40:44.861532Z 1 [Note] A temporary password is generated for root@localhost: EH<nfabwf3cv

• 启动MySQL

[root@lnmp1 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp1.err'.
 SUCCESS!

• 修改配置文件

[root@lnmp1 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0


disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce-gtid-consistency=ON

master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.21.1:33061"
group_replication_group_seeds= "172.25.21.1:33061,172.25.21.2:33061,172.25.21.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.21.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

• 重启MySQL

[root@lnmp1 data]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!

• 使用临时密码进入MySQL后，无法进行操作，需要先创建用户授权

[root@lnmp1 data]# mysql -p'EH<nfabwf3cv'

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

修改密码

mysql> alter user root@localhost identified by 'westos';

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2. sever1（引导节点） 的配置

• 先设定不记录日志

mysql> SET SQL_LOG_BIN=0;

• 创建认证用户

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';

• 授权

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

• 刷新授权表

mysql> FLUSH PRIVILEGES;

• 开启记录日志

mysql> SET SQL_LOG_BIN=1;

• 配置组复制

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';

• 配置全局组复制

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

• 开启组复制

mysql> START GROUP_REPLICATION;

【【server1是引导节点】】

• 只有引导组节点需要做执行下面的命令（其他节点不做）

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

• 查看当前组中节点是否ONLINE

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

3. sever2 的配置

• 先配置MySQL

[root@lnmp2 data]# rm -fr *

清空MySQL配置文件

[root@lnmp2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

初始化MySQL，保留临时密码

[root@lnmp2 data]# mysqld --initialize --user=mysql
2021-07-17T11:00:28.940589Z 1 [Note] A temporary password is generated for root@localhost: (GmyaWq*t3uW

变更MySQL配置

[root@lnmp2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce-gtid-consistency=ON

master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.21.2:33061"
group_replication_group_seeds= "172.25.21.1:33061,172.25.21.2:33061,172.25.21.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.21.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

启动MySQL

[root@lnmp2 data]# /etc/init.d/mysqld start 
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp2.err'.
. SUCCESS!

进入mysql -p（临时密码）

[root@lnmp2 data]# mysql -p'(GmyaWq*t3uW'

修改管理的密码

mysql> alter user root@localhost identified by 'westos';

• 配置server2的组复制

不要记录日志

mysql> SET SQL_LOG_BIN=0;

创建认证用户

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';

授权

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';

刷新权限表

mysql> FLUSH PRIVILEGES;

开启记录日志

mysql> SET SQL_LOG_BIN=1;

server2的组复制配置

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';

直接启动，不需要在server2上开启全局复制

（出错：1和2的数据不一致）
（出错：没有加入白名单）
（出错：加入一条命令）

这里出现问题，解决如下：
在配置文件中添加一条命令配置group_replication_allow_local_disjoint_gtids_join=ON

[root@lnmp2 data]# vim /etc/my.cnf
group_replication_allow_local_disjoint_gtids_join=ON

如果之前已经启动了组复制功能，需要先STOP，再START

[root@lnmp2 data]# /etc/init.d/mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (15.63 sec)

查看当前集群中的节点状态

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2       |        3306 | RECOVERING   |
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

4. server3 的配置

• MySQL配置

[root@lnmp3 data]# rm -fr *
[root@lnmp3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

[root@lnmp3 data]# mysqld --initialize --user=mysql
2021-07-17T11:18:09.292587Z 1 [Note] A temporary password is generated for root@localhost: TQg8Kkk9Qp+z

[root@lnmp3 data]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce-gtid-consistency=ON

master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address= "172.25.21.3:33061"
group_replication_group_seeds= "172.25.21.1:33061,172.25.21.2:33061,172.25.21.3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.21.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON	//记得添加这一条配置

[root@lnmp3 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/lnmp3.err'.
. SUCCESS!

[root@lnmp3 data]# mysql -p'TQg8Kkk9Qp+z'

mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.04 sec)

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

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

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.83 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (13.90 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2       |        3306 | ONLINE       |
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1       |        3306 | ONLINE       |
| group_replication_applier | aa5d0efe-e6f0-11eb-81fb-52540015cbaa | lnmp3       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

测试同步

1. 在任意一个节点上创建一个数据库test，在test库中创建一个必须有主键的表，插入数据

mysql> create database test;
Query OK, 1 row affected (0.03 sec)

mysql> use test;
Database changed
mysql> create table t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.40 sec)

mysql> insert into t1 values(1,'yao');
Query OK, 1 row affected (0.16 sec)

mysql> insert into t1 values(2,'qian');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(3,'look');
Query OK, 1 row affected (0.16 sec)

出错！！！！！！！

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2       |        3306 | RECOVERING   |
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1       |        3306 | ONLINE       |
| group_replication_applier | aa5d0efe-e6f0-11eb-81fb-52540015cbaa | lnmp3       |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

查看日志，进行排错，
错误日志：

2021-07-17T11:34:23.287246Z 27 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
2021-07-17T11:34:23.287256Z 27 [ERROR] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'
2021-07-17T11:34:23.287415Z 27 [Note] Plugin group_replication reported: 'Retrying group recovery connection with another donor. Attempt 6/10'

解决方法：
本地解析文件有误，本地文件中的IP地址要对应它的域名
我之所以出错，是因为没有将域名写入到本地解析文件中

2. server2查看同步的数据，并插入数据

mysql> select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | yao  |
|  2 | qian |
|  3 | look |
+----+------+

mysql> insert into test.t1 values(4,'Outlook');

3. server1查看到

mysql> select * from test.t1;
+----+---------+
| c1 | c2      |
+----+---------+
|  1 | yao     |
|  2 | qian    |
|  3 | look    |
|  4 | Outlook |
+----+---------+

4. server3查看

mysql> select * from test.t1;
+----+---------+
| c1 | c2      |
+----+---------+
|  1 | yao     |
|  2 | qian    |
|  3 | look    |
|  4 | Outlook |
+----+---------+

模拟一个节点宕机

1. server1停止组复制

mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (9.24 sec)

2. server2查看当前集群的状态，并插入数据

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2       |        3306 | ONLINE       |
| group_replication_applier | aa5d0efe-e6f0-11eb-81fb-52540015cbaa | lnmp3       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

mysql> insert into test.t1 values(5,'Outlook123');
Query OK, 1 row affected (0.17 sec)

3. server3查看

mysql> select * from test.t1;
+----+------------+
| c1 | c2         |
+----+------------+
|  1 | yao        |
|  2 | qian       |
|  3 | look       |
|  4 | Outlook    |
|  5 | Outlook123 |
+----+------------+

4. server1上线

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.35 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 326ae3c3-e6ee-11eb-81b9-525400f6b245 | lnmp2       |        3306 | ONLINE       |
| group_replication_applier | 708ab081-e6eb-11eb-984d-525400f25677 | lnmp1       |        3306 | ONLINE       |
| group_replication_applier | aa5d0efe-e6f0-11eb-81fb-52540015cbaa | lnmp3       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

数据恢复一致

mysql> select * from test.t1;
+----+------------+
| c1 | c2         |
+----+------------+
|  1 | yao        |
|  2 | qian       |
|  3 | look       |
|  4 | Outlook    |
|  5 | Outlook123 |
+----+------------+

[root@lnmp3 data]# /etc/init.d/mysqld stop
[root@lnmp2 data]# /etc/init.d/mysqld stop
[root@lnmp1 data]# /etc/init.d/mysqld stop