MySQL复制与读写分离

MySQL的复制架构就是以一台服务器充当主服务器，而一台或多台其它服务器充当从服务器。从服务器基于主服务器的二进制日志跟踪所有对数据库的更改操作，以而在从服务器上的数据拷贝执行相同的更改操作。使用MySQL复制架构可以通过在主服务器和从服务器之间分离客户处理负荷，从而得到更好的客户响应时间。使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。因而，MySQL复制架构主要应用于:分布数据、负载平衡(load balancing)、备份或其他高可用性(high availability)和容错的架构中。

大体上，复制有3个步骤：

1．主服务器把数据更改记录到二进制日志中（二进制日志事件Binary Log Events）

2．从服务器把主服务器的二进制日志事件拷贝到自己的中继日志（Relay Log）中。

3．从服务器重放中继日志中的事件，把更改应用到自己的数据上。

该过程的第一部分就是主服务器记录二进制日志。在每个事务更新数据完成之前，主服务器在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，主服务器通知存储引擎提交事务。

下一步就是从服务器将主服务器的binary log拷贝到它自己的中继日志。首先，从服务器开始一个工作线程——I/O线程。I/O线程在主服务器上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从主服务器的二进制日志中读取事件，如果已经跟上主服务器，它会睡眠并等待主服务器产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread处理该过程的最后一步。SQL线程从中继日志读取事件，更新slave的数据，使其与主服务器中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

可以使用“show slave status\G”从下面的变量中获取当前IO线程所状态:

•Master_Log_File – 最近从主服务器上复制过来的文件 (多数情况下它和主服务器最近写入的二进制日志文件相同)

•Read_Master_Log_Pos – 主服务器上二进制日志复制到从服务器上中继日志的位置.

可以从下面的变量中获取当前SQL线程的状态:

•Relay_Master_Log_File – 来自主服务器的二进制日志, 供SQL线程处理 (实际SQL线程是工作在中继日志上)

•Exec_Master_Log_Pos – SQL线程执行的二进制日志的位置.

•Seconds_behind_master - 显示从服务器的延迟

此外，在主服务器中也有一个工作线程：和其它MySQL的连接一样，从服务器在主服务器中打开一个连接也会使得主服务器开始一个线程。复制过程有一个很重要的限制——复制在从服务器上是串行化的，也就是说主服务器上的并行更新操作不能在从服务器上并行操作。

我们可以通过下列命令来了解主服务器的状态：

SHOW MASTER STATUS：命令查看主服务器当前二进制日志的位置和配置。

SHOW MASTER LOGS：查询主服务器哪个二进制日志在磁盘上。

SHOW BINLOG EVENTS：在二进制日志中查看复制事件。

 

下图是网上看到的一张图，它比较形象地显示了这个过程：

MySQL常见的复制拓扑结构如下：

1）主服务器和多个从服务器

这种配置在写较少，读取较多的时候最有用，可以把读取分摊到任意多的从服务器上，如：为不同的角色使用不同的从服务器；把一个从服务器当成待命主服务器，除去复制就没有其他数据流量；把远程的数据中心作为从服务器，以备灾恢复使用；延迟一个或多个从服务器，以备灾恢复；为备份、培训、开发或测试使用从服务器。

需要注意的是：当slave增加到一定数量时，slave对master的负载以及网络带宽都会成为一个严重的问题。 

2）主动-主动模式下的主-主复制

主-主复制也叫双主服务器复制或双向复制，包含两个服务器，每一个服务器都即是主服务器也是对方的从服务器。

这种配置最大的总是是如何解决冲突。问题通常发生在两个服务器同时更改了同一行数据，或同时向一个有AUTO_INCREMENT列的表中插入了数据。可能可以通过分区数据和权限避免一些问题。

3）主动-被动模式下的主-主复制

这种模式是由主动-主动模式下的主-主复制结构变化而来的，避免了M-M的缺点，是一种具有容错和高可用性的系统。这种配置与主动-主动模式下的主-主复制最大的不同在于其中一个服务只能进行只读操作。

这种配置可以轻易地来回交换主动服务器和被动服务器的角色，这时故障转移和故障恢复就容易了，它可以在不关闭服务器的情况下执行维护、优化表、升级操作系统（或者应用程序，硬件）及其他任务。

可以使用MySQL主-主复制管理工具(http://code.google.com/p/mysql-master-master)来创建和管理这种拓扑结构。它使很多复杂的动作变得自动化了，如恢复和重新同步复制，创建新的从服务器等。

4）有从服务器的主-主复制

这种配置是给主服务器对添加从服务器，这种配置的好处是额外的冗余度，对于位于不同地点的复制拓扑，它在每个站点都能消除一个故障点，同时还可以将读密集型的请求放到从服务器上。

还有一些不常见的结构：如环、树或金字塔结构等。

 

前面提到可以采用MySQL复制架构在主服务器和从服务器之间分离负载，最典型的案例就是读写分离。通常我们将读操作分流到从服务器上，而将写操作集中到主服务器上。在构建基于MySQL复制基础上的读写分离架构时，比较理想的MySQL复制拓扑结构是带从服务器的主动-被动模式下的主-主复制。这种拓扑结构还可以采用MySQL主-主复制管理工具MMM进行管理(http://code.google.com/p/mysql-master-master)。

读写分离的方案有多种，在Spring+Hibernate的架构下，为读写操作分别绑定到不同的数据库连接上，关键点在于配置为Spring+Hibernate配置多数据库，可以在网上找到许多关于Spring+Hibernate多数据库的解决方案。这种方案比较简单，容易实现，但不太利于扩展。单纯将读写操作分离到不同的服务器上，将会遇上这样的问题，用户提交更改记录后再次查询会出现显示的仍为原更改前的记录。这主要是由于复制延时造成的，针对这种问题，常见的策略有：

A．以查询为基础的分离：把所有写操作和一些不接受旧数据的读操作放在主服务器上，而其他读操作放在从服务器上。

B．旧数据分离：需要去检查从服务器的迟滞程度，然后决定这些数据对读操作是不是够“陈旧”。许多报表类的应用可以使用这个策略。

C．以session为基础的分离：如果用户对数据有过更新，就在session层设置一个标志，这样当用户执行读查询时，系统就会到主服务器上读取数据。

D．以版本为基础的分离：根据从服务器上读取对象的版本号和时间戳来判断这些数据是否“陈旧”。

E．全局版本/会话分离：以版本为基础和以会话为基础的变种方法。

在MySQL的复制过程中，总会出现一些不同的问题，比如：主服务器意外关闭或从服务器意外关闭、主服务器上的二进制日志损坏或从服务器上中继日志损坏、二进制日志和InnoDB事务日志不同步、没有磁盘空间、复制延时变大等等。不同的问题的解决方式不同，有时也许会为不同的用途增加从服务器，或发生故障时切换主服务器或提升一从服务器为主服务器等。

MMM工具可以帮忙我们解决其中一些问题。但我们仍需仔细考虑在从服务器上负载均衡，如上述集成在应用中的读写分离方案还需要再增加实现相应的功能，并随着应用的增长也会变得越来越复杂。我们也可以采用另外的方案，使用中间件来实现相应的读写分离。一般的是中间件方案都能比较方便地增加实现负载均衡和故障转移(failover)的功能。同时我们仍可以在应用中集成故障转移的功能，根据数据库接入层返回来的状态和应用中设定的阀值要求，在应用中发起故障转移。

很多负载均衡的方案使用HAProxy，HAProxy提供高可用性、负载均衡以及基于TCP和HTTP应用的代理。在百度百科中提到。(http://baike.baidu.com/view/2480120.htm)，HAProxy实现了一种事件驱动, 单一进程模型，此模型支持非常大的并发连接数。多进程或多线程模型受内存限制 、系统调度器限制以及无处不在的锁限制，很少能处理数千并发连接。事件驱动模型因为在有更好的资源和时间管理的用户端(User-Space) 实现所有这些任务，所以没有这些问题。此模型的弊端是，在多核系统上，这些程序通常扩展性较差。这就是为什么他们必须进行优化以使每个CPU时间片(Cycle)做更多的工作。HAProxy可与Keepalived一起来提供高可用性、负载均衡。HAProxy官方网址：http://haproxy.1wt.eu/, Keepalived官方网址：http://www.keepalived.org/

HAProxy是一种多用途的负载均衡器，并不是为MySQL专门设计的，它很难探测到MySQL的实际负荷，同时也易受应用中的连接池和持久性连接的影响（如增加从服务器时，由于连接池保存现有连接而不生成新的连接时，新增加的从服务器很难得到应用）。

MySQL Proxy是另外一种可选用的负载均衡中间件，可以实现主数据库处理事务性查询，从数据库处理SELECT查询，而数据库复制将事务性查询导致的变更同步到集群中的从数据库。关于这种使用方法以及连接池问题可以参考<MySQL Proxy learns R/W Splitting>一文。MySQL-Proxy是处在MySQL数据库客户和服务端之间的中间件，通过截断、改变并转发客户端的查询请求等然后代理和后端数据库之间的通信来实现其主要功能。主要的工作过程是：

(1) MySQL Proxy以服务器的身份接受客户端请求，根据Spy的配置对这些客户端的请求进行分析处理
(2) 以客户端的身份将客户端的请求转发给相应的后端数据库服务器，然后再接受服务器的信息，返回给客户端

MySQL Proxy是基于MySQL Protocol协议通过lua脚本来控制连接转发的机制，可以用来分析、监控和变换通信数据。MySQL Proxy有着非常广泛的使用场景：查询分析和日志、负载平衡和故障转移处理、SQL宏（SQL macros）、查询重写（query rewriting）、执行shell命令、实现数据分布策略等。

 

参考：

<<高性能MySQL >>

第6章：MySQL中的复制 http://dev.mysql.com/doc/refman/5.1/zh/replication.html 

                              http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-switch.html

http://www.severalnines.com/resources/clustercontrol-mysql-haproxy-load-balancing-tutorial#failover

http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/

MySQL 复制：GTIDS实现自动故障恢复 http://www.oschina.net/translate/mysql_replication_self_healing_recovery

MySQL高可用方案：基于MHA实现的自动故障转移群集 http://www.2cto.com/database/201304/205011.html

MySQL Master-Slave架构下使用MMM的必要性 http://blog.csdn.net/starxu85/article/details/2693199

MySQL Proxy learns R/W Splitting  http://jan.kneschke.de/2007/8/1/mysql-proxy-learns-r-w-splitting/