ProxySQL实现MySQL读写分离

MySQL读写分离(ProxySQL) 读写分离原理

读写分离就是用户在发送请求时，请求经过中间件，中间件将请求中的读和写操作分辨出来将读请求发送给后端的从服务器，将写请求发送给后端的主服务器，再又主服务器通过主从复制将数据复制给其他从服务器

常见MySQL中间件 名称公司站点地址
mysql-proxy   Oracle   https://downloads.mysql.com/archives/proxy  
Atlas   Qihoo   https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md  
dbproxy   美团   https://github.com/Meituan-Dianping/DBProxy  
Cetus   网易乐得   https://github.com/Lede-Inc/cetus  
Amoeba   https://sourceforge.net/projects/amoeba/  
Cobar   阿里巴巴   Amoeba的升级版  
Mycat   基于Cobar    
ProxySQL   https://proxysql.com/  

本文以ProxySQL为例来介绍读写分离的使用方法

ProxySQL简介

ProxySQL为MySQL的中间件，其有两个版本官方版和percona版，percona版是基于官方版基础上修改而来。ProxySQL是由C++语言开发，轻量级但性能优异（支持处理千亿级数据），其具有中间件所需要的绝大多数功能，如：

多种方式的读写分离

定制基于用户、基于schema、基于语言的规则对SQL语句进行路由

缓存查询结果

后端节点的控制
...
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki

ProxySQL安装后生成的文件 /etc/init.d/proxysql #此为服务脚本存放在init.d目录下所以需要使用service命令去启动他 /etc/proxysql.cnf /usr/bin/proxysql /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl ProxySQL所使用的端口

ProxySQL所使用的端口为6032和6033
6032:用来配置ProxySQL，是个管理接口
6033:用来被远程用户连接端口

ProxySQL内置数据库 MySQL [(none)]> 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)

以上这些库中主要配置的库为main库，里面存放了ProxySQL的各种配置。

ProxySQL main库内的表 MySQL [(none)]> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.00 sec)

main库中的表分为runtime开头和非runtime开头
runtime开头为运行时的设置
非runtime开头为需要设置的配置
所有的配置修改后需要执行命令才能加载到runtime生效

LOAD ... TO RUNTIME

所有的配置修改后需要执行命令才能永久保存

SAVE ... TO DISK 日志查看

查看read_only和replication_log的监控日志

MySQL [(none)]> select * from mysql_server_read_only_log; Empty set (0.00 sec) MySQL [(none)]> select * from mysql_server_replication_lag_log; Empty set (0.00 sec) ProxySQL实现读写分离

ProxySQL在实现读写分离之前先要实现主从复制的共功能
本实验总计使用4台主机，详细配置如下

主机ip地址
Client   192.168.73.113  
ProxySQL   192.168.73.112  
Master   192.168.73.110  
Slave   192.168.73.111