0%

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