欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

proxysql配置mysql读写分离

程序员文章站 2022-05-02 10:05:24
...

1. 不同类型的读写分离方案解析


数据库中间件最基本的功能就是实现读写分离, ProxySQL 当然也支持。而且 ProxySQL 支持的路由规则非常灵活,不仅可以实现最简单的读写分离,还可以将读/写都分散到多个不同的组,以及实现分库 sharding (分表sharding的规则比较难写,但也能实现)。

本文只描述通过规则制定的语句级读写分离,不讨论通过 ip/port, client, username, schemaname 实现的读写分离。

下面描述了ProxySQL能实现的常见读写分离类型

1.1 最简单的读写分离

proxysql配置mysql读写分离
这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1

在ProxySQL上,分两个组,一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)

这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可

这种读写分离模式,在环境较小时能满足绝大多数需求。但是需求复杂、环境较大时,这种模式就太过死板,因为一切都是monitor模块控制的

1.2 多个读组或写组的分离模式

前面那种读写分离模式,是通过 monitor 模块监控 read_only 来调整的,所以每一个后端集群必须只能分为一个写组,一个读组。
但如果想要区分不同的 select ,并将不同的 select 路由到不同的节点上。例如有些查询语句的开销非常大,想让它们独占一个节点/组,其它查询共享一个节点/组,怎么实现?

例如,下面这种模式
proxysql配置mysql读写分离
看上去非常简单。但是却能适应各种需求。例如,后端做了分库,对某库的查询要路由到特定的主机组

至于各个主机组是同一个主从集群(下图左边),还是互相独立的主从集群环境(下图右边),要看具体的需求,不过这种读写分离模式都能应付
proxysql配置mysql读写分离
在实现这种模式时,前提是不能开启monitor模块的read_only监控功能,也不要设置mysql_replication_hostgroup 表

例如,下面的配置实现的是上图左边的结构:写请求路由给HG=10,对test1库的select语句路由给HG=20,其它select路由给HG=30

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | host1    | 3306 | ONLINE | 1      |
| 20           | host2    | 3306 | ONLINE | 1      |
| 30           | host3    | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_users: 
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root     | 10                |
+----------+-------------------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT.*test1\..*   |
| 3       | 30                    | ^SELECT              |
+---------+-----------------------+----------------------+

查看表结构的方式:

PRAGMA  table_info("表名");

2. ProxySQL实现读写分离示例


环境说明:

IP 角色 所需服务
192.168.159.136 读写分离解析主机 proxysql
192.168.159.137 master mysql或者mariadb
192.168.159.144 slave mysql或者mariadb

在做实验前,需要:

  • 关闭防火墙和SELINUX
  • 安装mysql并配置主从

2.1 安装ProxySQL

//下载proxysql的安装包,并安装
[aaa@qq.com ~]# wget http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/7/proxysql-2.0.13-1-centos7.x86_64.rpm
[aaa@qq.com ~]# ls
anaconda-ks.cfg  proxysql-2.0.13-1-centos7.x86_64.rpm
[aaa@qq.com ~]# yum -y localinstall proxysql-2.0.13-1-centos7.x86_64.rpm

//启动proxysql并设置开机自动启动
[aaa@qq.com ~]# systemctl start proxysql
[aaa@qq.com ~]# chkconfig proxysql on
[aaa@qq.com ~]# ss -anlt
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      128             *:6032                        *:*                  
LISTEN     0      128             *:6033                        *:*                  
LISTEN     0      128             *:6033                        *:*                  
LISTEN     0      128             *:6033                        *:*                  
LISTEN     0      128             *:6033                        *:*                  
LISTEN     0      128             *:22                          *:*                  
LISTEN     0      100     127.0.0.1:25                          *:*                  
LISTEN     0      128            :::22                         :::*                  
LISTEN     0      100           ::1:25                         :::*

//下载mariadb(客户端就行)
[aaa@qq.com ~]# yum -y install mariadb

//登录proxysql
[aaa@qq.com ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
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)

2.2 配置主从

主上的操作

//配置网络源
[aaa@qq.com yum.repos.d]# ls
CentOS-Base.repo  epel.repo

//安装mariadb
[aaa@qq.com ~]# yum -y install mariadb*
............

//启动服务
[aaa@qq.com ~]# systemctl enable --now mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[aaa@qq.com ~]# ss -anlt
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      50              *:3306                        *:*                  
LISTEN     0      128             *:22                          *:*                  
LISTEN     0      100     127.0.0.1:25                          *:*                  
LISTEN     0      128            :::22                         :::*                  
LISTEN     0      100           ::1:25                         :::*

//创建一个同步账号授权给从数据库使用
[aaa@qq.com ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.159.144' identified by 'repl123.';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//修改配置文件
[aaa@qq.com ~]# vim /etc/my.cnf
............
//添加以下内容
server-id=10
log-bin=mysql-bin

//重启服务
[aaa@qq.com ~]# systemctl restart mariadb
[aaa@qq.com ~]# ss -anlt
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      50              *:3306                        *:*                  
LISTEN     0      128             *:22                          *:*                  
LISTEN     0      100     127.0.0.1:25                          *:*                  
LISTEN     0      128            :::22                         :::*                  
LISTEN     0      100           ::1:25                         :::*

//查看主库的状态
[aaa@qq.com ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

备上的操作

//配置网络源
[aaa@qq.com ~]# ls /etc/yum.repos.d/
CentOS-Base.repo  epel.repo

//安装mariadb
[aaa@qq.com ~]# yum -y install mariadb*
............

//修改配置文件
[aaa@qq.com ~]# vim /etc/my.cnf
............
//添加以下内容
server-id=20
relay-log=mysql-relay-bin

//重启服务
[aaa@qq.com ~]# systemctl restart mariadb
[aaa@qq.com ~]# ss -anlt
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN     0      50              *:3306                        *:*                  
LISTEN     0      128             *:22                          *:*                  
LISTEN     0      100     127.0.0.1:25                          *:*                  
LISTEN     0      128            :::22                         :::*                  
LISTEN     0      100           ::1:25                         :::*

//查看并启动主从复制
[aaa@qq.com ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='192.168.159.137',master_user='repl',master_password='repl123.',master_log_file='mysql-bin.000001',master_log_pos=245;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.159.137
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 823
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
1 row in set (0.00 sec)

测试验证有没有完成主从同步

//在从上创建个库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

//在备上查看
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

//成功配置好了主从

2.3 配置proxysql

2.3.1 mysql主库添加proxysql可以增删改查的账号

MariaDB [(none)]> grant all on *.* to 'proxysql'@'192.168.159.136' identified by 'aaa@qq.com';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.3.2 添加 mysql 主机到 mysql_servers 表中

hostgroup_id 10表示写组,20表示读组

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.159.137',3306,1,'Write Group');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.159.144',3306,1,'Read Group');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select hostgroup_id,hostname,port,weight,comment from mysql_servers;
+--------------+-----------------+------+--------+-------------+
| hostgroup_id | hostname        | port | weight | comment     |
+--------------+-----------------+------+--------+-------------+
| 10           | 192.168.159.137 | 3306 | 1      | Write Group |
| 20           | 192.168.159.144 | 3306 | 1      | Read Group  |
+--------------+-----------------+------+--------+-------------+
2 rows in set (0.00 sec)

//加载到runtime里去
MySQL [(none)]> load mysql servers to run;
Query OK, 0 rows affected (0.01 sec)

//保存到disk里去
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)

//添加刚才在master上创建的账号 proxysql
//default_hostgroup 默认组设置为写组,也就是1;
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','aaa@qq.com',1,1);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select * from mysql_users \G
*************************** 1. row ***************************
              username: proxysql    # 后端mysql实例的用户名
              password: aaa@qq.com     # 后端mysql实例的密码
                active: 1    # active=1表示用户生效,0表示不生效
               use_ssl: 0
     default_hostgroup: 1    # 用户默认登录到哪个hostgroup_id下的实例
        default_schema: NULL    # 用户默认登录后端mysql实例时连接的数据库,这个地方为NULL的话,则由全局变量mysql-default_schema决定,默认是information_schema
         schema_locked: 0
transaction_persistent: 1    # 如果设置为1,连接上ProxySQL的会话后,如果在一个hostgroup上开启了事务,那么后续的sql都继续维持在这个hostgroup上,不论是否会匹配上其它路由规则,直到事务结束。虽然默认是0
          fast_forward: 0    # 忽略查询重写/缓存层,直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能,一般不用,路由规则 .* 就行了
               backend: 1
              frontend: 1
       max_connections: 10000    # 该用户允许的最大连接数
               comment: 
1 row in set (0.00 sec)

//加载到runtime里去
MySQL [(none)]> load mysql users to run;
Query OK, 0 rows affected (0.00 sec)

//保存到disk里去
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

2.4 添加健康检测的帐号

2.4.1 在主上添加属于proxysql的只读账号

MariaDB [(none)]> grant select on *.* to 'monitor'@'192.168.159.%' identified by 'monitor';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2.4.2 在proxysql主机端修改变量设置健康检测的账号

MySQL [(none)]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)

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

MySQL [(none)]> save mysql variables to disk;
Query OK, 134 rows affected (0.00 sec)

2.5 添加读写分离的路由规则

  • 将 select 查询语句全部路由至 hostgroup_id=20的组(也就是读组)
  • 但是 select * from tb for update 这样的语句是会修改数据的,所以需要单独定义,将它路由至 hostgroup_id=10的组(也就是写组)
  • 其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users 表中的 default_hostgroup)
MySQL [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(30,1,'^SELECT',20,1);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,"^SHOW",20,1);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 2       | 1      | ^SHOW                | 20                    | 1     |
| 30      | 1      | ^SELECT              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+
3 rows in set (0.00 sec)

MySQL [(none)]> load mysql query rules to run;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)

2.6 验证读写分离

2.6.1 登录 proxysql 客户端

登录用户是刚才我们在 mysql_user 表中创建的用户,端口为6033

[aaa@qq.com ~]#  mysql -uproxysql aaa@qq.com -h127.0.0.1 -P6033
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
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 |
| school             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

2.6.2 修改数据库和查询

//创建两个数据库
MySQL [(none)]> create database teacher;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> create database sprite;
Query OK, 1 row affected (0.00 sec)

//查询数据库创建成功没
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sprite             |
| teacher            |
| test               |
+--------------------+
7 rows in set (0.00 sec)

2.6.3 验证读写分离是否成功

MySQL [(none)]> select * from stats_mysql_query_digest\G
*************************** 1. row ***************************
        hostgroup: 10
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x7088F6EA1D547993
      digest_text: create database sprite
       count_star: 1
       first_seen: 1597436113
        last_seen: 1597436113
         sum_time: 10000967
         min_time: 10000967
         max_time: 10000967
sum_rows_affected: 0
    sum_rows_sent: 0
*************************** 2. row ***************************
        hostgroup: 10
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x7A58004EB07C2DC9
      digest_text: create database teacher
       count_star: 1
       first_seen: 1597435469
        last_seen: 1597435469
         sum_time: 10001950
         min_time: 10001950
         max_time: 10001950
sum_rows_affected: 0
    sum_rows_sent: 0
*************************** 3. row ***************************
        hostgroup: 20
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0xFC83CD3FFE618224
      digest_text: select @@Version
       count_star: 1
       first_seen: 1597435251
        last_seen: 1597435251
         sum_time: 2283
         min_time: 2283
         max_time: 2283
sum_rows_affected: 0
    sum_rows_sent: 1
*************************** 4. row ***************************
        hostgroup: 20
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x02033E45904D3DF0
      digest_text: show databases
       count_star: 1
       first_seen: 1597436069
        last_seen: 1597436069
         sum_time: 864
         min_time: 864
         max_time: 864
sum_rows_affected: 0
    sum_rows_sent: 6
*************************** 5. row ***************************
        hostgroup: 1
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0x02033E45904D3DF0
      digest_text: show databases
       count_star: 6
       first_seen: 1597435164
        last_seen: 1597435640
         sum_time: 60006449
         min_time: 10000188
         max_time: 10001719
sum_rows_affected: 0
    sum_rows_sent: 0
*************************** 6. row ***************************
        hostgroup: 1
       schemaname: information_schema
         username: proxysql
   client_address: 
           digest: 0xD98E7BF9DDFEA113
      digest_text: KILL ?
       count_star: 2
       first_seen: 1597435617
        last_seen: 1597435622
         sum_time: 0
         min_time: 0
         max_time: 0
sum_rows_affected: 0
    sum_rows_sent: 0

至此,读写分离成功!!!

相关标签: linux mysql