MySQL8组复制MGR+ProxySQL

mtain 2023年07月26日 516次浏览

一、前提

先搭建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


参考文章:
https://www.cndba.cn/cndba/dave/article/108031