• 运维特工,战胜心魔!!
  • 有些事情其实没有那么难,只是我们感觉难,走出第一步!
  • 你所浪费的今天,是昨天死去的人奢望的明天!!!
  • 欢迎访问 unixfbi.com 运维特工社区http://unixfbi.com/
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧
  • 运维特工:http://www.unixfbi.com
  • 人生没有白走的路,每一步都算数!!
  • 空杯心态,沉淀自己!

MySQL中间件ProxySQL读写分离

MySQL unixfbi 1年前 (2018-07-28) 4582次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

一、基础环境信息

1.环境信息

主机名 IP 角色
proxysql-node1 192.168.199.212 ProxySQL
db-node1 192.168.199.230 Master
db-node2 192.168.199.231 Slave
db-node3 192.168.199.131 Slave

版本信息:

名称 版本
系统版本 CentOS Linux release 7.2.1511 (Core)
ProxySQL 1.4.9-3
MySQL 5.7.22

2.MySQL 主从信息

root@localhost:mysql3306.sock [(none)]> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|   2313306 |      | 3306 |   2303306 | 69b2292d-9174-11e8-b199-7845c401c236 |
|   1313306 |      | 3306 |   2303306 | 60c721c2-9174-11e8-9202-2c27d72e9d73 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

二、ProxySQL 添加后端 MySQL

登入 ProxySQL,把 MySQL 主从的信息添加进去。将主库 master 也就是做写入的节点放到 10 组中,Salve 节点做读放到 20 组中。

admin@127.0.0.1 [(none)] 17:47:10> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'db-node1',3306);     
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 17:47:30> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'db-node2',3306);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 17:47:49> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'db-node3',3306);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 [(none)] 17:48:50> select * from mysql_servers;
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | db-node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | db-node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | db-node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

三、配置账号

1.ProxySQL 配置监控账号

ProxySQL 会不间断的监控后端服务器,配置一些监控参数是非常重要的。
ProxySQL 监控后端服务器,首先需要一个监控账号,而且这个账号也必须在 MySQL 数据库里存在。
ProxySQL 上创建监控账号:

admin@127.0.0.1 [(none)] 17:49:15> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 18:23:43> UPDATE global_variables SET variable_value='unixfbi' WHERE variable_name='mysql-monitor_password'; 
Query OK, 1 row affected (0.00 sec)

ProxySQL 启用并持久化:

admin@127.0.0.1 [(none)] 18:33:11> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 18:36:32> SAVE MYSQL VARIABLES TO DISK;
Query OK, 95 rows affected (0.09 sec)

MySQL 实例创建监控账号:

root@localhost:mysql3306.sock [(none)]> grant USAGE on *.* to monitor@'%' IDENTIFIED  by 'unixfbi'; 
root@localhost:mysql3306.sock [(none)]> flush privileges;

2.添加应用账号

ProxySQL 上配置应用账号:

admin@127.0.0.1 [(none)] 01:09:24> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql_user1','unixfbi',10);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 01:09:50> select * from mysql_users;                              
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username       | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql_user1 | unixfbi  | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

可以看到 sbuser 密码是明文密码,也可以使用 password()函数加密后的密码进行代替。
启用并持久化:

admin@127.0.0.1 [(none)] 01:12:33> LOAD mysql users TO RUNTIME; 
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 01:12:57> SAVE mysql users TO disk;
Query OK, 0 rows affected (0.20 sec)

MySQL 数据库上添加应用账号:

root@localhost:mysql3306.sock [(none)]> grant all privileges on *.* to proxysql_user1@'%' identified by 'unixfbi';
root@localhost:mysql3306.sock [(none)]> flush privileges;
Query OK, 0 rows affected (0.03 sec)

这里的权限我先用了 all,生产环境一定要控制好权限。

3.查看监控状态

admin@127.0.0.1 [(none)] 01:14:38> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us    | connect_success_time_us | connect_error |
+----------+------+------------------+-------------------------+---------------+
| db-node3 | 3306 | 1532711971475485 | 2138                    | NULL          |
| db-node2 | 3306 | 1532711971465292 | 1308                    | NULL          |
| db-node1 | 3306 | 1532711971455098 | 1249                    | NULL          |
| db-node3 | 3306 | 1532711911475398 | 1910                    | NULL          |
| db-node2 | 3306 | 1532711911465209 | 1339                    | NULL          |
| db-node1 | 3306 | 1532711911455012 | 1508                    | NULL          |
| db-node3 | 3306 | 1532711851475349 | 2136                    | NULL          |
| db-node2 | 3306 | 1532711851465155 | 1346                    | NULL          |
| db-node1 | 3306 | 1532711851454952 | 1419                    | NULL          |
| db-node3 | 3306 | 1532711791475292 | 2102                    | NULL          |
+----------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 01:20:25> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+----------------------+------------+
| hostname | port | time_start_us    | ping_success_time_us | ping_error |
+----------+------+------------------+----------------------+------------+
| db-node3 | 3306 | 1532712051575840 | 628                  | NULL       |
| db-node2 | 3306 | 1532712051574102 | 288                  | NULL       |
| db-node1 | 3306 | 1532712051572375 | 458                  | NULL       |
| db-node3 | 3306 | 1532712041575911 | 629                  | NULL       |
| db-node2 | 3306 | 1532712041574172 | 285                  | NULL       |
| db-node1 | 3306 | 1532712041572442 | 284                  | NULL       |
| db-node3 | 3306 | 1532712031575720 | 559                  | NULL       |
| db-node2 | 3306 | 1532712031573984 | 286                  | NULL       |
| db-node1 | 3306 | 1532712031572242 | 432                  | NULL       |
| db-node3 | 3306 | 1532712021575669 | 561                  | NULL       |
+----------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)

4.通过 ProxySQL 连接数据库

通过 ProxySQL 访问 MySQL:

# mysql -uproxysql_user1 -punixfbi -h192.168.199.212 -P6033
proxysql_user1@192.168.199.212 [(none)] 13:32:24> create database db1;
Query OK, 1 row affected (0.03 sec)

proxysql_user1@192.168.199.212 [(none)] 13:32:32> create table db1.tb1(id int);
Query OK, 0 rows affected (0.36 sec)

写入测试数据:

proxysql_user1@192.168.199.212 [(none)] 13:33:20> insert into db1.tb1 values(1),(2),(3),(4),(5),(6);  
proxysql_user1@192.168.199.212 [(none)] 13:37:31> select * from db1.tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

查看 ProxySQL 的统计信息:

# mysql -uadmin -padmin -P6032 -h127.0.0.1
admin@127.0.0.1 [(none)] 13:29:35> select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;
+-----------------+---------------+-----------+
| Command         | Total_Time_us | Total_cnt |
+-----------------+---------------+-----------+
| CREATE_DATABASE | 39472         | 1         |
| CREATE_TABLE    | 360626        | 1         |
| INSERT          | 273530        | 2         |
| LOAD            | 10001042      | 1         |
| SELECT          | 10005156      | 14        |
| SHOW            | 50005157      | 8         |
| UNKNOWN         | 10000979      | 1         |
+-----------------+---------------+-----------+
7 rows in set (0.00 sec)

通过 ProxySQL 管理接口,查看各类 SQL 的执行情况

# mysql -uadmin -padmin -P6032 -h127.0.0.1

admin@127.0.0.1 [(none)] 13:40:13> select * from stats_mysql_query_digest;
+-----------+--------------------+----------------+--------------------+---------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username       | digest             | digest_text                                                                                                         | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------------+--------------------+---------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 10        | information_schema | proxysql_user1 | 0x99531AEFF718C501 | show tables                                                                                                         | 1          | 1532756398 | 1532756398 | 915      | 915      | 915      |
| 10        | information_schema | proxysql_user1 | 0xE4FA568266785F2A | select * from db1.tb1                                                                                               | 1          | 1532756259 | 1532756259 | 715      | 715      | 715      |
| 10        | information_schema | proxysql_user1 | 0x83B0D96C2425192A | create database db1                                                                                                 | 1          | 1532755952 | 1532755952 | 39472    | 39472    | 39472    |
| 10        | information_schema | proxysql_user1 | 0x226CD90D52A2BA0B | select @@version_comment limit ?                                                                                    | 4          | 1532712294 | 1532754833 | 0        | 0        | 0        |
| 10        | information_schema | proxysql_user1 | 0xADFD2C74F0F030BC | insert into db1.tb1 values(?),(?),(?)                                                                               | 2          | 1532756199 | 1532756251 | 273530   | 127068   | 146462   |
| 10        | information_schema | proxysql_user1 | 0x3EC6E34E860A7C88 | SHOW DATABASES                                                                                                      | 2          | 1532712401 | 1532713469 | 20001575 | 10000698 | 10000877 |
| 10        | information_schema | proxysql_user1 | 0x594F2C744B698066 | select USER()                                                                                                       | 4          | 1532712294 | 1532754833 | 0        | 0        | 0        |
| 10        | information_schema | proxysql_user1 | 0x82A12D4C4E7B0A28 | select @@hostname                                                                                                   | 1          | 1532755796 | 1532755796 | 600      | 600      | 600      |
| 10        | information_schema | proxysql_user1 | 0x40A43C25F47DD0E8 | create table db1.tb1(id int)                                                                                        | 1          | 1532756000 | 1532756000 | 360626   | 360626   | 360626   |
| 10        | information_schema | proxysql_user1 | 0xDEDCC6EB3CC5B810 | ?.?.?.? db-node1 ?.?.?.? db-node2 ?.?.?.? db-node3                                                                  | 1          | 1532712550 | 1532712550 | 10000979 | 10000979 | 10000979 |
| 10        | information_schema | proxysql_user1 | 0xDBBC46D02260D667 | show databaes                                                                                                       | 1          | 1532754555 | 1532754555 | 10000635 | 10000635 | 10000635 |
| 10        | information_schema | proxysql_user1 | 0xE834B6C84CFDBBC2 | select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >?                        | 1          | 1532756375 | 1532756375 | 609      | 609      | 609      |
| 10        | information_schema | proxysql_user1 | 0xFEA49AF8FEA49169 | select DATABASE(), USER() limit ?                                                                                   | 2          | 1532754710 | 1532754827 | 10002611 | 2271     | 10000340 |
| 10        | information_schema | proxysql_user1 | 0x74BD419C4BDF0C99 | LOAD MYSQL USERS TO RUNTIME                                                                                         | 1          | 1532754791 | 1532754791 | 10001042 | 10001042 | 10001042 |
| 10        | information_schema | proxysql_user1 | 0x02033E45904D3DF0 | show databases                                                                                                      | 4          | 1532712312 | 1532755944 | 20002032 | 740      | 10000403 |
| 10        | information_schema | proxysql_user1 | 0x61F40AD0B6AEFE66 | select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit ? | 1          | 1532754827 | 1532754827 | 621      | 621      | 621      |
+-----------+--------------------+----------------+--------------------+---------------------------------------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
16 rows in set (0.00 sec)

通过上面看到,在stats_mysql_query_digesthostgroup中发现,读和写全部都是走 10 这个hostgroup,没有用到Slave。主要原因就是 ProxySQL 的核心mysql_query_rules路由表还没有配置。proxysql 是通过自定义 sql 路由规则就可以实现读写分离。

四、配置读写分离

1.定义路由规则

我们设置 ProxySQL 路由规则为:只有 select * from tb for update 的 SQL 语句全部发送到 Slave 实例,其他的语句都发送到 Master 实例。

(1)设置请求到 Master 的规则

admin@127.0.0.1 [(none)] 13:51:13> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1); 

(2)设置请求到 Slave 的规则

admin@127.0.0.1 [(none)] 13:59:39> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',20,1);  

(3)加载并持久化设置的规则配置

admin@127.0.0.1 [(none)] 14:04:55> LOAD MYSQL QUERY RULES TO RUNTIME;  

admin@127.0.0.1 [(none)] 14:05:09> SAVE MYSQL QUERY RULES TO DISK;

说明:active表示是否启用这个 sql 路由项,match_pattern就是我们正则匹配项,destination_hostgroup表示我们要将该类 sql 转发到哪些 MySQL 实例上面去,apply为 1 表示该正则匹配后,将不再接受其他匹配,直接转发。路由规则添加完成,现在来测试下读写分离,先清空stats_mysql_query_digest统计表:

admin@127.0.0.1 [(none)] 14:05:22> SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

2.写入测试数据

# mysql -usbuser -psbuser -h192.168.199.180 -P6033

proxysql_user1@192.168.199.212 [(none)] 13:39:58> insert into db1.tb1 values(7),(8),(9),(10),(11),(12),(13);

proxysql_user1@192.168.199.212 [(none)] 14:13:16> select * from db1.tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
+------+
13 rows in set (0.00 sec)

3.查看统计信息

admin@127.0.0.1 [(none)] 14:14:37> select * from stats_mysql_query_digest;
+-----------+--------------------+----------------+--------------------+-------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username       | digest             | digest_text                                           | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------------+--------------------+-------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 10        | information_schema | proxysql_user1 | 0xBEECE534A6F8652D | select * from db1.tb1 for update                      | 2          | 1532758356 | 1532758396 | 1508     | 752      | 756      |
| 10        | information_schema | proxysql_user1 | 0x9AA5CEA72B9A054B | select * from sb for update                           | 1          | 1532758344 | 1532758344 | 580      | 580      | 580      |
| 20        | information_schema | proxysql_user1 | 0xE4FA568266785F2A | select * from db1.tb1                                 | 4          | 1532758251 | 1532758398 | 6623     | 897      | 3793     |
| 10        | information_schema | proxysql_user1 | 0x23F81FCC6C3E7C11 | insert into db1.tb1 values(?),(?),(?),(?),(?),(?),(?) | 1          | 1532758221 | 1532758221 | 162351   | 162351   | 162351   |
+-----------+--------------------+----------------+--------------------+-------------------------------------------------------+------------+------------+------------+----------+----------+----------+
4 rows in set (0.00 sec)

可以看出已经成功读写分离了。

从上的结果可以看出,已经成功读写分离了。在 ProxySQL 上配置的路由规则已经生效了。select 语句都已经在 Slave 实例上执行了。

参考文档

https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)
http://www.fordba.com/mysql_proxysql_rw_split.html
http://www.cnblogs.com/zhoujinyi/p/6838685.html

本文出自 “运维特工” 博客,转载请务必保留原文链接 和 http://unixfbi.com


UnixFBI 运维特工 , 版权所有丨如未注明 , 均为原创丨 转载请务必注明原文链接http://www.unixfbi.com/546.html
喜欢 (2)
[支付宝]
分享 (0)
unixfbi
关于作者:
运维工程师一枚,做有价值的事情!
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址