mysql group replication集群搭建

注： Limitations--------mysql5.7.17 group relication限制

     1.存储引擎必须为innodb

     2.每个表必须提供主键

     3.只支持ipv4，网络需求较高

     4.一个group最多只能有9台服务器

     5.不支持Replication event checksums，

     6.不支持Savepoints

     7.multi-primary mode部署方式不支持SERIALIZABLE事务隔离级别

     8.multi-primary mode部署方式不能完全支持级联外键约束

     9.multi-primary mode部署方式不支持在不同节点上对同一个数据库对象并发执行DDL(在 不同节点上对同一行并发进行RW事务，后发起的事务会失败)

详细参考英文文档:

http://mysqlhighavailability.com/mysqlha/gr/doc/limitations.html#requirements

http://mysqlhighavailability.com/mysqlha/gr/doc/limitations.html#limitations

搭建步骤:

1、mysql5.7.17官网下载地址:

http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

 

2、安装mysql5.7.17数据库:

./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data

--no-defaults:不从选项文件读取默认选项

--initialize:初始化

--explicit_defaults_for_timestamp:时间戳问题（不加这个参数安装会有提醒）

 

3、server1(192.168.1.231):

my3308.cnf文件:

[mysqld]

#base config 基础配置信息

server-id = 1

basedir=/usr/local/mysql5.7/

datadir=/usr/local/mysql5.7/data

user=mysql

explicit_defaults_for_timestamp

socket=/tmp/mysql3308.sock

port = 3308

#binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID

log-bin=mysql-bin

binlog-format = ROW

gtid-mode = ON

enforce-gtid-consistency = ON

log-slave-updates = ON

master-info-repository = TABLE

relay-log-info-repository = TABLE

binlog-checksum = NONE

#group replication

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "192.168.1.231:24901"

loose-group_replication_group_seeds= "192.168.1.231:24901,192.168.1.232:24901,192.168.1.233:24901"

loose-group_replication_bootstrap_group= off

loose-group_replication_single_primary_mode=FALSE   ###本次搭建的是mutil_mode

loose-group_replication_enforce_update_everywhere_checks= TRUE

启动mysql5.7:  ./bin/mysqld_safe --defaults-file=/etc/my3308.cnf --user=mysql &

修改登录密码:  ./bin/mysqladmin -S /tmp/mysql3308.sock -u root -p password

进入mysql创建授权用户:

>SET SQL_LOG_BIN=0;          ###创建授权用户不写入bin_log

>CREATE USER rpl_user@'%';

>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; #创建授权用户

>FLUSH PRIVILEGES;

>SET SQL_LOG_BIN=1;

>CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

安装mysql group replication:

> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

>SHOW PLUGINS;  ###查看group_replication插件安装情况,需要存在下面项

启动mysql group replication:

>SET GLOBAL group_replication_bootstrap_group=ON;   ###第一台server启动需要设置，重启也一样，详细见英文文档

>START GROUP_REPLICATION;     ###启动

>SET GLOBAL group_replication_bootstrap_group=OFF;  ###启动后关闭

>SELECT * FROM performance_schema.replication_group_members;  ###查看group在线机器

创建测试数据库：

> CREATE DATABASE test;

> use test

> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);

> INSERT INTO t1 VALUES (1, 'Luis');

>SHOW BINLOG EVENTS;    ###查看binlog事件

详细解析参考英文文档:

http://mysqlhighavailability.com/mysqlha/gr/doc/getting_started.html

 

4、server2(192.168.1.232):

my3308.cnf文件:

[mysqld]

#base config 基础配置信息

server-id = 2

basedir=/usr/local/mysql5.7/

datadir=/usr/local/mysql5.7/data

user=mysql

explicit_defaults_for_timestamp

socket=/tmp/mysql3308.sock

port = 3308 

#binlog binlog配置，Group Replication是要根据GTID来进行同步的，所以需要开启GTID

log-bin=mysql-bin

binlog-format = ROW

gtid-mode = ON

enforce-gtid-consistency = ON

log-slave-updates = ON

master-info-repository = TABLE

relay-log-info-repository = TABLE

binlog-checksum = NONE

#group replication

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_local_address= "192.168.1.232:24901"

loose-group_replication_group_seeds= "192.168.1.231:24901,192.168.1.232:24901,192.168.1.233:24901"

loose-group_replication_bootstrap_group= off

loose-group_replication_single_primary_mode=FALSE

loose-group_replication_enforce_update_everywhere_checks= TRUE

启动mysql5.7:  ./bin/mysqld_safe --defaults-file=/etc/my3308.cnf --user=mysql &

修改登录密码:  ./bin/mysqladmin -S /tmp/mysql3308.sock -u root -p password

进入mysql创建授权用户:

>SET SQL_LOG_BIN=0;          ###创建授权用户不写入bin_log

>CREATE USER rpl_user@'%';

>GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; #创建授权用户

>FLUSH PRIVILEGES;

>SET SQL_LOG_BIN=1;

>CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

安装mysql group replication:

> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

>START GROUP_REPLICATION;

出现报错:

解决方法:每台机器做hosts解析组内其他机器ip对应的主机名

>START GROUP_REPLICATION;  ###重启

还有报错:

解决方法:set global group_replication_allow_local_disjoint_gtids_join=ON;  #兼容加入组

>START GROUP_REPLICATION;  ###重新启动ok

>SELECT * FROM performance_schema.replication_group_members;  查看是否已经加入group

###查看是否已经同步server1建立的test数据库

> show databases;

>select * from test.t1;

 

server3(192.168.1.233)  ###跟server2原理一样。

参考英文文档:http://mysqlhighavailability.com/mysqlha/gr/doc/