数据库中间件ProxySQL读写自动分离实践
- 2019 年 12 月 30 日
- 筆記
参考文档
https://github.com/sysown/proxysql http://www.fordba.com/mysql_proxysql_rw_split.html https://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 603 tcp 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 -P6032 Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 2 Server 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,default_hostgroup) values ('testuser','TESTuser@20191226',0); select * from mysql_users;
例子：
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values ('testuser','TESTuser@20191226',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 -P6033 Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 4 Server 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 names You can turn off this feature to get a quicker startup with -A Database changed MySQL [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 -P6033 Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 2699 Server 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)
