MySQL之MGR

MGR

简介

• MySQL Group Replication（简称 MGR）是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案。
• MySQL 组复制提供了高可用、高扩展、高可靠的 MySQL 集群服务。

MGR特点

高一致性

• 基于原生复制以及paxos协议以及组复制技术
• 并以插件的方式提供,保证数据的一致性

高容错性

• 只要不是大多数节点失效MGR可以继续工作
• 当有多个节点对同一条数据进行操作的时候按照先到者优先的原则进行处理 保证多个线程不会操控同一条数据
• 内置自动化脑裂防护机制

高扩展性

• 节点的添加与移除都是自动的
• 新节点添加向其余节点同步数据 直到新节点与其余节点数据保持一致
• 某节点被移除会自动更新组信息

高灵活性

• 单主模式:会自动选主所有写操作在主上进行
• 多主模式:所有节点可以同时进行读写操作

MGR功能

多主

• 在多组模式下每一个组成员都可以执行读写操作
• 在多组模式下当执行一个事务的时候需要组内每一个成员都认可该事务无冲突才可以进行提交
• 多组模式下当对某一条数据进行操作的时候在组内进行广播 当并发冲突的时候 会按照先操作的提交后操作的回滚

弹性

• 节点的添加与移除都是自动调整
• 当节点有新的成员添加时 新的节点会像其余节点内的成员同步数据 直到数据一致
• 如果移除一个节点 那么剩下的节点会自动更新 不会再向这个节点进行广播事务操作

高同步

• 一个复制组由若干个节点(数据库实例)组成，组内各个节点维护各自的数据副本(Share Nothing)
• 组内成员通过一致性实现原子消息以及全局有序消息保证数据一致

MGR要求

• 存储引擎必须为innodb存储引擎 因为需要使用事务 myisam不支持事务
• 每个表必须有主键，在进行事务冲突检测时需要利用主键值对比
• 必须开启 binlog 且为 row 格式
• 开启 GTID，且主从状态信息存于表中（ --master-info-repository=TABLE 、 --relay-loginfo-repository=TABLE）， --log-slave-updates 打开

GTID

• 全局已提交事务的唯一编号
• GTID=UUID+TID
• UUID是mysql实例一个唯一标识
• TID是mysql已经提交事务的数量 会自增长

3E11FA47-71CA-11E1-9E33-C80AA9429562:23      # GTID

MGR部署

单主模式

实验环境

# 数据库服务器规划
序号 IP 地址 主机名 数据库 端口号 Server ID 操作系统
1 10.1.1.1 master MySQL-5.7.24 33061 100 CentOS 7.5
2 10.1.1.2 slave  MySQL-5.7.24 33062 101 CentOS 7.5
3 10.1.1.4 slave1 MySQL-5.7.24 33063 102 CentOS 7.5

DNS

mysql组复制用的是域名连接 需要在DNS进行声明

[root@master ~]# hostname      # 使用hostname查看当前主机名称
master

# 此时名称与MGR中的MEMBER_HOS保持一致
# 三台服务器都配置dns 此处只显示一处 实际配置三处
[root@master ~]# vim /etc/hosts
      10.1.1.1 master
      10.1.1.2 slave
      10.1.1.4 slave1

master配置文件

[root@master ~]# vim /etc/my.cnf

      # Group Replication
      server_id = 100 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 
      loose-group_replication_bootstrap_group = off	# 不启用引导组

slave配置文件

[root@slave~]# vim /etc/my.cnf
      
      # Group Replication
      server_id = 101 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 

[root@slave1~]# vim /etc/my.cnf
      
      # Group Replication
      server_id = 102 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录      
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 
      loose-group_replication_bootstrap_group = off	# 不启用引导组

master数据库

# 设置授权账号
mysql> grant replication slave on *.* to mgr@'10.1.1.%' identified by 'root123';

# 构建mgr集群
mysql> change master to master_user='mgr',master_password='root123' for channel 'group_replication_recovery';

# 安装mgr插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';

# 查看插件
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
|      |
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+--------- 

# 设置 group_replication_bootstrap_group 为 ON 是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。
mysql> set global group_replication_bootstrap_group=ON;	

# 开启mgr
mysql> start group_replication;

# 关闭自启动
mysql> set global group_replication_bootstrap_group=OFF;

# 查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 03c2d881-c5ce-11ea-b3bb-000c2900170d | master      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

slave数据库

# 设置授权账号
mysql> grant replication slave on *.* to mgr@'10.1.1.%' identified by 'root123';

# 构建mgr集群
mysql> change master to master_user='mgr',master_password='root123' for channel 'group_replication_recovery';

# 安装mgr插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';

# 查看插件
mysql> show plugins;

#添加到复制组（不用再设置启动，该组已在是master时启动）
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

# 开启mgr
mysql> start group_replication;

# 查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 03c2d881-c5ce-11ea-b3bb-000c2900170d | master      |        3306 | ONLINE       |
| group_replication_applier | ba3d51a8-cd7f-11ea-b790-000c29a27111 | slave1      |        3306 | ONLINE       |
| group_replication_applier | f98c5345-c5cf-11ea-9a8a-000c2997bf13 | slave       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

数据测试

# 在master上创建数据库
mysql> create database test;      
Query OK, 1 row affected (0.01 sec)

mysql> show databases;      # slave查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |      # 同步的test数据库
+--------------------+

# master插入数据并查看
mysql> create table test(id int primary key,name varchar(254));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values(1,'SR');
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | SR   |
+----+------+

# slave查看数据
mysql> show tables;      # 查看到同步的表
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test;      # 同步的数据
+----+------+
| id | name |
+----+------+
|  1 | SR   |
+----+------+

# slave进行数据插入
mysql> insert into test values(2,'MZ');      # 数据报错slave只允许读取数据 不允许插入数据
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

# master查看只读
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |      # 只读关闭
+---------------+-------+

# slave查看只读
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |      # 只读开启
+---------------+-------+

多主模式

实验环境

# 数据库服务器规划
序号 IP 地址 主机名 数据库 端口号 Server ID 操作系统
1 10.1.1.1 master MySQL-5.7.24 3306 100 CentOS 7.5
2 10.1.1.2 slave  MySQL-5.7.24 3306 101 CentOS 7.5
3 10.1.1.4 slave1 MySQL-5.7.24 3306 102 CentOS 7.5

DNS

mysql组复制用的是域名连接 需要在DNS进行声明

[root@master ~]# hostname      # 使用hostname查看当前主机名称
master

# 此时名称与MGR中的MEMBER_HOS保持一致
# 三台服务器都配置dns 此处只显示一处 实际配置三处
[root@master ~]# vim /etc/hosts
      10.1.1.1 master
      10.1.1.2 slave
      10.1.1.4 slave1

master配置文件

[root@master ~]# vim /etc/my.cnf

      # Group Replication
      server_id = 100 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 
      loose-group_replication_bootstrap_group = off	# 不启用引导组
      loose-group_replication_single_primary_mode=FALSE	# 关闭单主模式
      loose-group_replication_enforce_update_everywhere_checks= TRUE	# 更新的时候启动一致性校验

slave配置文件

[root@slave~]# vim /etc/my.cnf
      
      # Group Replication
      server_id = 101 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 
      loose-group_replication_single_primary_mode=FALSE	# 关闭单主模式
      loose-group_replication_enforce_update_everywhere_checks= TRUE	# 更新的时候启动一致性校验

[root@slave1~]# vim /etc/my.cnf
      
      # Group Replication
      server_id = 102 #服务 ID
      gtid_mode = ON #全局事务
      enforce_gtid_consistency = ON #强制 GTID 的一致性
      master_info_repository = TABLE #将 master.info 元数据保存在系统表中
      relay_log_info_repository = TABLE #将 relay.info 元数据保存在系统表中
      binlog_checksum = NONE #禁用二进制日志事件校验
      log_slave_updates = ON #级联复制
      log_bin = binlog #开启二进制日志记录
      binlog_format = ROW #以行的格式记录      
      transaction_write_set_extraction = XXHASH64 #使用哈希算法将其编码为散列
      loose-group_replication_group_name = '40036749-d098-11ea-a12c-000c2900170d' #加入的组名
      loose-group_replication_start_on_boot = off #不自动启用组复制集群
      loose-group_replication_local_address = 'master:33061' #以本机端口 33061 接受来自组中成员的传入连接
      loose-group_replication_group_seeds = 'master:33061, slave:33062,slave1:33063' #组中成员访问表
      loose-group_replication_bootstrap_group = off #不启用引导组 
      loose-group_replication_bootstrap_group = off	# 不启用引导组
      loose-group_replication_single_primary_mode=FALSE	# 关闭单主模式
      loose-group_replication_enforce_update_everywhere_checks= TRUE	# 更新的时候启动一致性校验

master数据库

# 设置授权账号
mysql> grant replication slave on *.* to mgr@'10.1.1.%' identified by 'root123';

# 构建mgr集群
mysql> change master to master_user='mgr',master_password='root123' for channel 'group_replication_recovery';

# 安装mgr插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';

# 查看插件
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
|      |
| group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
+----------------------------+----------+--------------------+----------------------+--------- 

# 设置 group_replication_bootstrap_group 为 ON 是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。
mysql> set global group_replication_bootstrap_group=ON;	

# 开启mgr
mysql> start group_replication;

# 关闭自启动
mysql> set global group_replication_bootstrap_group=OFF;

# 查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 03c2d881-c5ce-11ea-b3bb-000c2900170d | master      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

slave数据库

# 设置授权账号
mysql> grant replication slave on *.* to mgr@'10.1.1.%' identified by 'root123';

# 构建mgr集群
mysql> change master to master_user='mgr',master_password='root123' for channel 'group_replication_recovery';

# 安装mgr插件
mysql> install PLUGIN group_replication SONAME 'group_replication.so';

# 查看插件
mysql> show plugins;

#添加到复制组（不用再设置启动，该组已在是master时启动）
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

# 开启mgr
mysql> start group_replication;

# 查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 03c2d881-c5ce-11ea-b3bb-000c2900170d | master      |        3306 | ONLINE       |
| group_replication_applier | ba3d51a8-cd7f-11ea-b790-000c29a27111 | slave1      |        3306 | ONLINE       |
| group_replication_applier | f98c5345-c5cf-11ea-9a8a-000c2997bf13 | slave       |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+

测试数据

# 在master上创建数据库
mysql> create database test;      
Query OK, 1 row affected (0.01 sec)

mysql> show databases;      # slave查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |      # 同步的test数据库
+--------------------+

# master插入数据并查看
mysql> create table test(id int primary key,name varchar(254));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values(1,'SR');
Query OK, 1 row affected (0.05 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | SR   |
+----+------+

# slave查看数据
mysql> show tables;      # 查看到同步的表
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test;      # 同步的数据
+----+------+
| id | name |
+----+------+
|  1 | SR   |
+----+------+

# slave进行数据插入
mysql> insert into test values(2,'MZ');      # 数据报错slave只允许读取数据 不允许插入数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | SR   |
|  2 | MZ   |
+----+------+
2 rows in set (0.00 sec)

# 在master查看slave插入的数据

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | SR   |
|  2 | MZ   |
+----+------+
2 rows in set (0.00 sec)

# master查看只读状态
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+

# slave查看只读状态
mysql> show variables like 'read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+