mysql基于proxysql 实现读写分离

ProxySQL 读写分离

一、版本介绍

ProxySQL(mysql 中间件)：

两个版本：官方版和percona版，percona版是基于官方版基础上修改 C++语言开发，轻量级但性能优异(支持处理千亿级数据)，具有中间件所需的绝大多数功能，包括：

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

官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki

二、基础信息介绍

2.0 运行原理

​ proxysql代理,会通过一个监控账号连接到主从服务器上，通过判断是否有ready_only选项，来区分哪个是主从服务器，然后把读写服务器分别放在读写组里。读写组的ID和规则都是自定义的。

2.1 ProxySQL组成

• 服务脚本：/etc/init.d/proxysql
• 配置文件：/etc/proxysql.cnf
• 主程序：/usr/bin/proxysql
• 基于SQLITE的数据库文件：/var/lib/proxysql/

2.2 启动ProxySQL：

systemctl start proxysql

​ 启动后会监听两个默认端口
​ 6032：ProxySQL的管理端口
​ 6033：ProxySQL对外提供服务的端口

2.3 连接ProxySQL

​ 使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都
是admin：

mysql -uadmin -padmin -P6032 -h127.0.0.1

2.4 sqlite数据库说明：

​ proxysql 修改和保存数据都是通过内置的sqlite类型的数据库完成

​ main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改,只能修改对应的不以 runtime 开头的（在内存）里的表，然后 LOAD 使其生效，SAVE 使其存到硬盘以供下次重启加载.

mysql> 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)

数据库：

• disk 是持久化到硬盘的配置，sqlite数据文件
• stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、
• processlist、查询种类汇总/执行时间，等等
• monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查

其他：

• 在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，
  只能修改非runtime表

• 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效

• 执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中

• global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
  参考: https://github.com/sysown/proxysql/wiki/Global-variables

三、安装

• centos

  • 基于YUM仓库安装
    cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
    [proxysql_repo]
    name= ProxySQL YUM repository
    baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
    gpgcheck=1
    gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
    EOF

  • 基于RPM下载安装：https://github.com/sysown/proxysql/releases

• ubuntu

  step1

     apt-get install -y lsb-release
    	wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
  

  step2

  装1.4系列

  echo deb https://repo.proxysql.com/ProxySQL/proxysql-1.4.x/$(lsb_release -sc)/ ./ \
    | tee /etc/apt/sources.list.d/proxysql.list
  

  装2.0系列

    echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ \
    | tee /etc/apt/sources.list.d/proxysql.list
  

  step3

  apt-get update
  
  apt-get install proxysql  或 apt-get install proxysql=version
  

四、使用示例

​ 如图，proxysql 代理服务器，把来自客户数据更改的操作转发给主服务器，查询操作转发给从服务器

1. 搭建主从复制

注意：从服务器一定要配置read_only、 bind-address 0.0.0.0

2. 把所有mysql 服务器添加到mysql_servers 表中

注意：此处的hostgroup_id 不是随便 填写的，与定义的读写组ID相对应，后面定义完读写组ID后，监视账号会自动的把mysql 服务器添加对应的读写组里,(实际上把之前定义过的hostgroup_id，再修改一遍)

特别注意这里两条数据的hostgroup_id是一样的，权重weight也是一样的，前者保证同一个group以便负载均衡，后者权重可以影响负载均衡结果

先连上 proxy 数据库

mysql -uadmin -padmin -P6032 -h127.0.0.1

插入记录

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.168.1.101',3306); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.168.1.102',3306); 
Query OK, 1 row affected (0.00 sec)

添加到运行中的表，并且持久化保存

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql servers to disk;
Query OK, 139 rows affected (0.00 sec)

2、创建并且配置监控账号

在master 上创建，slave 会自动复制，不用创建

MariaDB [(none)]> grant replication client on *.* to 'm'@'192.168.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

在 proxsql 上

set mysql-monitor_username='m';

set mysql-monitor_password='123';

load mysql variables to runtime;

save mysql variables to disk;

3、设置分组信息

​ 旧版版本

​ 需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为1，读组的id为2

insert into mysql_replication_hostgroups values(1,2,"test");

load mysql servers to runtime;

save mysql servers to disk;

发现已经修改好了之前设置的id

mysql> select * from mysql_servers ;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.101 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.1.102 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

新版本的差异

改成

insert into mysql_replication_hostgroups values(1,2,'read_only','test');

查看当前版本

mysql> select version() ;
+--------------------+
| version()          |
+--------------------+
| 2.0.8-67-g877cab1e |
+--------------------+
1 row in set (0.00 sec)

发现多了一个check_type 字段，检查的类型，默认值是ready_only,用于判断主从的标准

check_type - 执行只读检查时检查的mysql变量，默认情况下为read_only（也可以使用super_read_only）。 对于aws aurora，应使用innodb_read_only

mysql> pragma table_info( mysql_replication_hostgroups);
+-----+------------------+---------+---------+-------------+----+
| cid | name             | type    | notnull | dflt_value  | pk |
+-----+------------------+---------+---------+-------------+----+
| 0   | writer_hostgroup | INT     | 1       | NULL        | 1  |
| 1   | reader_hostgroup | INT     | 1       | NULL        | 0  |
| 2   | check_type       | VARCHAR | 1       | 'read_only' | 0  |
| 3   | comment          | VARCHAR | 1       | ''          | 0  |
+-----+------------------+---------+---------+-------------+----+
4 rows in set (0.00 sec)

4. 配置发送sql 语句的账号

在master 上

MySQL> grant all privelegs on *.* to 's'@'192.168.%' identified by '123';

​ 从上应该刷权限

在ProxySQL配置，

将用户s添加到mysql_users表中， default_hostgroup默认组设置为写组1，当读写分离的路由规则不符合时，会访问默认组的数据库

insert into mysql_users(username,password,default_hostgroup) values('s','123',1);

load mysql users to runtime;

save mysql users to disk;

测试

使用s用户测试是否能路由到默认的1写组实现读、写数据

mysql -us -p123 -P6033 -h127.0.0.1 -e 'select @@server_id' 
mysql -us -p123 -P6033 -h127.0.0.1 -e 'create database testdb' 
mysql -us -p123 testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'

5. 在proxysql上配置路由规则，实现读写分离

​ 插入路由规则：将select语句分离到2的读组，select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁，应路由到1的写组

insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',1,1),(2,1,'^SELECT',2,1);

保存

load mysql query rules to runtime;

save mysql query rules to disk;

注意：

• 因ProxySQL根据rule_id顺序进行规则匹配，select … for update规则的rule_id必须要小于普通的select规则的rule_id
• 与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持

6. 读写分离测试

• 测试读操作是否路由给2的读组

  mysql -us -p123 -P6033 -h127.0.0.1 -e 'select @@server_id'
  

• 测试写操作，以事务方式进行测试

  mysql -us -p123 -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'
  

  提交是1，提交后是2

• 查看路由的信息：查询stats库中的stats_mysql_query_digest表

  读写数据，

  root@z3:~# mysql -us -p123  -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
  root@z3:~# mysql -us -p123  -P6033 -h127.0.0.1 -e 'select id from testdb.t' 
  

  在proxy看路由

mysql>  SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text                      |
+----+----------+------------+----------------------------------+
| 1  | 10309    | 1          | create table t(id int)           |
| 2  | 3653     | 3          | select @@server_id               |
| 1  | 3633     | 1          | insert testdb.t values (?)       |

7.查看信息

• 监控模块的指标保存在monitor库的log表中
• 查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)
  MySQL> select * from mysql_server_connect_log;
• 查看监控心跳信息 (对ping指标的监控)：
  MySQL> select * from mysql_server_ping_log;
• 查看read_only和replication_lag的监控日志
  MySQL> select * from mysql_server_read_only_log;
  MySQL> select * from mysql_server_replication_lag_log;

8.修改proxysql默认的监听地址与端口

root@z3:~# vim /etc/proxysql.cnf 

把6033 改成3306 ，才发现端口居然是倒着的，有意思

mysql_variables=
{
        threads=4
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
#       interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
        interfaces="0.0.0.0:3306"

重启服务即可 systemctl restart proxysql。

呵呵，这是你以为吧

然鹅并无卵用 , 下面是文档原话

#If the database file is found, ProxySQL initializes its in-memory configuration from
# the persisted on-disk database. So, disk configuration gets loaded into memory and# then propagated towards the runtime configuration.
## If the database file is not found and a config file exists, the config file is parsed
# and its content is loaded into the in-memory database, to then be both saved on-disk# database and loaded at runtime.
## IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
# ProxySQL initializes its in-memory configuration from the persisted on-disk# database ONLY. In other words, the configuration found in the proxysql.cnf
# file is only used to initial the on-disk database read on the first startup.

大概意思是说，这个配置文件是针对，初始化新的数据库使用的，即第一次启动proxysql ,如果你已经使用过了数据库，save to disk,下次启动就不会解析这个配置文件，除非找不到这个数据库的硬盘文件。

​ 所以正确的姿势是

mysql> update  global_variables  set variable_value='0.0.0.0:3306' where variable_name='mysql-interfaces';

mysql> save mysql variables to disk;

mysql> proxysql restart ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from global_variables  ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5

验证：用另一台服务器测试

root@Z4:~# mysql  -us -p123 -h192.168.1.103  -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+