MGR集群添加低版本mysql_MYSQL MGR 集群

MYSQL的 RAC来了! 除了无法共享内存外,基本上跟ORACLE RAC 类似!

MGR 配置非常简单,比PXC  SO EASY!

MGR的限制

仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测；

必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set

COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景

目前一个MGR集群最多支持9个节点

不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚

二进制日志不支持binlog event checksum

这个MGR提交图,在事务提交前,也事务执行后各个节点都要检查一下. 写BINLOG的时候,其他节点同时写RELAY LOG. 其实这可以看作MASTER-SLAVE的完全同步版本.

事务所在的节点当做是MASTER,其他节点是SLAVE. 这样节点相互都是对方的SLAVE,也同时是自己事务的MASTER.

我们拿最开始的 5.7来说

Server version: 5.7.19-log MySQL Community Server (GPL)

我把上次配了主从库 3个 通过VBOX复制了3份. 原来搭建的是主从,反向复制.

IP 地址如下 :

192.168.2.21

192.168.2.22

192.168.2.22

我们先干21

关掉MYSQL : service mysqld stop

编辑配置文件, 文件很长 截取重点部分

#----binlog-----------#

log_bin=/u01/mysqllogs/mylogbin.log

log_slave_updates=ON

binlog_format=ROW  #必须是ROW

sync_binlog = 1

expire_logs_days = 15

max_binlog_cache_size = 128M

max_binlog_size = 500M

binlog_cache_size = 64k

binlog_checksum=NONE #这个也要

#----MASTER---SLAVE-----#

server_id=21

gtid_mode=ON

enforce_gtid_consistency=on

master_info_repository=TABLE

relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64#以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。

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

#MGR组名字

loose-group_replication_start_on_boot=OFF

#启动时候不开启MGR

loose-group_replication_local_address= "192.168.2.21:33061"

#本节点IP

loose-group_replication_group_seeds= "192.168.2.21:33061,192.168.2.22:33061,192.168.2.23:33061"

#MGR组所有节点IP

loose-group_replication_bootstrap_group=OFF

report_host=192.168.2.21

report_port=3306

loose-group_replication_single_primary_mode = off  #关闭单主模式的参数

loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数

SERVICE MYSQLD START

....  很幸运一切正常

# 安装MGR插件

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#这句只有server21,在第一次执行引导组的时候执行.

SET GLOBAL group_replication_bootstrap_group=ON;

#创建复制账号

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';

#开启复制隧道

CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

#启动组复制

START GROUP_REPLICATION;

#LOG没有毛病

mysql>  SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

1 row in set (0.00 sec)

5.7.19 看不到节点角色

开启节点2

参数复制过去 并且修改

report_host=192.168.2.22

loose-group_replication_local_address= "192.168.2.22:33061"

server_id=22

SERVCIE MYSQLD STRAT

# 安装MGR插件

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';

CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

#加入组的问题 事务问题, 各个节点的事务状态不一致. 所有节点加入集群前最好不要修改任何数据.

set global group_replication_allow_local_disjoint_gtids_join=ON;

#启动组

START GROUP_REPLICATION;

mysql>  SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| group_replication_applier | 50c08571-5dff-11e8-ad74-080027cbedd8 | 192.168.2.22 |        3306 | ONLINE       |

| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

依样画葫芦 搞定节点3

# 安装MGR插件

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2%' IDENTIFIED BY 'repl';

CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

set global group_replication_allow_local_disjoint_gtids_join=ON;

START GROUP_REPLICATION;

mysql>  SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

| group_replication_applier | 50c08571-5dff-11e8-ad74-080027cbedd8 | 192.168.2.22 |        3306 | ONLINE       |

| group_replication_applier | ad216d41-5c3a-11e8-8720-08002791d97c | 192.168.2.21 |        3306 | ONLINE       |

| group_replication_applier | ae1c4db7-5f27-11e8-a331-0800275ee196 | 192.168.2.23 |        3306 | RECOVERING   |

+---------------------------+--------------------------------------+--------------+-------------+--------------+

节点3正在恢复中....

#测试

#23

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ilike_org          |

| ilike_worker       |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

#22

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ilike_org          |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

6 rows in set (0.03 sec)

#21

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ilike_org          |

| ilike_worker       |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

7 rows in set (0.04 sec)

#23 创建数据库

mysql> create database MGR_TEST;

#22

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MGR_TEST           |

| ilike_org          |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

#21

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| MGR_TEST           |

| ilike_org          |

| ilike_worker       |

| mysql              |

| performance_schema |

| sharkdb            |

| sys                |

+--------------------+

8 rows in set (0.00 sec)

#在节点23创建表

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 varchar(20));

在节点21插入数据

INSERT INTO t1 VALUES (1, 'lalala');

#3个节点都同步了数据

3个节点都是多主模式!