mysql group replication集群搭建

Love The Way You Lie 2022-07-13 02:45 216阅读 0赞

注： 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插件安装情况,需要存在下面项

![Center][]

启动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;

出现报错:

![Center 1][]

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

>START GROUP\_REPLICATION;  \#\#\#重启

还有报错:

![Center 2][]

解决方法:set global group\_replication\_allow\_local\_disjoint\_gtids\_join=ON;  \#兼容加入组

>START GROUP\_REPLICATION;  \#\#\#重新启动ok

>SELECT \* FROM performance\_schema.replication\_group\_members;  查看是否已经加入group

![Center 3][]

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

> show databases;

>select \* from test.t1;

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

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

[Center]: /images/20220713/6b1165d5370047f9834c5902305aa9ed.png
[Center 1]: /images/20220713/633aa05e5fe24cf0a6383259b3dd99c1.png
[Center 2]: /images/20220713/65ed68c638304808adeb30eb31a1f6a2.png
[Center 3]: /images/20220713/4c4c4783e2c649bc8cc76379bf22f12a.png