组复制是基于分布式一致性协议Paxos实现数据最终一致性的MySQL插件，通过这种插件可以实现弹性、高可用、容错复制拓扑结构。
MySQL组复制提供分布式状态机复制，在服务器之间具有强协调。当数据库服务器是属于同一组时，组复制机制可以自动协调它们。该组可以在具有自动选择新主库功能的单主模式下操作，这种情况下一个组只有主节点才可以做写操作。或者，对于更高级的用户，该组可以以多主模式部署，即多个节点都可以做写操作，即使它们是同时发过来的写请求。不过这种情况下，应用层会有部分额外的限制。
MySQL组复制是MySQL 5.7.17开始引入的新功能，为主从复制实现高可用功能。它支持单主模型和多主模型两种工作方式(默认是单主模型)。
- 单主模型：从复制组中众多个MySQL节点中自动选举一个master节点，只有master节点可以写，其他节点自动设置为read only。当master节点故障时，会自动选举一个新的master节点，选举成功后，它将设置为可写，其他slave将指向这个新的master。
- 多主模型：复制组中的任何一个节点都可以写，因此没有master和slave的概念，只要突然故障的节点数量不太多，这个多主模型就能继续可用。
我们将server1到server3设置为Master1到Master3
1. 恢复环境，更改配置文件
master1:
[root@server1 ~]# systemctl stop mysqld
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# ls
auto.cnf master.info redhat
ca-key.pem mysql relay-log.info
ca.pem mysql-bin.000001 server1-relay-bin.000001
client-cert.pem mysql-bin.000002 server1-relay-bin.000002
client-key.pem mysql-bin.000003 server1-relay-bin.index
ib_buffer_pool mysql-bin.index server-cert.pem
ibdata1 performance_schema server-key.pem
ib_logfile0 private_key.pem sys
ib_logfile1 public_key.pem
[root@server1 mysql]# rm -rf *
[root@server1 mysql]# ps ax | grep mysqld
6048 pts/0 S+ 0:00 grep --color=auto mysqld
[root@server1 mysql]# uuidgen ##随机生成UUID
ddf795db-90d4-4a0d-9a8f-3ec3ace25a50
更改配置文件：
29 server-id=1
30 gtid_mode=ON
31 enforce-gtid-consistency=true
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE #关闭binlog校验
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW #组复制依赖基于行的复制格式
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="ddf795db-90d4-4a0d-9a8f-3ec3ace25a50" #使用上面生成的
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.80.3:24901"
43 loose-group_replication_group_seeds= "172.25.80.1:24901,172. 25.80.2:24901,172.25.80.3:24901"
44 loose-group_replication_bootstrap_group=off ##插件是否自动引导，这个选项一般都要off掉，只需要由发起组复制的节点开启，并只启动一次，如果是on，下次再启动时，会生成一个同名的组，可能会发生脑裂
45 loose-group_replication_ip_whitelist="127.0.0.1,172.25.80.0/ 24"
46 loose-group_replication_enforce_update_everywhere_checks=ON
47 loose-group_replication_single_primary_mode=OFF #后两行是开启多主模式的参数
2.server1启动mysql，修改安全设定
[root@server1 mysql]# grep password /var/log/mysqld.log
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'Redhat1.'; #更改root用户的密码
Query OK, 0 rows affected (0.04 sec)
mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，防止传到其他server上
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat1.'; #创建用户用于复制
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='Redhat1.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.14 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #添加组复制的插件
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW PLUGINS; ##查看插件
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 (2.23 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; ##查看server1是否online
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 376250ff-7331-11e9-bc63-525400ccaaa5 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
3.添加server2到组内
1.关闭server2的mysql，删除信息，修改配置文件
[root@server2 mysql]# systemctl stop mysqld
[root@server2 mysql]# rm -rf *
[root@server2 mysql]# ls
[root@server2 mysql]# vim /etc/my.cnf
29 server-id=2
30 gtid_mode=ON
31 enforce-gtid-consistency=true
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="2956bfe4-7319-11e9-b4d6- 525400b84aa9"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.80.2:24901"
43 loose-group_replication_group_seeds= "172.25.80.1:24901,172. 25.80.2:24901,172.25.80.3:24901"
44 loose-group_replication_bootstrap_group=off
45 loose-group_replication_ip_whitelist="127.0.0.1,172.25.80.0/ 24"
46 loose-group_replication_enforce_update_everywhere_checks=ON
47 loose-group_replication_single_primary_mode=OFF
2.server2启动mysqld，修改安全设定
[root@server2 mysql]# systemctl start mysqld
[root@server2 mysql]# grep password /var/log/mysqld.log
[root@server2 mysql]# mysql -p
mysql> alter user root@localhost identified by 'Redhat1.';
Query OK, 0 rows affected (0.03 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Redhat1.';
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='Redhat1.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.12 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> set global group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.97 sec)
3.在server1上查看是否添加成功
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2f414643-7333-11e9-a4d7-525400b84aa9 | server2 | 3306 | ONLINE |
| group_replication_applier | 376250ff-7331-11e9-bc63-525400ccaaa5 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
4.server3与server2的配置步骤相同
配置文件 有差异如下:
[root@server3 ~]# vim /etc/my.cnf
29 server-id=3
30 gtid_mode=ON
31 enforce-gtid-consistency=true
32 master_info_repository=TABLE
33 relay_log_info_repository=TABLE
34 binlog_checksum=NONE
35 log_slave_updates=ON
36 log_bin=binlog
37 binlog_format=ROW
38
39 transaction_write_set_extraction=XXHASH64
40 loose-group_replication_group_name="2956bfe4-7319-11e9-b4d6- 525400b84aa9"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.80.3:24901"
43 loose-group_replication_group_seeds= "172.25.80.1:24901,172. 25.80.2:24901,172.25.80.3:24901"
44 loose-group_replication_bootstrap_group=off
45 loose-group_replication_ip_whitelist="127.0.0.1,172.25.80.0/ 24"
46 loose-group_replication_enforce_update_everywhere_checks=ON
5.添加完成后，在server1上验证是否添加成功
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2f414643-7333-11e9-a4d7-525400b84aa9 | server2 | 3306 | ONLINE |
| group_replication_applier | 376250ff-7331-11e9-bc63-525400ccaaa5 | server1 | 3306 | ONLINE |
| group_replication_applier | 7089ab88-7334-11e9-ab9f-525400cb2505 | server3 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
4.测试
在server1上:
mysql> CREATE DATABASE redhat;
Query OK, 1 row affected (0.04 sec)
mysql> USE redhat;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO
Display all 763 possibilities? (y or n)
mysql> INSERT INTO t1 VALUES (1,'USER1');
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM t1;
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | USER1 |
+----+-------+
1 row in set (0.00 sec)
在server2上
mysql> SELECT * FROM redhat.t1;
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | USER1 |
+----+-------+
1 row in set (0.00 sec)
在server2上:
mysql> SELECT * FROM redhat.t1;
+----+-------+
| c1 | c2 |
+----+-------+
| 1 | USER1 |
+----+-------+
1 row in set (0.00 sec)
组复制完成
扫一扫
265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
