MySQL

MySQL安装部署之MGR

概述

MySQL Group Replication(MGR)是MySQL 5.7引进的高可用扩展，以插件形式提供。MGR具有以下特点： 高一致性：MGR基于分布式paxos协议实现组复制，确保数据一致性。 高可用性：提供了故障检测及故障切换，只要超半数节点存活即可正常工作 高扩展性：MGR能够自由添加或删除节点，加入节点后自动同步数据 高灵活性：MGR支持单主和多主模式，可以来回自动切换模式

MGR使用限制

  • innodb引擎
  • 表必须要有主键或者非空的唯一键，否则数据会写入失败
  • MGR目前仅支持IPV4，对网络延迟带宽方面要求比较高
  • MGR不支持SERIALIZABLE隔离级别，官方建议使用RC
  • 多主模式下，不建议使用级联外键约束，可能造成冲突无法检测
  • 不支持超大事务
  • 多主模式下可能导致死锁，比如多个节点发起select for update，由于多节点锁不共享，很容易导致死锁的情况
  • 不支持复制过滤
  • MGR最大支持9个节点，超过9个将被拒绝加入

MGR冲突检测 MGR多主模式下，一个事务执行时，并不会做前置检查，但是在提交阶段会进行冲突检测，首先提交的事务获得优先权顺利提交，其它冲突事务则回退。在高并发的场景下大量事务回退导致性能降低，因此官方建议对同一记录的修改应该放在一个节点上，利用本地锁进行同步等待，避免事务冲突造成大量回滚。

MGR节点加入过程 在节点加入时，会在组内生成一个view_change事件，每个节点(包含新增节点)都将该事件写入binlog中。之后新增节点进入下面两个阶段：

一、新节点从现有节点中选取一个贡献者(donor)，如果遇到错误则会选取下一个贡献者，要是全都错误将会sleep一段时间再次重试。随后通过异步复制通道，拉取贡献者的binlog并应用。同时将当前组内的事务信息获取进入缓存队列，当binlog应用完成，异步复制通道关闭，则进入第二阶段。

二、新节点处理缓存的事务，当缓存队列内的信息应用完成，即缓存长度为0时，节点状态变为online。

在第一阶段中，应用binlog的开始位置由新节点的gtid_executed决定，结束位置由view_change事件决定。由于使用传统的异步复制通道，如果新节点的数据太旧可能会出现binlog衔接不上，新节点一直处于RECOVERING状态，多次尝试后则会退出复制组。另一种情况是拉取的binlog太多，应用较慢，导致第二阶段的缓存应用花费太多时间。因此建议在加入复制组前先利用最新的备份作为新节点的基础数据

可参考下列大致步骤：

  1. Innobackupex进行备份
  2. 新建实例，添加group_replication插件
  3. 恢复备份
  4. 启动数据库
  5. 清空relay-log文件，清理slave_master_info和slave_relay_log_info信息
  6. 查看当前GTID是否与xtrabackup_binlog_info记录一致，不一致则设置set gtid_purged
  7. 重启数据库
  8. Change master
  9. Start group_replication

MGR配置

参数要求

  • 参数binlog_checksum设置为NONE
  • 参数master_info_repository设置为TABLE
  • 参数relay_log_repository设置为TABLE
  • 参数log-bin设置binlog存储方式
  • 参数binlog_format设置为ROW
  • 开启GTID：gtid_mode=on
  • 开启log_slave_updates：log_slave_updates=on
  • 配置生成事务标识的算法：transaction_write_set_extraction=’XXHASH64’
  • 开启enforce_gtid_consistency：enforce_gtid_consistency=on
  • 如果开启了并行复制，必须设置slave-parallel-type=LOGICAL_CLOCK和slave-preserve-commit-order=1

安装GROUP REPLICATION插件

1

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

单主模式

在单主模式下，从节点为只读，当主节点宕机会将剩余的成员按UUID来排序选择第一个节点作为主节点。

参数配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

#通知插件它正在加入或创建的组，可通过select UUID()生成随机值
loose-group_replication_group_name="e91c4508-d45a-11e9-911e-005056ab71f1"
#指示插件在服务器启动时不自动引导组操作
loose-group_replication_start_on_boot=OFF
#成员内部通信地址
loose-group_replication_local_address="10.0.139.161:33007"
#设置组成员的信息
loose-group_replication_group_seeds="10.0.139.161:33007,10.0.139.162:33007,10.0.139.163:33007"
#插件是否引导组，只能在一个服务器上使用，通常是第一次引导组或重建MGR使用
loose-group_replication_bootstrap_group=off
#设置为单主模式
loose-group_replication_single_primary_mode=true
#关闭严格一致性检查
loose-group_replication_enforce_update_everywhere_checks=false

注意： 三个节点的server_id，loose-group_replication_local_address，report-host不能一样，其它参数保持一致

配置主从

1
2
3
4
5

mysql> set sql_log_bin=0;
mysql> grant replication slave on *.* to repl@'10.0.139.%' identified by 'Abcd123#';
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> change master to master_user='repl',master_password='Abcd123#' for channel 'group_replication_recovery';

选择主节点启动复制

1
2
3

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;

其它节点加入复制组

1

mysql> start group_replication;

查看复制状态

1

mysql> SELECT * FROM performance_schema.replication_group_members;

查看主节点

1

mysql> select variable_value from performance_schema.global_status where variable_name = 'group_replication_primary_member';

多主模式

在多主模式下，一个节点宕机，请求会直接访问其它节点。

参数配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

#通知插件它正在加入或创建的组，可通过select UUID()生成随机值
loose-group_replication_group_name="e91c4508-d45a-11e9-911e-005056ab71f1"
#指示插件在服务器启动时不自动引导组操作
loose-group_replication_start_on_boot=OFF
#成员内部通信地址
loose-group_replication_local_address="10.0.139.161:33007"
#设置组成员的信息
loose-group_replication_group_seeds="10.0.139.161:33007,10.0.139.162:33007,10.0.139.163:33007"
#插件是否引导组，只能在一个服务器上使用，通常是第一次引导组或重建MGR使用
loose-group_replication_bootstrap_group=off
#设置为非单主模式
loose-group_replication_single_primary_mode=false
#开启严格一致性检查
loose-group_replication_enforce_update_everywhere_checks=true

注意： 三个节点的server_id，loose-group_replication_local_address，report-host不能一样，其它参数保持一致

配置主从

1
2
3
4
5

mysql> set sql_log_bin=0;
mysql> grant replication slave on *.* to repl@'10.0.139.%' identified by 'Abcd123#';
mysql> flush privileges;
mysql> set sql_log_bin=1;
mysql> change master to master_user='repl',master_password='Abcd123#' for channel 'group_replication_recovery';

启动复制

1
2
3

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;

其它节点加入复制

1

mysql> start group_replication;

查看组复制状态

1

mysql> SELECT * FROM performance_schema.replication_group_members;

模式切换

单主切多主

停止复制

1

mysql> stop group_replication

关闭单主

1

mysql> set global group_replication_single_primary_mode=OFF;

启动复制

1
2
3

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;

多主切单主

停止复制

1

mysql> stop group_replication;

关闭一致性检查

1

mysql> set global group_replication_enforce_update_everywhere_checks=OFF;

开启单主模式

1

mysql> set global group_replication_single_primary_mode=ON;

主节点启动组复制

1
2
3

mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication
mysql> set global group_replication_bootstrap_group=OFF;

其它节点加入组复制

1

mysql> start group_replication

Tips：注意：相关参数需要持久化到参数文件中，否则重启则失效

ProxySQL实现读写分离

下载ProxySQL Download ProxySQL

安装ProxySQL

1

rpm -ivh proxysql-2.0.6-1-centos7.x86_64.rpm

创建数据库用户

1
2
3

mysql> grant select on sys.* to monitor@'%' identified by 'Abcd123#';
mysql> grant all on test.* to vkapp@'%' identified by 'Abcd123#';
mysql> flush privileges;

注意：monitor为proxysql监控用户，vkapp为程序用户

编辑配置文件

1
2
3

$ cat /etc/proxysql.conf
monitor_username="monitor"
monitor_password="Abcd123#"

启动ProxySQL

1

$ service proxysql start

进入ProxySQL

1

$ mysql -uadmin -padmin -h127.0.0.1 -P6032

配置监控账号

1
2

admin@(none) 10:54> set mysql-monitor_username='monitor';
admin@(none) 10:54> set mysql-monitor_password='Abcd123#';

ProxySQL指定分组规则

1

admin@(none) 10:56> insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (1,2,3,4,1,1,0,100);
  • writer_hostgroup：定义写入组ID，MGR所有写入阶段都应该包含在该组中
  • backup_writer_hostgroup：定义备用写入组ID，在多主情况下，如果可写入节点超过实际使用的写入节点数量，剩下的节点可在该组中存放
  • reader_hostgroup：该组具存放具有只读属性的MGR节点
  • offline_hostgroup：该组包含所有无法提供服务或者不处于online状态的节点
  • active：ProxySQL将会监察整个集权，并根据hostgroup和节点的属性，进行匹配
  • max_writers：控制MGR多主模式下实际对外提供写服务的节点数量
  • writer_is_also_reader：当启动时写入组的节点同时出现在只读组中，布尔值
  • max_transactions_behind：定义节点最大落后整个集群的事务数量(ProxySQL内部，非MGR)

ProxySQL配置用户

1

admin@(none) 13:48> insert into mysql_users(username,password,default_hostgroup) values('vkapp','Abcd123#',1)

插入后端数据库信息

1
2
3

admin@(none) 11:01> insert into mysql_servers (hostgroup_id,hostname,port) values (1,'10.0.139.161',33006);
admin@(none) 11:16> insert into mysql_servers (hostgroup_id,hostname,port) values (3,'10.0.139.162',33006);
admin@(none) 11:16> insert into mysql_servers (hostgroup_id,hostname,port) values (3,'10.0.139.163',33006);

注意：单主模式模式下仅将主节点加入writer_hostgroup，其它节点加入reader_hostgroup

配置路由规则

1
2

admin@(none) 13:49> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
admin@(none) 13:51> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',3,1);

保存并应用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

admin@(none) 11:16> save mysql servers to disk;
admin@(none) 13:54> save mysql users to disk;
admin@(none) 13:55> save mysql query rules to disk;
admin@(none) 13:55> save mysql variables to disk;
admin@(none) 13:57> save admin variables to disk;
admin@(none) 11:38> load mysql servers to runtime;
admin@(none) 13:57> load mysql users to runtime;
admin@(none) 13:58> load mysql query rules to runtime;
admin@(none) 13:59> load mysql variables to runtime;
admin@(none) 13:59> load admin variables to runtime;

应用应该登陆proxysql查看数据库

1
2
3
4
5
6
7
8

$ mysql -uvkapp -pAbcd123# -h127.0.0.1 –P6033
vkapp@(none) 14:01> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+

ProxySQL监控MySQL

1

admin@(none) 11:39> select * from mysql_server_group_replication_log order by time_start_us desc  limit 5 ;

数据库添加监控视图

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

$ cat addition_to_sys.sql
USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

查看MGR成员信息

1
2
3
4
5
6

root@sys 11:36:  select * from gr_member_routing_candidate_status;
+------------------+-----------+---------------------+-------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+--------
| YES            | NO      |                0 |                 0 |
+------------------+-----------+---------------------+--------+

模拟读写测试

1
2
3
4
5
6
7

$ mysql -uvkapp -pAbcd123# -h127.0.0.1 -P6033
#模拟select
vkapp@(none) 14:01> select * from test.tab1 limit 1;
#模拟update
vkapp@(none) 14:06> update test.tab1 set name='luhengxing' where id=1;
#模拟select for update
vkapp@(none) 14:08> select * from test.tab1 where name='luhengxing' for update;

查看路由状态 MGR_1

模拟节点故障

1

$ /etc/init.d/mysqld stop

查看节点状态，163升级为主节点，hostgroup变成写入组，161变成offline MGR_2

恢复故障节点

1
2
3

$ /etc/init.d/mysqld start
$ mysql -uroot -p
sql> start group_replication;

节点重新加入复制组提供服务 MGR_3

Install
Licensed under CC BY-NC-SA 4.0
comments powered by Disqus