MySQL中间件之ProxySQL_读写分离/查询重写配置
1.闲扯几句
读写分离这是一个扯了好多年的话题，实现方式也也是层出不穷。笔者也曾经使用keepalive+lvs的方式给别人做过读写分离，效果还不错，也不是特别麻烦，用起来蛮好，就是应用有点不喜欢，需要配置读IP和写IP，应用感觉麻烦，不愿用。那么引入中间件，这些都不是事，暴露给应用的还是一个IP一个port，什么读写分离，交给中间件好了，想怎么路由SQL就怎么路由SQL，你开心就好。
2.配个读写分离
2.1准备工作
这里我带领大家配个简单的一主两从，搞个读写分离，配个查询缓存，玩下查询重写。
node1 (192.168.56.101:24801) , mysql master node2 (192.168.56.101:24802) , mysql slave node3 (192.168.56.101:24803) , mysql slave app (192.168.56.101:6033) , app+proxysql
看起来还不错，但是mysql高可用这块还需读者自己去想办法，MHA，MMM,group replication，pxc都可以的哟。
mysql一主两从读者自行搭建。
root@localhost [(none)] 01:43:27>>> show slave hosts; +-----------+------+-------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+-------+-----------+--------------------------------------+ | 3 | | 24803 | 1 | 87626cdd-c918-11e6-b3a6-0800278bcede | | 2 | | 24802 | 1 | 81850b66-c918-11e6-b22e-0800278bcede | +-----------+------+-------+-----------+--------------------------------------+ 2 rows in set (0.00 sec)
2.2配置
现在我们把一主两从的mysql数据库信息添加到proxysql中。我们将主库master也就是做写入的节点放到group 0中，salve节点做读放到group1。
登录proxysql
admin@127.0.0.1 [(none)] 01:48:32>>>INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.56.101',24801); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 01:48:45>>>INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1,'192.168.56.101',24802); Query OK, 1 row affected (0.01 sec) admin@127.0.0.1 [(none)] 01:48:45>>>INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1,'192.168.56.101',24803); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 01:48:46>>>select hostgroup_id,hostname,port,status from mysql_servers; +--------------+----------------+-------+--------+ | hostgroup_id | hostname | port | status | +--------------+----------------+-------+--------+ | 0 | 192.168.56.101 | 24801 | ONLINE | | 1 | 192.168.56.101 | 24802 | ONLINE | | 1 | 192.168.56.101 | 24803 | ONLINE | +--------------+----------------+-------+--------+ 3 rows in set (0.00 sec)
根据安装部署篇的提示，我们在数据库中需要配置好了监控账号(ProxySQL)和应用账号(sbuser)。以下SQL在master节点执行。
CREATE USER 'ProxySQL'@'%' IDENTIFIED BY 'ProxySQLPa55'; GRANT USAGE ON *.* TO 'ProxySQL'@'%'; CREATE USER 'sbuser'@'%' IDENTIFIED BY 'sbpass'; GRANT ALL ON * . * TO 'sbuser'@'%'; FLUSH PRIVILEGES;
确认账号OK：
root@localhost [(none)] 01:57:12>>>select host,user from mysql.user; +-----------+-----------+ | host | user | +-----------+-----------+ | % | proxysql | | % | rpl_user | | % | sbuser | | localhost | mysql.sys | | localhost | root | +-----------+-----------+ 5 rows in set (0.00 sec)
在proxysql中添加账号，
admin@127.0.0.1 [(none)] 01:59:49>>>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('sbuser','sbpass',0); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 01:59:50>>>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.01 sec) admin@127.0.0.1 [(none)] 01:59:50>>>UPDATE global_variables SET variable_value='ProxySQLPa55' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 01:59:51>>>LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) /*将配置应用于proxysql运行环境*/ admin@127.0.0.1 [(none)] 02:00:45>>>SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.05 sec) /*将配置存储到sqlite数据库中*/
查看proxysql监控数据库的一些指标
admin@127.0.0.1 [(none)] 02:20:48>>>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start DESC LIMIT 10; +----------------+-------+------------+----------------------+---------------+ | hostname | port | time_start | connect_success_time | connect_error | +----------------+-------+------------+----------------------+---------------+ | 192.168.56.101 | 24803 | 604984013 | 360 | NULL | | 192.168.56.101 | 24802 | 604983410 | 383 | NULL | | 192.168.56.101 | 24801 | 604982712 | 472 | NULL | | 192.168.56.101 | 24803 | 602983284 | 301 | NULL | | 192.168.56.101 | 24802 | 602982770 | 360 | NULL | | 192.168.56.101 | 24801 | 602982025 | 576 | NULL | | 192.168.56.101 | 24803 | 600982931 | 394 | NULL | | 192.168.56.101 | 24802 | 600982168 | 1540 | NULL | | 192.168.56.101 | 24801 | 600981405 | 507 | NULL | | 192.168.56.101 | 24803 | 598982425 | 982 | NULL | +----------------+-------+------------+----------------------+---------------+ 10 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 02:20:50>>>SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start DESC LIMIT 10; +----------------+-------+------------+-------------------+------------+ | hostname | port | time_start | ping_success_time | ping_error | +----------------+-------+------------+-------------------+------------+ | 192.168.56.101 | 24803 | 606998231 | 58 | NULL | | 192.168.56.101 | 24802 | 606997865 | 75 | NULL | | 192.168.56.101 | 24801 | 606996830 | 93 | NULL | | 192.168.56.101 | 24803 | 604997051 | 39 | NULL | | 192.168.56.101 | 24802 | 604996633 | 42 | NULL | | 192.168.56.101 | 24801 | 604996278 | 54 | NULL | | 192.168.56.101 | 24803 | 602997075 | 42 | NULL | | 192.168.56.101 | 24802 | 602996658 | 85 | NULL | | 192.168.56.101 | 24801 | 602996275 | 63 | NULL | | 192.168.56.101 | 24803 | 600997230 | 47 | NULL | +----------------+-------+------------+-------------------+------------+ 10 rows in set (0.00 sec)
使用业务用户通过proxysql登录mysql。如果卡住了，可能是DNS解析引起。
[mysql@hpc01 ~]$ mysql -u sbuser -psbpass -h 127.0.0.1 -P6033 -e "select @@port" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | @@port | +--------+ | 24801 | +--------+
好了，用户通过proxysql登录master，现在初始化数据库。
[mysql@hpc01 ~]$ sysbench --test=oltp --oltp-table-size=4000 --oltp-read-only=off --init-rng=on --num-threads=5 --max-requests=0 --oltp-dist-type=uniform --max-time=36 --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=sbtest --oltp-tables-count=5 --report-interval=1 prepare sysbench 1.0: multi-threaded system evaluation benchmark Creating table 'sbtest1'... Inserting 4000 records into 'sbtest1' Creating secondary indexes on 'sbtest1'... Creating table 'sbtest2'... Inserting 4000 records into 'sbtest2' Creating secondary indexes on 'sbtest2'... Creating table 'sbtest3'... Inserting 4000 records into 'sbtest3' Creating secondary indexes on 'sbtest3'... Creating table 'sbtest4'... Inserting 4000 records into 'sbtest4' Creating secondary indexes on 'sbtest4'... Creating table 'sbtest5'... Inserting 4000 records into 'sbtest5' Creating secondary indexes on 'sbtest5'...
清空查询统计，然后运行一次sysbench，查看proxysql统计信息。
下面这个sql在proxysql是专门清空stats_mysql_query_digest表的。
admin@127.0.0.1 [(none)] 02:24:45>>>SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.01 sec)
使用sysbench来压测mysql。
[mysql@hpc01 ~]$ sysbench --test=oltp --oltp-table-size=4000 --oltp-read-only=off --init-rng=on --num-threads=5 --max-requests=0 --oltp-dist-type=uniform --max-time=36 --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=sbtest --oltp-tables-count=5 --report-interval=1 run
然后我们查看proxysql为我们统计了那些信息呢。
非常不错的统计信息，各种命令的聚合统计，方便我们监控数据库负载趋势，一眼就可以看出读多还是写多。
还有更具体的统计信息。
proxysql converts queries into fingerprints。然后对每类sql做聚合统计。
2.3读写分离配置
细心同学就发现了，现在所有的sql都发送到了group 0，也就是我们的master主库节点。好咯，我们来做一个读写分离。
要注意，proxysql没有设计多么复杂的读写分离算法，proxysql的实现方式很简单，就是做sql路由，我们自定义sql路由规则就可以实现读写分离。这里我们将所有除了select* from update的select全部发送到slave，其他的的语句发送到master。
登录proxysql配置路由项。
admin@127.0.0.1 [(none)] 02:41:58>>>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',0,1); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:41:59>>>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1,1); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:41:59>>>LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 [(none)] 02:42:20>>>SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.04 sec)
聪明的同学就发现了，原来这么匹配是基于正则表达式的啊。right。
active表示是否启用这个sql路由项，
match_pattern就是我们正则匹配项，
destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去，这里我们将select转发到group 1，也就是两个slave上。
apply为1表示该正则匹配后，将不再接受其他匹配，直接转发。
添加了sql路由，我们来看看是否实现了读写分离呢。
首先记得清空proxysql的query统计
admin@127.0.0.1 [(none)] 02:48:56>>>SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
直接用sysbench压测
[mysql@hpc01 ~]$ sysbench --test=oltp --oltp-table-size=4000 --oltp-read-only=off --init-rng=on --num-threads=5 --max-requests=0 --oltp-dist-type=uniform --max-time=36 --mysql-user=sbuser --mysql-password='sbpass' --db-driver=mysql --mysql-port=6033 --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-db=sbtest --oltp-tables-count=5 --report-interval=1 run
查看结果
可以看到，所有的非select*for update的查询语句都已经转发到slave了，也就是group 1.
登录到slave上我们确实可以到很多查询已经切过来了。
3.查询重写
3.1配置
查询重写这种东西，对于线上环境紧急故障处理还是很有用处的。如果定位到了问题所在，必须修改SQL，时间紧急，让应用重新发布上线是不太现实了，这时查询重写这个东西就非常有用了。
举个简单的例子
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c
这类SQL是有优化余地的，我们可以去掉ORDER BY c，多此一举，因为在做DISTINCT时我们已经做了排序。需要改成如下模式
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?
其实查询重写的实现在proxysql中也实现为正则匹配替换。是不是非常赞，不需要非常复杂的算法就实现了，非常赞，用户可控度也大，用着非常舒服。
我们编辑一条SQL路由规则。
INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern,apply) VALUES (1,'DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
这条规则什么意思呢，这条路由规则表示当proxysql匹配到DISTINCT<若干字符>ORDER BY c这个模式后，就将这个模式的ORDER BY c去掉。那么DISTINCT\1中的\1是什么意思呢，玩过sed的同学很快就反应过来，这不就是向前引用么，哈哈答对了。举个例子你就明白了。
假如我们有这样一个文件:
[root@hpc01 ~]$ cat aaa ,90909 ,dasfsdfssd98908
我们想把每行的逗号去掉，我们可以利用sed的向前引用:
[root@hpc01 ~]$ sed "s/,\(.*\)/\1/g" aaa 90909 dasfsdfssd98908
逗号后面的所有字符用\(.*\)表示，在后面替换我们用\1表示这部分。sed最大支持到\9，还是很强大的。如果实在不明白什么意思，推荐学习学习《sed与awk》。反正要玩proxysql正则表达式要玩得溜溜的。
还要注意，我们要重写的selete语句，你可曾记得，我们已经将所有非seletc* from update语句已经重定向到slave，也就是select语句已经有了一条路由规则了。而且那条路由已经将apply字段设为1，也就是不再接受其他路由规则。好，我们需要更改apply字段才可以使这条规则生效。
update mysql_query_rules set apply=0 where match_pattern='^SELECT';
使配置生效
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
注意，以上配置都是在proxysql中操作的，别跑错了跑到数据库中搞啊。
3.2验证配置
首先清空以前的统计信息，不然混在一起，无法辨认。
admin@127.0.0.1 [(none)] 03:16:37>>>SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.01 sec)
直接使用sysbench压测
然后查看proxysql统计信息。
哈哈，是不是distinct后面的order by已经没有了。厉害了，word proxysql。
