Proxysql搭建mysql读写分离

读写分离可能是数据库中间件最常用的功能之一，其他比较常用的查询路由功能是分片和路由查询语句到指定副本等

配置读写分离用不同端口

如果使用正常的tcp协议的mysql中间件，通常需要配置2个端口，是配置读写分离的通用方法。

以下是一个基于Proxysql admin命令行的读写分离的例子，假定你已配置好 proxysql主机组：写用hostgroup为1，read用hostgroup为2。

配置proxysql监听的2个端口，然后重启， mysql-interfaces是少数几个需要更改后重启的服务之一。

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402'; ## save it on disk and restart proxysql SAVE MYSQL VARIABLES TO DISK; PROXYSQL RESTART;

增加路由匹配端口规则：

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,1,1), (2,1,6402,2,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

现在从端口6401进入的请求发往主机组1，端口6402进入的队列发往主机组2

基于流入端口区分读写的限制：

先清理掉之前配置的规则：

DELETE FROM mysql_query_rules;

创建通用的读写分离规则

UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG1 LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; # if you want this change to be permanent INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',1,1), (2,1,'^SELECT',2,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

这些语句有如下作用：

SELECT FOR UPDATE 语句被发送到HG1组；

其他的SELECT语句被发送到HG2组；

其他所有内容被发送到HG1组；

以上的规则是有问题的，不能用于生产环境，更好的方案是依据实际负载来制定合理的规则，清空上述规则然后执行：

DELETE FROM mysql_query_rules; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

智能的读/写规则，使用正则和摘要

有效的规则建议：

开始配置一台读/写的主机，即主库；

在表stats_mysql_query_digest进行分析，找出最耗资源的（就是最贵）的几条语句

确定哪些语句流入读节点（评估读节点的配置负载是否能支撑这些语句）

完成mysql_query_rules配置，选择性的流入副本

在表stats_mysql_query_digest中发现高负载语句的规律

下面的例子展示怎么在stats库中找出潜在分流到副本的语句，你可以直接执行sql而非必须在CLI模式下

这是一组运行几个月的sql语句统计

执行时间前五的语句：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5; +--------------------+--------------------------+------------+---------------+ | digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      | +--------------------+--------------------------+------------+---------------+ | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 | | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 | | 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 | | 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  | | 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  | +--------------------+--------------------------+------------+---------------+ 5 rows in set (0.01 sec)

执行次数前五的语句：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5; +--------------------+--------------------------+------------+---------------+ | digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      | +--------------------+--------------------------+------------+---------------+ | 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 | | 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 | | 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  | | 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  | | 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  | +--------------------+--------------------------+------------+---------------+ 5 rows in set (0.00 sec)

最大执行时间排名前五的语句：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5; +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ | digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  | +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ | 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 | | 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 | | 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 | | 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 | | 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 | +--------------------+--------------------------+------------+--------------+----------+----------+-----------+ 5 rows in set (0.01 sec)

总执行时间前五并且最小执行时间大于1000毫秒的语句

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5; +--------------------+--------------------------+------------+-------------+----------+----------+----------+ | digest             | SUBSTR(digest_text,0,20) | count_star | sum_time    | avg_time | min_time | max_time | +--------------------+--------------------------+------------+-------------+----------+----------+----------+ | 0x9EED412C6E63E477 | SELECT a.id as acco      | 961733     | 24115349801 | 25074    | 10994    | 7046628  | | 0x8DDD43A9EA37750D | Select ( Coalesce((      | 107069     | 3156179256  | 29477    | 1069     | 24600674 | | 0x9EED412C6E63E477 | SELECT a.id as acco      | 91996      | 1883354396  | 20472    | 10095    | 497877   | | 0x08B23A268C35C08E | SELECT id as reward      | 49401      | 244088592   | 4940     | 1237     | 1483791  | | 0x437C846F935344F8 | SELECT Distinct i.e      | 164        | 163873101   | 999226   | 1383     | 7905811  | +--------------------+--------------------------+------------+-------------+----------+----------+----------+ 5 rows in set (0.01 sec)

总执行时间前五，并且平均时间大于1秒且显示这条语句执行时间在所有语句执行时间里所占的百分 比

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5; +--------------------+--------------------------+------------+--------------+----------+-------+ | digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | pct   | +--------------------+--------------------------+------------+--------------+----------+-------+ | 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 2.11  | | 0xD38895B4F4D2A4B3 | SELECT instance.name as  | 9783       | 12409642528  | 1268490  | 0.141 | | 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 0.015 | +--------------------+--------------------------+------------+--------------+----------+-------+ 3 rows in set (0.00 sec)

并不是所有查询语句都要走主库，取决于实际中的需要，如果一条语句执行只需15ms,却很少更新，引流到从库比较合适

还有种情况，有些语句最大执行时间很大，最小执行时间很小，平均时间也是很慢，这种情况可能要研究下为什么会有如此大的随机性，说不定能查出点什么问题呢

在通过可靠的分析后，我们把摘要是0x38BE36BDFFDBE638放至从库

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (1,1,'0x38BE36BDFFDBE638',20,1);

同样的，检查 类 SELECT COUNT(*)语句的执行情况

SELECT digest,digest_text,count_star,sum_time,sum_time/count_star avg_time,       ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT COUNT%' ORDER BY sum_time DESC;

分析后发现将所有SELECT COUNT(*)开头的语句放到从库是个好的选择

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT COUNT\(\*\)',20,1);

载入运行和disk配置

LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

最后，评估你的工作流中是否有新的语句需要优化，它肯定会发生变化，这是个持续的过程，运用合理的方法去分析，目标是达到最大的价值