使用ProxySQL＋MHA实现mysql读写分离

一、目的

目前很多大型网站为了实现大量的并发访问，除了在网站实现分布式负载均衡，但是远远不够。到了数据业务层、数据访问层，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库可能会崩溃，如果数据丢失的话，后果不堪设想。这时候，我们会考虑如何减少数据库的连接，一方面采用优秀的代码框架，进行代码的优化。另一方面可以考虑数据层优化，利用MySQL主从配置，实现读写分离，减轻数据库压力。

二、环境

系统均为Red Hat Enterprise Linux Server release 6.5 (Santiago) ProxySQL版本：proxysql-1.4.12-1-centos67.x86_64.rpm

Mysql版本：Server version: 5.7.23 MySQL Community Server (GPL)

ProxySQL主机IP：192.0.2.102

Mysql主库IP：192.0.2.101

Mysql从库IP：192.0.2.102

前提条件： Mysql主从已经配置好了同步

主从配置可参看我上篇文章。

三、配置

① 配置后端MySQL

登入ProxySQL，把MySQL主从的信息添加进去。将主库master也就是做写入的节点放到HG 100中，salve节点做读放到HG 1000。

mysql -uadmin -padmin -h 127.0.0.1 -P 6032

admin@127.0.0.1 [mian] 03:43:53>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.0.2.101',3306,1,1000,10,'readproxysql');

Query OK, 1 row affected (0.02 sec)

admin@127.0.0.1 [mian] 03:45:48>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.0.2.102',3306,1,1000,10,'readproxysql');

Query OK, 1 row affected (0.00 sec)

② 在主库master创建 proxysql监控账号和程序账号：

-- 监控

GRANT USAGE ON *.* TO 'proxysql'@'192.0.2.102' IDENTIFIED BY 'proxysql';

-- 程序

GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'sbuser'@'192.0.2.102' identified by'sbpasswd';

创建proxysql用户用于访问数据库

default_hostgroup需要和上面的默认值对应hostgroup_id

insertintomysql_users(username,password,active,default_hostgroup,transaction_persistent)values('sbuser','sbpasswd',1,100,1);

③ 配置监测账号

④ 加载配置和变量

admin@127.0.0.1 [main] 03:55:58>load mysql servers to runtime;

Query OK, 0 rows affected (0.07 sec)

admin@127.0.0.1 [main] 03:56:56>load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [main] 03:57:02>load mysql variables to runtime;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [main] 03:57:10>save mysql servers to disk;

Query OK, 0 rows affected (0.14 sec)

admin@127.0.0.1 [main] 03:57:18>save mysql users to disk;

Query OK, 0 rows affected (0.04 sec)

admin@127.0.0.1 [main] 03:57:24>save mysql variables to disk;

Query OK, 97 rows affected (0.05 sec)

⑤ 连接数据库

mysql -usbuser -p'sbpasswd' -h192.0.2.102 -P6033

写入测试数据：

从查看各类SQL的执行情况，读写还是走的主库，ProxySQL已经正常启动，但是在stats_mysql_query_digest的hostgroup中发现，读和写全部都是走100这个Master的HG，没有用到从库。主要原因就是ProxySQL的mysql_query_rules路由表没有配置。proxysql是通过自定义sql路由规则就可以实现读写分离的。

⑥ 定义路由规则

如：除select * from tb for update的select全部发送到slave，其他的的语句发送到master。

在proxy上执行如下配置规则

测试如下：

从上面的结果可知，路由规则已经生效，select语句均到从库上执行了。到此所有配置都完成。

四、总结

ProxySQL通过MHA可以实现读写分离，但是这种方式真的就没有问题了吗，如果是一些要求实时性非常高的SQL的话，似乎被路由到了从库就会出现BUG。为了解决这个问题我们可以选择在程序端控制这些参数，ProxySQL只作为一个负载均衡来使用，给ProxySQL创建多个账号，一个读写，一个只读。然后程序去实现读写分离。

ProxySQL是分三层来设计运行的，分别为RUNTIME ，MEMORY ，DISK ：

RUNTIME代表的是ProxySQL当前生效的配置，包括 global_variables,mysql_servers,mysql_users,mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。

就像在配置的时候我每次都会load，MEMORY 是平时在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像DISK / CONFIG FILE 持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。