之前我们配置了mysql 一主二从模式,本文将利用mysql中间件ProxySQL 实现读写分离。
首先下载镜像
docker pull proxysql/proxysql
在home目录下创建proxysql/proxysql.cnf,内容如下
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin;radmin:radmin" ##管理端账号密码
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor" ##监控账号
monitor_password="monitor" ##监控密码
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
docker run -p 16032:6032 -p 16033:6033 -p 16070:6070 --name=proxysql -d -v /home/proxysql/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql
生成容器后,我们进入之前mysql 3个容器中任意一个
docker exec -it mysql-master /bin/bash
root@8c6fba5006e3:/# mysql -h服务器ip -P16032 -uradmin -pradmin --prompt "ProxySQL Admin>"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQL Admin>
进入proxysql admin管理窗口后,
ProxySQL Admin>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.01 sec)
ProxySQL Admin>
可见有五个库: main、disk、stats 、monitor 和 stats_history
main: 内存配置数据库,即 MEMORY,表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息:
ProxySQL Admin>show tables;
+----------------------------------------------------+
| tables |
+----------------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_sqli_fingerprints |
| mysql_firewall_whitelist_users |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| restapi_routes |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users |
| runtime_mysql_galera_hostgroups |
| 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_restapi_routes |
| runtime_scheduler |
| scheduler |
+----------------------------------------------------+
32 rows in set (0.00 sec)
ProxySQL Admin>
库下的主要表: mysql_servers
: 后端可以连接 MySQL 服务器的列表 mysql_users
: 配置后端数据库的账号和监控的账号。 mysql_query_rules
: 指定 Query 路由到后端不同服务器的规则列表。
注: 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容,不能通过 DML 语句修改。
只能修改对应的不以 runtime 开头的表,然后 “LOAD” 使其生效,“SAVE” 使其存到硬盘以供下次重启加载。
disk :持久化的磁盘的配置
stats: 统计信息的汇总
monitor:一些监控的收集信息,比如数据库的健康状态等
stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标
接下来我们配置proxysql所需账户
在 Master 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户
注意 mysql8.0.x 用户认证的方式需要修改为 mysql_native_password
需要在my.cnf 加上这个用户认证方式,再来创建用户
root@8c6fba5006e3:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'monitor'@'172.18.0.%' identified by 'monitor';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'monitor'@'172.18.0.%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'proxysql'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'proxysql'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin from user;
+------------------+------------+-----------------------+
| user | host | plugin |
+------------------+------------+-----------------------+
| proxysql | % | mysql_native_password |
| root | % | mysql_native_password |
| zhang-m | % | mysql_native_password |
| monitor | 172.18.0.% | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+------------+-----------------------+
8 rows in set (0.00 sec)
proxysql配置
整套配置系统分为三层:顶层为 RUNTIME ,中间层为 MEMORY , 底层也就是持久层 DISK 和 CONFIG FILE 。
RUNTIME : 代表 ProxySQL 当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层 “load” 进来。
MEMORY: MEMORY 层上面连接 RUNTIME 层,下面连接持久层。这层可以正常操作 ProxySQL 配置,随便修改,不会影响生产环境。修改一个配置一般都是现在 MEMORY 层完成的,确认正常之后在加载达到 RUNTIME 和 持久化的磁盘上。
DISK 和 CONFIG FILE:持久化配置信息,重启后内存中的配置信息会丢失,所需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来。
在不同层次间移动配置
为了将配置持久化到磁盘或者应用到 runtime,在管理接口下有一系列管理命令来实现它们。
要重新配置 MySQL 用户,可执行下面的其中一个命令:
1、LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
将内存数据库中的配置加载到 runtime 数据结构,反之亦然。
2、SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
将 MySQL 用户从 runtime 持久化到内存数据库。
3、LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
从磁盘数据库中加载 MySQL 用户到内存数据库中。
4、SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
将内存数据库中的 MySQL 用户持久化到磁盘数据库中。
5、LOAD MYSQL USERS FROM CONFIG
从配置文件中加载 MySQL 用户到内存数据库中。
要处理 MySQL server:
1、LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
将 MySQL server 从内存数据库中加载到 runtime。
2、SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
将 MySQL server 从 runtime 持久化到内存数据库中。
3、LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
从磁盘数据库中加载 MySQL server 到内存数据库。
4、SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
从内存数据库中将 MySQL server 持久化到磁盘数据库中。
5、LOAD MYSQL SERVERS FROM CONFIG
从配置文件中加载 MySQL server 到内存数据库中
要处理 MySQL 的查询规则(mysql query rules):
1、 LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
将 MySQL query rules 从内存数据库加载到 runtime 数据结构。
2、 SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
将 MySQL query rules 从 runtime 数据结构中持久化到内存数据库。
3、 LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
从磁盘数据库中加载 MySQL query rules 到内存数据库中。
4、 SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。
5、 LOAD MYSQL QUERY RULES FROM CONFIG
从配置文件中加载 MySQL query rules 到内存数据库中。
要处理 MySQL 变量(MySQL variables):
1、 LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
将 MySQL variables 从内存数据库加载到 runtime 数据结构。
2、 SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
将 MySQL variables 从 runtime 数据结构中持久化到内存数据中。
3、 LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
从磁盘数据库中加载 MySQL variables 到内存数据库中。
4、 SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
将 MySQL variables 从内存数据库中持久化到磁盘数据库中。
5、 LOAD MYSQL VARIABLES FROM CONFIG
从配置文件中加载 MySQL variables 到内存数据库中。
要处理管理变量(admin variables):
1、 LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
将 admin variables 从内存数据库加载到 runtime 数据结构。
2、 SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
将 admin variables 从 runtime 持久化到内存数据库中。
3、 LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
从磁盘数据库中加载 admin variables 到内存数据库中。
4、 SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
将 admin variables 从内存数据库中持久化到磁盘数据库。
5、 LOAD ADMIN VARIABLES FROM CONFIG
从配置文件中加载 admin variables 到内存数据库中。
一般在内存那层修改 ,然后保存到运行系统,保存到磁盘数据库系统
load xxx to runtime;
save xxx to disk;
管理配置
disk -> 是sqlite3 数据库 ,默认位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)
config file 是一个传统配置文件:一般不更改
在内存中动态更改配置,如果重启,没进行持久化(save) 则会丢失。
三则之间关系:
-
proxysql 启动时,首先去找/etc/proxysql.cnf 找到它的datadir,如果datadir下有proxysql.db 就加载proxysql.db的配置
-
如果启动proxysql时带有
--init
标志,会用/etc/proxsql.cnf的配置,把Runtime,disk全部初始化一下 -
在调用是调用
--reload
会把/etc/proxysql.cnf 和disk 中配置进行合并。如果冲突需要用户干预。disk会覆盖config file。
关于传统的配置文件
传统配置文件默认路径为/etc/proxysql.cnf,也可以在二进制程序proxysql上使用-c或–config来手动指定配置文件。
默认情况下:几乎不需要手动去配置proxysql.cnf。
端口号,管理proxysql用户密码,可以在这里修改
这里有几个最常用的命令:如何让修改的配置生效(runtime),以及如何持久化到磁盘上(disk)。
记住,只要不是加载到 runtime,修改的配置就不会生效。
LOAD MYSQL USERS TO RUNTIME; 将内存数据库中的配置加载到 runtime 数据结构
SAVE MYSQL USERS TO DISK; 将内存数据库中的 MySQL 用户持久化到磁盘数据库中。
LOAD MYSQL SERVERS TO RUNTIME; 将 MySQL server 从内存数据库中加载到 runtime。
SAVE MYSQL SERVERS TO DISK; 从内存数据库中将 MySQL server 持久化到磁盘数据库中。
LOAD MYSQL QUERY RULES TO RUNTIME; 将 MySQL query rules 从内存数据库加载到 runtime 数据结构。
SAVE MYSQL QUERY RULES TO DISK; 将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。
LOAD MYSQL VARIABLES TO RUNTIME; 将 MySQL variables 从内存数据库加载到 runtime 数据结构。
SAVE MYSQL VARIABLES TO DISK; 将 MySQL variables 从内存数据库中持久化到磁盘数据库中。
LOAD ADMIN VARIABLES TO RUNTIME; 将 admin variables 从内存数据库加载到 runtime 数据结构。
SAVE ADMIN VARIABLES TO DISK; 将 admin variables 从内存数据库中持久化到磁盘数据库。
只有加载到 runtime 状态时才会去做最后的有效性验证。在保存到内存数据库或持久化到磁盘上时,都不会发生任何警告或错误。当加载到 runtime 时,如果出现错误,将恢复为之前保存得状态,这时可以去检查错误日志。
配置 ProxySQL 主从分组信息
用到 这个表 mysql_replication_hostgroup,表结构信息如下
ProxySQL Admin>show create table mysql_replication_hostgroups\G;
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.01 sec)
ERROR:
No query specified
ProxySQL Admin>
writer_hostgroup
和reader_hostgroup
写组和读组都要大于0且不能相同,我的环境下,写组定义与10,读组定义为20
创建组
ProxySQL Admin>insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
ProxySQL Admin>save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
ProxySQL 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
ProxySQL Admin>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | proxy |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)
ProxySQL Admin>select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | proxy |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)
ProxySQL Admin>use disk;
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
ProxySQL Admin>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10 | 20 | read_only | proxy |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)
ProxySQL Admin>
ProxySQL 会根据server 的read _only 的取值将服务器进行分组。 read_only=0 的server,master被分到编号为10的写组,read_only=1 的server,slave则被分到编号20的读组
添加主从服务器节点
用到mysql_servers 这个表
ProxySQL Admin>insert into mysql_servers(hostgroup_id,hostname,port) values(10,'MYSQL-MASTER节点ip',3306);
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin>insert into mysql_servers(hostgroup_id,hostname,port) values(20,'从节点1ip',3306);
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin>insert into mysql_servers(hostgroup_id,hostname,port) values(20,'从节点2ip',3306);
Query OK, 1 row affected (0.01 sec)
ProxySQL Admin>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin>save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
roxySQL Admin>select * from mysql_servers;
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20 | 172.18.0.3 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.18.0.4 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 172.18.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
ProxySQL监控 MySQL 后端节点
为 ProxySQL 配置监控账号
ProxySQL Admin>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
修改后,保存到runtime和disk
ProxySQL Admin>load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin>save mysql variables to disk;
Query OK, 147 rows affected (0.01 sec)
验证监控信息,ProxySQL 监控模块的指标都保存在monitor库的log表中
以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常
ProxySQL Admin>select * from monitor.mysql_server_connect_log;
+------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+------------+------+------------------+-------------------------+---------------+
| 172.18.0.2 | 3306 | 1637812649307891 | 639 | NULL |
| 172.18.0.3 | 3306 | 1637812649763677 | 654 | NULL |
| 172.18.0.4 | 3306 | 1637812650219452 | 645 | NULL |
| 172.18.0.4 | 3306 | 1637812709307967 | 649 | NULL |
| 172.18.0.3 | 3306 | 1637812709818585 | 598 | NULL |
| 172.18.0.2 | 3306 | 1637812710329212 | 709 | NULL |
| 172.18.0.2 | 3306 | 1637812769308026 | 650 | NULL |
| 172.18.0.4 | 3306 | 1637812769751543 | 653 | NULL |
| 172.18.0.3 | 3306 | 1637812770195036 | 748 | NULL |
| 172.18.0.2 | 3306 | 1637812829308104 | 654 | NULL |
| 172.18.0.3 | 3306 | 1637812830036258 | 659 | NULL |
| 172.18.0.4 | 3306 | 1637812830764335 | 615 | NULL |
| 172.18.0.2 | 3306 | 1637812889308177 | 660 | NULL |
| 172.18.0.4 | 3306 | 1637812889823761 | 668 | NULL |
| 172.18.0.3 | 3306 | 1637812890339321 | 744 | NULL |
| 172.18.0.3 | 3306 | 1637812949308279 | 633 | NULL |
| 172.18.0.2 | 3306 | 1637812949738104 | 668 | NULL |
| 172.18.0.4 | 3306 | 1637812950167964 | 737 | NULL |
| 172.18.0.4 | 3306 | 1637813009308331 | 635 | NULL |
| 172.18.0.3 | 3306 | 1637813009759778 | 685 | NULL |
| 172.18.0.2 | 3306 | 1637813010211269 | 700 | NULL |
| 172.18.0.3 | 3306 | 1637813069308387 | 681 | NULL |
| 172.18.0.2 | 3306 | 1637813070045728 | 715 | NULL |
| 172.18.0.4 | 3306 | 1637813070783034 | 638 | NULL |
| 172.18.0.2 | 3306 | 1637813129308453 | 656 | NULL |
| 172.18.0.4 | 3306 | 1637813129816893 | 632 | NULL |
| 172.18.0.3 | 3306 | 1637813130325287 | 688 | NULL |
| 172.18.0.2 | 3306 | 1637813189308496 | 671 | NULL |
| 172.18.0.4 | 3306 | 1637813189760137 | 641 | NULL |
| 172.18.0.3 | 3306 | 1637813190211775 | 1804 | NULL |
| 172.18.0.4 | 3306 | 1637813200164931 | 594 | NULL |
| 172.18.0.2 | 3306 | 1637813200584629 | 671 | NULL |
| 172.18.0.3 | 3306 | 1637813201004303 | 640 | NULL |
+------------+------+------------------+-------------------------+---------------+
33 rows in set (0.01 sec)
对心跳信息的监控(对ping 指标的监控)
ProxySQL Admin>select * from mysql_server_ping_log limit 10;
+------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+------------+------+------------------+----------------------+------------+
| 172.18.0.3 | 3306 | 1637812629304013 | 114 | NULL |
| 172.18.0.4 | 3306 | 1637812629382784 | 114 | NULL |
| 172.18.0.2 | 3306 | 1637812629461550 | 121 | NULL |
| 172.18.0.3 | 3306 | 1637812639304079 | 118 | NULL |
| 172.18.0.2 | 3306 | 1637812639418117 | 128 | NULL |
| 172.18.0.4 | 3306 | 1637812639532121 | 127 | NULL |
| 172.18.0.3 | 3306 | 1637812649304116 | 116 | NULL |
| 172.18.0.4 | 3306 | 1637812649432345 | 118 | NULL |
| 172.18.0.2 | 3306 | 1637812649560548 | 129 | NULL |
| 172.18.0.4 | 3306 | 1637812659304223 | 120 | NULL |
+------------+------+------------------+----------------------+------------+
10 rows in set (0.01 sec)
看看read_only的日志监控
ProxySQL Admin>select * from mysql_server_read_only_log limit 10;
+------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+------------+------+------------------+-----------------+-----------+-------+
| 172.18.0.2 | 3306 | 1637812638650747 | 288 | 0 | NULL |
| 172.18.0.3 | 3306 | 1637812638666669 | 226 | 0 | NULL |
| 172.18.0.4 | 3306 | 1637812638682602 | 183 | 0 | NULL |
| 172.18.0.4 | 3306 | 1637812640150809 | 278 | 0 | NULL |
| 172.18.0.3 | 3306 | 1637812640161781 | 202 | 0 | NULL |
| 172.18.0.2 | 3306 | 1637812640172786 | 182 | 0 | NULL |
| 172.18.0.4 | 3306 | 1637812641650944 | 283 | 0 | NULL |
| 172.18.0.2 | 3306 | 1637812641662389 | 204 | 0 | NULL |
| 172.18.0.3 | 3306 | 1637812641673840 | 191 | 0 | NULL |
| 172.18.0.2 | 3306 | 1637812643150982 | 244 | 0 | NULL |
+------------+------+------------------+-----------------+-----------+-------+
10 rows in set (0.00 sec)
Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组
为 ProxySQL 配置对外访问账号
SQL 请求所使用的用户配置,都需要在 MySQL 节点创建上。在之前步骤中:配置ProxySQL 账户,我创建的对外访问账户是:用户:proxysql,密码:123456
ProxySQL Admin>show create table mysql_users\G;
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
ERROR:
No query specified
ProxySQL Admin>insert into mysql_users (username,password,default_hostgroup) values ('proxysql','123456',10);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin>load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
ProxySQL Admin>save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
ProxySQL Admin>select * from mysql_users\G;
*************************** 1. row ***************************
username: proxysql
password: 123456
active: 1 #是否启用该账户,为0表示禁用
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1 #事务透明化
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000 #用户链接10000,可以修改
attributes:
comment:
1 row in set (0.01 sec)
ERROR:
No query specified
ProxySQL Admin>
mysql_users 表有不少字段,最主要的三个字段username,password,default_hostgroup
username: 前端链接ProxySQL ,以及ProxySQL 将SQL 语句路由给MySQL所使用的的用户名
password:用户名对应的密码,。可以是明文密码,也可以是hash密码。如果想使用hash密码,可以先在某个MySQL节点上执行select password(PASSWORD)
,然后将加密结果复制到该字段。
default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用 proxysql 用户发送的SQL语句默认情况下将路由到hostgroup_id=10 组中的某个节点。
我这里 hostgroup_id = 10的组中只有一个节点就是 master :172.18.0.2
配置读写分离策略:路由规则
注意:我这只是试验,只是配置了几个简单的路由规则,实际情况配置路由规则,不应该是就根据所谓的读、写操作来进行读写分离,而是从收集(慢日志)的各项指标找出压力大,执行频繁的语句单独写规则,做缓存等等。比如 先在测试几个核心sql语句,分析性能提升的百分比,在逐渐慢慢完善路由规则。 生产中使用读写分离
:建议基于hash code 值做读写分离,不要建太多的规则
和查询规则有关的表有两个:mysql_query_rules
和mysql_query_rules_fast_routing
表mysql_query_rules_fast_routing是mysql_query_rules的扩展,并在以后评估快速路由策略和属性(仅在ProxySQL 1.4.7+中可用)。
介绍一下改表mysql_query_rules的几个字段: active
:是否启用这个规则,1表示启用,0表示禁用 match_pattern
字段就是代表设置规则 destination_hostgroup
字段代表默认指定的分组, apply
代表真正执行应用规则。
7.1 创建规则
这里我创建两个规则:
1、把所有以select 开头的语句全部分配到读组中,读组编号是20
2、把 select .. for update 语句,这是一个特殊的select语句,会产生一个写锁(排他锁),把他分到编号为10 的写组中,其他所有操作都会默认路由到写组中
ProxySQL Admin>insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin>insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin>load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin>save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)
select … for update规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。
测试读写分离
现在mster上创建一个库,然后生成几条数据
root@8c6fba5006e3:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 107
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database kkke;
Query OK, 1 row affected (0.01 sec)
mysql> use kkke;
Database changed
mysql> create table bete1(id int primary key auto_increment,name varchar(16));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into bete1 values(1,'zzj'),(2,'yjdd'),(3,'4446f');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> exit
测试读
通过proxysql 连接看看读操作,是否路由给了读组:
root@8c6fba5006e3:/# mysql -uproxysql -p884127 -P 16033 -h 服务器ip -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
root@8c6fba5006e3:/# mysql -uproxysql -p884127 -P 16033 -h 服务器ip -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
root@8c6fba5006e3:/# mysql -uproxysql -p884127 -P 16033 -h 服务器ip -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
root@8c6fba5006e3:/# mysql -uproxysql -p884127 -P 16033 -h 服务器ip -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
root@8c6fba5006e3:/# mysql -uproxysql -p884127 -P 16033 -h 121.40.33.71 -e "select @@server_id";
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
只要是select 操作都分给了读组
如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest
ProxySQL Admin>select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------+------------------------------------------------------+------------+
| hostgroup | schemaname | username | digest_text | count_star |
+-----------+--------------------+----------+------------------------------------------------------+------------+
| 20 | kkke | proxysql | select @@server_id | 2 |
| 10 | kkke | proxysql | insert into bete1 values(?,?) | 2 |
| 10 | kkke | proxysql | insert into values(?,?) | 1 |
| 20 | information_schema | proxysql | select @@server_id | 10 |
| 20 | information_schema | proxysql | SELECT DATABASE() | 3 |
| 10 | information_schema | proxysql | select @@version_comment limit ? | 9 |
| 10 | yinghuo | proxysql | SHOW FULL TABLES WHERE Table_type != ? | 1 |
| 10 | yinghuo | proxysql | SELECT @@character_set_database,@@collation_database | 1 |
| 10 | information_schema | proxysql | SHOW DATABASES | 1 |
| 10 | information_schema | proxysql | SHOW VARIABLES LIKE ? | 2 |
| 10 | information_schema | proxysql | SET NAMES utf8 | 2 |
+-----------+--------------------+----------+------------------------------------------------------+------------+
11 rows in set (0.00 sec)
count_start 统计sql 语句次数,可以分析哪些 sql ,频繁执行
读写分离设置成功后,还可以调权重,比如让某台机器承受更多的读操作
列:
update mysql_servers set weight=10 hostname='172.18.0.4';
load mysql servers to runtime;
save mysql servers to disk;