高可用 proxysql + mysql MGR
MGR 配置
安装
系统:centos 7.9
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only ps-8x-innovation release
percona-release enable tools release
yum install percona-server-server
第一台机器配置
# mgr
# basic configure
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON
######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.128:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON
操作
-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
-- 创建复制用户后,必须提供 服务器的用户凭证,用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';
-- 查看group_replication是否加载
SHOW PLUGINS;
SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
-- 启动组复制
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- 查看组复制状态
SELECT * FROM performance_schema.replication_group_members;
第二台机器配置
# mgr
# basic configure
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON
######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.139:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=40
group_replication_single_primary_mode=ON
操作
-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
-- 创建复制用户后,必须提供 服务器的用户凭证,用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';
-- 查看group_replication是否加载
SHOW PLUGINS;
SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
-- 加入组复制
START GROUP_REPLICATION;
第三台机器配置
# mgr
# basic configure
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
log_replica_updates=ON
######################### MGR ##########################
plugin_load_add='group_replication.so'
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.30.140:3406"
loose-group_replication_group_seeds= "192.168.30.128:3406,192.168.30.139:3406,192.168.30.140:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=40
group_replication_single_primary_mode=ON
操作
-- 创建 分布式恢复的复制用户
SET SQL_LOG_BIN=0;
CREATE USER dba_repl@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT REPLICATION SLAVE ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
-- 创建复制用户后,必须提供 服务器的用户凭证,用于分布式 恢复。
CHANGE REPLICATION SOURCE TO SOURCE_USER='dba_repl',
SOURCE_PASSWORD='MA6RuouuZZn4x_Hd'
FOR CHANNEL 'group_replication_recovery';
-- 查看group_replication是否加载
SHOW PLUGINS;
SELECT PLUGIN_NAME,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_LIBRARY,PLUGIN_LICENSE FROM information_schema.plugins WHERE PLUGIN_NAME='group_replication';
-- 加入组复制
START GROUP_REPLICATION;
# 查看成员状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | bba9be3c-6b5d-11ef-ad42-000c2915875d | mysql_2 | 3306 | ONLINE | PRIMARY | 8.3.0 | XCom |
| group_replication_applier | d270a98c-6b2e-11ef-bc60-000c29e07cfa | mysql_1 | 3306 | ONLINE | SECONDARY | 8.3.0 | XCom |
| group_replication_applier | ef278bf7-6b5d-11ef-9936-000c2939881a | mysql_3 | 3306 | ONLINE | SECONDARY | 8.3.0 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)
输出结果中主要几个列的解读如下:
MEMBER_ID
列值就是各节点的 server_uuid,用于唯一标识每个节点,在命令行模式下,调用 udf 时传入 MEMBER_ID 以指定各节点。MEMBER_ROLE
表示各节点的角色,如果是 PRIMARY 则表示该节点可接受读写事务,如果是 SECONDARY 则表示该节点只能接受只读事务。如果只有一个节点是 PRIMARY,其余都是 SECONDARY,则表示当前处于 单主模式;如果所有节点都是 PRIMARY,则表示当前处于 多主模式。MEMBER_STATE
表示各节点的状态,共有几种状态:ONLINE、RECOVERING、OFFLINE、ERROR、UNREACHABLE 等,下面分别介绍几种状态。ONLINE
,表示节点处于正常状态,可提供服务。RECOVERING
,表示节点正在进行分布式恢复,等待加入集群,这时候有可能正在从donor节点利用clone复制数据,或者传输binlog中。OFFLINE
,表示该节点当前处于离线状态。提醒,在正要加入或重加入集群时,可能也会有很短瞬间的状态显示为 OFFLINE。ERROR
,表示该节点当前处于错误状态,无法成为集群的一员。当节点正在进行分布式恢复或应用事务时,也是有可能处于这个状态的。当节点处于ERROR状态时,是无法参与集群事务裁决的。节点正在加入或重加入集群时,在完成兼容性检查成为正式MGR节点前,可能也会显示为ERROR状态。UNREACHABLE
,当组通信消息收发超时时,故障检测机制会将本节点标记为怀疑状态,怀疑其可能无法和其他节点连接,例如当某个节点意外断开连接时。当在某个节点上看到其他节点处于 UNREACHABLE 状态时,有可能意味着此时部分节点发生了网络分区,也就是多个节点分裂成两个或多个子集,子集内的节点可以互通,但子集间无法互通。- 当节点的状态不是 ONLINE 时,就应当立即发出告警并检查发生了什么。
在节点状态发生变化时,或者有节点加入、退出时,表 performance_schema.replication_group_members 的数据都会更新,各节点间会交换和共享这些状态信息,因此可以在任意节点查看。
proxysql
proxysql 安装
yum install proxysql
systemctl start proxysql
systemctl enable proxysql
proxysql 配置
添加组配置
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 (
2, -- writer_hostgroup (写操作主机组)
4, -- backup_writer_hostgroup (备份写操作主机组)
3, -- reader_hostgroup (读操作主机组)
1, -- offline_hostgroup (离线主机组)
1, -- active (激活状态)
1, -- max_writers (最大写操作主机数)
1, -- writer_is_also_reader (写主机是否也充当读操作主机)
1000 -- max_transactions_behind (最大滞后事务数)
);
-- 加载到RUNTIME,保存到disk
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
配置监控
要在 MySQL 中创建用户,请连接到 PRIMARY 并执行
CREATE USER 'monitor'@'%' IDENTIFIED BY 'Monitor@123456';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
在proxysql 中执行
set mysql-monitor_username='monitor';
set mysql-monitor_password='Monitor@123456';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Monitor@123456' WHERE variable_name='mysql-monitor_password';
--- 配置各种监控间隔
-- UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
-- 加载到RUNTIME,保存到disk
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
添加后端
INSERT INTO mysql_servers (
hostgroup_id,
hostname,
port,
weight
) VALUES
(2, '192.168.30.128', 3306, 100),
(2, '192.168.30.139', 3306, 100),
(3, '192.168.30.140', 3306, 100);
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
-- 查看主机
select * from runtime_mysql_servers;
添加用户
insert into mysql_users
(username,password,default_hostgroup,default_schema)
values
('test','Zzj@123456',2,'information_schema');
配置读写分离
insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(3,'test',1,'^SELECT.*FOR UPDATE$',2,1);
insert into mysql_query_rules(rule_id,username,active,match_digest,destination_hostgroup,apply)values(4,'test',1,'^SELECT',3,1);
--查看规则
select rule_id,username,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
验证负载均衡
for i in {1..112}; do mysql -utest -pZzj@123456 -h192.168.30.128 -P6033 -e 'select @@hostname' -s -N; done
ProxySQL其它的参数上的调整
mysql-max_stmts_per_connection 从 20 调整为100
-- 【放弃,影响不大】 mysql-threads 从 4 调整到 8
update global_variables set variable_value='100' where variable_name ='mysql-max_stmts_per_connection';
-- update global_variables set variable_value='8' where variable_name ='mysql-threads';
-- 从原先的64MB改到128MB
update global_variables set variable_value='134217728' where variable_name ='mysql-max_allowed_packet';
update global_variables set variable_value='2000' where variable_name in('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
-- 改下隔离级别,必须步骤!否则会出现RR隔离级别查到脏数据 【测试环境复现出来的问题】
update global_variables set variable_value='REPEATABLE-READ' where variable_name ='mysql-default_tx_isolation';
-- 更改下默认的字符集和字符排序集
-- mysql-default_charset 默认是 utf8
-- mysql-default_collation_connection 默认是 utf8_general_ci
update global_variables set variable_value='utf8mb4' where variable_name ='mysql-default_charset';
update global_variables set variable_value='utf8mb4_0900_ai_ci' where variable_name ='mysql-default_collation_connection';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
报错处理
-- onnection is locked to hostgroup 2 but trying to reach hostgroup 3
-- https://stackoverflow.com/questions/72722871/9006-proxysql-error-connection-is-locked-to-hostgroup-xx-but-trying-to-reach-ho
set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;