mysql proxy 监控_mysql读写分离与监控的使用（proxysql）-CSDN博客

本文链接：https://blog.csdn.net/weixin_42397024/article/details/113593489

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;

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

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

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

systemctl start proxysql

根据 /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

下面搜索到了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

Database changed

Admin> show tables;

+--------------------------------------------+

| tables |

+--------------------------------------------+

| global_variables |

| mysql_collations |

| mysql_group_replication_hostgroups |

| mysql_query_rules |

| mysql_query_rules_fast_routing |

| mysql_replication_hostgroups |

| mysql_servers |

| mysql_users |

| proxysql_servers |

| runtime_checksums_values |

| runtime_global_variables |

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules |

| runtime_mysql_query_rules_fast_routing |

| runtime_mysql_replication_hostgroups |

| runtime_mysql_servers |

| runtime_mysql_users |

| runtime_proxysql_servers |

| runtime_scheduler |

| scheduler |

+--------------------------------------------+

20 rows in set (0.00 sec)

下面查看全局变量

Admin> select * from global_variables;

+-----------------------------------------------------+--------------------+

| variable_name | variable_value |

+-----------------------------------------------------+--------------------+

| mysql-shun_on_failures | 5 |

| mysql-shun_recovery_time_sec | 10 |

| mysql-query_retries_on_failure | 1 |

| mysql-client_multi_statements | true |

| mysql-connect_retries_delay | 1 |

| mysql-connection_delay_multiplex_ms | 0 |

| mysql-connection_max_age_ms | 0 |

| mysql-connect_timeout_server_max | 10000 |

| mysql-eventslog_filename | |

| mysql-eventslog_filesize | 104857600 |

| mysql-default_charset | utf8 |

| mysql-free_connections_pct | 10 |

| mysql-session_idle_ms | 1000 |

| mysql-client_found_rows | true |

| mysql-monitor_enabled | true |

| mysql-monitor_connect_timeout | 600 |

| mysql-monitor_ping_max_failures | 3 |

| mysql-monitor_ping_timeout | 1000 |

| mysql-monitor_read_only_max_timeout_count | 3 |

| mysql-monitor_replication_lag_interval | 10000 |

| mysql-monitor_replication_lag_timeout | 1000 |

| mysql-monitor_groupreplication_healthcheck_interval | 5000 |

| mysql-monitor_groupreplication_healthcheck_timeout | 800 |

| mysql-monitor_replication_lag_use_percona_heartbeat | |

| mysql-monitor_query_interval | 60000 |

| mysql-monitor_query_timeout | 100 |

| mysql-monitor_slave_lag_when_null | 60 |

| mysql-monitor_wait_timeout | true |

| mysql-monitor_writer_is_also_reader | true |

| mysql-max_allowed_packet | 4194304 |

| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |

| mysql-max_transaction_time | 14400000 |

| mysql-multiplexing | true |

| mysql-forward_autocommit | false |

| mysql-enforce_autocommit_on_reads | false |

| mysql-autocommit_false_not_reusable | false |

| mysql-autocommit_false_is_transaction | false |

| mysql-verbose_query_error | false |

| mysql-hostgroup_manager_verbose | 1 |

| mysql-threshold_query_length | 524288 |

| mysql-threshold_resultset_size | 4194304 |

| mysql-query_digests_max_digest_length | 2048 |

| mysql-query_digests_max_query_length | 65000 |

| mysql-wait_timeout | 28800000 |

| mysql-throttle_max_bytes_per_second_to_client | 2147483647 |

| mysql-throttle_ratio_server_to_client | 0 |

| mysql-max_stmts_per_connection | 20 |

| mysql-max_stmts_cache | 10000 |

| mysql-mirror_max_concurrency | 16 |

| mysql-mirror_max_queue_length | 32000 |

| mysql-default_max_latency_ms | 1000 |

| mysql-query_processor_iterations | 0 |

| mysql-query_processor_regex | 1 |

| mysql-auto_increment_delay_multiplex | 5 |

| mysql-long_query_time | 1000 |

| mysql-query_cache_size_MB | 256 |

| mysql-poll_timeout_on_failure | 100 |

| mysql-server_capabilities | 45578 |

| mysql-kill_backend_connection_when_disconnect | true |

| mysql-session_idle_show_processlist | true |

| mysql-query_digests | true |

| mysql-query_digests_lowercase | false |

| mysql-servers_stats | true |

| mysql-default_reconnect | true |

| mysql-ssl_p2s_ca | |

| mysql-ssl_p2s_cert | |

| mysql-ssl_p2s_key | |

| mysql-ssl_p2s_cipher | |

| mysql-init_connect | |

| mysql-default_sql_mode | |

| mysql-default_time_zone | SYSTEM |

| mysql-connpoll_reset_queue_length | 50 |

| mysql-stats_time_backend_query | false |

| mysql-stats_time_query_processor | false |

| admin-stats_credentials | stats:stats |

| admin-stats_mysql_connections | 60 |

| admin-stats_mysql_connection_pool | 60 |

| admin-stats_mysql_query_cache | 60 |

| admin-stats_system_cpu | 60 |

| admin-stats_system_memory | 60 |

| admin-telnet_admin_ifaces | (null) |

| admin-telnet_stats_ifaces | (null) |

| admin-refresh_interval | 2000 |

| admin-read_only | false |

| admin-hash_passwords | true |

| admin-version | 1.4.15-1-g61bc777 |

| admin-cluster_username | |

| admin-cluster_password | |

| admin-cluster_check_interval_ms | 1000 |

| admin-cluster_check_status_frequency | 10 |

| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |

| admin-cluster_mysql_servers_diffs_before_sync | 3 |

| admin-cluster_mysql_users_diffs_before_sync | 3 |

| admin-cluster_proxysql_servers_diffs_before_sync | 3 |

| admin-cluster_mysql_query_rules_save_to_disk | true |

| admin-cluster_mysql_servers_save_to_disk | true |

| admin-cluster_mysql_users_save_to_disk | true |

| admin-cluster_proxysql_servers_save_to_disk | true |

| admin-checksum_mysql_query_rules | true |

| admin-checksum_mysql_servers | true |

| admin-checksum_mysql_users | true |

| admin-web_enabled | false |

| admin-web_port | 6080 |

| admin-admin_credentials | admin:admin |

| admin-mysql_ifaces | 0.0.0.0:6032 |

| mysql-threads | 4 |

| mysql-max_connections | 2048 |

| mysql-default_query_delay | 0 |

| mysql-default_query_timeout | 36000000 |

| mysql-have_compress | true |

| mysql-poll_timeout | 2000 |

| mysql-interfaces | 0.0.0.0:6033 |

| mysql-default_schema | information_schema |

| mysql-stacksize | 1048576 |

| mysql-server_version | 5.5.30 |

| mysql-connect_timeout_server | 3000 |

| mysql-monitor_username | monitor |

| mysql-monitor_password | monitor |

| mysql-monitor_history | 600000 |

| mysql-monitor_connect_interval | 60000 |

| mysql-monitor_ping_interval | 10000 |

| mysql-monitor_read_only_interval | 1500 |

| mysql-monitor_read_only_timeout | 500 |

| mysql-ping_interval_server_msec | 120000 |

| mysql-ping_timeout_server | 500 |

| mysql-commands_stats | true |

| mysql-sessions_sort | true |

| mysql-connect_retries_on_failure | 10 |

+-----------------------------------------------------+--------------------+

128 rows in set (0.00 sec)

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;

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

select * from mysql_server_ping_log;

select * from mysql_server_ping_log\G;

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

查看各组信息

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;

+--------------+-------------+------+--------+--------+

+--------------+-------------+------+--------+--------+

| 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;

+--------------+-------------+------+--------+--------+

+--------------+-------------+------+--------+--------+

| 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_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;

只有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)

测试刚才的用户能否使用

读写分离：配置路由规则

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 -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)

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

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

+----+----------+------------+----------------------------------+

+----+----------+------------+----------------------------------+

| 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

percona站点：

https：//www.percona.com/downloads/proxysql/

https://www.percona.com/downloads/proxysql/proxysql-1.4.9/binary/redhat/7

下载包

参考到的网站

http://dev.mysql.com/downloads/mysql-proxy/

http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/7/

http://www.proxysql.com/

http://www.proxysql.com/compare

https://www.percona.com/downloads/proxysql/

https://github.com/sysown/proxysql/releases

https://github.com/sysown/ProxySQL

https://github.com/sysown/proxysql/wiki