一、前提
先搭建mysql组复制MGR
二、安装ProxySQL
https://www.proxysql.com/
https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/8/
# 安装
rpm -ivh proxysql-2.5.4-1-centos8.x86_64.rpm
# 启动
systemctl start proxysql
# 登录配置
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
三、配置ProxySQL
1. MGR主节点创建相关函数
创建函数、视图提供给ProxySQL判断该节点状态
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 ;
检查视图是否创建成功
# 主节点
mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.02 sec)
# 从节点
mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | YES | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.03 sec)
2. 创建相关用户（MGR主节点）
在MGR主节点执行下面SQL语句,创建监控用户用于ProxySQL监控数据库状态：
mysql> create user 'monitor'@'%' identified by '[密码]';
Query OK, 0 rows affected (0.02 sec)
mysql> grant select on sys.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select on performance_schema.* to 'monitor'@'%';
Query OK, 0 rows affected (0.01 sec)
后来一直报错，所以就给monitor赋所有的权限：`grant all on *.* to 'monitor'@'%';`
2023-07-27 10:16:10 MySQL_Monitor.cpp:7615:monitor_replication_lag_process_ready_tasks(): [ERROR] Error after 1ms on server 192.168.100.103:3306 : Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
在MGR主节点创建用户，用于ProxySQL访问
mysql> create user 'proxysql'@'%' identified by '[密码]';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'proxysql'@'%';
Query OK, 0 rows affected (0.00 sec)
3. 配置ProxySQL
登入管理端口（默认管理用户admin，密码admin，管理端口6032，客户端口6033）
# mysql -uadmin -padmin --prompt='proxysql> ' -P6032 -h127.0.0.1
proxysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
proxysql中添加后端节点
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.100.101',3306,1,3000,10,'mysql-s01');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.100.102',3306,1,3000,10,'mysql-s02');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (10,'192.168.100.103',3306,1,3000,10,'mysql-s03');
# 将mysql_servers表加载到runtime
proxysql> LOAD mysql users TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
# 将mysql_servers表保存到磁盘
proxysql> SAVE mysql servers TO DISK;
Query OK, 0 rows affected (0.04 sec)
设置监控用户账户密码
set mysql-monitor_username='monitor';
set mysql-monitor_password='[密码]';
LOAD mysql variables TO RUNTIME;
SAVE mysql variables TO DISK;
设置提供访问的用户
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values('proxysql','[密码]',1,10,1);
load mysql users to runtime;
save mysql users to disk;
也可以加入其它账户
配置mysql_group_replication_hostgroups表：
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(10,20,30,40,1,1,0,0);
load mysql servers to runtime;
save mysql servers to disk;
设置读写分离规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);
load mysql query rules to runtime;
save mysql query rules to disk;
设置mysql版本（mysql8移除了query_cache_size,否则会报错）
update global_variables set variable_value="8.0.16 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to runtime;
save mysql variables to disk;
安装过程，查看tail -f /var/lib/proxysql/proxysql.log日志是否有错误
四、查看相关状态
1. 查看后端节点健康状态
proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10 ;
+-----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-----------------+------+------------------+-------------------------+---------------+
| 192.168.100.101 | 3306 | 1690365135302112 | 1886 | NULL |
| 192.168.100.102 | 3306 | 1690365134675658 | 3318 | NULL |
| 192.168.100.103 | 3306 | 1690365134049196 | 2388 | NULL |
| 192.168.100.102 | 3306 | 1690365075027121 | 3053 | NULL |
| 192.168.100.103 | 3306 | 1690365074538032 | 2500 | NULL |
| 192.168.100.101 | 3306 | 1690365074049040 | 1804 | NULL |
+-----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.00 sec)
proxysql>
proxysql>
proxysql>
proxysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+-----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-----------------+------+------------------+----------------------+------------+
| 192.168.100.101 | 3306 | 1690365164287489 | 346 | NULL |
| 192.168.100.102 | 3306 | 1690365164287486 | 552 | NULL |
| 192.168.100.103 | 3306 | 1690365164287484 | 476 | NULL |
| 192.168.100.102 | 3306 | 1690365154287127 | 493 | NULL |
| 192.168.100.101 | 3306 | 1690365154286668 | 388 | NULL |
| 192.168.100.103 | 3306 | 1690365154286588 | 625 | NULL |
| 192.168.100.103 | 3306 | 1690365144285304 | 501 | NULL |
| 192.168.100.102 | 3306 | 1690365144285285 | 615 | NULL |
| 192.168.100.101 | 3306 | 1690365144285282 | 317 | NULL |
| 192.168.100.101 | 3306 | 1690365134284156 | 441 | NULL |
+-----------------+------+------------------+----------------------+------------+
10 rows in set (0.01 sec)
2. 查看MGR配置
proxysql> select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10 | 20 | 30 | 40 | 1 | 1 | 0 | 0 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
3. 查看MGR相关的监控指标
proxysql> select * from mysql_server_group_replication_log desc limit 10;
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------+
| hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------+
| 192.168.100.101 | 3306 | 1690365050105067 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365055105779 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365060105280 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365065106013 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365070105346 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365075106377 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365080105668 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365085106947 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365090106266 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
| 192.168.100.101 | 3306 | 1690365095107464 | 0 | NO | YES | -1 | SELECT command denied to user 'monitor'@'mysql-s01' for table 'replication_group_members' |
+----------------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
五、读写分离测试
# 读测试
for i in `seq 1 10`; do mysql -u[用户] -p[密码] -h127.0.0.1 -P6033 -e 'select * from performance_schema.global_variables where variable_name="server_id";' ; done | grep server
# 写测试
create database test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
use test
CREATE TABLE user (
id INT AUTO_INCREMENT PRIMARY KEY,
username varchar(64) NULL
);
for i in `seq 1 100`; do mysql -u[用户] -p[密码] -h127.0.0.1 -P6033 -e 'insert into test.user(username) values(UUID())' ; done