mysql组复制

一.组复制背景

创建容错系统的最常见方法是创建组件冗余,换句话说,组件可以删除,而系统应该继续按预期运行。 这就造成了一系列的挑战,将这种系统的复杂性提高到一个完全不同的水平。 具体来说,复制的数据库需要同时维护和管理若干个 server 成员,而不只是一个。 此外,当多个 server协同工作时,系统必须处理其他一些常见的分布式系统问题,诸如断网或脑裂等情况。
因此,最大的挑战是将数据库和数据复制的逻辑与若干个 server 以简单一致的方式协调运行的逻辑相融合。 换句话说,也就是使多个 server 成员关于系统的状态和系统每次变更的数据保持一致。 这可以被概括为使多个 server 对于每个数据库状态转换达成共识,从而使它们都作为一个独立的数据库运行,或者说它们最终达到相同状态。 这就意味着它们需要作为(分布式) statemachine 运行。
MySQL 组复制提供了一种强大的 server 间协调机制的分布式 state machine 复制。 组中的server 成员会自动地进行协调。
组复制可以在两种模式下运行。 在单主模式下,组复制具有自动选主功能,每次只有一个 server成员接受更新。在多主模式下运行时,所有的 server 成员都可以同时接受更新。
这种功能就要求应用程序不得不解决部署所带来的限制。内置的组成员服务,用于保持组视图的一致性,并在任何给定的时间点对于所有 server 可用。当 Server 离开或加入组时,视图会相应地进行更新。 server 也可能会意外离开组,故障检测机制会自动检测到此情况,并通知组该视图已更改。

二.组复制

组复制是一种可用于实现容错系统的技术。 复制组是一个通过消息传递相互交互的 server 集群。通信层提供了原子消息(atomic message)和完全有序信息交互等保障机制。 这些是非常强大的功能,我们可以据此架构设计更高级的数据库复制解决方案。
MySQL 组复制以这些功能和架构为基础,实现了基于复制协议的多主更新。复制组由多个 server成员构成,并且组中的每个 server 成员可以独立地执行事务。但所有读写(RW)事务只有在冲突检测成功后才会提交。只读(RO)事务不需要在冲突检测,可以立即提交。换句话说,对于任何 RW 事务,提交操作并不是由始发 server 单向决定的,而是由组来决定是否提交。准确地说,在始发 server 上,当事务准备好提交时,该 server 会广播写入值(已改变的行)和对应的写入集(已更新的行的唯一标识符)。然后会为该事务建立一个全局的顺序。最终,这意味着所有 server 成员以相同的顺序接收同一组事务。因此,所有 server 成员以相同的顺序应用相同的更改,以确保组内一致。
在不同 server 上并发执行的事务可能存在冲突。 根据组复制的冲突检测机制,对两个不同的并发事务的写集合进行检测。如在不同的 server 成员执行两个更新同一行的并发事务,则会出现冲突。排在最前面的事务可以在所有 server 成员上提交,第二个事务在源 server 上回滚,并在组中的其他 server 上删除。 这就是分布式的先提交当选规则

三.部署组复制：

创建数据目录，数据库初始化

[root@server1 ~]# cd /data/mysql
[root@server1 mysql]# rm -fr *
[root@server1 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid-mode=ON
enforce-gtid-consistency=ON
server-id=1
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d
[root@server1 mysql]# mysqld --initialize --user=mysql
2020-08-19T02:32:47.366171Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 3994
2020-08-19T02:32:47.373629Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-19T02:32:48.120061Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-19T02:32:49.485952Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: yM5XCrp7-q3f

server3 ，server4上进行同样的操作

[root@server3 ~]# cd /data/mysql 
[root@server3 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 
[root@server3 mysql]# rm -fr *
[root@server3 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid_mode=ON
enforce-gtid-consistency=ON
server-id=2
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d
[root@server3 mysql]# mysqld --initialize --user=mysql
2020-08-19T02:39:34.855284Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 8906
2020-08-19T02:39:34.864696Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-19T02:39:36.155539Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-19T02:39:37.650425Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: yilpl8NrjB!k

[root@server4 ~]# cd /data/mysql
[root@server4 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid_mode=ON
enforce-gtid-consistency=ON
server-id=3
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d
[root@server4 mysql]# rm -fr *
[root@server4 mysql]# mysqld --initialize --user=mysql
2020-08-19T02:53:20.395019Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 10172
2020-08-19T02:53:20.404297Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-19T02:53:21.665693Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-19T02:53:23.192860Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wqf&PsXyJ3Kf

四.配置组复制server:

[root@server1 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid-mode=ON
enforce-gtid-consistency=ON
server-id=1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.13.1:33061"
group_replication_group_seeds="172.25.13.1:33061,172.25.13.3:33061,172.25.13.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.13.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d
[root@server1 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server1.err'.
 SUCCESS! 

server3 ，server4上进行同样的操作

[root@server3 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid_mode=ON
enforce-gtid-consistency=ON
server-id=2
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.13.3:33061"
group_replication_group_seeds="172.25.13.1:33061,172.25.13.3:33061,172.25.13.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.13.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d

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

[root@server4 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
gtid_mode=ON
enforce-gtid-consistency=ON
server-id=3
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
default_authentication_plugin=mysql_native_password
plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.13.4:33061"
group_replication_group_seeds="172.25.13.1:33061,172.25.13.3:33061,172.25.13.4:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="172.25.13.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
#skip-grant-tables
#
##
## include all files from the config directory
##
!includedir /etc/my.cnf.d

五.用户凭据：

[root@server1 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21

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

##创建具有 REPLICATION-SLAVE 权限的 MySQL 用户。 此操作不应记录到二进制日志中,以避免将更改传递到其他 server 实例

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

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

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
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> GRANT BACKUP_ADMIN 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)

##将 server 配置为,在下次需要从其他成员恢复其状态时,使用 group_replication_recovery 复制通道的给定凭据。 

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

## 启动组复制

mysql> SET GLOBAL group_replication_bootstrap_group=ON;  
##只有server1操作
Query OK, 0 rows affected (0.00 sec)

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

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;  
##只有server1操作
Query OK, 0 rows affected (0.01 sec)

## 此时组中已经有一位成员

mysql> SELECT * FROM performance_schema.replication_`g`roup_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 45f81d46-e1c4-11ea-9881-525400647a16 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

mysql> exit
Bye

server3 ，server4操作相同

[root@server3 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21

Copyright (c) 2000, 2020, 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> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
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> GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 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='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.92 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 | 393e28c9-e1c5-11ea-973e-525400b6900f | server3     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 45f81d46-e1c4-11ea-9881-525400647a16 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

[root@server4 mysql]# mysql -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock' (2)
[root@server4 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server4.err'.
. SUCCESS! 
[root@server4 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21

Copyright (c) 2000, 2020, 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> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'password';
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> GRANT BACKUP_ADMIN 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='password' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.62 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 | 253dfee4-e1c7-11ea-9b6b-52540012284e | server4     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 393e28c9-e1c5-11ea-973e-525400b6900f | server3     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
| group_replication_applier | 45f81d46-e1c4-11ea-9881-525400647a16 | server1     |        3306 | ONLINE       | PRIMARY     | 8.0.21         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

六.组复制测试

1.server1上新建test数据库，建立表t1并插入一行数据，三个server上都可查看

[root@server1 mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

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

mysql> use test;
Database changed

mysql> create table t1 (c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from client;
ERROR 1146 (42S02): Table 'test.client' doesn't exist
mysql> select * from t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
+----+----+
1 row in set (0.00 sec)

server3
mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
+----+----+
1 row in set (0.00 sec)

server4
mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
+----+----+
1 row in set (0.00 sec)

2.server3在t1表中插入数据

mysql> insert into test.t1 values (2,'sg');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
+----+----+
2 rows in set (0.00 sec)

server1
mysql> select * from t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
+----+----+
2 rows in set (0.00 sec)

server4
mysql> select * from t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
+----+----+
2 rows in set (0.00 sec)

3.server4在t1表中插入数据

mysql> insert into test.t1 values (3,'jk');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
|  3 | jk |
+----+----+
3 rows in set (0.00 sec)

server1
mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
|  3 | jk |
+----+----+

server3
mysql> select * from test.t1;
+----+----+
| c1 | c2 |
+----+----+
|  1 | lj |
|  2 | sg |
|  3 | jk |
+----+----+