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中的資訊
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]
在這一段中，将展示一個如何使用正規表達式來實作讀寫分離的示例。
-- >>>>>>>>>>>>>>>>>>配置方式一(不建議):
！！！！請不要在生産中使用下面這個示例配置！！！！
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、結果驗證
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 ![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)