latte Studio

mysql读写分离与反代

mysql读写分离与反代
 运维/数据库  3413  7 分钟  

读写分离

常用读写分离路由

  • mysql-proxy，oracle，已经不推荐使用，官方推荐mysql router
  • atlas，奇虎360，https://github.com/Qihoo360/Atlas
  • dbproxy，美团
  • cetus，网易
  • amoeba，http://amoeba.sourceforge.net/doc/amoeba_for_mysql.htm
  • cobar，阿里巴巴，基于amoeba
  • mycat，基于cobar
  • proxysql，常用！https://proxysql.com/

proxysql

简介

proxysql是mysql的中间件，可以实现：分离sql的读写请求，分流集群负载，sql的安全检查和过滤，sql路由，对应用层隔离透明，高级的sql路由规则等功能

有2个版本，官方版，和percona版，percona是基于官方版开发，c++，轻量且性能优异

具有功能：

  • 多种方式的读写分离；
  • 定制基于用户，schema，语句规则，对sql进行路由
  • 缓存查询结果
  • 后端节点监控

官方站点

https://github.com/sysown/proxysql/wiki

https://proxysql.com/

https://www.percona.com/doc/proxysql/index.html

安装

1、准备，先实现主从复制

slave节点要设置read-only=1

略...

2、基于yum仓库安装，或基于rpm安装

#配置yum源
[root@host4 ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
> [proxysql_repo]
> name= ProxySQL YUM repository
> baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
> gpgcheck=1
> gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
> EOF


#安装
[root@host4 yum.repos.d]# yum install -y proxysql

proxysql软件组成

[root@host4 yum.repos.d]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
#配置文件
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
#服务脚本
/usr/bin/proxysql
#主程序
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

[root@host4 yum.repos.d]# ll /var/lib/proxysql/
total 0
#数据库文件目录，采用的是sqlite数据库

启停、基本管理

[root@host4 yum.repos.d]# systemctl start proxysql
[root@host4 yum.repos.d]# ss -nlt
State      Recv-Q Send-Q Local Address:Port                Peer Address:Port              
LISTEN     0      128                *:6032                           *:*                  
LISTEN     0      128                *:6033                           *:*               
#6032管理端口
#6033对外提供服务端口

#连入管理端口后，默认有5个库，其中disk和stats_history是由对应磁盘文件的，可持久化的库
[root@host4 yum.repos.d]# mysql -uadmin -padmin -P6032 -h127.0.0.1
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 |
+-----+---------------+-------------------------------------+

proxysql数据库组成

【main】是proxysql默认的数据库，表中存储了后端mysql实例的信息，ip，用户密码，路由规则等；
采用的是sqlite数据库存储

runtime_开头的表，为proxysql当前运行的配置内容，不可修改
修改时需修改对应的表，但不以runtime开头，然后load使其生效，save到磁盘使其永久生效
#main库中，runtime开头的表基本都有一个对应的表，如下：
MySQL [(none)]> use main
MySQL [main]> show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| mysql_servers                                      |
| mysql_users                                        |
...
| runtime_mysql_servers                              |
| runtime_mysql_users 

【disk】 持久化到磁盘上的配置，sqlite数据文件
【stats】 proxysql运行时抓取的统计信息，
【monitor】 库存储monitor模块收集的信息，主要是对后端db的健康/延迟检查信息
  1. main库和monitor库中，runtime_开头的表是运行时配置，无法修改，只能修改同名的但不以runtime开头的表
  2. 修改后，执行load 。。to runtime立即生效
  3. 执行save 。。 to disk持久生效，保存到proxysql.db文件中
  4. global_variadbles变量可以设置监听端口，管理账户等，参考官方文档：https://github.com/sysown/proxysql/wiki/Global-variables

配置实现读写分离

  1. proxysql上：添加后端mysql实例节点

  2. 后端mysql节点上：添加用于proxysql监控的用户，并在proxysql上添加此用户

  3. proxysql上：检查监控是否正常

  4. proxysql上：设置分组信息，设置为读组，和写组

  5. mysql主节点上：配置proxysql用来发送sql的用户，并在proxysql上添加

  6. proxysql上：配置读写分离的路由规则

  7. 测试读写操作，是否正常路由

  8. proxysql上：查看状态库中，路由相关状态表

  9. proxysql上：添加后端mysql实例节点

    #查看mysql_server表的结构，
   
MySQL [main]> select * from sqlite_master where name='mysql_servers'\G;
*************************** 1. row ***************************
    type: table
    name: mysql_servers
tbl_name: mysql_servers
rootpage: 2
     sql: CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0 , hostname VARCHAR NOT NULL , port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306 , gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0 , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1 , compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000 , max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0 , use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0 , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0 , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.01 sec)
   
   
#默认是空，向其插入2个节点，为2个mysql实例的节点信息，默认都在10分组；
MySQL [main]> select * from mysql_servers;
Empty set (0.00 sec)
   
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.101',3306);
Query OK, 1 row affected (0.00 sec)
   
MySQL [main]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.80.102',3306);
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.02 sec)
   
   
#查看是否生效
MySQL [main]> select * from mysql_servers\G;
*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.101
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
*************************** 2. row ***************************
       hostgroup_id: 10
           hostname: 192.168.80.102
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
2 rows in set (0.00 sec)

  10. 后端mysql节点上：添加用于proxysql监控的用户，并在proxysql上添加此用户

    #主节点创建监控用的 用户，从节点自动同步
MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.80.%' identified by 'wang';
   
#proxysql添加该用户
#利用该用户连接各个mysql实例，通过read-only值来判断各个节点属于读还是写组
   
MySQL [main]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
MySQL [main]> set mysql-monitor_password='wang';
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, 136 rows affected (0.01 sec)

  11. proxysql上：检查监控是否正常

    MySQL [monitor]> select * from mysql_server_connect_log;
   
MySQL [monitor]> select * from mysql_server_ping_log;
#检查是否有，连接错误，和后端节点ping日志，无错说明连接正常
#实验中，发现连接报错，原因为monitor密码写错
   
MySQL [monitor]> select * from mysql_server_read_only_log;
   
MySQL [monitor]> select * from mysql_server_replication_lag_log;

  12. proxysql上：设置分组信息，设置为读组，和写组

    # 查看该表的字段信息
   
MySQL [main]> select * from sqlite_master where name='mysql_replication_hostgroups'\G;
*************************** 1. row ***************************
    type: table
    name: mysql_replication_hostgroups
tbl_name: mysql_replication_hostgroups
rootpage: 19
     sql: CREATE TABLE mysql_replication_hostgroups (writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY , reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0) , check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only' , comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
        
#该表4个字段，分别为写组id，读组id，检查类型，注释
#向其插入2个分组id，id分别为10，和20，分别对应写组，读组，通过read-only的值区分，
   
MySQL [main]> insert into mysql_replication_hostgroups values(10,20,"read_only","test");
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.02 sec)
   
#可以看到，自动对mysql节点进行归组，利用读取的read-only值是否为1
MySQL [main]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname       | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10           | 192.168.80.101 | 3306 | ONLINE | 1      |
| 20           | 192.168.80.102 | 3306 | ONLINE | 1      |
+--------------+----------------+------+--------+--------+

  13. mysql主节点上：配置proxysql用来发送sql的用户，并在proxysql上添加

    #配置发送sql的用户
MariaDB [(none)]> grant all on *.* to sqluser@'192.168.80.%' identified by 'wang';
Query OK, 0 rows affected (0.000 sec)
   
#proxysql节点上，将该用户信息配置好，该用户默认归属10组，即写组；
MySQL [main]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','wang',10);
Query OK, 1 row affected (0.00 sec)
   
MySQL [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
   
MySQL [main]> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
   
#测试默认组的路由是否成功；
#因为配置了10是默认组，此时的读、写，都是默认组10，即发送到192.168.80.101上，server_id是2，
#以下测试，读写均可以，写主库，从库也可以同步
[root@host4 yum.repos.d]# mysql -usqluser -pwang -P6033 -h127.0.0.1 -e "select @@server_id";+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
   
[root@host4 yum.repos.d]# mysql -usqluser -pwang -P6033 -h127.0.0.1 -e "create database testdb1";
[root@host4 yum.repos.d]# mysql -usqluser -pwang testdb1 -P6033 -h127.0.0.1 -e "create table t1(id int)";

  14. proxysql上：配置读写分离的路由规则

    #规则相关的表，mysql_query_rules，myql_query_rules_fast_routing，
   
#插入路由规则，将select语句分到20的读组，其中select..for update会写操作，应路由到10的写组，
#利用正则表达式来匹配sql语句，并将其路由到需要的组；
   
#插入2条规则，
MySQL [(none)]> 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 [(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
   
MySQL [(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)
   
#匹配规则时，根据rule_id顺序进行匹配，因此select..for update的范围小，要放到select...的前面

  15. 测试读写操作，是否正常路由

    #测试读写分离，读请求已经发到了3上，为从库
[root@host4 yum.repos.d]# mysql -usqluser -pwang -P6033 -h127.0.0.1 -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
   
#一个事务内发起的读请求，仍然会发到写组上，serverid为主库的2；
[root@host4 yum.repos.d]# mysql -usqluser -pwang -P6033 -h127.0.0.1 \
> -e 'start transaction;select @@server_id;commit;select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+

  16. proxysql上：查看状态库中，路由相关状态表

    # 查看sql路由分发情况的状态统计信息，
# 读操作都发到了10分组上，读写分离成功；
MySQL [(none)]> select hostgroup,sum_time,count_star,digest_text from 
stats_mysql_query_digest order by sum_time desc;
+-----------+----------+------------+----------------------------------+
| hostgroup | sum_time | count_star | digest_text                      |
+-----------+----------+------------+----------------------------------+
| 10        | 9727     | 1          | create table t1(id int)          |
| 10        | 6323     | 4          | select @@server_id               |
| 20        | 3645     | 2          | select @@server_id               |
| 10        | 3037     | 1          | insert testdb1.t1 values(?)      |
| 10        | 2020     | 1          | create database testdb1          |
| 20        | 1280     | 1          | select * from testdb1            |
| 10        | 1268     | 1          | create databse testdb1           |
| 20        | 1244     | 1          | select * from testdb1.t1         |
| 10        | 1081     | 1          | start transaction                |
| 10        | 592      | 1          | commit                           |
| 10        | 0        | 10         | select @@version_comment limit ? |
| 10        | 0        | 1          | select @@version_comment limit ? |
+-----------+----------+------------+----------------------------------+
updatedupdated2021-03-092021-03-09

相关文章：

加载评论