MySQL ProxySQL读写分离实践

liuxiaoyun111 · 发表于 2017-12-11 18:47:00

② 从库延迟/从库停止复制　　在上一篇文章中已经建立了监控账号：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'>　　
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.97　　
S：
192.168.200.245　　

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

dba@192.168.200.97 : proxysql 12:39:39>GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.200.24'>
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>
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 proxysql
　　
Database changed
　　
proxysql@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为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
　　

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 sbtest
　　
Database changed
　　
sbuser@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.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'>　　
--健康检测账号

　　
GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'192.168.200.24'>　　

　　配置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;　　

　　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 访问
　　
+------+

　　
|>　　
+------+
　　
| 5 |
　　
| 55 |
　　
+------+
　　
2 rows in set (0.00 sec)
　　

　　
mtest@192.168.200.24 : (none) 11:27:47>use M1 --切换数据库
　　
Database changed
　　
mtest@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;
　　
+------+

　　
|>　　
+------+
　　
| 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;
　　
+-------+

　　
|>　　
+-------+
　　
|    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  --切换到另一个实例的db
　　
Database changed
　　
mtest@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;
　　
+------+

　　
|>　　
+------+
　　
| 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;
　　
+------+
　　
|>　　
+------+
　　
| 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>
重写成
　　

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>　　

　　其实查询重写的实现在proxysql中也实现为正则匹配替换，表示当proxysql匹配到<若干字符>order by>　　加载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 changed
　　
mtest@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;
　　
+-------+

　　
|>　　
+-------+
　　
|    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>　　
+-------+

　　
|>　　
+-------+
　　
|    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>　　
+-------+

　　
|>　　
+-------+
　　
|    1 |
　　
| 11 |
　　
| 111 |
　　
|  1111 |
　　
| 11111 |
　　
+-------+
　　
5 rows in set (0.01 sec)
　　

　　
----以上执行了2次order by>　　

　　
--验证

　　
#查询统计信息，查看没有order by>　　
admin@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.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之读写分离与分库路由演示
　　ProxySQL_读写分离/查询重写配置
　　http://proxysql.blogspot.jp/2015/09/proxysql-tutorial-setup-in-mysql.html
　　https://severalnines.com/blog/how-set-read-write-split-galera-cluster-using-proxysql

posted @ 2017-05-17 12:22 jyzhou 阅读(...) 评论(...) 编辑收藏
刷新评论刷新页面返回顶部

公告

Copyright ©2017 jyzhou

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] MySQL ProxySQL读写分离实践

浏览过的版块

扫码加入运维网微信交流群