MySQL高可用实现：主从结构下ProxySQL中的读写分离

以你之姓@ 2022-01-11 16:07 375阅读 0赞

> ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。  
> ProxySQL提供强大的路由规则。当应用程序自身不支持读写分离时，DBA可以通过配置路由规则为应用程序提供透明的读写分离，使用Keepalived + ProxySQL + Orchestrator为主从提供高可用时，能够有效的避免keepalived + 双主结构 由于keepalived脑裂而造成数据被写错乱的痛点。

**介绍**

--------------------

ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。具有以下特性：

> 1.为对多个数据库的应用程序请求提供“智能”的负载均衡。  
> 2.实现了MySQL请求协议，能够对应用程序提供透明的读写分离，避免了应用程序实现复杂的读写分离。  
> 3.能够自动感知数据库健康状态和拓扑结构并且能够自动将应用程序请求路由到处于健康状态的MySQL实例。  
> 4.保护应用程序不受底层数据库复杂拓扑结构变化而受影响，能够自动将写请求转发的主库，自动按照权重将读请求发送的主库和从库。  
> 5.提供了对查询SQL的监控分析统计。  
> 6.为管理员提供了强大的控制机制，可以在代理层缓存查询，以便更快地响应查询、重新路由查询，甚至重新改写那些质量较差的查询语句。

**模块**

--------------------

> ![bb][]

ProxySQL组成模块

> Qurey Processor 用于匹配查询规则并根据规则决定是否缓存查询或者将查询加入黑名单或者重新路由、重写查询或者镜像查询到其他hostgroup。  
> User Auth 为底层后端数据库认证提供了用户凭证。  
> Hostgroup manager – 负责管理发送SQL请求都后端数据库并跟踪SQL请求状态。  
> Connection pool – 负责管理后端数据库连接，连接池中建立的连接被所有的前端应用程序共享。  
> Monitoring – 负责监控后端数据库健康状态主从复制延时并临时下线不正常的数据库实例。

**安装**

--------------------

1.从 https://github.com/sysown/proxysql/releases 下载相应的版本。  
2.yum local×××tall proxysql-1.x.rpm  
3.启动ProxySQL

> /etc/init.d/proxysql start

**配置结构**

--------------------

ProxySQL配置可以存储到SQLite数据库并通过SQL语句来管理配置，并通过如下三层配置来管理ProxySQL。

> DISK: 使用SQLite来持久存储ProxySQL配置，以防ProxySQL重启后配置丢失。  
> Memory: 存在于内存中的配置，也是用户通过SQL直接管理的配置。  
> Runtime: 当前正在使用的配置，处于生效部分。

![223.jpg][]

使用ProxySQL修改配置时，可以通过SQL语句直接修改Memory中的配置，然后使用load命令将Memory中的配置加载的到runtime层来验证配置是否正确，如果验证通过可以通过save将配置保存到SQLite数据库中，如果验证不通过也可以通过load命令将DISK层中的配置加载到Memory和runtime层中，达到回滚到效果。

**内置库表介绍**

--------------------

登录到proxysql管理端口，默认用户名密码为：admin/admin

> mysql -uadmin -padmin -h127.0.0.1 -P6032
> 
> Welcome to the MySQL monitor. Commands end with ; or \\g.
> 
> Your MySQL connection id is 2
> 
> Server version: 5.6.30 (ProxySQL Admin Module)
> 
> Admin> show databases;
> 
> \+-----+---------+-------------------------------+
> 
> | seq | name | file |
> 
> \+-----+---------+-------------------------------+
> 
> | 0 | main | |
> 
> | 2 | disk | /var/lib/proxysql/proxysql.db |
> 
> | 3 | stats | |
> 
> | 4 | monitor | |
> 
> \+-----+---------+-------------------------------+

\* main: 数据库里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。  
\* disk 是持久化到硬盘的配置，sqlite数据文件。  
\* stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等。  
\* monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。

> Admin> show tables;  
> \+--------------------------------------+  
> | tables |  
> \+--------------------------------------+  
> | global\_variables |  
> | mysql\_collations |  
> | mysql\_query\_rules |  
> | mysql\_replication\_hostgroups |  
> | mysql\_servers |  
> | mysql\_users |  
> | runtime\_global\_variables |  
> | runtime\_mysql\_query\_rules |  
> | runtime\_mysql\_replication\_hostgroups |  
> | runtime\_mysql\_servers |  
> | runtime\_mysql\_users |  
> | runtime\_scheduler |  
> | scheduler |  
> \+--------------------------------------+  
> 13 rows in set (0.00 sec)
> 
> Admin> show tables from stats;  
> \+--------------------------------+  
> | tables |  
> \+--------------------------------+  
> | global\_variables |  
> | stats\_mysql\_commands\_counters |  
> | stats\_mysql\_connection\_pool |  
> | stats\_mysql\_global |  
> | stats\_mysql\_processlist |  
> | stats\_mysql\_query\_digest |  
> | stats\_mysql\_query\_digest\_reset |  
> | stats\_mysql\_query\_rules |  
> \+--------------------------------+  
> 8 rows in set (0.00 sec)

**mysql\_servers**

> Admin> show create table mysql\_servers\\G
> 
>  CREATE TABLE mysql\_servers ( 
> 
> hostgroup\_id INT NOT NULL DEFAULT 0, 
> 
> hostname VARCHAR NOT NULL, 
> 
> port INT NOT NULL DEFAULT 3306, 
> 
> status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE\_SOFT', 'OFFLINE\_HARD')) NOT NULL DEFAULT 'ONLINE', 
> 
> weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1, 
> 
> compression INT CHECK (compression >=0 AND compression <= 102400) 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.00 sec)

mysql\_servers对数据库实例进行了分组和实例信息配置。  
·hostgroup\_id: MySQL实例所属组  
 · status:  
 · ONLINE: 运行状态  
 · SHUNNED: 数据库被处于暂时踢出状态，由于后台数据库出现“too many connections error”或者后端数据库主从延时超过了允许的阈值。  
 · OFFLINE\_SOFT: “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完  
 · OFFLINE\_HARD: “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。  
 · weight：负载均衡时选择后端数据库的权重值，权重越高被选中的比率越高。  
 · max\_connections：允许连接到该后端实例的最大连接数，不要设置此值大于后端数据库的最多连接数。  
 · max\_latency\_ms：mysql\_ping 响应时间，大于这个阀值会把它从连接池剔除（即使是ONLINE）

**mysql\_replication\_hostgroups**

此表用于传统的异步/半同步的主从复制，对于MGT/GALERA需要使用mysql\_group\_replication\_hostgroups或者mysql\_galera\_hostgroups（ProxySQL 2.x之后）。在此表中的每一行代表一对writer\_hostgroup和reader\_hostgroup。ProxySQL将监控read\_only的值，ProxySQL将基于read\_only的值来分配MySQL实例为reader\_hostgroup还是writer\_hostgroup,，如果发现从库的 read\_only 变为0、主库变为1，则认为角色互换了，自动改写 mysql\_servers 表里面 hostgroup 关系，达到自动 Failover 效果。

>   
> 
> 
> Admin> SHOW CREATE TABLE mysql\_replication\_hostgroups\\G
> 
> \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 
> 
>  table: mysql\_replication\_hostgroupsCreate Table: 
> 
> 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')) NOT NULL DEFAULT 'read\_only',
> 
>  comment VARCHAR, UNIQUE (reader\_hostgroup))
> 
> 1 row in set (0.00 sec)

**mysql\_users**

> CREATE TABLE mysql\_users ( 
> 
>  username VARCHAR NOT NULL, 
> 
>  password VARCHAR, 
> 
>  active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, use\_ssl INT CHECK (
> 
>  use\_ssl IN (0,1)) NOT NULL DEFAULT 0,
> 
>  default\_hostgroup INT NOT NULL DEFAULT 0, 
> 
>  default\_schema VARCHAR, 
> 
>  schema\_locked INT CHECK (schema\_locked IN (0,1)) NOT NULL DEFAULT 0,
> 
>  transaction\_persistent INT CHECK (transaction\_persistent IN (0,1)) NOT NULL DEFAULT 0, 
> 
>  fast\_forward INT CHECK (fast\_forward IN (0,1)) NOT NULL DEFAULT 0, 
> 
>  backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1, 
> 
>  frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1, 
> 
>  max\_connections INT CHECK (max\_connections >=0) NOT NULL DEFAULT 10000, 
> 
>  PRIMARY KEY (username, backend), 
> 
>  UNIQUE (username, frontend)
> 
> )

· username, password: 连接后端db的用户密码。这个密码你可以插入明文，也可以插入hash加密后的密文。  
· default\_hostgroup: 这个用户的请求没有匹配到规则时，默认发到这个 hostgroup。  
· default\_schema: 这个用户连接时没有指定 database name 时，默认使用的schema 注意表面上看默认为NULL，但实际上受到变量 mysql-default\_schema 的影响，默认为 information\_schema。  
· transaction\_persistent: 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。  
· fast\_forward: 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .\* 就行了。

**mysql\_query\_rules**

> CREATE TABLE mysql\_query\_rules ( 
> 
>  rule\_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
> 
>  active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, 
> 
> username VARCHAR, 
> 
>  schemaname VARCHAR, 
> 
>  flagIN INT NOT NULL DEFAULT 0, 
> 
>  client\_addr VARCHAR, 
> 
>  proxy\_addr VARCHAR,
> 
>  proxy\_port INT,
> 
>  digest VARCHAR, 
> 
>  match\_digest VARCHAR, 
> 
>  match\_pattern VARCHAR, 
> 
>  negate\_match\_pattern INT CHECK (negate\_match\_pattern IN (0,1)) NOT NULL DEFAULT 0, 
> 
>  flagOUT INT, 
> 
>  replace\_pattern VARCHAR,
> 
>  destination\_hostgroup INT DEFAULT NULL,
> 
>  cache\_ttl INT CHECK(cache\_ttl > 0),
> 
>  reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
> 
>  timeout INT UNSIGNED,
> 
>  retries INT CHECK (retries>=0 AND retries <=1000),
> 
>  delay INT UNSIGNED, mirror\_flagOUT INT UNSIGNED,
> 
>  mirror\_hostgroup INT UNSIGNED, error\_msg VARCHAR,
> 
>  log INT CHECK (log IN (0,1)),
> 
>  apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
> 
>  comment VARCHAR)

· rule\_id: 表主键，自增。规则处理是以 rule\_id 的顺序进行。

· active: 只有 active=1 的规则才会参与匹配。  
username: 如果非 NULL，只有连接用户是 username 的值才会匹配  
 · : 如果非 NULL，只有查询连接使用的db是 schemaname 的值才会匹配。  
 · client\_addr: 匹配客户端来源IP  
· digest: 精确的匹配一类查询。  
 · match\_digest: 使用正则来匹配查询指纹（去掉查询参数后的查询）  
· match\_pattern: 正则匹配查询文本。  
· negate\_match\_pattern: 反向匹配，相当于对 match\_digest/match\_pattern 的匹配取反。  
· re\_modifiers: 修改正则匹配的参数，比如默认的：忽略大小写CASELESS、禁用GLOBAL上面都是匹配规则。  
· replace\_pattern: 查询重写，默认为空，不rewrite。  
· destination\_hostgroup: 路由查询到这个 hostgroup。当然如果用户显式 start transaction 且 transaction\_persistent=1，那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走。  
· cache\_ttl: 查询结果缓存的毫秒数。  
· timeout: 这一类查询执行的最大时间（毫秒），超时则自动kill.  
· retries: 语句在执行时失败时，重试次数。默认由 mysql-query\_retries\_on\_failure变量指定，为1。  
· delay: 查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。  
· error\_msg: 默认为NULL，如果指定了则这个查询直接被 block 掉，马上返回这个错误信息。  
· multiplex: 连接是否复用。  
· log: 是否记录查询日志。可以看到log是否记录的对象是根据规则。

**读写分离**

--------------------

ProxySQL作为中间件能够监听接收到应用程序端的数据库请求，并解析前端的SQL语句并将解析结果与查询规则进行匹配，将匹配的SQL发送到相应的MySQL实例从而实现读写分离。这个过程入下图所示：

![bb 1][]

1. 搭建MySQL主从，结构如下：

> master： 192.168.20.31:3306
> 
> slave1： 192.168.20.32:3306
> 
> slave2: 192.168.20.33:3306

2. 在主库中创建监控账号用于ProxySQL监控目标主机。

> create user 'monitor'@'%' identified with mysql\_native\_password by 'monitor';
> 
> grant select on sys.\* to 'monitor'@'%';
> 
> grant select on performance\_schema.\* to 'monitor'@'%';
> 
> flush privileges;

3. 插入3个数据库实例节点到ProxySQL

> Admin> ×××ert into mysql\_replication\_hostgroups (writer\_hostgroup,reader\_hostgroup) values(10,20);
> 
> Admin> ×××ert into mysql\_servers (hostgroup\_id,hostname,port) values (10,'192.168.20.31',3306);
> 
> Admin> ×××ert into mysql\_servers (hostgroup\_id,hostname,port) values (20,'192.168.20.32',3306);
> 
> Admin> ×××ert into mysql\_servers (hostgroup\_id,hostname,port) values (10,'192.168.20.33',3306);
> 
> Admin> save mysql servers to disk;
> 
> Admin> load mysql servers to runtime;

4. 配置读写分离规则

> Admin> 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);
> 
> Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
> 
> Admin> SAVE MYSQL QUERY RULES TO DISK;

5. 配置MySQL用户到ProxySQL

> Admin> INSERT INTO mysql\_users(username,password,default\_hostgroup) VALUES ('app','pass',10);
> 
> Admin> save mysql users to disk;
> 
> Admin> load mysql users to runtime;

**ProxySQL + Orchestrator实现高可用**

--------------------

Orchestrator（https://github.com/github/orchestrator）是MySQL复制结构的一个拓扑管理工具,能够自动检测MySQL拓扑结构，当主库出现故障时能够自动将“最优”从库提升为主库。Orchestrator提供了丰富的API接口和故障检测以及故障切换的钩子函数，MyData正是通过Orchestrator提供的钩子函数配合ProxySQL、Keepalived、HAProxy实现对应用程序无感知的故障切换。

**切换流程：**

1. 首先Orchestrator检测的后端主库出现故障后会通过从库二次确认主库出现故障，避免了主库高负载时出现误判主库down机。

2. Orchestrator切换之前将调用PreFailoverProcesses钩子函数，MyData在钩子函数中首先将“down”机的主库从ProxySQL中踢出,避免应用程序将请求写入到“假死”的主库，执行语句如下：

> \#强制关闭与假死主库的连接，避免数据被写入的假死的老主库。  
> Admin> update runtime\_mysql\_servers set status="HARD\_OFFLINE" where hostname='192.168.20.31' and port='3306'
> 
>   
> 
> 
> \#将假死的老主库提出集群，避免后续数据被写入到老主库  
> Admin> delete from mysql\_servers where hostname='192.168.20.31' and port='3306'  
> Admin> load mysql servers to runtime  
> Admin> save mysql serbers to disk

3. MyData能够根据用户RPO、RTO“智能”作出恢复决定。

4. MyData通知Orchestrator开发切换，Orchestrator切换完成之后，将修改新主库的read\_only值为0。

5. ProxySQL将新的写请求路由到主库。

整个流程之中MyData作了大量优化，能够在满足用户RTO情况下，能将用户丢失的数据减少到原来的10%以下，MyData还为ProxySQL本身提供了高可用方案，避免了ProxySQL本身的单点故障。

**总结**

--------------------

ProxySQL提供强大的路由规则。当应用程序自身不支持读写分离时，DBA可以通过配置路由规则为应用程序提供透明的读写分离，使用Keepalived + ProxySQL + Orchestrator为主从提供高可用时，能够有效的避免keepalived + 双主结构 由于keepalived脑裂而造成数据被写错乱的痛点。

[郑州不孕不育医院：http://jbk.39.net/yiyuanzaixian/zztjyy//][http_jbk.39.net_yiyuanzaixian_zztjyy]

**关于MyData**

--------------------

MyData是云和恩墨自主研发的，针对MySQL数据库提供高可用、高可靠、高安全性和易于使用的整体解决方案。MyData融合了云和恩墨资深数据库工程师的经验和最佳实践，来帮助客户快速构建高可用的数据库集群环境，保证了MySQL数据库运行环境符合企业级数据库的要求，帮助客户提高快速交付的能力。  
云和恩墨对MyData提供专业、灵动的端到端服务，涵盖规划设计、建设实施、运营管理和优化提升四个阶段，为客户构建安全、连续、高效和稳定的数据环境。  
MyData目前已经在政府和金融行业拥有多个最佳实践的案例，致力于为企业提供开展开源数据库一体化的解决方案。

转载于:https://blog.51cto.com/14393777/2409359

[bb]: http://img.blog.itpub.net/blog/2019/06/14/d2e3ac59e29dad74.jpeg?x-oss-process=style/bb
[223.jpg]: http://blog.itpub.net/29715045/viewspace-2647675/public_resources/ue/upload/image/20190613/1560413756150031946.jpg
[bb 1]: http://img.blog.itpub.net/blog/2019/06/14/cbb7870dfa28b776.jpeg?x-oss-process=style/bb
[http_jbk.39.net_yiyuanzaixian_zztjyy]: http://www.xbzztj.com/