MySQL MGR-组复制安装配置
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:192.168.67.153
server2:192.168.67.152
server3:192.168.67.151
server1,server2,server3都停止mysql服务
server1:
1.清空缓存
[root@server1 ~]# cd /var/lib/mysql
[root@server1 mysql]# rm -fr *
2.修改配置文件
[root@server1 mysql]# vim /etc/my.cnf
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
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
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="8e2b8a90-9c7d-11e8-969e-525400efdb91" ##UUID
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.67.153:24901"
loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"
3.初始化mysql
[root@server1 mysql]# /etc/init.d/mysqld start
[root@server1 mysql]# cat /var/log/mysqld.log |greppassword #查看原始密码
[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.18-log
Copyright (c) 2000, 2016, Oracle and/orits affiliates. All rights reserved.
Oracle is a registered trademark ofOracle Corporation and/or its
affiliates. Other names may betrademarks of their respective
owners.
Type 'help;' or'\h' for help. Type '\c' to clear thecurrent 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 *.* torpl_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 tomaster_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 globalgroup_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 globalgroup_replication_ip_whitelist='192.168.67.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 * fromperformance_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 notnull);
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
server-id=2 #修改为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
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="8e2b8a90-9c7d-11e8-969e-525400efdb91"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.67.152:24901" #更改ip
loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"
3.初始化mysql
[root@server2 mysql]# /etc/init.d/mysqld start
[root@server2 mysql]# cat /var/log/mysqld.log |greppassword
[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.18-log
Copyright (c) 2000, 2016, Oracle and/orits affiliates. All rights reserved.
Oracle is a registered trademark ofOracle Corporation and/or its
affiliates. Other names may betrademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
4.组复制配置
mysql> alter user root@localhostidentified 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 tomaster_user='rpl_user',master_password='Westos+123'for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings(0.80 sec)
mysql> install plugingroup_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 * fromperformance_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
server-id=3 #修改为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
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="8e2b8a90-9c7d-11e8-969e-525400efdb91"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.67.151:24901" #更改ip
loose-group_replication_group_seeds="192.168.67.153:24901,192.168.67.152:24901,192.168.67.151:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everywhere_checks=on
loose-group_replication_ip_whitelist="192.168.67.0/24,127.0.0.1/8"
[root@server2 mysql]# /etc/init.d/mysqld start
3.初始化mysql
[root@server3 mysql]# cat /var/log/mysqld.log |greppassword
[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.18-log
Copyright (c) 2000, 2016, Oracle and/or its affiliates.All rights reserved.
Oracle is a registered trademark of Oracle Corporationand/or its
affiliates. Other names may be trademarks of theirrespective
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 *.* torpl_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 * fromperformance_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 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
server1，server2，server3组复制信息，状态为ONLINE
3 rows in set (0.00 sec)
MYSQL组复制配置成功
