MySQL 5.7组复制实践-多主模式

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5758012.html

  2016年12月12日，在 5.7.17发布，其中包含期待很久的Group Replication plugin的GA版本。Group Replication是目前最热门的技术话题之一，这是官方出品类似 pxc 的数据库高可用解决方案，影响深远。从异步复制，到半同步复制，到组复制，mysql高可用技术越来越强大。随着mgr的逐渐成熟，将会越来越多的应用MySQL的多种场景中。

MySQL Group Replication is a MySQL Server plugin that provides distributed state machine replication with strong coordination between servers. Servers coordinate themselves automatically, when they are part of the same replication group.

组复制默认是单主模式，本文是关于多主模式的实践。

1.环境规划：

• CentOS6.6
• mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

主机名	IP	端口号	数据目录	组复制端口
mgr01	192.168.0.11	3306	/data/mysql/mysql_3306/{data，logs,tmp}	23306
mgr02	192.168.0.12	3306	/data/mysql/mysql_3306/{data，logs,tmp}	23306
mgr03	192.168.0.13	3306	/data/mysql/mysql_3306/{data，logs,tmp}	23306

2.MySQL 5.7.17安装

tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz -C /opt/mysql/
mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}

cd /usr/local
ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /data/mysql

初始化数据库：
/usr/local/mysqlbin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

3.组复制需求配置
启用group repliacation的前提：
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
binlog_format=ROW

4.组复制参数配置

可以直接在my.cnf中配置，也可以动态在线配置：
set global transaction_write_set_extraction = XXHASH64
set global group_replication_start_on_boot = OFF
set global group_replication_bootstrap_group = OFF
set global group_replication_group_name = b6ddfda0-d8bc-4272-a58f-4ea75acbbc79
set global group_replication_local_address = '192.16.0.11:23306'
set global group_replication_group_seeds = '192.16.0.11:23306,192.16.0.12:23306,192.16.0.13:23306'

开启多主模式的参数：
set global group_replication_single_primary_mode=FALSE
set global group_replication_enforce_update_everywhere_checks=TRUE

5.创建复制账户

"root@localhost:mysql3306.sock  [(none)]>SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>CREATE USER repl@'%';
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

6.安装组复制插件

"root@localhost:mysql3306.sock  [(none)]>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)

7.启动组复制

"root@localhost:mysql3306.sock  [(none)]>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (1.13 sec)

注：group_replication_bootstrap_group参数设置为ON，是为了标示以后加入集群的服务器都已这台服务器为基准，只在某一个节点上使用。本例是以第一个节点为准。

检查组复制状态：
"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

8.创建测试数据(略)
9.加入第二个节点mgr02，步骤如下:

"root@localhost:mysql3306.sock  [(none)]>SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>CREATE USER repl@'%';
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected, 1 warning (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

"root@localhost:mysql3306.sock  [(none)]>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)

"root@localhost:mysql3306.sock  [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (5.62 sec)

"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1a30ac44-d4e7-11e6-9bd3-080027f57e91 | mgr02       |        3306 | ONLINE       |
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

10.加入第三个节点mgr03
步骤与上一步(8)相同
"root@localhost:mysql3306.sock  [(none)]>CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log

"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>SET GLOBAL group_replication_group_name = "b6ddfda0-d8bc-4272-a58f-4ea75acbbc79";
Query OK, 0 rows affected (0.00 sec)

"root@localhost:mysql3306.sock  [(none)]>
"root@localhost:mysql3306.sock  [(none)]>START GROUP_REPLICATION;
Query OK, 0 rows affected (2.27 sec)

"root@localhost:mysql3306.sock  [(none)]>SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 1a30ac44-d4e7-11e6-9bd3-080027f57e91 | mgr02       |        3306 | ONLINE       |
| group_replication_applier | 28a76053-d4e7-11e6-9d54-080027231a47 | mgr03       |        3306 | ONLINE       |
| group_replication_applier | 8d4ccab2-d4e6-11e6-a83c-080027feaf82 | mgr01       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

11.检查和验证
(1)检查节点1的测试数据，是否同步到节点2和节点3(略)
(2)在多主上进行dml操作检测

Multi-Master Group Replication的集群到这里就完成搭建啦...

--The end