mysql5.7 组复制 多主_MySQL 5.7组复制实践-多主模式

这是官方出品类似 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，是为了标示以后加入集群的服务器都已这台服务器为基准，只在某一个节点上使用。本例是以第一个节点为准。