ProxySQL如何帮助MySQL实行读写分离

本文主要给大家介绍ProxySQL如何帮助MySQL实行读写分离，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下ProxySQL如何帮助MySQL实行读写分离吧。

       ProxySQL是一个基于C++开发的高性能轻量级产品，是一款强大的mysql的中间件，他可以实现多种方式的读写分离。

Master IP：172.16.75.4    CentOS 7.5D        server_id：401

Slave IP：172.16.75.3       CentOS 7.5C         server_id：301

1.首先，yum安装即可，然后启动，启动端口为6032（我的proxysql安装在了master上）；

[root@slave2 ~]# ss -tnl State      Recv-Q Send-Q           Local Address:Port                          Peer Address:Port              LISTEN     0      50                           *:3306                                     *:*                  LISTEN     0      128                          *:111                                      *:*                  LISTEN     0      128                          *:6032                                     *:*                  LISTEN     0      128                          *:6033                                     *:*                  LISTEN     0      128                          *:6033                                     *:*                  LISTEN     0      128                          *:6033                                     *:*                  LISTEN     0      128                          *:6033                                     *:*

2.使用mysql客户端工具登录proxysql，用户名和密码都是admin，端口为6032，默认不允许localhost登录，所以要用127.0.0.1IP地址登录；

[root@slave2 ~]# mysql -uadmin  -padmin -h227.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.00 sec)

3.主要介绍其中mian库和monitor库；

MySQL [(none)]> show tables from main; +--------------------------------------------+ | tables                                     | +--------------------------------------------+ | global_variables                           | | mysql_collations                           | | mysql_group_replication_hostgroups         | | mysql_query_rules                          | | mysql_query_rules_fast_routing             | | mysql_replication_hostgroups               | | mysql_servers                              |  插入监控节点，master节点和slave节点 | mysql_users                                | | proxysql_servers                           | | runtime_checksums_values                   | | runtime_global_variables                   | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules                  | | runtime_mysql_query_rules_fast_routing     | | runtime_mysql_replication_hostgroups       | | runtime_mysql_servers                      | | runtime_mysql_users                        | | runtime_proxysql_servers                   | | runtime_scheduler                          | | scheduler                                  | +--------------------------------------------+ 20 rows in set (0.00 sec)

4.在mysql_servers表中有几个重要的属性：

hostgroup_id：组ID，用于区分master和slave；

hostname：后端master和slave的IP地址；

port：后端master和slave的IP端口，默认3306；

MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.4',3306),(20,'172.16.75.3',3306); Query OK, 2 rows affected (0.02 sec)   MySQL [main]> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec)   MySQL [main]> save mysql servers to disk; Query OK, 0 rows affected (0.06 sec)

注意：

1.设置的master的hostgroup_id为10（写组）；

2.设置的slave的hostgroup_id为20（读组）；

3.每次在proxysql执行完操作之后，需要手动加载至内存上，然后手动保存至磁盘上，表名中的”_”改为空格； mysql_servers à mysql servers

5.在master上授权一个监控用户，用于监控后端的节点（注意：是master的mysql，不是Proxysql）；

       这个用户需要的权限：replication client和replication slave

MariaDB [(none)]> grant replication client,replication slave on *.* to 'monitor'@'%' identified by '123456'; Query OK, 0 rows affected (0.02 sec)

6.在proxysql上加入该节点；

MySQL [main]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec)   MySQL [main]> set mysql-monitor_password='123456'; Query OK, 1 row affected (0.00 sec)   MySQL [main]> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec)   MySQL [main]> save mysql variables to disk; Query OK, 95 rows affected (0.03 sec)

通过查看表mysql_server_ping_log了解后端云服务器状态信息；

MySQL [main]> select * from mysql_server_ping_log limit 1,10; +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ | hostname    | port | time_start_us    | ping_success_time_us | ping_error                                                           | +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ | 172.16.75.4 | 3306 | 1541505676594192 | 0                    | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505686592082 | 0                    | Host '172.16.75.4' is not allowed to connect to this MariaDB server  | | 172.16.75.4 | 3306 | 1541505686594872 | 0                    | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505696592635 | 0                    | Host '172.16.75.4' is not allowed to connect to this MariaDB server  | | 172.16.75.4 | 3306 | 1541505696595442 | 0                    | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505706593101 | 0                    | Host '172.16.75.4' is not allowed to connect to this MariaDB server  | | 172.16.75.4 | 3306 | 1541505706596427 | 0                    | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505716593471 | 0                    | Host '172.16.75.4' is not allowed to connect to this MariaDB server  | | 172.16.75.4 | 3306 | 1541505716596416 | 0                    | Access denied for user 'monitor'@'172.16.75.4' (using password: YES) | | 172.16.75.3 | 3306 | 1541505726593810 | 0                    | Host '172.16.75.4' is not allowed to connect to this MariaDB server  | +-------------+------+------------------+----------------------+----------------------------------------------------------------------+ 10 rows in set (0.00 sec)

7.对后端定义的云服务器的分组进行读组和写组的设定，mysql_replication_hostgroups表中添加定义即可，通过查看monitor库中的mysql_server_read_only_log表查看后端节点是否具有read_only权限；

MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20); Query OK, 1 row affected (0.00 sec)   MySQL [main]> load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec)   MySQL [main]> save mysql servers to disk; Query OK, 0 rows affected (0.03 sec)   MySQL [main]> select * from monitor.mysql_server_read_only_log limit 3; +-------------+------+------------------+-----------------+-----------+-------+ | hostname    | port | time_start_us    | success_time_us | read_only | error | +-------------+------+------------------+-----------------+-----------+-------+ | 172.16.75.4 | 3306 | 1541506648164762 | 766             | 0         | NULL  | | 172.16.75.3 | 3306 | 1541506648162822 | 3585            | 1         | NULL  | | 172.16.75.3 | 3306 | 1541506649664049 | 993             | 1         | NULL  | +-------------+------+------------------+-----------------+-----------+-------+ 3 rows in set (0.00 sec)

8.至此，基本配置完毕，我们在后端的master上创建两个用户账户，在proxysql上添加不同的hostgroup_id，完成基于不同用户之间进行读写分离；

Master mysql：

MariaDB [(none)]> grant all on *.* to 'reader'@'%' identified by  '123456'; Query OK, 0 rows affected (0.01 sec)   MariaDB [(none)]> grant all on *.* to 'writer'@'%' identified by  '123456'; Query OK, 0 rows affected (0.00 sec)

Proxysql：

MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('reader','123456',20),('writer','123456',10); Query OK, 2 rows affected (0.00 sec)   MySQL [main]> load mysql users to runtime; Query OK, 0 rows affected (0.01 sec)   MySQL [main]> save mysql users to disk; Query OK, 0 rows affected (0.03 sec)   MySQL [main]> select * from mysql_users\G *************************** 1. row ***************************               username: reader               password: 123456                 active: 1                use_ssl: 0      default_hostgroup: 20         default_schema: NULL          schema_locked: 0 transaction_persistent: 1           fast_forward: 0                backend: 1               frontend: 1        max_connections: 10000 *************************** 2. row ***************************               username: writer               password: 123456                 active: 1                use_ssl: 0      default_hostgroup: 10         default_schema: NULL          schema_locked: 0 transaction_persistent: 1           fast_forward: 0                backend: 1               frontend: 1        max_connections: 10000 2 rows in set (0.00 sec)

8.任意一台主机，测试基于用户的读写分离机制；

[root@slave2 ~]# mysql -uwriter -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id'; +-------------+ | @@server_id | +-------------+ |         401 | +-------------+ [root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'select @@server_id'; +-------------+ | @@server_id | +-------------+ |         301 | +-------------+

9.基于SQL语句实现读写分离；

需要在mysql_query_rules表中添加两条正则表达式的规则；

MySQL [main]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); Query OK, 2 rows affected (0.00 sec)   MySQL [main]> load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec)   MySQL [main]> save mysql query rules to disk; Query OK, 0 rows affected (0.02 sec)

10.任意一台主机测试基于SQL语句读写分离的机制；

[root@slave2 ~]# mysql -ureader -h272.16.75.4 -P6033 -p123456 -e 'set @@autocommit=0;\ start transaction;\ use hellodb;\ insert into coc (ID,ClassID,CourseID) values (100,100,100);\ select @@server_id;\ commit;' +-------------+ | @@server_id | +-------------+ |         301 | +-------------+

Master中的hellodb数据库进行查看；

MariaDB [hellodb]> select * from coc; +----+---------+----------+ | ID | ClassID | CourseID | +----+---------+----------+ |  1 |       1 |        2 | |  2 |       1 |        5 | |  3 |       2 |        2 | |  4 |       2 |        6 | |  5 |       3 |        1 | |  6 |       3 |        7 | |  7 |       4 |        5 | |  8 |       4 |        2 | |  9 |       5 |        1 | | 10 |       5 |        9 | | 11 |       6 |        3 | | 12 |       6 |        4 | | 13 |       7 |        4 | | 14 |       7 |        3 | +----+---------+----------+ 14 rows in set (0.00 sec)   MariaDB [hellodb]> select * from coc; +-----+---------+----------+ | ID  | ClassID | CourseID | +-----+---------+----------+ |   1 |       1 |        2 | |   2 |       1 |        5 | |   3 |       2 |        2 | |   4 |       2 |        6 | |   5 |       3 |        1 | |   6 |       3 |        7 | |   7 |       4 |        5 | |   8 |       4 |        2 | |   9 |       5 |        1 | |  10 |       5 |        9 | |  11 |       6 |        3 | |  12 |       6 |        4 | |  13 |       7 |        4 | |  14 |       7 |        3 | | 100 |     100 |      100 | +-----+---------+----------+ 15 rows in set (0.00 sec)

看完以上关于ProxySQL如何帮助MySQL实行读写分离，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。