首页 > 代码库 > MySQL ProxySQL读写分离实践

MySQL ProxySQL读写分离实践

目的

      在上一篇文章MySQL ProxySQL读写分离使用初探里初步介绍了ProxySQL的使用,本文继续介绍它的一些特点和DBProxy的性能差异。深入一些去了解ProxySQL,通过例子来说明ProxySQL的一些特性和用sysbench对其进行测试来对比分析说明。

环境: 

Distributor ID: UbuntuDescription   : Ubuntu 14.04.5 LTSRelease       : 14.04Codename      : trusty
MySQL Master :
192.168.200.202MySQL Slave :192.168.200.132APP IP :192.168.200.25/64

测试

本文测试环境是在上一篇文章的基础上进行的,所以已经做了一主一从的读写分离。在此基础上,若从库挂了,会怎么样?这里先把从库(132)shutdown掉,看看读去了哪里。

tips:如何修改管理接口的用户名密码?除了初始化时候修改配置文件,还有一个方法是在管理端口设置:

admin@127.0.0.1 : (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加载:

admin@127.0.0.1 : (none) 12:53:02>load admin variables to runtime;Query OK, 0 rows affected (0.00 sec)admin@127.0.0.1 : (none) 12:53:33>save admin variables to disk;Query OK, 9 rows affected (0.00 sec)

1,从库不可用

① 关闭从库

当前ProxySQL下后端MySQL的运行情况:

admin@127.0.0.1 : (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的运行情况:

admin@127.0.0.1 : (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 |+--------------+-----------------+------+---------+

此时读的操作会报超时:

sbuser@192.168.200.24 : 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设小,当读不到从库,回去主库查询。

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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      |+--------------+-----------------+------+--------+admin@127.0.0.1 : (none) 11:54:03>load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec)admin@127.0.0.1 : (none) 11:54:28>save mysql servers to disk;                                                                                                             Query OK, 0 rows affected (0.00 sec)admin@127.0.0.1 : (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  |+--------------+-----------------+------+---------+

此时读的操作正常:

sbuser@192.168.200.24 : 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仅适用于从,但也可以将其配置为所有主机,无论是从还是主(不会有任何影响)。

-- 设置监控账号权限dba@192.168.200.202 : 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)-- 设置延迟的阈值admin@127.0.0.1 : (none) 11:04:50>UPDATE mysql_servers SET max_replication_lag=5;                                                                                                    Query OK, 3 rows affected (0.00 sec)-- 应用配置admin@127.0.0.1 : (none) 11:04:54>load mysql servers to runtime;                                                                                                                   Query OK, 0 rows affected (0.01 sec)admin@127.0.0.1 : (none) 11:05:04>save mysql servers to disk;                                                                                                                      Query OK, 0 rows affected (0.01 sec)

主从复制正常的情况下,后端MySQL的情况:

admin@127.0.0.1 : (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的情况:

admin@127.0.0.1 : (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。

admin@127.0.0.1 : (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。延迟的记录也可以通过表来查看:

admin@127.0.0.1 : (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.97S:192.168.200.245

授权账号:程序和监控账号

dba@192.168.200.97 : 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)dba@192.168.200.97 : proxysql 12:42:50>grant select,insert,update,delete on proxysql.* to proxysql@192.168.200.24 identified by proxysql;Query OK, 0 rows affected (0.00 sec)

配置ProxySQL: 

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (none) 12:56:47>admin@127.0.0.1 : (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)-- 应用保存配置admin@127.0.0.1 : (none) 12:56:55>load mysql servers to runtime;admin@127.0.0.1 : (none) 12:57:00>load mysql users to runtime;
admin@127.0.0.1 : (none) 12:57:04>load mysql query rules to runtime;admin@127.0.0.1 : (none) 12:57:11>save mysql servers to disk;admin@127.0.0.1 : (none) 12:57:17>save mysql users to disk;admin@127.0.0.1 : (none) 12:57:21>save mysql query rules to disk;

rules、servers、users信息:

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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...proxysql@192.168.200.24 : (none) 03:32:11>show databases;+--------------------+| Database           |+--------------------+| information_schema || proxysql           |+--------------------+2 rows in set (0.00 sec)proxysql@192.168.200.24 : (none) 03:32:13>use proxysqlDatabase changedproxysql@192.168.200.24 : proxysql 03:32:17>show tables;+--------------------+| Tables_in_proxysql |+--------------------+| xx                 |+--------------------+1 row in set (0.00 sec)proxysql@192.168.200.24 : proxysql 03:32:24>insert into xx values(999);Query OK, 1 row affected (0.00 sec)proxysql@192.168.200.24 : 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的主从上了:

admin@127.0.0.1 : (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为3的规则匹配上,因为rule_id=3的是匹配所有库并且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

admin@127.0.0.1 : (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)admin@127.0.0.1 : (none) 04:18:56>load mysql query rules to runtime;                                                                                                                 Query OK, 0 rows affected (0.00 sec)admin@127.0.0.1 : (none) 04:18:59>save mysql query rules to disk;                                                                                                                    Query OK, 0 rows affected (0.00 sec)admin@127.0.0.1 : (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连接,得到的信息:发现全部走了正确的路由。

admin@127.0.0.1 : (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)

查看路由规则的命中情况:

admin@127.0.0.1 : (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只要设置好路由规则,可以有多个主库。

② 根据用户名路由

和多主路由一样,区别是写入到路由表的字段不一样:

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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:

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (none) 01:19:44>set mysql-monitor_username=proxysql;Query OK, 1 row affected (0.00 sec)admin@127.0.0.1 : (none) 01:19:44>set mysql-monitor_password=proxysql;Query OK, 1 row affected (0.00 sec)admin@127.0.0.1 : (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)admin@127.0.0.1 : (none) 01:58:18>select username,default_hostgroup from mysql_users;+----------+-------------------+| username | default_hostgroup |+----------+-------------------+| sbuser   | 100               || proxysql | 101               |+----------+-------------------+

再配置flagOUT/flagIN,flag20是读,flag21是写:

admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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)admin@127.0.0.1 : (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...sbuser@192.168.200.24 : (none) 02:19:41>show databases;                                                                                                                           +--------------------+| Database           |+--------------------+| information_schema || sbtest             |+--------------------+2 rows in set (0.00 sec)sbuser@192.168.200.24 : (none) 02:19:44>use sbtestDatabase changedsbuser@192.168.200.24 : sbtest 02:19:48>show tables;...sbuser@192.168.200.24 : sbtest 02:19:57>insert into x values(10000);...sbuser@192.168.200.24 : sbtest 02:20:10>select * from x;...

相关信息:路由的信息都是正确的

admin@127.0.0.1 : (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)
--路由命中admin@127.0.0.1 : (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.25192.168.200.64M1:     IP:192.168.200.202     Port:3306     DB:M1、M2、M3S1:     IP:192.168.200.132     Port:3306     DB:M1、M2、M3M2:     IP:192.168.200.97     Port:3306     DB:M4、M5、M6S2:     IP:192.168.200.245     Port:3306     DB:M4、M5、M6ProxySQL: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=0INSERT INTO mysql_query_rules(rule_id,active,match_pattern,apply,flagOUT) VALUES(49,1,^SELECT.*FOR UPDATE$,0,21);--读:读的入口 flagIN=0INSERT 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;

技术分享 
app连接测试:

~$ mysql -umtest -pmtest -h192.168.200.24 -P6033 -A...mtest@192.168.200.24 : (none) 11:27:29>show databases;  --触发了定义的路由                                                                                                                          ERROR 1148 (42000): No query rules matched (by ProxySQL)mtest@192.168.200.24 : (none) 11:27:34>select * from M5.mtest5; --可以直接用schema.tables 访问+------+| id   |+------+|    5 ||   55 |+------+2 rows in set (0.00 sec)mtest@192.168.200.24 : (none) 11:27:47>use M1 --切换数据库Database changedmtest@192.168.200.24 : M1 11:27:52>show tables;  --可以show了+--------------+| Tables_in_M1 |+--------------+| mtest1       |+--------------+1 row in set (0.00 sec)mtest@192.168.200.24 : M1 11:27:56>select * from mtest1;+------+| id   |+------+|    1 ||   11 ||  111 || 1111 |+------+4 rows in set (0.00 sec)mtest@192.168.200.24 : M1 11:28:02>insert into mtest1 values(11111);Query OK, 1 row affected (0.00 sec)mtest@192.168.200.24 : M1 11:28:11>select * from mtest1;+-------+| id    |+-------+|     1 ||    11 ||   111 ||  1111 || 11111 |+-------+5 rows in set (0.00 sec)mtest@192.168.200.24 : M1 11:28:12>show databases; --可以show了+--------------------+| Database           |+--------------------+| information_schema || M1                 || M2                 || M3                 |+--------------------+4 rows in set (0.00 sec)mtest@192.168.200.24 : M1 11:28:20>use M5  --切换到另一个实例的dbDatabase changedmtest@192.168.200.24 : M5 11:28:52>show databases;+--------------------+| Database           |+--------------------+| information_schema || M4                 || M5                 || M6                 |+--------------------+4 rows in set (0.00 sec)mtest@192.168.200.24 : M5 11:28:55>select * from mtest5;+------+| id   |+------+|    5 ||   55 |+------+2 rows in set (0.00 sec)mtest@192.168.200.24 : M5 11:29:03>insert into mtest5 values(555);Query OK, 1 row affected (0.01 sec)mtest@192.168.200.24 : 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统计信息:

admin@127.0.0.1 : (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之后,查看是否重写成功:

--初始#查询路由命中信息admin@127.0.0.1 : (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)#查询统计信息admin@127.0.0.1 : (none) 02:45:09>select * from stats_mysql_query_digest;Empty set (0.00 sec)--操作~$ mysql -umtest -pmtest -h192.168.200.24 -P6033 -A...mtest@192.168.200.24 : (none) 02:45:27>use M1                                                                                                                                        Database changedmtest@192.168.200.24 : M1 02:45:31>show tables;+--------------+| Tables_in_M1 |+--------------+| mtest1       |+--------------+1 row in set (0.00 sec)mtest@192.168.200.24 : M1 02:45:33>select * from mtest1;+-------+| id    |+-------+|     1 ||    11 ||   111 ||  1111 || 11111 |+-------+5 rows in set (0.00 sec)mtest@192.168.200.24 : M1 02:45:37>select * from mtest1 order by id;+-------+| id    |+-------+|     1 ||    11 ||   111 ||  1111 || 11111 |+-------+5 rows in set (0.00 sec)mtest@192.168.200.24 : 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的SQLadmin@127.0.0.1 : (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次admin@127.0.0.1 : (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.24DBProxy  :192.168.200.24M:     IP:192.168.200.202     Port:3306     DB:sbtestS:     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之读写分离与分库路由演示

ProxySQL_读写分离/查询重写配置

http://proxysql.blogspot.jp/2015/09/proxysql-tutorial-setup-in-mysql.html 

<style></style><style></style><style></style>

MySQL ProxySQL读写分离实践