多应用+插件架构,代码干净,二开方便,首家独创一键云编译技术,文档视频完善,免费商用码云13.8K 广告
搭建第一个节点 [root@slave2 ~]# cat /etc/my.cnf server_id = 3 gtid-mode=on enforce-gtid-consistency=on log-bin=mysql-bin log-slave-updates=true master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE binlog_format=ROW # Group Replication configuration 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= "127.0.0.1:33061" loose-group_replication_group_seeds= "192.168.237.131:33061,192.168.237.132:33061" loose-group_replication_bootstrap_group= off 重启mysql /etc/init.d/mysql.server restart 配置复制用户: mysql> CREATE USER 'repl'@'192.168.237.%' IDENTIFIED BY 'mysql'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.237.%'; 执行change master命令: mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery'; 安装组复制插件: mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 启动组复制: mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; 查看组复制成员状态: mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+-------- ------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+-------- ------+ | group_replication_applier | fab79358-4c05-11e7-96aa-000c29e99435 | slave2 | 3308 | ONLINE | mysql> create database test; Query OK, 1 row affected (0.02 sec) mysql> use test; Database changed mysql> create table students(sid int primary key,sname varchar(10),gender int,dept_id int); Query OK, 0 rows affected (0.02 sec) mysql> insert into students values(1,'a',1,1); Query OK, 1 row affected (0.00 sec) mysql> create table students2(sid int,sname varchar(10)); Query OK, 0 rows affected (0.07 sec) mysql> insert into students2 values(1,'a'); ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin. 该表没有主键,插入数据时报错。 添加第二个节点: server_id = 4 log-bin=mysql-bin gtid-mode=on enforce-gtid-consistency=on binlog-format=row log-slave-updates=true master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE binlog_format=ROW # Group Replication configuration 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.237.132:33061" loose-group_replication_group_seeds= "192.168.237.131:33061,192.168.237.132:33061" loose-group_replication_bootstrap_group= off group_replication_allow_local_disjoint_gtids_join=ON #####在加入group之前的binlog记录的信息剔除掉 重启服务 /etc/init.d/mysql.server restart 在第二个节点上创建复制用户 mysql> CREATE USER 'repl'@'192.168.237.%' IDENTIFIED BY 'mysql'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.237.%'; 在第二个节点上执行change master命令 mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='mysql' FOR CHANNEL 'group_replication_recovery'; 在第二个节点上安装插件 mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 在第二个节点上启动组复制 mysql> START GROUP_REPLICATION; 查看两个节点的状态 mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+-------- ------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+-------- ------+ | group_replication_applier | d6f5df86-5d91-11e7-9848-000c29173afe | mycat | 3308 | RECOVERING | | group_replication_applier | fab79358-4c05-11e7-96aa-000c29e99435 | slave2 | 3308 | ONLINE | 在第二个节点上已经能够看到被复制过来的数据: mysql> use test; mysql> select * from students; +-----+-------+--------+---------+ | sid | sname | gender | dept_id | +-----+-------+--------+---------+ | 1 | a | 1 | 1 | 验证数据同步 在第一个节点执行: mysql> create database test2; mysql> use test2; Database changed mysql> create students(sid int primary key,sname varchar(10)); mysql> create table students(sid int primary key,sname varchar(10)); mysql> insert into students values(1,'a'); 在第二个节点查看: mysql> use test2; Database changed mysql> select * from students; +-----+-------+ | sid | sname | +-----+-------+ | 1 | a | 在第二个节点操作: mysql> insert into students values(2,'b'); ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement 从节点不允许插入操作 在已运行的一个组复制集群中增加第三个节点: 在第一个节点上执行mysqldump ./mysqldump --master-data=2 --databases test2 -u root -p >test2.dump 将文件复制到第三个节点上 第三个节点的my.cnf配置和第二个节点相同 配置复制用户 将备份文件应用到第三个节点上 Source /root/test2.dump 安装组复制插件 启动复制 Start group_replication; 查看整个集群: mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+------------- -+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+------------- -+ | group_replication_applier | 7d1a5504-948b-11e7-88e1-000c29173afe | mycat | 3308 | ONLINE | | group_replication_applier | 88befebf-949d-11e7-b8a7-000c2958aaef | slave4 | 3308 | ONLINE | | group_replication_applier | fab79358-4c05-11e7-96aa-000c29e99435 | slave2 | 3308 | ONLINE 多主模式搭建: 在两个节点的/etc/my.cnf中增加以下参数: loose-group_replication_single_primary_mode=FALSE loose-group_replication_enforce_update_everywhere_checks= TRUE 并重启mysql /etc/init.d/mysql.server start 在两个节点上开启start group_replication 查看两个节点的状态都是非read_only模式: mysql> select @@read_only; +-------------+ | @@read_only | +-------------+ | 0 | 在两个节点上分别写入数据都能同步到对方: mysql> insert into students values(3,'a'); mysql> insert into students values(4,'b'); mysql> select * from students; +-----+-------+ | sid | sname | +-----+-------+ | 1 | a | | 2 | b | | 3 | a | | 4 | b |