mysql读写分离与监控的使用（proxysql）

原创

mb63774a171e569 2022-11-19 10:07:09 博主文章分类：mysql ©著作权

文章标签 mysql sql 数据库 文章分类 Spark 大数据

©著作权归作者所有：来自51CTO博客作者mb63774a171e569的原创作品，请联系作者获取转载授权，否则将追究法律责任

os:rhel 7.3
mysql: 5.7
proxysql:1.4.15-1

ip 规划如下：

172.25.11.1 node1 (proxysql)

172.25.11.2 node2 (mysql master)
172.25.11.3 node3 (mysql slave)
172.25.11.4 node4 (mysql slave)

node 2 3 4 安装mysql5.7并且初始化

yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

node2

server-id=1
log-bin=mysql-bin
gtid-mode=on
enforce-gtid-consistency=1

grep password /var/log/mysqld.log

mysql> alter user root@localhost identified by 'Yakexi_007';
Query OK, 0 rows affected (0.12 sec)

mysql> grant replication slave on *.* to repl@'172.25.11.%' identified by 'Yakexi+007';
Query OK, 0 rows affected, 1 warning (0.40 sec)

mysql> Flush privileges;
Query OK, 0 rows affected (0.19 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000002 | 843 | | | ba8f407d-389f-11e9-95ea-525400d000e0:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

注意点：slave节点需要设置read_only=1。

node3

server-id=2
gtid-mode=on
enforce-gtid-consistency=1

read_only=1

mysql> change master to master_host='172.25.11.2',
    -> master_user='repl',
    -> master_password='Yakexi+007',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=843;

start slave;

node4

server-id=3
gtid-mode=on
enforce-gtid-consistency=1

read_only=1

mysql> change master to master_host='172.25.11.2',
    -> master_user='repl',
    -> master_password='Yakexi+007',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=843;

start slave;

node1 安装proxysql

解决依赖性

yum install -y automake bzip2 cmake make g++ gcc git openssl debconf-utils

yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL -y

yum install proxysql-1.4.15-1-centos7.x86_64.rpm -y

mysql读写分离与监控的使用（proxysql）_sql

cp /etc/proxysql.cnf /etc/proxysql.cnf.bak

systemctl start proxysql

mysql读写分离与监控的使用（proxysql）_sql_02

mysql读写分离与监控的使用（proxysql）_sql_03

根据 /etc/proxysql.cnf 文件内容，6032 是管理端口，6033 是 mysql 连接端口。

用server1本地连接proxysql 他的默认帐号密码admin

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

也可以mysql -uadmin -padmin -h 127.0.0.1 -P 6032

mysql读写分离与监控的使用（proxysql）_sql_04

mysql读写分离与监控的使用（proxysql）_sql_05

下面搜索到了main主库的资料

main 数据库

内存配置数据库，即MEMORY，表里存放后端db实例、用户验证、路由规则等信息。表名以runtime_开头的表示ProxySQL当前运行的配置内容，不能通过DML语句修改。只能修改对应的不以 runtime开头的表，然后“LOAD”使其生效，“SAVE”使其存到硬盘以供下次重启加载。

disk 数据库

持久化到硬盘的配置，sqlite数据文件。

stats 数据库

proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。

monitor 数据库

存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

Admin> use main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

下面查看全局变量

mysql_query_rules
指定Query路由到后端不同服务器的规则列表。

mysql_replication_hostgroups
监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组

定义 hostgroup 的主从关系。ProxySQL monitor 模块会监控 HG 后端所有servers 的 read_only 变量，如果发现从库的 read_only 变为0、主库变为1，则认为角色互换了，自动改写 mysql_servers 表里面 hostgroup 关系，达到自动 Failover 效果。

mysql_servers
后端可以连接MySQL服务器的列表。

mysql_users
配置后端数据库的账号和监控的账号

scheduler
调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。

Admin> set mysql-threads = 100;

runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_ 表，修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。

现在添加节点

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.25.11.2',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.25.11.3',3306);

insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.25.11.4',3306);

#使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 10 表示写组，20表示读组。

select * from mysql_servers\G;

查看状态

*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 172.25.11.2
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment:
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 172.25.11.3
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment:
*************************** 3. row ***************************
       hostgroup_id: 10
           hostname: 172.25.11.4
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment:
3 rows in set (0.00 sec)

修改后，加载到RUNTIME，并保存到disk。

load mysql servers to runtime;

save mysql servers to disk;

监控后端MySQL节点

添加节点之后，还需要监控后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整它们是属于读组还是写组。

首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication client权限。这里直接赋予这个权限。

MASTER上

create user monitor@'172.25.11.%' identified by 'Yakexi+007';

grant replication client on *.* to monitor@'172.25.11.%';

ProxySQL上配置监控

mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password='Yakexi+007';
Query OK, 1 row affected (0.00 sec)

修改后，加载到RUNTIME，并保存到disk。

mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> save mysql variables to disk;
Query OK, 97 rows affected (0.19 sec)

验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。

select * from mysql_server_connect_log\G;

select * from mysql_server_connect_log;

mysql读写分离与监控的使用（proxysql）_数据库_06

心跳信息的监控(对ping指标的监控)

select * from mysql_server_ping_log;

select * from mysql_server_ping_log\G;

mysql读写分离与监控的使用（proxysql）_数据库_07

select hostgroup_id,hostname,port,status,weight from mysql_servers;

查看各组信息

mysql读写分离与监控的使用（proxysql）_数据库_08

mysql> select * from mysql_server_read_only_log;
Empty set (0.01 sec)

mysql> select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)

发现日志为空

下面修改

mysql> insert into mysql_replication_hostgroups values(10,20,1);
Query OK, 1 row affected (0.01 sec)

mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+-------------+------+--------+--------+
| hostgroup_id | hostname    | port | status | weight |
+--------------+-------------+------+--------+--------+
| 10           | 172.25.11.2 | 3306 | ONLINE | 1      |
| 10           | 172.25.11.3 | 3306 | ONLINE | 1      |
| 10           | 172.25.11.4 | 3306 | ONLINE | 1      |
+--------------+-------------+------+--------+--------+
3 rows in set (0.00 sec)

写入磁盘后会生效

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.45 sec)

mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+-------------+------+--------+--------+
| hostgroup_id | hostname    | port | status | weight |
+--------------+-------------+------+--------+--------+
| 10           | 172.25.11.2 | 3306 | ONLINE | 1      |
| 20           | 172.25.11.4 | 3306 | ONLINE | 1      |
| 20           | 172.25.11.3 | 3306 | ONLINE | 1      |
+--------------+-------------+------+--------+--------+
3 rows in set (0.00 sec)

所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。

mysql读写分离与监控的使用（proxysql）_数据库_09

这时就会有监控日志了

配置mysql_users

上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送 SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和test两个用户名为例。

master节点上执行：(只需master执行即可，会复制给两个slave)

grant all on *.* to root@'172.25.11.%' identified by 'Yakexi_007';

grant all on *.* to test@'172.25.11.%' identified by 'Yakexi_007';

ProxySQL端，配置mysql_users表，将刚才的两个用户添加到该表中。

mysql> insert into mysql_users(username,password,default_hostgroup) values('root','Yakexi_007',10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_users(username,password,default_hostgroup) values('test','Yakexi_007',10);
Query OK, 1 row affected (0.00 sec)

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.27 sec)

mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup：

username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。
default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认情况下将路由到hostgroup_id=10组中的某个节点。

select * from mysql_users;

mysql读写分离与监控的使用（proxysql）_mysql_10

只有active=1的用户才是有效的用户。

至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，所有的语句都路由到同一个组中，避免语句分散到不同组。

在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。如果为0，则执行下面的语句修改为1。

update mysql_users set transaction_persistent=1 where username='root';

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.27 sec)

测试刚才的用户能否使用

mysql读写分离与监控的使用（proxysql）_sql_11

读写分离：配置路由规则

ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。本文只是实验，实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个：mysql_query_rules和 mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后才支持该快速路由表。本文只介绍第一个表。插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组。

PROXYSQL端

mysql> insert into
-> mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.36 sec)

select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。

测试下，读操作是否路由给了hostgroup_id=20的读组。

mysql -uroot -pYakexi_007 -P6033 -h127.0.0.1 -e 'select @@server_id'

mysql读写分离与监控的使用（proxysql）_数据库_12

看看写操作。这里以事务持久化进行测试。

mysql -uroot -pYakexi_007 -P6033 -h127.0.0.1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)

mysql读写分离与监控的使用（proxysql）_mysql_13

查看路由的信息，可查询stats库中的stats_mysql_query_digest表。

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 10 | 114385   | 1          | create database proxy_test       |
| 20 | 6455     | 3          | select @@server_id               |
| 10 | 2018     | 1          | show databases                   |
| 10 | 1062     | 1          | commit                           |
| 10 | 787      | 1          | start transaction                |
| 10 | 0        | 7          | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
7 rows in set (0.00 sec)

可以看出读的组为20 写的组为10