服务器集群（五）ProxySQL实现数据库读写分离

本文章已收录至本站专栏
服务器集群系列

1、为什么需要读写分离

主从复制、读写分离一般是一起使用的。 目的很简单，就是为了提高数据库的并发性能。
你想，假设是单机，读写都在一台MySQL上面完成，性能肯定不高。
如果有三台MySQL，一台mater只负责写操作，两台salve只负责读操作，性能不就能大大提高了吗？
——SegmentFault

2、实现读写分离的方式

MySQL 实现读写分离的方式有以下几种：

• 代码层实现逻辑1：对读/写请求进行解析，针对性地分发到不同的数据库中，从而实现读写分离；
• 代码层实现逻辑2：二次验证方式，主库获取最新的GTID，到从库进行验证是否应用，成功就读取数据
• 基于类似ProxySQL，MyCat中间件来实现读写分离的效果.通过不同的端口或机制进行读写分离。

中间件实现读写分离，优点十分明显，只需要进行配置就可以实现读写分离，除此之外并且当主库宕机时，还可以通过配置方式进行主从自动切换，这样即使主库宕机整个集群也不会丧失写的功能.

• 多副本写入方式：这种方式国产分布式数据库里有很多体现，确认写入另一个复制集群中。

本系列文章以中间件方式为例，实现MySQL负载均衡和读写分离。

3、MySQL中间件

1. Cetus（开源）

项目地址
https://github.com/cetus-tools/cetus/releases

Centus 由C语言开发的关系型数据库MySQL的中间件，基于MySQL Proxy开发，分为读写分离版和分库版本两个版本，是MySQL Router前身。社区目前活动轨迹非常少，但还有企业使用。

2. MaxScale（闭源）

一款由MariaDB公司出品的中间件，该中间件能实现读写分离和读负载均衡。
MaxScale虽是开放源代码的，但并不是一个“Open Source”的项目，使用了特有的授权协议：BSL协议（https://mariadb.com/projects-using-bsl-11/）

3. Vitess（开源）

Youtube生产在使用的中间件，架构确实很复杂。 与以往中间件不同，使用Vitess应用改动比较大要 使用他提供语言的API接口。支持读写分离，分库分表，故障切换和数据备份。

4. MySQL Router（开源）

MySQL官方推荐的读写分离中间件，MySQL Router是MySQL Proxy的替代方案，可以实现读写分离，无法动态更改配置。在MGR中充当代理。

5. ProxySQL（开源）

ProxySQL是percona用C++语言开发的，一个轻量级开源软件，性能和功能满足读写中间件所需的绝大多数功能，其配置数据基于SQLite存储，目前已到v2.4.2版本。
可以实现读写分离，基于语句的自定义识别规则，实现简单的sharding。可以动态更改配置，请求SQL都要经过ProxySQL服务器，对CPU，内存有一定的要求。在MGR中充当代理。

GitHub官网
https://github.com/sysown/proxysql/releases
Percona官网
https://www.percona.com/downloads/proxysql

6. MyCAT（开源）

基于阿里开源的Cobar产品而二次研发的开源产品。
应用端链接MyCAT服务器，能支持读写分离，支持多种分库分表算法。
支持MySQL，PostgreSQL，Oracle ，SQL Server，性能损耗 20%~50%
整体设计思路和实现都很好，也是比较成熟的解决方案，在那个年代，MyCAT的整体设计思路和实现方式都是比较成熟的解决方案。
但目前社区现状很不好，基本慢慢被抛弃，原因可以自行了解。目前爱可生维护的衍生版本 DBLE。

6. ShardingSphere（开源）

ShardingSphere=Shardingjdbc+ShardingProxy+ShardingSidecar

Shardingjdbc:轻量级Java架构，在Java的JDBC层提供的额外服务，就是jdbc驱动二次包装jar，实现读写分离，复杂的分库分表逻辑 ，性能损耗7%
ShardingProxy:类似与MyCAT，中间件软件实现读写分离，复杂分库分表。性能损耗20%
已于2020年4月16日成为 Apache 软件基金会的顶级项目。

除以上列出的，还有商业版本的淘宝的DRDS，平民软件 OneProxy 等。

本站统一采用ProxySQL，以CentOS和Ubuntu上的配置为例。

4、安装ProxySQL

注意：
先到官网查看最新版本，本站提供的安装地址可能是旧版的。
需要自行替换下文中安装指令的URL。

CentOS：

1
2

wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql-2.4.2-1-centos7.x86_64.rpm
yum install proxysql-2.4.2-1-centos7.x86_64.rpm

Ubuntu：

1
2

wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
dpkg -i proxysql_2.4.2-ubuntu20_amd64.deb

5、配置ProxySQL

5.1、前期

首先在/etc/proxysql.cnf中，大约第37行：

1
2
3
4
5
6
7
8

admin_variables=
{
    admin_credentials="admin:admin"
#    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
    mysql_ifaces="0.0.0.0:6032"
#    refresh_interval=2000
#    debug=true
}

在admin_credentials后面添加自己想要的管理员账号密码，格式：账号:密码，分号分隔。
例如：

此处内容需要评论回复（自动审核）或加入 QQ 技术交流群（立即获得内容）后方可阅读。赞助（二维码在文章下方）后联系作者可一次性解锁所有（包括之后的新文章）。

注意：如果已经启动过ProxySQL，那么这个配置将不会生效，需要删除：
/var/lib/proxysql/proxysql.db
重启，即可重新从配置文件加载配置，但是之前的配置会被清空。

管理命令：

1
2
3
4
5

systemctl start proxysql #启动
systemctl stop proxysql # 停止
systemctl status proxysql # 查看状态
systemctl enable proxysql # 设置开机自启动
systemctl disable proxysql # 取消开机自启动

启动后，执行

1

mysql -uadmin -padmin -h127.0.0.1 -P6032

进入，admin可以替换为前面自定义的账号密码。

如果服务器上没有MySQL客户端，需要远程配置，那么一定要在前面配置账号密码，使用自定义的账密登录。替换指令中的127.0.0.1为IP地址即可。

5.2、公网集群

如果是公网集群，需要在每一台有MySQL服务（仅从机需要安装，主机无需配置，保持原样）的服务器上安装ProxySQL，按照内网集群的方法会导致SQL查询延迟从1秒上升至10秒，得不偿失。

在后文的ProxySQL配置中，每一台服务器只需要配置两个mysql_servers，一个是本地（127.0.0.1）的读服务器，一个是主机的写服务器。

5.3、内网集群

如果是内网集群，那网络问题不再是瓶颈，因此只需要在主机或者单独的一台服务器上安装ProxySQL，需要在mysql_servers中配置一台主机对写服务器，和所有从机的读服务器。

5.4、读写配置

注意：下文中，服务器和对应的HostGroup分别为：

登录进ProxySQL的命令行，执行

1

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'x.x.x.x','3306',1,'Read Group');

添加读服务器，如果有多个读服务器可以修改倒数第二个数字（1，对应字段weight），设置权重。

执行

1

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'x.x.x.x','3306',2,'Write Group');

添加写服务器。

执行

1
2
3

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

意思是把读操作分配给HostGroup为2的服务器，写操作分配给HostGroup为1的服务器。

执行

1

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('username','password',1);

以添加一个MySQL账号密码。

注意：
此处为MySQL主从数据库对应数据库的账密，一般主从数据库账密一致。
后端（如PHP）连接数据库用的是哪个账密就添加哪个，最好不要用root。

最后，执行

此处内容需要评论回复（自动审核）或加入 QQ 技术交流群（立即获得内容）后方可阅读。赞助（二维码在文章下方）后联系作者可一次性解锁所有（包括之后的新文章）。

如果后期需要查看添加的数据，同样登录进数据库，执行

1
2
3

select * from mysql_servers;
select * from mysql_query_rules;
select * from mysql_users;

以列出上述配置，

执行

1
2
3

delete from mysql_servers;
delete from mysql_query_rules;
delete from mysql_users;

以清除上述配置。

5.5、监控配置

按上文方法登录进ProxySQL命令行，执行：

1
2
3

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

自行替换其中的monitor为你想要的账号密码。

然后执行

此处内容需要评论回复（自动审核）或加入 QQ 技术交流群（立即获得内容）后方可阅读。赞助（二维码在文章下方）后联系作者可一次性解锁所有（包括之后的新文章）。

登录进服务器MySQL数据库，执行

1
2

CREATE USER 'monitor' IDENTIFIED BY 'monitor';
grant replication client on *.* to monitor@'%';

如果不方便登录命令行，可以手动用phpMyAdmin创建用户，允许%（全部主机）连接，授权所有权限。

6、配置后端

完成上述配置，就可以把后端的数据库地址改为ProxySQL的IP:6033，其他无需改动。
这就是使用中间件的方便之处（笑）。