MySQL Group Replication(MGR)是MySQL 5.7引进的高可用扩展，以插件形式提供。MGR具有以下特点： 高一致性：MGR基于分布式paxos协议实现组复制，确保数据一致性。 高可用性：提供了故障检测及故障切换，只要超半数节点存活即可正常工作 高扩展性：MGR能够自由添加或删除节点，加入节点后自动同步数据 高灵活性：MGR支持单主和多主模式，可以来回自动切换模式
MGR使用限制
innodb引擎
表必须要有主键或者非空的唯一键，否则数据会写入失败
MGR目前仅支持IPV4，对网络延迟带宽方面要求比较高
MGR不支持SERIALIZABLE隔离级别，官方建议使用RC
多主模式下，不建议使用级联外键约束，可能造成冲突无法检测
不支持超大事务
多主模式下可能导致死锁，比如多个节点发起select for update，由于多节点锁不共享，很容易导致死锁的情况
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';
选择主节点启动复制
mysql> set global group_replication_bootstrap_group=ON; mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF;
其它节点加入复制组
mysql> start group_replication;
查看复制状态
mysql> SELECT * FROM performance_schema.replication_group_members;
查看主节点
mysql> select variable_value from performance_schema.global_status where variable_name = 'group_replication_primary_member';
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';
启动复制
mysql> set global group_replication_bootstrap_group=ON; mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF;
其它节点加入复制
mysql> start group_replication;
查看组复制状态
mysql> SELECT * FROM performance_schema.replication_group_members;
模式切换
单主切多主
停止复制
mysql> stop group_replication
关闭单主
mysql> set global group_replication_single_primary_mode=OFF;
启动复制
mysql> set global group_replication_bootstrap_group=ON; mysql> start group_replication; mysql> set global group_replication_bootstrap_group=OFF;
多主切单主
停止复制
mysql> stop group_replication;
关闭一致性检查
mysql> set global group_replication_enforce_update_everywhere_checks=OFF;
开启单主模式
mysql> set global group_replication_single_primary_mode=ON;
主节点启动组复制
mysql> set global group_replication_bootstrap_group=ON; mysql> start group_replication mysql> set global group_replication_bootstrap_group=OFF;
mysql> grant select on sys.* to monitor@'%' identified by 'Abcd123#'; mysql> grant all on test.* to vkapp@'%' identified by 'Abcd123#'; mysql> flush privileges;
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);
保存并应用
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查看数据库
$ mysql -uvkapp -pAbcd123# -h127.0.0.1 –P6033 vkapp@(none) 14:01> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+
ProxySQL监控MySQL
admin@(none) 11:39> select * from mysql_server_group_replication_log order by time_start_us desc limit 5 ;
数据库添加监控视图
$ 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;$$
$ 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;
查看路由状态
模拟节点故障
$ /etc/init.d/mysqld stop
查看节点状态，163升级为主节点，hostgroup变成写入组，161变成offline
恢复故障节点
$ /etc/init.d/mysqld start $ mysql -uroot -p sql> start group_replication;
)