MySQL读写分离方案选型

1.读写分离方案

目前要实现mysql的主从读写分离，主要有以下几种方案：

1.1 修改源码

通过程序实现，网上很多现成的代码，比较复杂，如果添加从服务器要更改多台服务器的代码。

1.2 自研

自己开发接口实现，这种方案门槛高，开发成本高，不是一般的小公司能承担得起。

1.3 中间件

由于mysql-proxy的主从读写分离是通过lua脚本来实现，目前lua的脚本的开发跟不上节奏，而写没有完美的现成的脚本，因此导致用于生产环境的话风险比较大，据网上很多人说mysql-proxy的性能不高。阿里开源项目Amoeba具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库，并且安装配置非常简单。

2.读写分离中间件

2.1 活跃中的项目

2.1.1 [活跃]-MySQL-Router(官方)

官网地址: https://dev.mysql.com/doc/mysql-router/8.0/en/

由MySQL官方提供，MySQL Router用于取代MySQL Proxy，建议MySQL Router与应用程序部署在一台机器。应用程序像访问MySQL一样访问MySQL Proxy，由MySQL Proxy将数据转发给后端的MySQL。支持各种操作系统。

架构图

使用MySQL-Router实现应用程序的高可用: https://segmentfault.com/a/1190000011970688

2.1.2 [活跃]-MySQL-Cluster(官方)

MySQL Cluster 是MySQL 官方集群部署方案，它的历史较久。支持通过自动分片支持读写扩展，通过实时备份冗余数据，是可用性最高的方案，声称可做到99.999%的可用性。MySQL-Cluster 的稳定性也不是太好。

MySQL-Cluster

实战体验几种MySQLCluster方案: https://www.2cto.com/database/201504/387166.html

2.1.3 [活跃]-MaxScale(MariaDB)

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，使用XtraDB（英语：XtraDB）来代替MySQL的InnoDB。 MariaDB由MySQL的创始人Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自Michael Widenius的女儿Maria的名字。

MariaDB基于事务的Maria存储引擎，替换了MySQL的MyISAM存储引擎，它使用了Percona的 XtraDB，InnoDB的变体，分支的开发者希望提供访问即将到来的MySQL 5.4 InnoDB性能。这个版本还包括了 PrimeBase XT (PBXT) 和 FederatedX存储引擎。

MaxScale是mariadb开发的一个MySQL数据中间件，配置好MySQL的主从复制架构后，可以实现读写分离，把读操作分散到从服务器中，并且对多个服务器实现负载均衡。MaxScale是插件式结构，允许用户开发适合自己的插件。MaxScale 使用 C 语言开发，利用 Linux 下的异步 I/O 功能。使用 epoll 作为事件驱动框架。

MaxScale有两种方式实现读/写分离。一种是基于connect的，类似于Haproxy，不解析SQL语句，可以通过PHP Yii框架或Java Mybatis框架实现。在此方式中，用Maxscale做多台slave的负载均衡，并且支持主从同步延迟检测功能。

负载均衡

读写分离

官网地址: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-20/

利用Maxscale实现MySQL读写分离：https://www.cnblogs.com/darren-lee/p/7591416.html

2.1.4 [活跃]-Galera-Cluster(MariaDB)

Mariadb Galera Cluster是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能，并且稳定性要大大超过MySQL-Proxy，建议大家用来替代MySQL-Proxy，甚至MySQL-Cluster。

浅谈MariaDB Galera Cluster架构: https://www.cnblogs.com/vadim/p/6930566.html

2.2 停滞或未开源的项目

2.2.1 [停滞]-MySQL-Proxy(官方)

mysql官方提供的mysql中间件服务，上游可接入若干个mysql-client，后端可连接若干个mysql-server。它使用mysql协议，任何使用mysql-client的上游无需修改任何代码，即可迁移至mysql-proxy上。MySQL Proxy强大的一项功能是实现“读写分离（Read/Write Splitting）”。基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

MySQL-Proxy实现读写分离提高并发负载案例：http://blog.jobbole.com/94606/

MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开–keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；

2.2.2 [停滞]-Atlas(MySQL-Proxy增强版)

Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了大量bug，添加了很多功能特性。由于团队精力有限, 不再主要维护，很多Issue很难再继续满足。

项目地址: https://github.com/Qihoo360/Atlas

2.2.3 [停滞]-DBProxy(Atlas美团版)

项目地址: https://github.com/Meituan-Dianping/DBProxy

DBProxy是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性。最后提交时间2016年。

2.2.4 [未开源]-MTAtlas(Atlas美团版)

原美团DBA团队在开源Atlas基础上做的一系列升级改造。在读写分离、单库分表的基础上，完成了分库分表的功能开发。

2.2.5 [未开源]-ArkProxy(极数云舟)

极数云舟(http://www.cloud-ark.com) 公司开发的数据库中间件，核心特性包括:透明读写分离,兼容性,友好性,权重分发,消息压缩,从库接入,用户连接数限制及统计,丰富的参数配置,连接池,失败重试。

ArkProxy

2.2.6 [停滞]-Amoeba(阿里)

Amoeba是淘宝的得力作品。支持读写分离，表和库级别的读写分离，数据库水平分割，垂直分割，还有集群。mysql-proxy 只是轻量级的读写分离程序，虽然C写的，但是驱动是需要lua的脚本跑，而且在高并发下经常挂掉。程序还忽略了一些字符设定，如果数据库不是同一编码还会出现乱码,amoeba就不存在。

Amoeba致力于MySQL的分布式数据库前端代理层，它主要在应用层访问MySQL的时候充当SQL路由功能，专注于分布式数据库代理层（Database Proxy）开发。座落与 Client、DB Server(s)之间,对客户端透明。具有负载均衡、高可用性、SQL 过滤、读写分离、可路由相关的到目标数据库、可并发请求多台数据库合并结果。

Amoeba作者陈思儒离职去盛大之后，阿里巴巴内部考虑到Amoeba的稳定性、性能和功能支持，以及其他因素，重新设立了一个项目组并且更换名称为Cobar，现在Cobar已经开源了，详情见： http://www.mysqlops.com/2012/06/19/cobar-alibaba.html

项目地址：https://sourceforge.net/projects/amoeba/

3.数据同步工具

3.1 [离线]-DataX

阿里的Datax是比较优秀的产品，基于python，提供各种数据库的读写插件，多线程执行，使用起来也很简单，定义好配置json文件执行脚本就可以了，非常适合离线数据，增量数据可以使用一些编码的方式实现，但是也仅仅针对insert数据比较有效，update数据就不适合。

github地址：https://github.com/alibaba/DataX

3.2 [离线]-Sqoop

Sqoop(发音：skup)是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql…)间进行数据的传递，可以将一个关系型数据库（例如： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。Sqoop在数据库的支持的丰富性上不如DataX，但是如果你用hadoop，用sqoop是更好的选择，因为做Apache的顶级项目，他背后的支持远远比阿里一家公司靠谱的多。

官网：http://sqoop.apache.org/

3.3 [离线]-Kettle

Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行，数据抽取高效稳定。Kettle作为传统ETL工具，目前也都已经有了nosql数据库的支持，而且kettle还有图形界面可以用，使用起来简单多了。而且本来就是专门做ETL的，是Pentaho指定的ETL组件，对于数据清洗等处理数据的环节支持更好。但是数据效率一般，而且在生产环境也很少弄台windows机器，适合小项目，数据量比较小的同步。

Kettle 中文名称叫水壶，该项目的主程序员MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。

官网：https://community.hitachivantara.com/docs/DOC-1009855

3.4 [实时]-Canal

Canal是基于mysql的binlog进行数据同步的中间件。简单来说，Canal 会将自己伪装成 MySQL 从节点（Slave），并从主节点（Master）获取 Binlog，解析和贮存后供下游消费端使用。Canal 包含两个组成部分：服务端和客户端。服务端负责连接至不同的 MySQL 实例，并为每个实例维护一个事件消息队列；客户端则可以订阅这些队列中的数据变更事件，处理并存储到数据仓库中。

使用的话，安装好canal，配置好数据库参数，再编写一个客户端消费canal传过来的数据就可以了。

github地址：https://github.com/alibaba/canal

3.5 [实时]-Otter

otter是在canal基础上又重新实现了可配置的消费者，使用otter的话，刚才说过的消费者就不需要写了，而otter提供了一个web界面，可以自定义同步任务及map表。非常适合mysql库之间的同步。而且通过retl_buff表的监控，也可以实现一些全量数据的同步。

但是otter也有一些不好的地方，比如界面上的参数并不是所有的都有用，文档写的一般，不是很清晰。但是想想省了好多事，还是非常好的一款中间件。

github地址：https://github.com/alibaba/otter

3.6 [实时]-Databus

Databus是LinkedIn开源的一款低延迟的分布式数据库同步系统（a source-agnostic distributed change data capture system），它提供可靠的数据捕获、流转和数据处理功能。Databus将数据库作为唯一真实数据来源，并将变更从事务或提交日志中提取出来，然后通知相关的衍生数据库或缓存。 Databus传输层端到端的延迟是微秒级别的，这意味着每台服务器每秒可以处理数千次数据吞吐变更事件,同时还支持无限回溯能力和丰富的变更订阅功能，

项目地址: https://github.com/linkedin/databus

3.7 [实时]-MySQL主从同步

1.asynchronous 异步复制

原理：在异步复制中，master写数据到binlog且sync，无需等待slave。slave request binlog后写入relay-log并flush disk。

优点：复制的性能最好

缺点：master挂掉后，slave可能会丢失事务

代表：MySQL原生的复制

异步复制

2.fully synchronous 全同步复制

原理：在全同步复制中，master写数据到binlog且sync，所有slave request binlog后写入relay-log并flush disk，并且回放完日志再commit。

优点：数据不会丢失

缺点：会阻塞master session，性能太差，非常依赖网络

代表：MySQL-Cluster

全同步复制

3.semi synchronous 半同步复制

原理: 在半同步复制中，master写数据到binlog且sync，且commit，然后一直等待ACK。当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack（不需要回放完日志）

优点：会有数据丢失风险（低）

缺点：会阻塞master session，性能差，非常依赖网络

代表：after commit, 原生的半同步

重点：由于master是在三段提交的最后commit阶段完成后才等待，所以master的其他session是可以看到这个提交事务的，所以这时候master上的数据和slave不一致，master crash后，slave数据丢失。

半同步复制

4.lossless replication 无损复制

原理: 在半同步复制中，master写数据到binlog且sync，然后一直等待ACK. 当至少一个slave request bilog后写入到relay-log并flush disk，就返回ack（不需要回放完日志）

优点：数据零丢失（前提是让其一直是lossless replication），性能好

缺点：会阻塞master session，非常依赖网络

代表：after sync, 原生的半同步

重点：由于master是在三段提交的第二阶段sync binlog完成后才等待, 所以master的其他session是看不见这个提交事务的，所以这时候master上的数据和slave一致，master crash后，slave没有丢失数据。

无损复制

4.延迟同步如何解决？

4.1 主从同步的原理

主库针对写操作，顺序写binlog，从库单线程去主库顺序读”写操作的binlog”，从库取到binlog在本地原样执行（随机写），来保证主从数据逻辑上一致。mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。master可以并发，Slave_SQL_Running线程不可以并发。

4.2 延迟怎么产生的

当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高。

次要原因：读写binlog带来的性能影响，网络传输延迟。

4.3 同步延迟解决方案

一、架构方面

1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

4.不同业务的mysql物理上放在不同机器，分散压力。

5.使用比主库更好的硬件设备作为slave

总结，mysql压力小，延迟自然会变小。

二、硬件方面

1.采用好服务器，比如4u比2u性能明显好，2u比1u性能明显好。

2.存储用ssd或者盘阵或者san，提升随机写的性能。

3.主从间保证处在同一个交换机下面，并且是万兆环境。

总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。

三、主从同步加速

1、sync_binlog在slave端设置为0

2、–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

3、直接禁用slave端的binlog

4、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

四、文件系统属性优化

master端修改linux、Unix文件系统中文件的etime属性，由于每当读文件时OS都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响I/O性能。可以通过设置文件系统的mount属性，组织操作系统写atime信息，在linux上的操作为：
打开/etc/fstab，加上noatime参数 /dev/sdb1 /data reiserfs noatime 1 2 然后重新mount文件系统 #mount -oremount /data