MySQL中间件之ProxySQL(2):初试读写分离
1.实现一个简单的读写分离
这里通过一个简单的示例实现ProxySQL的读写分离功能,算是ProxySQL的快速入门。即使是快速入门,需要配置的内容也很多,包括:后端MySQL配置、监控配置、发送SQL语句的用户、SQL语句的路由规则。所以,想要实现一个ProxySQL+MySQL,即使只实现最基本的功能,步骤也是挺多的,不过配置的逻辑都很简单。
实验环境:
角色 | 主机IP | server_id | 数据状态 |
---|---|---|---|
Proxysql | 192.168.100.21 | null | 无 |
Master | 192.168.100.22 | 110 | 刚安装的全新MySQL实例 |
Slave1 | 192.168.100.23 | 120 | 刚安装的全新MySQL实例 |
Slave2 | 192.168.100.24 | 130 | 刚安装的全新MySQL实例 |
为了演示完整的过程,这里把后端MySQL主从复制的基本配置步骤也列出来了。如了解配置过程,可跳过主从配置的部分。
注意点:slave节点需要设置read_only=1
。如果后端是PXC/MGR/MariaDB Galera,则无需手动设置,因为会自动设置。
1.1 配置后端的主从复制
提供3个MySQL节点的配置文件。
# 以下是Master的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=110 log-bin=/data/master-bin sync-binlog=1 log-error=/data/error.log pid-file=/data/mysqld.pid # 以下是slave1的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=120 relay_log=/data/relay-log log-error=/data/error.log pid-file=/data/mysqld.pid read_only=1 # 以下是slave2的配置文件 [mysqld] datadir=/data socket=/data/mysql.sock server-id=130 relay_log=/data/relay-log log-error=/data/error.log pid-file=/data/mysqld.pid read_only=1
为3个MySQL节点提供数据目录/data
mkdir /data chown -R mysql.mysql /data
初始化三个MySQL节点。
mysqld --initialize-insecure --user=mysql --datadir=/data
启动3个MySQL节点的mysqld服务。
systemctl start mysqld
连上master,修改root密码,创建用于复制的用户repl。
# 以下在master上执行 mysql> alter user root@localhost identified by 'P@ssword1!'; mysql> create user repl@'192.168.100.%' identified by 'P@ssword1!'; mysql> grant replication slave on *.* to repl@'192.168.100.%';
连上两个slave,开启复制线程。
# 以下在两个slave节点上都执行 change master to master_host='192.168.100.22', master_user='repl', master_password='P@ssword1!', master_port=3306, master_log_file='master-bin.000001', master_log_pos=4; start slave;
如此配置之后,3个MySQL节点就保持了同步。
1.2 向ProxySQL中添加MySQL节点
首先启动ProxySQL。
service proxysql start
启动后会监听两个端口,默认为6032和6033。6032端口是ProxySQL的管理端口,6033是ProxySQL对外提供服务的端口。
[root@s1 ~]# netstat -tnlp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1231/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1231/proxysql tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1152/sshd tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 2151/master tcp6 0 0 :::22 :::* LISTEN 1152/sshd tcp6 0 0 ::1:25 :::* LISTEN 2151/master
然后使用mysql客户端连接到ProxySQL的管理接口(admin interface),该接口的默认管理员用户和密码都是admin。
[root@s1 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> '
我这里重新设置了提示符。
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.00 sec)
ProxySQL提供了几个库,每个库都有各自的意义。本文只是快速入门文章,不会详细介绍每个库中的每个表以及每个字段,不过在接下来的文章中,我会详细介绍到每个字段,因为每个字段都重要。
在本文,主要修改main和monitor数据库中的表。
admin> show tables from main; +--------------------------------------------+ | 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 | +--------------------------------------------+ admin> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+
runtime_
开头的是运行时的配置,这些是不能修改的。要修改ProxySQL的配置,需要修改了非runtime_
表,修改后必须执行LOAD ... TO RUNTIME
才能加载到RUNTIME生效,执行save ... to disk
才能将配置持久化保存到磁盘。具体操作见后文。
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.22',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.23',3306); insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.100.24',3306);
注:上面语句中没有先切换到main库也执行成功了,因为ProxySQL内部使用的SQLite3数据库引擎,和MySQL的解析方式是不一样的。即使执行了
USE main
语句也是无任何效果的,但不会报错。
查看这3个节点是否插入成功,以及它们的状态。请认真读一读每个字段的名称,混个眼熟。
admin> select * from mysql_servers\G *************************** 1. row *************************** hostgroup_id: 10 hostname: 192.168.100.22 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: 192.168.100.23 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: 192.168.100.24 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;
1.3 监控后端MySQL节点
添加节点之后,还需要监控后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only
值来自动调整它们是属于读组还是写组。
首先在后端master节点上创建一个用于监控的用户名(只需在master上创建即可,因为会复制到slave上),这个用户名只需具有USAGE
权限即可。如果还需要监控复制结构中slave是否严重延迟于master(先混个眼熟:这个俗语叫做"拖后腿",术语叫做"replication lag"),则还需具备replication client
权限。这里直接赋予这个权限。
# 在master上执行: mysql> create user monitor@'192.168.100.%' identified by 'P@ssword1!'; mysql> grant replication client on *.* to monitor@'192.168.100.%';
然后回到ProxySQL上配置监控。
set mysql-monitor_username='monitor'; set mysql-monitor_password='P@ssword1!';
以上设置实际上是在修改global_variables表,它和下面两个语句是等价的:
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value='P@ssword1!' WHERE variable_name='mysql-monitor_password';
修改后,加载到RUNTIME,并保存到disk。
load mysql variables to runtime; save mysql variables to disk;
验证监控结果:ProxySQL监控模块的指标都保存在monitor库的log表中。
以下是连接是否正常的监控(对connect指标的监控):(在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常)
admin> select * from mysql_server_connect_log; +----------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +----------------+------+------------------+-------------------------+---------------+ | 192.168.100.22 | 3306 | 1530968712977867 | 4174 | NULL | | 192.168.100.23 | 3306 | 1530968712988986 | 4908 | NULL | | 192.168.100.24 | 3306 | 1530968713000074 | 3044 | NULL | | 192.168.100.22 | 3306 | 1530968772978982 | 3407 | NULL | | 192.168.100.23 | 3306 | 1530968772989627 | 3404 | NULL | | 192.168.100.24 | 3306 | 1530968773000778 | 3444 | NULL | +----------------+------+------------------+-------------------------+---------------+
以下是对心跳信息的监控(对ping指标的监控):
admin> select * from mysql_server_ping_log; +----------------+------+------------------+----------------------+-------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +----------------+------+------------------+----------------------+-------------+ | 192.168.100.22 | 3306 | 1530968712666540 | 452 | NULL | | 192.168.100.23 | 3306 | 1530968712668779 | 458 | NULL | | 192.168.100.24 | 3306 | 1530968712671541 | 324 | NULL | | 192.168.100.22 | 3306 | 1530968722667071 | 1190 | NULL | | 192.168.100.23 | 3306 | 1530968722669574 | 1162 | NULL | | 192.168.100.24 | 3306 | 1530968722673162 | 1380 | NULL | | 192.168.100.22 | 3306 | 1530968732668840 | 1065 | NULL | | 192.168.100.23 | 3306 | 1530968732670709 | 1054 | NULL | | 192.168.100.24 | 3306 | 1530968732672703 | 1040 | NULL | +----------------+------+------------------+----------------------+-------------+
但是,read_only和replication_lag的监控日志都为空。
admin> select * from mysql_server_read_only_log; Empty set (0.00 sec) admin> select * from mysql_server_replication_lag_log; Empty set (0.00 sec)
这是因为还没有对ProxySQL中的节点分组:writer_hostgroup、reader_hostgroup。设置分组信息,需要修改的是main库中的mysql_replication_hostgroups表,该表只有3个字段:第一个字段名为writer_hostgroup,第二个字段为reader_hostgroup,第三个字段为注释字段,可随意写。
例如,指定写组的id为10,读组的id为20。
insert into mysql_replication_hostgroups values(10,20);
在该配置加载到RUNTIME生效之前,先查看下各mysql server所在的组。
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+----------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------------+------+--------+--------+ | 10 | 192.168.100.22 | 3306 | ONLINE | 1 | | 10 | 192.168.100.23 | 3306 | ONLINE | 1 | | 10 | 192.168.100.24 | 3306 | ONLINE | 1 | +--------------+----------------+------+--------+--------+
3个节点都在hostgroup_id=10的组中。
现在,将刚才mysql_replication_hostgroups
表的修改加载到RUNTIME生效。
load mysql servers to runtime; save mysql servers to disk;
一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10
的写组,slave1和slave2都是slave,它们的read_only=1,这两个节点将会移动到id=20
的组。如果一开始这3节点都在id=20
的读组,那么移动的将是Master节点,会移动到id=10
的写组。
看结果:
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+----------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+----------------+------+--------+--------+ | 10 | 192.168.100.22 | 3306 | ONLINE | 1 | | 20 | 192.168.100.23 | 3306 | ONLINE | 1 | | 20 | 192.168.100.24 | 3306 | ONLINE | 1 | +--------------+----------------+------+--------+--------+
admin> select * from mysql_server_read_only_log; +----------------+------+------------------+-----------------+-----------+--------+ | hostname | port | time_start_us | success_time_us | read_only | error | +----------------+------+------------------+-----------------+-----------+--------+ | 192.168.100.22 | 3306 | 1530970372197917 | 8487 | 0 | NULL | | 192.168.100.23 | 3306 | 1530970372198992 | 7907 | 1 | NULL | | 192.168.100.24 | 3306 | 1530970372199835 | 8064 | 1 | NULL | | 192.168.100.22 | 3306 | 1530970373698824 | 10078 | 0 | NULL | | 192.168.100.23 | 3306 | 1530970373699825 | 9845 | 1 | NULL | | 192.168.100.24 | 3306 | 1530970373700786 | 10745 | 1 | NULL | +----------------+------+------------------+-----------------+-----------+--------+
1.4 配置mysql_users
上面的所有配置都是关于后端MySQL节点的,现在可以配置关于SQL语句的,包括:发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。
本小节是SQL请求所使用的用户配置,例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例。
首先,在master节点上执行:(只需master执行即可,会复制给两个slave)
grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!'; grant all on *.* to sqlsender@'192.168.100.%' identified by 'P@ssword1!';
然后回到ProxySQL,配置mysql_users表,将刚才的两个用户添加到该表中。
insert into mysql_users(username,password,default_hostgroup) values('root','P@ssword1!',10); insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10); load mysql users to runtime; save mysql users to disk;
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
组中的某个节点。
admin> select * from mysql_users\G *************************** 1. row *************************** username: root password: P@ssword1! active: 1 # 注意本行 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 *************************** 2. row *************************** username: sqlsender password: P@ssword1! active: 1 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
虽然本文不详细介绍mysql_users表,但上面标注了"注意本行"的两个字段必须要引起注意。
只有active=1
的用户才是有效的用户。
至于transaction_persistent
字段,当它的值为1时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,所有的语句都路由到同一个组中,避免语句分散到不同组。在以前的版本中,默认值为0,不知道从哪个版本开始,它的默认值为1。我们期望的值为1,所以在继续下面的步骤之前,先查看下这个值,如果为0,则执行下面的语句修改为1。
update mysql_users set transaction_persistent=1 where username='root'; update mysql_users set transaction_persistent=1 where username='sqlsender'; load mysql users to runtime; save mysql users to disk;
然后,另开一个终端,分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10
(它是一个写组)读、写数据。
[root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "select @@server_id" +-------------+ | @@server_id | +-------------+ | 110 | +-------------+ [root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "create database proxy_test" [root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | proxy_test | | sys | +--------------------+ [root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e '\ use proxy_test;\ create table t(id int);' [root@s1 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;' +-------------------------+ | Tables_in_proxy_test | +-------------------------+ | t | +-------------------------+
1.5 读写分离:配置路由规则
ProxySQL的路由规则非常灵活,可以基于用户、基于schema以及基于每个语句实现路由规则的定制。
本文作为入门文章,实现一个最简单的语句级路由规则,从而实现读写分离。必须注意,这只是实验,实际的路由规则绝不应该仅根据所谓的读、写操作进行分离,而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。
和查询规则有关的表有两个:mysql_query_rules
和mysql_query_rules_fast_routing
,后者是前者的扩展表,1.4.7之后才支持该快速路由表。本文只介绍第一个表。
插入两个规则,目的是将select语句分离到hostgroup_id=20
的读组,但由于select语句中有一个特殊语句SELECT...FOR UPDATE
它会申请写锁,所以应该路由到hostgroup_id=10
的写组。
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); load mysql query rules to runtime; save mysql query rules to disk;
select ... for update
规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。
再来测试下,读操作是否路由给了hostgroup_id=20
的读组。
[root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id' mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 120 | +-------------+ [root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e 'select @@server_id' mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 130 | +-------------+
读操作已经路由给读组,再看看写操作。这里以事务持久化进行测试。
[root@s1 ~]# mysql -uroot -pP@ssword1! -P6033 -h127.0.0.1 -e '\ start transaction;\ select @@server_id;\ commit;\ select @@server_id;' +-------------+ | @@server_id | +-------------+ | 110 | +-------------+ +-------------+ | @@server_id | +-------------+ | 120 | +-------------+
显然,一切都按照预期进行。
最后,如果想查看路由的信息,可查询stats库中的stats_mysql_query_digest
表。以下是该表的一个输出格式示例(和本文无关)。
admin> 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 | +----+----------+------------+-------------------------------------------------------------+ | 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? | | 1 | 3142041 | 5001 | COMMIT | | 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | | 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? | | 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? | | 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? | | 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? | | 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | | 1 | 875343 | 5005 | BEGIN | +----+----------+------------+-------------------------------------------------------------+
至此,MySQL的读写分离尝鲜结束。该系列后面的文章将详细介绍ProxySQL的各个方面。
上一篇: 夜晚拍照不能对焦的解决方法
下一篇: 雾霾天怎么样拍出好照片?雾霾天的拍照技巧