数据库中间件ProxySQL读写自动分离实践

参考文档

https://github.com/sysown/proxysqlhttp://www.fordba.com/mysql_proxysql_rw_split.htmlhttps://www.cnblogs.com/zhoujinyi/p/6829983.html

腾讯云cdb可以提供主库VIP+只读从库VIP的形式，这样开发使用时需要维护2个VIP而且不能自动读写分离，基于现状计划使用proxysql结合clb及cdb来实现读写自动分离。
架构如下：
app--clb四层--proxysql--主vip+自读vip

一.ProxySQL部署
1.下载安装启动

[root@VM_8_24_centos ~]# wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm[root@VM_8_24_centos ~]# rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm[root@VM_8_24_centos ~]# systemctl start proxysql[root@VM_8_24_centos ~]# systemctl enable proxysql[root@VM_8_24_centos ~]# netstat -tulpn |grep 603tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      26063/proxysql      tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      26063/proxysql 

2.登陆测试

[root@VM_8_24_centos ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+-----+---------------+-------------------------------------+| seq | name          | file                                |+-----+---------------+-------------------------------------+| 0   | main          |                                     || 2   | disk          | /var/lib/proxysql/proxysql.db       || 3   | stats         |                                     || 4   | monitor       |                                     || 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |+-----+---------------+-------------------------------------+5 rows in set (0.01 sec)MySQL [(none)]> 

二.ProxySQL配置

1.架构
node1 (vip 192.168.0.9:3306) , mysql master
node2 (vip 192.168.0.42:3306) , mysql slave
prxoy (192.168.8.24:6033) , proxysql
clb (vip 192.168.9.27:6033), clb proxysql
app (192.168.0.26), mysql client

2.proxysql上添加mysql主从数据库信息
proxysql上执行，将主库master也就是做写入的节点放到group 0中，salve节点做读放到group 1中
语法如下：

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(0,'192.168.0.9',3306,1,2000,10,'TESTuser');insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1,'192.168.0.42',3306,1,2000,10,'TESTuser');select * from mysql_servers;

例子：

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values(0,'192.168.0.9',3306,1,2000,10,'TESTuser');Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,ax_replication_lag,comment) values(1,'192.168.0.42',3306,1,2000,10,'TESTuser');Query OK, 1 row affected (0.00 sec)MySQL [(none)]> select * from mysql_servers;+--------------+-----------+------+--------+--------+-------------+-----------------+--------------------+---------+----------------+---------+| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_relication_lag | use_ssl | max_latency_ms | comment |+--------------+-----------+------+--------+--------+-------------+-----------------+--------------------+---------+----------------+---------+| 0            | 192.168.0.9  | 3306 | ONLINE | 1      | 0           | 2000            | 10                 | 0       | 0              | TESTuser    || 1            | 192.168.0.42 | 3306 | ONLINE | 1      | 0           | 2000            | 10                 | 0       | 0              | TESTuser    |+--------------+-----------+------+--------+--------+-------------+-----------------+--------------------+---------+----------------+---------+2 rows in set (0.01 sec)

3.mysql主库上添加proxysql监控账号及应用程序操作账号
mysql master上执行，注意应用程序操作账号根据实际情况授予权限，最好只授权给proxysql主机
语法如下：

grant usage on *.* to 'proxysql'@'192.168.8.24' identified by 'ProxySQL@20191226';grant select, insert, update, delete ON *.* TO 'testuser'@'192.168.8.24' identified by 'TESTuser@20191226';flush privileges;select host,user from mysql.user;

例子：

MySQL [(none)]> grant usage on *.* to 'proxysql'@'192.168.8.24' identified by 'ProxySQL@20191226';Query OK, 0 rows affected, 1 warning (0.00 sec)MySQL [(none)]> grant select, insert, update, delete ON *.* TO 'testuser'@'192.168.8.24' identified by 'TESTuser@20191226';Query OK, 0 rows affected, 1 warning (0.00 sec)MySQL [(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> select host,user from mysql.user;+-----------+------------------+| host      | user             |+-----------+------------------+| %         | TESTuser_data_servic || %         | TESTuser_rule_rw     || 192.168.8.24 | proxysql         || 192.168.8.24 | testuser         || 127.0.0.1 | root             || localhost | mysql.sys        || localhost | root             |+-----------+------------------+7 rows in set (0.01 sec)

4.proxysql上添加应用操作账号
proxysql上执行，注意应用账号授权给主hostgroup，这里是0
语法如下：

insert into mysql_users(username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0);select * from mysql_users;

例子：

MySQL [(none)]> insert into mysql_users(username,password,active,default_hostgroup) values ('testuser','TESTuser@20191226',1,0);Query OK, 1 row affected (0.00 sec)MySQL [(none)]> select * from mysql_users;+----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| username | password      | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |+----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| testuser | TESTuser@20191226 | 1      | 0       | 0                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |+----------+---------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)

注意：要是是用明文密码设置mysql_users，在这里可以用save命令来转换成了hash值的密码

语法如下：

save mysql users to mem;

例子：

MySQL [(none)]> save mysql users to mem;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> select username,password from mysql_users;+----------+-------------------------------------------+| username | password                                  |+----------+-------------------------------------------+| testuser | *59D7241B6C5F951F331FF88505C582CBCD42482F |+----------+-------------------------------------------+1 row in set (0.00 sec)

5.设置监控账号
proxysql上操作，账号密码与mysql主库上账号密码保持一致
语法如下：

update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username';update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password';

例子：

MySQL [(none)]> update global_variables set variable_value='proxysql' where variable_name='mysql-monitor_username';Query OK, 1 row affected (0.00 sec)MySQL [(none)]> update global_variables set variable_value='ProxySQL@20191226' where variable_name='mysql-monitor_password';Query OK, 1 row affected (0.00 sec)

6.应用到线上
proxysql上操作
语法如下：

load mysql servers to runtime;load mysql users to runtime;load mysql variables to runtime;

例子：

MySQL [(none)]> load mysql servers to runtime;Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> load mysql users to runtime;Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> load mysql variables to runtime;Query OK, 0 rows affected (0.00 sec)

7.持久化
proxysql上操作
语法如下：

save mysql servers to disk;save mysql users to disk;save mysql variables to disk;

例子：

MySQL [(none)]> save mysql servers to disk;Query OK, 0 rows affected (0.07 sec)MySQL [(none)]> save mysql users to disk;Query OK, 0 rows affected (0.04 sec)MySQL [(none)]> save mysql variables to disk;Query OK, 97 rows affected (0.02 sec)

8.自动读写分离配置
proxysql上操作，定义路由规则，如：除select * from tb for update的select全部发送到slave，其他的的语句发送到master。

语法如下：

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',0,1);insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',1,1);load mysql query rules to runtime;save mysql  query rules to disk;

例子：

MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',0,1);Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',1,1);Query OK, 1 row affected (0.00 sec)MySQL [(none)]> load mysql query rules to runtime;Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> save mysql  query rules to disk;Query OK, 0 rows affected (0.10 sec)

说明：active表示是否启用这个sql路由项，match_pattern就是我们正则匹配项，destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去，apply为1表示该正则匹配后，将不再接受其他匹配，直接转发。

9.客户端测试效果
连接数据库，通过proxysql的客户端接口访问(6033)

[root@VM_8_24_centos ~]# mysql -utestuser -pTESTuser@20191226 -h 127.0.0.1 -P6033Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+-------------------------+| Database                |+-------------------------+| information_schema      || mysql                   || performance_schema      || TESTuser_data_services_dev  || TESTuser_data_services_test || TESTuser_rule               |+-------------------------+7 rows in set (0.01 sec)MySQL [(none)]> use TESTuser_rule;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMySQL [TESTuser_rule]> show tables;+-------------------------------+| Tables_in_TESTuser_rule           |+-------------------------------+| organ_1_b_12                  || organ_1_b_13                  || organ_1_b_14                  || organ_1_b_15                  || organ_1_b_16                  || organ_1_b_19                  |+-------------------------------+6 rows in set (0.00 sec)MySQL [TESTuser_rule]> select * from organ_1_b_12;+----+--------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+---------+-----------+---------------------+| id | t0                 | t1   | t2   | t3   | t4   | t5   | t6   | t7   | t8   | t9   | t10  | t11  | t12  | t13  | t14  | t15  | t16  | t17  | t18  | t19  | user_id | nick_name | created             |+----+--------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+---------+-----------+---------------------+|  1 | 513427199402063414 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |       1 | admin     | 2018-10-31 11:50:38 ||  2 | 140502198811102244 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |       1 | admin     | 2018-10-31 14:35:36 |+----+--------------------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+---------+-----------+---------------------+2 rows in set (0.00 sec)

10.proxysql统计效果
通过proxysql接口正常操作数据，从管理接口看看ProxySQL的统计信息：
语法如下：

select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;

例子：

MySQL [(none)]> select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;+---------+---------------+-----------+| Command | Total_Time_us | Total_cnt |+---------+---------------+-----------+| SELECT  | 5650          | 3         || SHOW    | 7044          | 4         |+---------+---------------+-----------+2 rows in set (0.00 sec)

查看各类SQL的执行情况
语法如下：

select * from stats_mysql_query_digest;

例子：

MySQL [(none)]> select * from stats_mysql_query_digest;+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+| hostgroup | schemaname         | username | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time |+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+| 1         | TESTuser_rule          | testuser | 0x504CF0771C9E548B | select * from organ_1_b_12       | 1          | 1577341448 | 1577341448 | 3746     | 3746     | 3746     || 0         | TESTuser_rule          | testuser | 0x99531AEFF718C501 | show tables                      | 2          | 1577341415 | 1577341425 | 2252     | 669      | 1583     || 1         | information_schema | testuser | 0x620B328FE9D6D71A | SELECT DATABASE()                | 1          | 1577341415 | 1577341415 | 1904     | 1904     | 1904     || 0         | information_schema | testuser | 0x02033E45904D3DF0 | show databases                   | 1          | 1577341374 | 1577341374 | 2326     | 2326     | 2326     || 0         | TESTuser_rule          | testuser | 0x02033E45904D3DF0 | show databases                   | 1          | 1577341415 | 1577341415 | 2466     | 2466     | 2466     || 0         | information_schema | testuser | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1          | 1577341364 | 1577341364 | 0        | 0        | 0        |+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+6 rows in set (0.00 sec)

11.通过clb解决proxysql单点故障并负债均衡
内网clb 192.168.9.27 tcp 6033端口到192.168.8.24 6033端口，其他proxysql绑定即可。

[root@VM_0_26_centos ~]# mysql -utestuser -p -h 192.168.9.27 -P6033Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 2699Server version: 5.5.30 (ProxySQL)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+-------------------------+| Database                |+-------------------------+| information_schema      || mysql                   || performance_schema      || TESTuser_data_services_dev  || TESTuser_data_services_test || TESTuser_rule               |+-------------------------+7 rows in set (0.01 sec)MySQL [(none)]> select user,host from mysql.user;+------------------+-----------+| user             | host      |+------------------+-----------+| TESTuser_data_servic | %         || TESTuser_rule_rw     | %         || proxysql         | 192.168.8.24 || testuser         | 192.168.8.24 || root             | 127.0.0.1 || mysql.sys        | localhost || root             | localhost |+------------------+-----------+7 rows in set (0.01 sec)