15_ProxySQL读写分离
备注：文章编写时间201904-201905期间，后续官方在github的更新没有被写入 ~ ~ ProxySQL读写分离设置[ProxySQL Read Write Split]
查询路由是ProxySQL的核心功能之一。读写分离可能是最常用的查询路由之一，而另一种最常用的查询路由实现是用于分片(sharding)。
一、使用不同的端口实现读写分离[Read/write split using different ports]
如果使用类似haproxy的代理，可以将其配置为监听两个端口：一个端口作为写入的端口，另一个端口作为读取的端口。人们经常会问如何使用相同的方法配置ProxySQL，以及如何现实基于传入端口的路由查询。
下面是一个关于如何基于传入端口实现查询路由的示例，要在ProxySQL的Admin管理界面运行下面的内容。这里假设您已经在正确的hostgroups中配置了master和slaves： hostgroup 10中的mysql writer，hostgroup 20中的mysql readers。如果您正在使用Galera或组复制，则将应用类似的方法。步骤如下：
1、修改接入端口相关参数
将ProxySQL配置为监听两个端口并重新启动：mysql-interfaces 是少数几个在运行时无法动态更改，需要重新启动的变量之一。
1）当前参数值
Admin> SELECT * FROM global_variables WHERE variable_name ='mysql-interfaces';
+------------------+----------------+
| variable_name | variable_value |
+------------------+----------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+----------------+
1 row in set (0.00 sec)
或用SHOW命令
Admin> SHOW VARIABLES LIKE 'mysql-interfaces';
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+--------------+
1 row in set (0.00 sec)
2）修改配置
Admin> UPDATE global_variables SET variable_value='0.0.0.0:6401;0.0.0.0:6402' WHERE variable_name ='mysql-interfaces';
或
Admin> SET mysql-interfaces = '0.0.0.0:6401;0.0.0.0:6402';
3）查看修改结果
Admin> SELECT * FROM global_variables where variable_name ='mysql-interfaces';
+------------------+---------------------------+
| variable_name | variable_value |
+------------------+---------------------------+
| mysql-interfaces | 0.0.0.0:6401;0.0.0.0:6402 |
+------------------+---------------------------+
1 row in set (0.00 sec)
4）将修改结果持久化
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.00 sec)
5）重启ProxySQL服务
Admin> PROXYSQL RESTART;
2、根据传入端口添加路由规则
1）查看当前后端MySQL主机组配置
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 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)
2）清除旧的分组
Admin> DELETE FROM mysql_servers ;
Query OK, 6 rows affected (0.00 sec)
Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
3）新增mysql writer组10和mysql readers组20
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES(10,'188.188.0.68',3306),(20,'188.188.0.69',3306),(20,'188.188.0.70',3306);
Query OK, 3 rows affected (0.00 sec)
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 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
4）查看当前拥有的规则
Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 5 | 1 | NULL | NULL | 1 |
| 10 | 1 | NULL | 2 | 1 |
| 20 | 1 | NULL | 2 | 0 |
| 30 | 1 | NULL | NULL | 1 |
+---------+--------+------------+-----------------------+-------+
4 rows in set (0.00 sec)
5）删除旧规则
Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)
Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
Empty set (0.00 sec)
6）插入路由规则
Admin> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
-> VALUES (1,1,6401,10,1), (2,1,6402,20,1);
Query OK, 2 rows affected (0.00 sec)
Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1 | 1 | 6401 | 10 | 1 |
| 2 | 1 | 6402 | 20 | 1 |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)
7）将修改后的配置加载到RUNTIME层
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
Admin> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 188.188.0.70 | 3306 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM runtime_mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1 | 1 | 6401 | 10 | 1 |
| 2 | 1 | 6402 | 20 | 1 |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)
8）将修改后的配置持久化到DISK层
Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.02 sec)
Admin> SELECT * FROM disk.mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)
Admin> SELECT rule_id,active,proxy_port,destination_hostgroup,apply FROM disk.mysql_query_rules;
+---------+--------+------------+-----------------------+-------+
| rule_id | active | proxy_port | destination_hostgroup | apply |
+---------+--------+------------+-----------------------+-------+
| 1 | 1 | 6401 | 10 | 1 |
| 2 | 1 | 6402 | 20 | 1 |
+---------+--------+------------+-----------------------+-------+
2 rows in set (0.00 sec)
到此配置完成！ 现在，所有到 6401 端口的查询都将发送到hostgroup 10中的mysql服务器，而所有到 6402 端口的查询都将发送到hostgroup 20中的一个mysql服务器。
3、验证结果
1）查看当前规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules
-> NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 0 | 1 | NULL | NULL | NULL | NULL | 1 |
| 0 | 2 | NULL | NULL | NULL | NULL | 1 |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.01 sec)
2）在后端可写MySQL上新建测试表
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
3）通过读写账户 6401 连入 ProxySQL 执行写入操作：
[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6401
mysql> use test;
mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
4）查看ProxySQL中的信息
查看Proxy SQL中进程信息
Admin> show full processlist;
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2 | 134 | test_rw | test | 188.188.0.70 | 37072 | 10 | | | | | Sleep | 10010 | | |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)
可以看到，我们在188.188.0.70发起连接，操作的是写组 hostgroup 10，库为test；和我们预期的一样。
查看规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 6 | 1 | NULL | NULL | NULL | NULL | 1 |
| 0 | 2 | NULL | NULL | NULL | NULL | 1 |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
规则1有了命中信息。 备注:只要是向 6401 发出的指令，如show、use等都会使hits增加。
5）通过读写账户 6402 连入 ProxySQL 执行读取操作：
[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6402
mysql> SELECT * FROM test.t1 ;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
6）查看ProxySQL中的信息
查看Proxy SQL中进程信息
Admin> show full processlist;
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 0 | 135 | test_rw | information_schema | 188.188.0.70 | 47732 | 20 | | | | | Sleep | 18018 | | |
+----------+-----------+---------+--------------------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)
可以看到，我们在188.188.0.70发起连接，操作的是写组 hostgroup 20，库为test；和我们预期的一样。
查看规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+--------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+--------------+---------------+-----------------+-----------+-------+
| 6 | 1 | NULL | NULL | NULL | NULL | 1 |
| 2 | 2 | NULL | NULL | NULL | NULL | 1 |
+------+---------+--------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
规则2也有了命中信息。
说明：如果先前连入过，则会受到连接池的影响，在连入时不会产生命中数增加。
到此，不同端口的读写分离配置完毕！！
二、基于传入端口的读写分离限制[Limitation of read/write split based on incoming port]
在前面提到过人们经常会问如何配置ProxySQL以使用基于传入端口的路由。虽然这有时是一种有效的方法，但在我看来它有一个很大的缺点： 使用数据库的应用程序需要具有内置的读写分离功能，以便区分读和写。但通常情况并非如此。 通常应用程序只使用一个连接端点，当然它是MySQL主节点。如果使用ProxySQL，则可以在单个端口中接受所有的流量，并可以根据查询类型分析流量以执行读/写拆分。这非常方便，因为它不需要任何应用程序更改。
尽管如此，其主要优势不是能够在不更改应用程序的情况下路由流量。主要的优点是DBA现在有了控制发送到数据库流量的工具。DBA是一个在午夜，由于数据库服务器过载，而被叫醒的群体；而且，因为当时周围没有开发人员，想在应用程序中进行更改是不可能的；但他们现在有了控制流量的选项。
三、(基于1个端口)使用正则表达式的实现基本读写分离[basic read/write split using regex]
在这一段中，将展示一个如何使用正则表达式来实现读写分离的示例。
-- >>>>>>>>>>>>>>>>>>配置方式一(不建议):
！！！！请不要在生产中使用下面这个示例配置！！！！
1、修改接入端口相关参数
Admin> SHOW VARIABLES LIKE 'mysql_interfaces';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| mysql-interfaces | 0.0.0.0:6401;0.0.0.0:6402 |
+------------------+---------------------------+
1 row in set (0.00 sec)
改用一个端口对外提供接入。
Admin> SET mysql-interfaces ='0.0.0.0:6033';
Query OK, 1 row affected (0.00 sec)
将修改持久化
Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.02 sec)
重启ProxySQL服务，使配置生效
Admin> PROXYSQL RESTART;
查看修改结果
Admin> SHOW VARIABLES LIKE 'mysql_interfaces';
+------------------+--------------+
| Variable_name | Value |
+------------------+--------------+
| mysql-interfaces | 0.0.0.0:6033 |
+------------------+--------------+
1 row in set (0.00 sec)
2、实验前删除以前创建的查询规则：
Admin> DELETE FROM mysql_query_rules;
Query OK, 6 rows affected (0.00 sec)
Admin> SELECT * FROM mysql_query_rules ;
Empty set (0.00 sec)
3、创建读/写的基本规则：
1）修改目标用户的默认组
Admin> SELECT username,active,default_hostgroup FROM mysql_users WHERE username='test_rw';
+----------+--------+-------------------+
| username | active | default_hostgroup |
+----------+--------+-------------------+
| test_rw | 1 | 1 |
+----------+--------+-------------------+
1 row in set (0.00 sec)
# by default, all goes to HG10
Admin> UPDATE mysql_users SET default_hostgroup=10 WHERE username='test_rw';
Query OK, 1 row affected (0.00 sec)
Admin> SELECT username,active,default_hostgroup FROM mysql_users WHERE username='test_rw';
+----------+--------+-------------------+
| username | active | default_hostgroup |
+----------+--------+-------------------+
| test_rw | 1 | 10 |
+----------+--------+-------------------+
1 row in set (0.00 sec)
2）加载配置到RUNTIME层，使其生效
Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT * FROM runtime_mysql_users WHERE username='test_rw';
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 10 | | 0 | 1 | 0 | 1 | 0 | 10000 | |
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 10 | | 0 | 1 | 0 | 0 | 1 | 10000 | |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)
3）持久化配置到DISK层
Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT * FROM disk.mysql_users WHERE username='test_rw';
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 10 | | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)
4）插入读写规则
Admin> 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)
Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 10 | 1 |
| 2 | 1 | ^SELECT | 20 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
5）加载配置到RUNTIME层，使其生效
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM runtime_mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 10 | 1 |
| 2 | 1 | ^SELECT | 20 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
6）持久化配置到DISK层
Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)
Admin> SELECT rule_id,active,match_digest,destination_hostgroup,apply FROM disk.mysql_query_rules ;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | 10 | 1 |
| 2 | 1 | ^SELECT | 20 | 1 |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
现在路由将会按如下工作： 1）所有 SELECT FOR UPDATE 查询都发往 HG10 ; 2）其他所有的 SELECT 查询都发往 HG20 ; 3）除以上2种查询以外，其他的查询都将发往 HG10 (默认组);
4、结果验证
1）查看当前规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules
-> NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 0 | 1 | ^SELECT.*FOR UPDATE$ | NULL | NULL | NULL | 1 |
| 0 | 2 | ^SELECT | NULL | NULL | NULL | 1 |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
规则1、2都是0。
2）通过 6033 端口连入 ProxySQL 执行操作：
[root@localhost ~]# mysql -utest_rw -p -h 188.188.0.71 -P 6033
mysql> use test; ==>触发一次规则2 hits
mysql> select * from t1; ==>触发一次规则2 hits
Empty set (0.00 sec)
mysql> insert into t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1; ==>触发一次规则2 hits
3）操作后，查看ProxySQL中的信息
查看连接信息
Admin> SHOW FULL PROCESSLIST;
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2 | 3 | test_rw | test | 188.188.0.70 | 55705 | 20 | | | | | Sleep | 22022 | | |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)
最后的select请求流入了H20，与预设一致。
查看规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules
-> NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 0 | 1 | ^SELECT.*FOR UPDATE$ | NULL | NULL | NULL | 1 |
| 3 | 2 | ^SELECT | NULL | NULL | NULL | 1 |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
三次触发，使得hits增加了3。
4）测试FOR UPDATE
mysql> select * from t1 for update ;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
5）操作后，查看ProxySQL中的信息
查看连接信息
Admin> SHOW FULL PROCESSLIST;
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
| 2 | 3 | test_rw | test | 188.188.0.70 | 55705 | 10 | | | | | Sleep | 12012 | | |
+----------+-----------+---------+------+--------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+--------------+
1 row in set (0.00 sec)
FOR UPDATE请求流入了HG10，与预设一致。
查看规则命中信息
Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
-> FROM mysql_query_rules
-> NATURAL JOIN stats.stats_mysql_query_rules
-> ORDER BY mysql_query_rules.rule_id;
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
| 1 | 1 | ^SELECT.*FOR UPDATE$ | NULL | NULL | NULL | 1 |
| 3 | 2 | ^SELECT | NULL | NULL | NULL | 1 |
+------+---------+----------------------+---------------+-----------------+-----------+-------+
2 rows in set (0.00 sec)
一次请求，使得hits增加了1。
请注意，我认为上述方法不是读取/写入拆分的好方法。 我经常使用这个例子来描述如何配置规则，但它却经常被错误地解释为配置读/写拆分的方法。
！！！！请不要在生产中使用上述示例配置！！！！
-- >>>>>>>>>>>>>>>>>>配置方式二(推荐):
在下面这段中，将展示一种更好的方法。
现在，让我们删除所有规则：
Admin> DELETE FROM mysql_query_rules;
Query OK, 2 rows affected (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.02 sec)
Admin> SELECT * FROM runtime_mysql_query_rules;
Empty set (0.00 sec)
Admin> SELECT * FROM disk.mysql_query_rules;
Empty set (0.00 sec)
1、使用regex和digest进行读/写拆分[read/write split using regex and digest]
有效设置读/写拆分的配置过程如下： 1）配置 ProxySQL 只将所有流量发送到一个 MySQL 节点——master(写和读); 2）在 stats_mysql_query_digest 中查找执行成本较高的 SELECT 语句; 3）确定哪些成本较高的 SELECT 语句应该被移动到读节点上执行； 4）配置 mysql_query_rules 将需要移动的 SELECT 语句发送到读节点； 因此，这个想法非常简单：只发送你想发送给 Slave/Reader 的内容，而不只是任何SELECT语句。
2、使用stats_mysql_query_digest查找成本较高的查询[Find expensive queries using stats_mysql_query_digest]
下面是一个示例列表，说明如何识别可以发送给Readers的潜在查询。由于 ProxySQL 在表中记录了所有度量信息，所以可以创建复杂的查询来收集信息。 这些结果是基于一个运行了几个月的非常繁忙的 ProxySQL 实例；到目前为止，该实例已经处理了大约千亿个查询。
1）根据总执行时间查找最耗时的前5个查询：
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats.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)
2）根据计数查找前5个查询：
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)
嗯...，这些查询中的一些可以缓存吗？也许proxysql查询缓存可以帮助您！
3）根据最长执行时间查找前5个查询：
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)
具体结果表明，有些查询的最大执行时间很高，而最小执行时间很小，平均速度也相当慢。 例如，Digest为0x36CE5295726DB5B4的查询，平均执行时间为1.27秒，最小执行时间为0.4ms，最大执行时间为237.34秒。也许值得调查一下为什么执行时间不均匀。
4）查找按总执行时间排序的前5个查询，最小执行时间至少为1毫秒：
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)
```
#### 5）查找按总执行时间排序的前5个查询，平均执行时间至少为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)
```
所有这些查询都需要在master上执行？如果一个查询的平均执行时间超过1秒，则答案可能是"No"。
对于某些应用程序，即使运行平均执行时间为15毫秒的查询也可能需要转到slave。
#### 6）编写查询规则
在与应用程序开发人员确认之后，例如，我们可以将digset为 0x38BE36BDFFDBE638 的查询转移到Slave上执行：
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES (1,1,'0x38BE36BDFFDBE638',20,1);
#### 7）同样，假设要将所有以SELECT COUNT(*)开头的查询都可以转到Slaves
查看该类语句的执行情况：
```
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;
```
编写查询规则：
```
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT COUNT\(\*\)',20,1);
```
#### 8）最后，将每个规则加载到RUNTIME层和持久化到DISK层：
```
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent
```
## 四、结论[Conclusion]
ProxySQL可以有效地用于非常有选择性的查询路由。
对于某些应用程序，可以接受将所有 SELECT 发送给Reader/Slave，而将剩余的其他查询则发送给Writer/Master；但对于许多其他applications/workloads来说，并不是那么简单。
DBA应该能够用复杂的规则配置ProxySQL，只将不需要在Master上执行的查询发送到Slaves，而不需要更改任何应用程序。
~
~
完毕！！
