目的
在上一篇文章MySQL ProxySQL讀寫分離使用初探裡初步介紹了ProxySQL的使用,本文繼續介紹它的一些特點和DBProxy的性能差異。深入一些去了解ProxySQL,通過例子來說明ProxySQL的一些特性和用sysbench對其進行測試來對比分析說明。
環境:
Distributor ID: Ubuntu
Description : Ubuntu 14.04.5 LTS
Release : 14.04
Codename : trusty
MySQL Master :192.168.200.202
MySQL Slave :192.168.200.132
APP IP :192.168.200.25/64
測試
本文測試環境是在上一篇文章的基礎上進行的,是以已經做了一主一從的讀寫分離。在此基礎上,若從庫挂了,會怎麼樣?這裡先把從庫(132)shutdown掉,看看讀去了哪裡。
tips:如何修改管理接口的使用者名密碼?除了初始化時候修改配置檔案,還有一個方法是在管理端口設定:
[email protected] : (none) 12:52:53>set admin-admin_credentials='zjy:zjy'; Query OK, 1 row affected (0.00 sec)
admin子產品修改(select * from global_variables where variable_name like 'admin%';)需要用admin加載:
[email protected] : (none) 12:53:02>load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)
[email protected] : (none) 12:53:33>save admin variables to disk;
Query OK, 9 rows affected (0.00 sec)
1,從庫不可用
① 關閉從庫
目前ProxySQL下後端MySQL的運作情況:
[email protected] : (none) 11:25:26>select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 100 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.132 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
shutdown從庫(132)後,後端MySQL的運作情況:
[email protected] : (none) 11:33:24>select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 100 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.132 | 3306 | SHUNNED |
+--------------+-----------------+------+---------+
此時讀的操作會報逾時:
[email protected] : sbtest 11:30:40>select * from x;
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 1000 after 10000ms
原因是proxysql的核心都在規則,shutdown從之後,proxysql還是想路由到 hostgroup=1000,它不會自動選擇預設的100(mysql_users裡配置的default_hostgroup) 。
這裡解決的辦法是:在mysql_servers的hostgroup 1000 裡面要插一條主庫的記錄,然後把weight設小,當讀不到從庫,回去主庫查詢。
[email protected] : (none) 11:50:13>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.200.202',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 11:50:42>update mysql_servers set weight=9 where hostgroup_id=1000 and hostname='192.168.200.132';
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 11:53:41>select hostgroup_id,hostname,port,weight from mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | weight |
+--------------+-----------------+------+--------+
| 100 | 192.168.200.202 | 3306 | 1 |
| 1000 | 192.168.200.132 | 3306 | 9 |
| 1000 | 192.168.200.202 | 3306 | 1 |
+--------------+-----------------+------+--------+
[email protected] : (none) 11:54:03>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
[email protected] : (none) 11:54:28>save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)
[email protected] : (none) 11:54:38>select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+-----------------+------+---------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+---------+
| 100 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.132 | 3306 | SHUNNED |
| 1000 | 192.168.200.202 | 3306 | ONLINE |
+--------------+-----------------+------+---------+
此時讀的操作正常:
[email protected] : sbtest 11:52:37>select * from x;
+------+
| id |
+------+
| 123 |
| 123 |
| 123 |
+------+
3 rows in set (0.01 sec)
說明從關閉了之後讀操作确實去主上執行了。當從庫恢複之後,以後的讀操作主庫也可以處理 1/10 的讀請求。
② 從庫延遲/從庫停止複制
在上一篇文章中已經建立了監控賬号:proxysql,由于需要執行show slave status的指令來獲得延遲時間,是以需要權限SUPER 和 REPLICATION CLIENT。并且需要設定mysql_servers.max_replication_lag的值,由于mysql_servers.max_replication_lag僅适用于從,但也可以将其配置為所有主機,無論是從還是主(不會有任何影響)。
-- 設定監控賬号權限
[email protected] : sbtest 10:44:38>GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY PASSWORD '*BF27B4C7AAD278126E228AA8427806E870F64F39';
Query OK, 0 rows affected (0.00 sec)
-- 設定延遲的門檻值
[email protected] : (none) 11:04:50>UPDATE mysql_servers SET max_replication_lag=5; Query OK, 3 rows affected (0.00 sec)
-- 應用配置
[email protected] : (none) 11:04:54>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
[email protected] : (none) 11:05:04>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
主從複制正常的情況下,後端MySQL的情況:
[email protected] : (none) 11:05:13>select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers;
+--------------+-----------------+------+--------+---------------------+
| hostgroup_id | hostname | port | status | max_replication_lag |
+--------------+-----------------+------+--------+---------------------+
| 1000 | 192.168.200.132 | 3306 | ONLINE | 5 |
| 1000 | 192.168.200.202 | 3306 | ONLINE | 5 |
| 100 | 192.168.200.202 | 3306 | ONLINE | 5 |
+--------------+-----------------+------+--------+---------------------+
3 rows in set (0.00 sec)
從庫執行stop slave之後,後端MySQL的情況:
[email protected] : (none) 11:06:52>select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers;
+--------------+-----------------+------+---------+---------------------+
| hostgroup_id | hostname | port | status | max_replication_lag |
+--------------+-----------------+------+---------+---------------------+
| 1000 | 192.168.200.132 | 3306 | SHUNNED | 5 |
| 1000 | 192.168.200.202 | 3306 | ONLINE | 5 |
| 100 | 192.168.200.202 | 3306 | ONLINE | 5 |
+--------------+-----------------+------+---------+---------------------+
3 rows in set (0.00 sec)
此時,132從庫不可用,讀都到了HG 1000的202上去了,可以自行測試。 也可以在日志裡看到:
2017-05-11 11:06:43 MySQL_HostGroups_Manager.cpp:934:replication_lag_action(): [WARNING] Shunning server 192.168.200.132:3306 with replication lag of 60 second
日志顯示延遲60s,這個是怎麼回事?這裡需要說明下幾個變量:
mysql-monitor_replication_lag_interval:主從延遲檢測時間,預設10秒。
mysql-monitor_slave_lag_when_null:當為null時,設定的延遲值,預設為60。
[email protected] : (none) 11:08:35>select * from global_variables where variable_name like 'mysql-monitor%lag%';
+----------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------+----------------+
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_slave_lag_when_null | 60 |
+----------------------------------------+----------------+
3 rows in set (0.00 sec)
根據mysql_servers.max_replication_lag設定的門檻值,這2個參數可以根據自己的情況來設定,比如設定檢測時間為1500。延遲的記錄也可以通過表來檢視:
[email protected] : (none) 11:19:47>select * from mysql_server_replication_lag_log limit 3;
+-----------------+------+------------------+-----------------+----------+-------+
| hostname | port | time_start_us | success_time_us | repl_lag | error |
+-----------------+------+------------------+-----------------+----------+-------+
| 192.168.200.132 | 3306 | 1494472189886932 | 411 | 0 | NULL |
| 192.168.200.202 | 3306 | 1494472189887224 | 372 | NULL | NULL |
| 192.168.200.202 | 3306 | 1494472189887640 | 325 | NULL | NULL |
+-----------------+------+------------------+-----------------+----------+-------+
3 rows in set (0.00 sec)
主從延遲的情況和stop slave的情況一樣,隻是stop slave是把延遲設定成了60s。
小結:通過上面的測試說明ProxySQL可以在從庫不可用時進行下線,不需要人為再進行幹預,等到恢複正常之後自動上線提供服務。
2,多路由規則
① 根據庫路由
在現有基礎上再增加一個主從:
M:192.168.200.97
S:192.168.200.245
授權賬号:程式和監控賬号
[email protected] : proxysql 12:39:39>GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY PASSWORD '*BF27B4C7AAD278126E228AA8427806E870F64F39';
Query OK, 0 rows affected (0.01 sec)
[email protected] : proxysql 12:42:50>grant select,insert,update,delete on proxysql.* to [email protected] identified by 'proxysql';
Query OK, 0 rows affected (0.00 sec)
配置ProxySQL:
[email protected] : (none) 12:43:35>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'192.168.200.97',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 12:45:15>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1001,'192.168.200.245',3306,9,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 12:45:24>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1001,'192.168.200.97',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 12:45:36>insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql',1,101,1);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 12:46:55>INSERT INTO mysql_query_rules(active,schemaname,match_pattern,destination_hostgroup,apply) VALUES(1,'proxysql','^SELECT.*FOR UPDATE$',101,1);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 12:56:47>
[email protected] : (none) 12:56:47>INSERT INTO mysql_query_rules(active,schemaname,match_pattern,destination_hostgroup,apply) VALUES(1,'proxysql','^SELECT',1001,1);
Query OK, 1 row affected (0.00 sec)
-- 應用儲存配置
[email protected] : (none) 12:56:55>load mysql servers to runtime;
[email protected] : (none) 12:57:00>load mysql users to runtime;
[email protected] : (none) 12:57:04>load mysql query rules to runtime;
[email protected] : (none) 12:57:11>save mysql servers to disk;
[email protected] : (none) 12:57:17>save mysql users to disk;
[email protected] : (none) 12:57:21>save mysql query rules to disk;
rules、servers、users資訊:
[email protected] : (none) 03:28:11>select rule_id,active,username,schemaname,client_addr,destination_hostgroup,match_pattern,flagIN,flagOUT,apply from mysql_query_rules;
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| rule_id | active | username | schemaname | client_addr | destination_hostgroup | match_pattern | flagIN | flagOUT | apply |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| 3 | 1 | NULL | NULL | NULL | 100 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 |
| 4 | 1 | NULL | NULL | NULL | 1000 | ^SELECT | 0 | NULL | 1 |
| 5 | 1 | NULL | proxysql | NULL | 101 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 |
| 6 | 1 | NULL | proxysql | NULL | 1001 | ^SELECT | 0 | NULL | 1 |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
4 rows in set (0.00 sec)
[email protected] : (none) 03:29:10>select username,default_hostgroup from mysql_users;
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| sbuser | 100 |
| proxysql | 101 |
+----------+-------------------+
2 rows in set (0.00 sec)
[email protected] : (none) 03:29:28>select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 1000 | 192.168.200.132 | 3306 | ONLINE |
| 100 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.202 | 3306 | ONLINE |
| 101 | 192.168.200.97 | 3306 | ONLINE |
| 1001 | 192.168.200.245 | 3306 | ONLINE |
| 1001 | 192.168.200.97 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
6 rows in set (0.00 sec)
模拟app連接配接:
/Users/jinyizhou [15:32:09] ~$ mysql -uproxysql -pproxysql -h192.168.200.24 -P6033 -A
...
[email protected] : (none) 03:32:11>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| proxysql |
+--------------------+
2 rows in set (0.00 sec)
[email protected] : (none) 03:32:13>use proxysql
Database changed
[email protected] : proxysql 03:32:17>show tables;
+--------------------+
| Tables_in_proxysql |
+--------------------+
| xx |
+--------------------+
1 row in set (0.00 sec)
[email protected] : proxysql 03:32:24>insert into xx values(999);
Query OK, 1 row affected (0.00 sec)
[email protected] : proxysql 03:35:49>select * from xx;
ERROR 1044 (#4200): Access denied for user 'proxysql'@'192.168.200.24' to database 'proxysql'
隻有select的時候沒有權限,其他insert,update等都是有權限的,為啥呢?原因是這裡的路由關系,ProxySQL的讀寫分離核心就是路由,這裡因為select的路由錯了,到了HG為1000的主從上了:
[email protected] : (none) 03:32:28>select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------------------------+
| hostgroup | schemaname | username | digest_text |
+-----------+--------------------+----------+----------------------------------+
| 1000 | proxysql | proxysql | select * from xx |
| 101 | proxysql | proxysql | show tables |
| 101 | information_schema | proxysql | show databases |
| 1000 | information_schema | proxysql | SELECT DATABASE() |
| 101 | information_schema | proxysql | select USER() |
| 101 | information_schema | proxysql | select @@version_comment limit ? |
+-----------+--------------------+----------+----------------------------------+
mysql_query_rules是整個ProxySQL的核心,上篇文章已經對該表進行了說明,在這裡對這例子再次講解下:
rule_id是表的自增主鍵,路由規則處理是以 rule_id 的順序進行比對,若沒有找到規則就直接去mysql_users.default_hostgroup字段裡找。上面資訊中除了select之外的其他操作都找不到規則就直接去users表裡取,是以這些操作不會報錯。而我們執行的select被rule_id為4的規則比對上,因為rule_id=4的是比對所有庫并且apply=1表示該正則比對後,将不再接受其他比對,直接轉發。這樣就轉發到了HG為1000上面的主機上了,就報沒有權限的錯誤。若apply=0則繼續比對下面,若沒有找到路由規則,則傳回再看flagOUT是否為NULL,是NULL則直接比對,否則報錯。大緻的流程如下:
flagIN, flagOUT, apply: 用來定義路由鍊 chains of rules
首先會檢查 flagIN=0 的規則,以rule_id的順序;如果都沒比對上,則走這個使用者的default_hostgroup
當比對一條規則後,會檢查 apply是否為1,是1則直接轉發,不是1則繼續比對,比對到就轉發,否則看flagOUT,
如果不為NULL,并且 flagIN != flagOUT ,則進入以flagIN為上一個flagOUT值的新規則鍊
如果不為NULL,并且 flagIN = flagOUT,則應用這條規則
如果為NULL,則結束,應用這條規則
通過上面的說明,如何讀取到正确的HG呢?這裡可以設定apply=0
[email protected] : (none) 04:18:45>update mysql_query_rules set apply=0 where rule_id in (3,4);
Query OK, 2 rows affected (0.00 sec)
[email protected] : (none) 04:18:56>load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec)
[email protected] : (none) 04:18:59>save mysql query rules to disk; Query OK, 0 rows affected (0.00 sec)
[email protected] : (none) 04:19:01>select rule_id,active,username,schemaname,client_addr,destination_hostgroup,match_pattern,flagIN,flagOUT,apply from mysql_query_rules;
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| rule_id | active | username | schemaname | client_addr | destination_hostgroup | match_pattern | flagIN | flagOUT | apply |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| 3 | 1 | NULL | NULL | NULL | 100 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 0 |
| 4 | 1 | NULL | NULL | NULL | 1000 | ^SELECT | 0 | NULL | 0 |
| 5 | 1 | NULL | proxysql | NULL | 101 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 |
| 6 | 1 | NULL | proxysql | NULL | 1001 | ^SELECT | 0 | NULL | 1 |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
4 rows in set (0.00 sec)
和上面一樣模拟app連接配接,得到的資訊:發現全部走了正确的路由。
[email protected] : (none) 05:58:55>select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest;
+-----------+------------+----------+----------------------------------+
| hostgroup | schemaname | username | digest_text |
+-----------+------------+----------+----------------------------------+
| 101 | proxysql | proxysql | insert into xx values(?) |
| 1001 | proxysql | proxysql | select * from xx |
| 1001 | proxysql | proxysql | SELECT DATABASE() |
| 101 | proxysql | proxysql | select USER() |
| 101 | proxysql | proxysql | select @@version_comment limit ? |
+-----------+------------+----------+----------------------------------+
5 rows in set (0.00 sec)
檢視路由規則的命中情況:
[email protected] : (none) 05:59:19>select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 3 | 0 |
| 4 | 4 |
| 5 | 0 |
| 6 | 4 |
+---------+------+
4 rows in set (0.00 sec)
從上面看到,apply=0 & falgOUT=null,會繼續往下找路由,找到了rule_id=6的,直接進行轉發。apply=1 直接轉發,flagOUT != null 直接轉發。
小結:通過上面的測試說明ProxySQL隻要設定好路由規則,可以有多個主庫。
② 根據使用者名路由
和多主路由一樣,差別是寫入到路由表的字段不一樣:
[email protected] : (none) 06:09:20>INSERT INTO mysql_query_rules(active,username,match_pattern,destination_hostgroup,apply) VALUES(1,'proxysql','^SELECT.*FOR UPDATE$',101,1);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 06:10:09>INSERT INTO mysql_query_rules(active,username,match_pattern,destination_hostgroup,apply) VALUES(1,'proxysql','^SELECT',1001,1);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 06:10:32>select rule_id,active,username,schemaname,client_addr,destination_hostgroup,match_pattern,flagIN,flagOUT,apply from mysql_query_rules;
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| rule_id | active | username | schemaname | client_addr | destination_hostgroup | match_pattern | flagIN | flagOUT | apply |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| 3 | 1 | NULL | NULL | NULL | 100 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 |
| 4 | 1 | NULL | NULL | NULL | 1000 | ^SELECT | 0 | NULL | 1 |
| 1405 | 1 | proxysql | NULL | NULL | 101 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 |
| 1406 | 1 | proxysql | NULL | NULL | 1001 | ^SELECT | 0 | NULL | 1 |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
4 rows in set (0.00 sec)
3,flagIN/flahOUT規則鍊實作多執行個體(推薦)
和2中的條件一樣,先配置ProxySQL的servers,users:
[email protected] : (none) 01:09:52>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment)
-> values
-> (100, '192.168.200.202', 3306, 1, 10, 'ReadWrite'),
-> (1000, '192.168.200.202', 3306, 1, 10, 'ReadWrite'),
-> (1000, '192.168.200.132', 3306, 9, 10, 'ReadOnly');
Query OK, 3 rows affected (0.00 sec)
[email protected] : (none) 01:09:54>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment)
-> values
-> (101, '192.168.200.97', 3306, 1, 10, 'ReadWrite'),
-> (1001, '192.168.200.97', 3306, 1, 10, 'ReadWrite'),
-> (1001, '192.168.200.245', 3306, 9, 10, 'ReadOnly');
Query OK, 3 rows affected (0.01 sec)
[email protected] : (none) 01:11:01>insert into mysql_users(username, password,active,default_hostgroup,transaction_persistent)
-> values
-> ('sbuser', 'sbuser', 1, 100, 1),
-> ('proxysql', 'proxysql', 1, 101, 1);
Query OK, 2 rows affected (0.00 sec)
[email protected] : (none) 01:19:44>set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 01:19:44>set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 01:56:09>select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 100 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.202 | 3306 | ONLINE |
| 1000 | 192.168.200.132 | 3306 | ONLINE |
| 101 | 192.168.200.97 | 3306 | ONLINE |
| 1001 | 192.168.200.97 | 3306 | ONLINE |
| 1001 | 192.168.200.245 | 3306 | ONLINE |
+--------------+-----------------+------+--------+
6 rows in set (0.00 sec)
[email protected] : (none) 01:58:18>select username,default_hostgroup from mysql_users;
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| sbuser | 100 |
| proxysql | 101 |
+----------+-------------------+
再配置flagOUT/flagIN,flag20是讀,flag21是寫:
[email protected] : (none) 01:21:34>INSERT INTO mysql_query_rules(rule_id,active,match_pattern,apply,flagOUT) VALUES(49,1,'^SELECT.*FOR UPDATE$',0,21);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 01:27:18>INSERT INTO mysql_query_rules(rule_id,active,match_pattern,apply,flagOUT) VALUES(50,1,'^SELECT',0,20);
Query OK, 1 row affected (0.00 sec)
[email protected] : (none) 01:32:11>insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values
-> (1,'sbtest',100,1,21,21),
-> (1,'proxysql',101,1,21,21);
Query OK, 2 rows affected (0.00 sec)
[email protected] : (none) 01:32:53>insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values
-> (1,'sbtest',1000,1,20,20),
-> (1,'proxysql',1001,1,20,20);
Query OK, 2 rows affected (0.00 sec)
[email protected] : (none) 01:58:28>select rule_id,active,username,schemaname,client_addr,destination_hostgroup,match_pattern,flagIN,flagOUT,apply from mysql_query_rules;
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| rule_id | active | username | schemaname | client_addr | destination_hostgroup | match_pattern | flagIN | flagOUT | apply |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
| 49 | 1 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | 0 | 21 | 0 |
| 50 | 1 | NULL | NULL | NULL | NULL | ^SELECT | 0 | 20 | 0 |
| 51 | 1 | NULL | sbtest | NULL | 100 | NULL | 21 | 21 | 1 |
| 52 | 1 | NULL | proxysql | NULL | 101 | NULL | 21 | 21 | 1 |
| 53 | 1 | NULL | sbtest | NULL | 1000 | NULL | 20 | 20 | 1 |
| 54 | 1 | NULL | proxysql | NULL | 1001 | NULL | 20 | 20 | 1 |
+---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+
最後儲存上線:
-- 應用
load mysql users to runtime;
load mysql servers to runtime;
load mysql variables to runtime;
LOAD MYSQL QUERY RULES TO RUN;
-- 儲存到磁盤
save mysql users to disk;
save mysql servers to disk;
save mysql variables to disk;
SAVE MYSQL QUERY RULES TO DISK;
save mysql users to mem; -- 可以屏蔽看到的明文密碼
app連接配接測試:
1)連接配接執行個體202
[zhoujy@localhost ~]$ mysql -usbuser -psbuser -h192.168.200.24 -P6033
...
[email protected] : (none) 02:19:41>show databases; +--------------------+
| Database |
+--------------------+
| information_schema |
| sbtest |
+--------------------+
2 rows in set (0.00 sec)
[email protected] : (none) 02:19:44>use sbtest
Database changed
[email protected] : sbtest 02:19:48>show tables;
...
[email protected] : sbtest 02:19:57>insert into x values(10000);
...
[email protected] : sbtest 02:20:10>select * from x;
...
相關資訊:路由的資訊都是正确的
[email protected] : (none) 02:24:15>select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------------------------+------------+
| hostgroup | schemaname | username | digest_text | count_star |
+-----------+--------------------+----------+----------------------------------+------------+
| 1000 | sbtest | sbuser | select * from x | 5 |
| 100 | sbtest | sbuser | insert into x values(?) | 5 |
| 100 | sbtest | sbuser | show tables | 2 |
| 100 | sbtest | sbuser | show databases | 1 |
| 100 | information_schema | sbuser | SELECT DATABASE() | 1 |
| 100 | information_schema | sbuser | show databases | 1 |
| 100 | information_schema | sbuser | select USER() | 1 |
| 100 | information_schema | sbuser | select @@version_comment limit ? | 1 |
+-----------+--------------------+----------+----------------------------------+------------+
8 rows in set (0.00 sec)
--路由命中
[email protected] : (none) 02:25:13>admin@1* from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 49 | 0 |
| 50 | 6 |
| 51 | 0 |
| 52 | 0 |
| 53 | 5 |
| 54 | 0 |
+---------+------+
6 rows in set (0.00 sec)
結論:通過執行個體202的賬号通路ProxySQL,首先會檢查flagIN=0,在其上面進行比對(Proxysql入口都是flagIN =0,順序往下), 比對到之後檢查flagOUT,發現 flagOUT不為NULL且flagIN !(0)= flagOUT (20),則進入以flagIN為上一個flagOUT值的新規則鍊,即20。再去flagIN=20裡比對,最終比對到了rule_id=53的規則,最後轉發。
2)連接配接執行個體97
相關情況和上面一樣,最終通過rule_id=54進行轉發。
建議:若要用ProxySQL來控制多主從執行個體的讀寫分離,推薦使用flagIN/flahOUT規則鍊實作多執行個體。
4,flagIN/flahOUT規則鍊實作分庫
目的:用戶端應用連接配接上 proxysql 的ip:port,連接配接時指定分庫db名,自動路由到對應的執行個體、對應的庫。
① :環境
APP:192.168.200.25、192.168.200.64
M1:
IP:192.168.200.202
Port:3306
DB:M1、M2、M3
S1:
IP:192.168.200.132
Port:3306
DB:M1、M2、M3
M2:
IP:192.168.200.97
Port:3306
DB:M4、M5、M6
S2:
IP:192.168.200.245
Port:3306
DB:M4、M5、M6
ProxySQL:192.168.200.24
② 搭建
和之前一樣先在後端資料庫建立程式和檢測賬号:
--程式賬号
GRANT SELECT,INSERT,UPDATE,DELETE ON `mtest%`.* TO 'mtest'@'192.168.200.24' identified by 'mtest';
--健康檢測賬号
GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY 'proxysql';
配置ProxySQL:
--插入後端使用者資訊
insert into mysql_users(username, password,active,transaction_persistent) values('mtest','mtest',1,1);
--插入後端資料庫資訊
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(100,'192.168.200.202',3306,1,10,'test proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(1000,'192.168.200.132',3306,9,10,'test proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(1000,'192.168.200.202',3306,1,10,'test proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(101,'192.168.200.97',3306,1,10,'test proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(1001,'192.168.200.245',3306,9,10,'test proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_replication_lag,comment) values(1001,'192.168.200.97',3306,1,10,'test proxysql');
--配置健康檢測資訊
set mysql-monitor_username='proxysql';
set mysql-monitor_password='proxysql';
應用儲存配置:
-- 應用
load mysql users to runtime;
load mysql servers to runtime;
load mysql variables to runtime;
-- 儲存到磁盤
save mysql users to disk;
save mysql servers to disk;
save mysql variables to disk;
save mysql users to mem; -- 可以屏蔽看到的明文密碼
配置路由規則:
----添加讀寫分離的路由
--寫:寫的入口 flagIN=0
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,apply,flagOUT) VALUES(49,1,'^SELECT.*FOR UPDATE$',0,21);
--讀:讀的入口 flagIN=0
INSERT INTO mysql_query_rules(rule_id,active,match_pattern,apply,flagOUT) VALUES(50,1,'^SELECT',0,20);
--反向比對,相當于對 match_digest/match_pattern 的比對取反,非select,即寫
INSERT INTO mysql_query_rules(rule_id, active,match_pattern,negate_match_pattern,apply,flagOUT) values(60, 1,'^SELECT',1,0,21);
----為後端伺服器配置路由
--讀
insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values(1,'M1',1000,1,20,20),(1,'M2',1000,1,20,20),(1,'M3',1000,1,20,20);
insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values(1,'M4',1001,1,20,20),(1,'M5',1001,1,20,20),(1,'M6',1001,1,20,20);
--寫
insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values(1,'M1',100,1,21,21),(1,'M2',100,1,21,21),(1,'M3',100,1,21,21);
insert into mysql_query_rules(active,schemaname,destination_hostgroup,apply,flagIN,flagOUT) values(1,'M4',101,1,21,21),(1,'M5',101,1,21,21),(1,'M6',101,1,21,21);
--連接配接時,若沒有指定資料庫,則進行show databases/tables、use 等會逾時出錯,連接配接時,預設的資料庫是在information_schema,是以寫一條根據information_schema庫的路由,直接傳回錯誤資訊。
insert into mysql_query_rules(rule_id,active,schemaname,apply,flagOUT) values(20,1,'information_schema',0,302);
insert into mysql_query_rules(rule_id,active,apply, flagIN,flagOUT,error_msg) values(9999,1,1, 302,302,'No query rules matched (by ProxySQL)');
--連接配接時,若沒有指定資料庫,則可以使用 schemaname.tablename 的形式比對資料。
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,flagIN,flagOUT) values(1000,1,'([\s\`])M(1|2|3)([\.\`])',100,1,302,302);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply,flagIN,flagOUT) values(1001,1,'([\s\`])M(4|5|6)([\.\`])',101,1,302,302);
應用規則:
LOAD MYSQL QUERY RULES TO RUN;
SAVE MYSQL QUERY RULES TO DISK;
最終的路由規則如下:
select rule_id,schemaname,destination_hostgroup,match_pattern,negate_match_pattern,flagIN,flagOUT,apply,error_msg from mysql_query_rules;
~$ mysql -umtest -pmtest -h192.168.200.24 -P6033 -A
...
[email protected] : (none) 11:27:29>show databases; --觸發了定義的路由 ERROR 1148 (42000): No query rules matched (by ProxySQL)
[email protected] : (none) 11:27:34>select * from M5.mtest5; --可以直接用schema.tables 通路
+------+
| id |
+------+
| 5 |
| 55 |
+------+
2 rows in set (0.00 sec)
[email protected] : (none) 11:27:47>use M1 --切換資料庫
Database changed
[email protected] : M1 11:27:52>show tables; --可以show了
+--------------+
| Tables_in_M1 |
+--------------+
| mtest1 |
+--------------+
1 row in set (0.00 sec)
[email protected] : M1 11:27:56>select * from mtest1;
+------+
| id |
+------+
| 1 |
| 11 |
| 111 |
| 1111 |
+------+
4 rows in set (0.00 sec)
[email protected] : M1 11:28:02>insert into mtest1 values(11111);
Query OK, 1 row affected (0.00 sec)
[email protected] : M1 11:28:11>select * from mtest1;
+-------+
| id |
+-------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
+-------+
5 rows in set (0.00 sec)
[email protected] : M1 11:28:12>show databases; --可以show了
+--------------------+
| Database |
+--------------------+
| information_schema |
| M1 |
| M2 |
| M3 |
+--------------------+
4 rows in set (0.00 sec)
[email protected] : M1 11:28:20>use M5 --切換到另一個執行個體的db
Database changed
[email protected] : M5 11:28:52>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| M4 |
| M5 |
| M6 |
+--------------------+
4 rows in set (0.00 sec)
[email protected] : M5 11:28:55>select * from mtest5;
+------+
| id |
+------+
| 5 |
| 55 |
+------+
2 rows in set (0.00 sec)
[email protected] : M5 11:29:03>insert into mtest5 values(555);
Query OK, 1 row affected (0.01 sec)
[email protected] : M5 11:29:12>select * from mtest5;
+------+
| id |
+------+
| 5 |
| 55 |
| 555 |
+------+
3 rows in set (0.00 sec)
檢視路由命中率:
select active,hits, mysql_query_rules.rule_id, schemaname,match_pattern,destination_hostgroup hostgroup,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
檢視SQL統計資訊:
[email protected] : (none) 11:36:46>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;
+-----------+--------------------+----------+----------------------------------+------------+
| hostgroup | schemaname | username | substr(digest_text,120,-120) | count_star |
+-----------+--------------------+----------+----------------------------------+------------+
| 101 | M5 | mtest | show databases | 1 |
| 1000 | M1 | mtest | SELECT DATABASE() | 1 |
| 101 | M5 | mtest | insert into mtest5 values(?) | 1 |
| 100 | M1 | mtest | show databases | 1 |
| 100 | M1 | mtest | insert into mtest1 values(?) | 1 |
| 1000 | M1 | mtest | select * from mtest1 | 2 |
| 1001 | M5 | mtest | select * from mtest5 | 2 |
| 100 | M1 | mtest | show tables | 1 |
| 101 | information_schema | mtest | select * from M5.mtest5 | 1 |
| 0 | information_schema | mtest | show databases | 1 |
| 0 | information_schema | mtest | SELECT DATABASE() | 1 |
| 0 | information_schema | mtest | select USER() | 1 |
| 0 | information_schema | mtest | select @@version_comment limit ? | 1 |
+-----------+--------------------+----------+----------------------------------+------------+
具體的說明可以看ProxySQL之讀寫分離與分庫路由示範,到此讀寫分離的測試介紹完畢,
5,查詢重寫
查詢重寫這種對于線上環境SQL問題引起的緊急故障處理還是很有用處的。如果定位到了問題所在,必須修改SQL,時間緊急,這時查詢重寫這個東西就非常有用了。類似于MySQL5.7的查詢重寫插件。這裡做下相關的說明:
ProxySQL的核心就是路由,查詢重寫也隻是添加一條路由而已,在4的基礎上進行測試:
select * from mtest1 order by id
重寫成
select * from mtest1
添加路由:
--查詢的路由,flagIN=0,當比對上規則後進行重寫,并且不應用,而通過flagOUT下去繼續查詢
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply,flagOUT) VALUES (48,1,'(.*)order by id','\1',0,20);
其實查詢重寫的實作在proxysql中也實作為正則比對替換,表示當proxysql比對到<若幹字元>order by id這個模式後,就将這個模式的order by id去掉。那麼\1是什麼意思呢,就是sed的向前引用。
加載load和save完rules之後,檢視是否重寫成功:
--初始
#查詢路由命中資訊
[email protected] : (none) 02:44:52>select * from stats_mysql_query_rules; +---------+------+
| rule_id | hits |
+---------+------+
| 20 | 0 |
| 48 | 0 |
| 49 | 0 |
| 50 | 0 |
| 60 | 0 |
| 61 | 0 |
| 62 | 0 |
| 63 | 0 |
| 64 | 0 |
| 65 | 0 |
| 66 | 0 |
| 67 | 0 |
| 68 | 0 |
| 69 | 0 |
| 70 | 0 |
| 71 | 0 |
| 72 | 0 |
| 1000 | 0 |
| 1001 | 0 |
| 9999 | 0 |
+---------+------+
20 rows in set (0.00 sec)
#查詢統計資訊
[email protected] : (none) 02:45:09>select * from stats_mysql_query_digest;
Empty set (0.00 sec)
--操作
~$ mysql -umtest -pmtest -h192.168.200.24 -P6033 -A
...
[email protected] : (none) 02:45:27>use M1 Database changed
[email protected] : M1 02:45:31>show tables;
+--------------+
| Tables_in_M1 |
+--------------+
| mtest1 |
+--------------+
1 row in set (0.00 sec)
[email protected] : M1 02:45:33>select * from mtest1;
+-------+
| id |
+-------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
+-------+
5 rows in set (0.00 sec)
[email protected] : M1 02:45:37>select * from mtest1 order by id;
+-------+
| id |
+-------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
+-------+
5 rows in set (0.00 sec)
[email protected] : M1 02:45:46>select * from mtest1 order by id;
+-------+
| id |
+-------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
+-------+
5 rows in set (0.01 sec)
----以上執行了2次order by id和1此沒有order by id的查詢,去查詢統計應該得到的值是3次沒有order by id的查詢。
--驗證
#查詢統計資訊,檢視沒有order by id的SQL出現了3次,沒有出現有order by id的SQL
[email protected] : (none) 02:49:49>select hostgroup,schemaname,digest_text,count_star from stats_mysql_query_digest;
+-----------+--------------------+----------------------------------+------------+
| hostgroup | schemaname | digest_text | count_star |
+-----------+--------------------+----------------------------------+------------+
| 1000 | M1 | select * from mtest1 | 3 |
| 100 | M1 | show tables | 1 |
| 0 | information_schema | SELECT DATABASE() | 1 |
| 0 | information_schema | select USER() | 1 |
| 0 | information_schema | select @@version_comment limit ? | 1 |
+-----------+--------------------+----------------------------------+------------+
5 rows in set (0.00 sec)
#重寫查詢的路由命中了2次
[email protected] : (none) 02:50:12>select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 20 | 1 |
| 48 | 2 |
| 49 | 0 |
| 50 | 1 |
| 60 | 1 |
| 61 | 3 |
| 62 | 0 |
| 63 | 0 |
| 64 | 0 |
| 65 | 0 |
| 66 | 0 |
| 67 | 1 |
| 68 | 0 |
| 69 | 0 |
| 70 | 0 |
| 71 | 0 |
| 72 | 0 |
| 1000 | 0 |
| 1001 | 0 |
| 9999 | 1 |
+---------+------+
20 rows in set (0.00 sec)
從上面的結果看,查詢重寫已經測試通過。到此,關于ProxySQL的相關測試已經結束,下面分析下和DBProxy的特性差别和性能差異。
性能測試
環境:
ProxySQL:192.168.200.24
DBProxy :192.168.200.24
M:
IP:192.168.200.202
Port:3306
DB:sbtest
S:
IP:192.168.200.132
Port:3306
DB:sbtest
讀寫混合(oltp_read_write.lua)測試對比:
直連資料庫:
./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.202 --mysql-port=3306 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest --report-interval=10 --max-requests=0 --time=120 --threads=1 --tables=3 --table-size=1000000 prepare/run/cleanup
ProxySQL連接配接資料庫:
./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.24 --mysql-port=6033 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest --report-interval=10 --max-requests=0 --time=120 --threads=1 --tables=3 --table-size=1000000 --skip-trx=on --db-ps-mode=disable --mysql-ignore-errors=1062 prepare/run/cleanup
DBProxy連接配接資料庫
./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.24 --mysql-port=3308 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest --report-interval=10 --max-requests=0 --time=120 --threads=1 --tables=3 --table-size=1000000 --skip-trx=on --db-ps-mode=disable --mysql-ignore-errors=1062 prepare/run/cleanup
測試的線程:1、2、4、8、16、32、64、128
把上面資料以曲線圖的形式表現:
TPS:
QPS:
測試小結:
在讀寫混合的模式下:線程越少差距越大,測試結果和美團點評DBProxy讀寫分離使用說明裡的測試報告基本吻合,這裡主要對比ProxySQL和DBProxy的性能情況,從上圖看到二者性能差不多,不過DBProxy的CPU消耗是ProxySQL的1到1.5倍。
總結:
通過上面的一些基本介紹,大緻了解了ProxySQL讀寫分離功能的使用,關于ProxySQL的其他功能内容在手冊裡有了詳盡的介紹,具體的情況請參考手冊說明。現在大緻整理下ProxySQL和DBproxy的差别:
①:連接配接池,是 multiplexing。
②:強大的正則路由,可以自己幹預讀寫路由算法。
③:從庫不可用自動下線,不需要人為幹預,支援多主庫。
④:支援重寫SQL。
⑤:足夠輕量,配置簡單。
但是在安全配置上面,DBProxy比ProxySQL要強,ProxySQL前後端賬号未分離,可以通過mysql_users檢視,前後端公用一個賬号,但是在runting_mysql_users裡面前後端賬号是分離的(backend、frontend)。其他的相關安全可以參考美團點評DBProxy讀寫分離使用說明。最後根據情況選擇到底使用哪個proxy,要是使用的是MySQL Server 5.7,因DBProxy沒有對5.7進行測試,是以推薦使用ProxySQL。至于如何防止ProxySQL的單點問題,也可以用lvs來解決,具體的說可以看LVS+Keepalived實作DBProxy的高可用。
參考文檔
https://github.com/sysown/proxysql/wiki
ProxySQL之讀寫分離與分庫路由示範