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

原文： http://www.enmotech.com/web/detail/1/714/1.html （复制链接，打开浏览器即可查看原文）

10年积累的网站设计制作、网站建设经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有海州免费网站建设让你可以放心的选择与我们合作。

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

介绍

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

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

模块

ProxySQL组成模块

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

安装

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

/etc/init.d/proxysql start

配置结构

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

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

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

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

内置库表介绍

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

mysql -uadmin -padmin -h227.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实例从而实现读写分离。这个过程入下图所示：

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

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> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(10,20);
Admin> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'192.168.20.31',3306);
Admin> insert into mysql_servers (hostgroup_id,hostname,port) values (20,'192.168.20.32',3306);
Admin> insert 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脑裂而造成数据被写错乱的痛点。

关于MyData

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

想了解更多关于数据库、云技术吗？

快来关注”数据和云“公众号，”云和恩墨“官方网站，我们期待与大家一同学习和进步。

网页题目：MySQL高可用实现：主从结构下ProxySQL中的读写分离
本文路径：http://scyanting.com/article/jepiie.html

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

其他资讯