MySQL-ProxySQL读写分离连接池负载均衡分库分表故障切换查询重写流量镜像SQL审计自动重连自动下线...

市场分析

如题，我们首先分析市场上已有的同类产品：

• MySQL Route：是现在 MySQL 官方Oracle公司发布出来的一个中间件。
• Atlas：是由奇虎360公发的基于MySQL协议的数据库中间件产品，它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了若干Bug，并增加了很多功能特性。目前该产品在360内部得到了广泛应用。
• DBProxy：是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性。
• Cobar：是阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护 了，阿里也开发了tddl替代cobar。
• MyCAT：是社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去。

ProxySQL

今天给大家介绍的一款是 proxySQL ，ProxySQL是使用C++语言开发的，强悍性能截图：

其官网有个ProxySQL1.3.2与MariaDB MaxScale 2.0.3、HAProxy1.7、NGINX1.11.8、MySQL Router 2.0等 ProxySQL产品对比信息 ，可以看出其优秀值得使用。至少有 很多大公司巨兽已经在使用ProxySQL ，比如 亚马逊数据库集群自动切换 。

ProxySQL官网文档 也是很齐全， MySQL5.7结合ProxySQL 案例 讲述的也很清晰，现在先简单介绍下其特色功能点：

• 查询缓存
• 查询路由
• 故障转移
• 在线配置立刻生效无需重启
• 应用层代理
• 跨平台
• 高级拓展支持
• 防火墙

通过上述，我们可以看到ProxySQL可以做许多事情，已经不仅仅是纯粹的MySQL读写分离，其实我们通过后面所述结合业务发散，ProxySQL还可以支持以下高级功能：

• 读写分离
• 数据库集群、分片
• 分库分表
• 主从切换
• SQL审计
• 连接池 多路复用
• 负载均衡
• 查询重写
• 流量镜像
• 自动重连
• 自动下线
• 高可用
• .........

高可用架构

ProxySQL实战

准备

要实现MySQL的读写分离，首先要准备好MySQL主从复制架构（请SA协助或者自己百度），比如：

主（ 写 ）服务器： 10.0.16.1

一般都是两台从（ 读 ）服务器： 10.0.16.2 、 10.0. 17 .2

除主从复制架构外，还需要准备一台布置中间件的主机，这里是 10.0.16.88

一共 4 台机器，下面是我的主从配置，大家可以参考下：

10.0.16.1主服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log_bin=/var/lib/mysql/binlog
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log=/var/lib/mysql/slavelog

10.0.16.2从服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=21
relay_log=/var/lib/mysql/slavelog
set @@global.read_only=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1

10.0. 17 .2 从服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=22
relay_log=/var/lib/mysql/slavelog
set @@global.read_only=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1

安装

wget https://github.com/sysown/proxysql/releases/download/v1.4.12/proxysql_1.4.12-ubuntu16_amd64.deb
sudo dpkg -i proxysql_1.4.12-ubuntu16_amd64.deb

操作

版本：sudo proxysql --version
启动：sudo service proxysql start
暂停：sudo service proxysql stop
重启：sudo service proxysql restart
状态：sudo service proxysql status

概念

客户端：6033端口
管理端：6032端口

配置文件

//不推荐
/etc/proxysql.cnf

控制台

上述之所以不推荐，是因为我们可以通过ProxySQL控制台在线修改配置，无需重启，立即生效。

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

设置SQL日志记录【ProxySQL】

SET mysql-eventslog_filename='queries.log';

添加主从【ProxySQL】

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'10.0.16.1',3306,1,'主库');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.0.16.2',3306,9,'从库1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.0.17.2',3306,1,'从库2');

查看主从【ProxySQL】

SELECT * FROM mysql_servers;

创建主从账号【MySQL】

CREATE USER 'proxysql'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' WITH GRANT OPTION;

添加主从账号【ProxySQL】

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

查看主从账号【ProxySQL】

select * from mysql_users;

创建监控账号【MySQL】

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'%' WITH GRANT OPTION;

添加监控账号【ProxySQL】

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';

查看监控账号【ProxySQL】

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

检测监控【ProxySQL】

检测上述配置是否正确：connect_error为NULL则正确。

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

读写映射【ProxySQL】

这里配置主从自动切换：互为主从，自动切换，保证高可用。

insert into mysql_replication_hostgroups values(1,2,'高可用');

读写路由【ProxySQL】

读写分离规则，正则写法，也支持全匹配SQL，同时支持按照MySQL账号、库名、表名、客户IP等特征自动识别DB，从而达到分库分表、读写分离。ProxySQL的核心！

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(1,1,'^UPDATE',1,1,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(2,1,'^SELECT',2,1,1);

查看路由【ProxySQL】

检测上述配置的路由规则。

select * from mysql_query_rules\G;

配置生效【ProxySQL】

//加载到内存
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

//永久生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;

PHP、 JAVA 、CLI端使用

ProxySQL 6033端口正好是MySQL 3306端口的反转！

后端程序PDO直接连接此DSN，一切分发路由 由ProxySQL来代理，对于后端小白来说无需关心如此复杂的DBA幕后故事也可以实现MYSQL读写分离、分库分表高可用！

mysql -uproxysql -p123456 -h127.0.0.1 -P6033 --default-auth=mysql_native_password

验证【ProxySQL】

select * from stats_mysql_query_rules;
select * from stats_mysql_query_digest;
select * from stats_mysql_query_digest_reset;//清空日志
select * from stats_mysql_commands_counters where Total_cnt>0;

注意【ProxySQL】

//选表
show tables from stats;

ProxySQL教程