MySQL组复制是一种基于Paxos协议的高可用复制解决方案，提供单主或多主模式，确保数据一致性。在节点故障时能自动检测并处理，支持自动成员管理和容错。配置包括设置单主或多主模式，以及在各节点上进行初始化和配置。
MGR介绍
组复制是基于分布式一致性协议Paxos实现数据最终一致性的MySQL插件，通过这种插件可以实现弹性、高可用、容错复制拓扑结构。
MySQL组复制提供分布式状态机复制，在服务器之间具有强协调。当数据库服务器是属于同一组时，组复制机制可以自动协调它们。该组可以在具有自动选择新主库功能的单主模式下操作，这种情况下一个组只有主节点才可以做写操作。或者，对于更高级的用户，该组可以以多主模式部署，即多个节点都可以做写操作，即使它们是同时发过来的写请求。不过这种情况下，应用层会有部分额外的限制。
1.数据一致性保证
对于只读(RO)事务，组间实例无需进行通讯，就可以处理事务；但是对于读写(RW)事务，需要经过组内大多数节点决议，来决定该事务是否可以提交。
2.事务并发冲突处理
在高并发的多写模式（MGR的一种运行模式）下，节点间事务的提交可能会产生冲突，比如，两个不同的事务在两个节点上操作了同一行数据，这个时候就会产生冲突。首先，Group Replication（GR）能够识别到这个冲突，然后对此的处理采用乐观策略：依赖事务提交的时间先后顺序，先发起提交的节点能够正确提交，而后面的提交，会失败。
3.节点故障自动检测
GR自带故障检测机制，可以识别组内成员是否挂掉(组内节点心跳检测)。当一个节点失效，将由其他节点决定是否将这个失效的节点从group里面剔除。当然，这是建立在满足大多数节点存活并且可以进行决议的前提上的。
4.组成员自动管理
GR自动维护组内节点的状态(在线？存活？挂掉？)，对于失效的节点，由其他节点决定是否剔除。对于新加入的节点，GR会自动维护它的视图与其他节点的视图保持一致。
关于集群内节点的状态，可以通performance_schema.replication_group_members表查看。
5.容错能力
GR基于分布式一致性算法实现，一个组允许部分节点挂掉，只要保证大多数节点仍然存活并且之间的通讯是没有问题的，那么这个组对外仍然能够提供服务。
6.两种模式
GR提供了single-primary和multi-primary两种模式。single-primary模式下，组内只有一个节点负责写入，读可以从任意一个节点读取，组内数据保持最终一致；而multi-primary模式即为多写方案，即写操作会下发到组内所有节点，组内所有节点同时可读可写，该模式也是能够保证组内数据最终一致性。
注意，一个GR的所有节点必须配置使用同一种模式，不可混用。比如说A、B、C三个节点组成一个GR组，那么要么都运行在single-primary模式下，要么都运行在multi-primary模式下。
由my.cnf里的配置项group_replication_single_primary_mode来配置节点到底是运行在single-primary模式还是multi-primary模式
MGR配置
实验环境：（虚拟机rhel6.5.x86_64）
server1:172.25.8.1
server2:172.25.8.2
server3:172.25.8.3
server1,server2,server3都停止mysql服务
[root@server1 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]server1:
1.清空缓存
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# rm -fr *2.修改配置文件
[root@server1 mysql]# vim /etc/my.cnf
29 server_id=1
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
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="8e2b8a90-9c7d-11e8-969e-525400efdb91" ##UUID
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.8.1:24901"
43 loose-group_replication_group_seeds="172.25.8.1:24901,172.25.8.2:24901,17 2.25.8.3:24901"
44 loose-group_replication_bootstrap_group= off
45 loose-group_replication_single_primary_mode=off
46 loose-group_replication_enforce_update_everywhere_checks=on
47 loose-group_replication_ip_whitelist="172.25.8.0/24,127.0.0.1/8"
[root@server1 mysql]# /etc/init.d/mysqld start
3.初始化mysql
[root@server1 mysql]# cat /var/log/mysqld.log |grep password #查看原始密码
[root@server1 mysql]# mysql -p #利用初始密码登陆mysql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log
Copyright (c) 2000, 2016, 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.
4.组复制配置
mysql> alter user root@localhost identified by 'Westos+123'; #更改密码
Query OK, 0 rows affected (0.15 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> set sql_log_bin=0; #关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rpl_user@'%' identified by 'Westos+123'; #授权
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.00 sec)
mysql> reset master; #重置
Query OK, 0 rows affected (0.32 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+123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.75 sec)
mysql> install plugin group_replication soname 'group_replication.so'; #安装组复制插件
Query OK, 0 rows affected (0.59 sec)
mysql> show plugins;
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> stop group_replication;
Query OK, 0 rows affected (0.00 sec)
mysql> set global group_replication_ip_whitelist='172.25.8.0/24,127.0.0.1/8';
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication; #开启组复制
Query OK, 0 rows affected (1.54 sec)
mysql> select * from performance_schema.replication_group_members; #查看组复制信息
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 47a0c94d-9c7c-11e8-9f77-525400cfed2c | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)server1组复制信息，状态为ONLINE
测试：
mysql> create database test;
Query OK, 1 row affected (0.14 sec)
mysql> use test;
Database changed
mysql> create table t1 (c1 int primary key,c2 text not null);
Query OK, 0 rows affected (0.61 sec)
mysql> insert into t1 values (1,'Luis');
Query OK, 1 row affected (0.16 sec)
mysql> select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
+----+------+
1 row in set (0.00 sec)server2：
1.清空缓存
[root@server2 ~]# cd /var/lib/mysql
[root@server2 mysql]# rm -fr *2.修改配置文件
[root@server2 mysql]# vim /etc/my.cnf
29 server-id=2 #修改为2
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
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="8e2b8a90-9c7d-11e8-969e-525400efdb91"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.8.2:24901" #更改ip
43 loose-group_replication_group_seeds="172.25.8.1:24901,172.25.8.2:24901,172.25.8.3:24901"
44 loose-group_replication_bootstrap_group= off
45 loose-group_replication_single_primary_mode=off
46 loose-group_replication_enforce_update_everywhere_checks=on
47 loose-group_replication_ip_whitelist="172.25.8.0/24,127.0.0.1/8"
[root@server2 mysql]# /etc/init.d/mysqld start
3.初始化mysql
[root@server2 mysql]# cat /var/log/mysqld.log |grep password
[root@server2 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log
Copyright (c) 2000, 2016, 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.
4.组复制配置
mysql> alter user root@localhost identified by 'Westos+123';
Query OK, 0 rows affected (0.63 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rpl_user@'%' identified by 'Westos+123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.58 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+123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.80 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.58 sec)
mysql> start group_replication;
Query OK, 0 rows affected (6.75 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 25d01958-9c8b-11e8-ba1f-525400fd9fef | server2 | 3306 | ONLINE |
| group_replication_applier | dd9bf855-9c89-11e8-b06b-525400cfed2c | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)server1，server2组复制信息，状态为ONLINE
server3：
与server2配置相同
1.清空缓存
[root@server3 ~]# cd /var/lib/mysql
[root@server3 mysql]# rm -fr *2.修改配置文件
[root@server2 mysql]# vim /etc/my.cnf
29 server-id=3 #修改为3
30 gtid_mode=ON
31 enforce_gtid_consistency=ON
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="8e2b8a90-9c7d-11e8-969e-525400efdb91"
41 loose-group_replication_start_on_boot=off
42 loose-group_replication_local_address= "172.25.8.3:24901" #更改ip
43 loose-group_replication_group_seeds="172.25.8.1:24901,172.25.8.2:24901,172.25.8.3:24901"
44 loose-group_replication_bootstrap_group= off
45 loose-group_replication_single_primary_mode=off
46 loose-group_replication_enforce_update_everywhere_checks=on
47 loose-group_replication_ip_whitelist="172.25.8.0/24,127.0.0.1/8"
[root@server2 mysql]# /etc/init.d/mysqld start3.初始化mysql
[root@server3 mysql]# cat /var/log/mysqld.log |grep password
[root@server3 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log
Copyright (c) 2000, 2016, 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.
4.组复制配置
mysql> alter user root@localhost identified by 'Westos+123';
Query OK, 0 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rpl_user@'%' identified by 'Westos+123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.44 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+123' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.90 sec)
mysql> install plugin group_replication soname 'group_replication.so';
Query OK, 0 rows affected (0.29 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.40 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 25d01958-9c8b-11e8-ba1f-525400fd9fef | server2 | 3306 | ONLINE |
| group_replication_applier | 36acd5f1-9c8c-11e8-b1d8-525400efdb91 | server3 | 3306 | ONLINE |
| group_replication_applier | dd9bf855-9c89-11e8-b06b-525400cfed2c | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)server1，server2，server3组复制信息，状态为ONLINE
MYSQL组复制配置成功
扫一扫
606
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
