搭建 · 王康宁的笔记

搭建第一个节点 [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 |