1、MySQL主从复制的常见拓扑原理分析以及如何提高主从复制的效率总结讲解MySQL主从复制的常见拓扑、原理分析以及如何提高主从复制的效率总结一、主从复制搭建方法参考1、MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解请参考： 2、使用mysqlreplicate命令快速搭建 Mysql 主从复制： 二、Mysql 主从复制的常用拓扑结构2.1、一主一从这里写图片描述是最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。2.2、一主多从一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。还可以对多台 Slave 进行分工，服
2、务于不同的系统，例如一部分 Slave 负责网站前台的读请求，另一部分 Slave 负责后台统计系统的请求。因为不同系统的查询需求不同，对 Slave 分工后，可以创建不同的索引，使其更好的服务于目标系统。2.3、双主复制Master 存在下线的可能，例如故障或者维护，需要把 Slave 切换为 Master。在原来的 Master 恢复可用后，由于其数据已经不是最新的了，不能再做主，需要做为 Slave 添加进来。那么就需要对其重新搭建复制环境，需要耗费一定的工作量。双主结构就是用来解决这个问题的，互相将对方作为自己的 Master，自己作为对方的 Slave 来进行复制，但对外来讲，还是一
3、个主和一个从。当 主Master 下线时，备Master 切换为 主Master，当原来的 主Master 上线后，因为他记录了自己当前复制到对方的什么位置了，就会自动从之前的位置开始重新复制，不需要人为地干预，大大提升了效率。2.4、级联复制当直接从属于 Master 的 Slave 过多时，连到 Master 的 Slave IO 线程就比较多，对 Master 的压力是很大的。 级联结构就是通过减少直接从属于 Master 的 Slave 数量，减轻 Master 的压力，分散复制请求，从而提高整体的复制效率。2.5、双主级联级联复制结构解决了 Slave 过多导致的瓶颈问题，但还是有单
4、主结构中切换主时的维护问题。那么为了解决这个问题，就可以加入上面的双主结构。在必要时，可以再对 Slaves 进行分级。Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据自己的具体情况，并评估好目标结构的延时对系统的影响。三、Mysql 主从复制过程及原理3.1、Binary Log 简单介绍因为Binlog dump 线程操作的文件是bin-log 日志文件，并且实现主从复制在主服务器上主要依靠bin-log日志文件，所以我们简单介绍一下bin-log日志文件。3.2、原理MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方
5、案，特点是异步复制，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。与之对应的是另一个同步技术是MySQL Cluster，但因为MySQL Cluster配置比较复杂，所以使用者较少。MySQL Replication 就是从服务器拉取主服务器上的 二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。MySQL的Replication是一个异步复制的过程（mysql5.1.7以上版本分为异步复制和半同步两种模式），它是从一个Mysql instance（instance英文为实例）(我们称之为Master)复
6、制到另一个Mysql instance(我们称之slave)。3.3、三个线程在master与slave之间实现整个复制过程主要由三个线程来完成：1、Slave SQL thread线程，在slave端2、Slave I/O thread线程，在slave端3、Binlog dump thread线程(也可称为IO线程)，在master端123注意：如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程。要实现MySQL的Replication，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则
7、无法实现mysql的主从复制。因为mysql的整个主从复制过程实际上就是：slave端从master端获取binlog日志，然后再在自己身上完全顺序的执行该日志中所记录的各种SQL操作。有关具体如何开启mysql的binlog日志功能，请大家自己在网上搜。3.4、主从复制流程MySQL主从复制的基本交互过程，如下：1、slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。2、master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定bin
8、log日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。3、slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存slave端）文件中，以便在下一次读取的时候能够清楚的告诉master“我
9、需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。4、slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺丰依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据完全一样的。以上mysql主从复制交互过程比较拗口，理解起来也比较麻烦，我简化了该交互过程。如下：1、master在执行sql之后，记录二进制log文件（bin-log）。2、slave连接master，并从mas
10、ter获取binlog，存于本地relay-log中，然后从上次记住的位置起执行SQL语句，一旦遇到错误则停止同步。从以上mysql的Replication原理可以看出：主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间主从数据不一致的情况。如果主从的网络断开，则从库会在网络恢复正常后，批量进行同步。如果对从库进行修改数据，那么如果此时从库正在在执行主库的bin-log时，则会出现错误而停止同步，这个是很危险的操作。所以一般情况下，我们要非常小心的修改从库上的数据。一个衍生的配置是双主、互为主从配置，只要双方的修改不冲突，则可以工作良好。如果需要多主库的话，可以用环形配置，这样任意一个节
11、点的修改都可以同步到所有节点。3.5、整体过程就是：MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定。MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器
12、创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程，即I/O线程，将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。四、MySQL支持的复制类型及其优缺点bin-lo
13、g 日志文件有两种格式，一种是Statement-Based，另一种是Row-Based。（）：基于语句的复制（Statement-Based）： 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。 （）：基于行的复制（Row-Based）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持 （）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。4.1、Statement-Based优点和缺点分析优点bin
14、-log日志包含了描述数据库操作的事件，但是这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录，并且它记录的是语句，所以相对于Row-Based来说这样会占用更少的存储空间。因为bin-log日志文件记录了所有的改变数据库的语句，所以此文件可以作为以后的数据库的审核依据缺点不安全，并不是所有的改变数据的语句都会被记录复制。任何的非确定性的行为都是很难被记录复制的。例如：对于delete 或者update语句，如果使用了limit但是并没有 order by ，这就属于非确定性的
15、语句，就不会被记录对于没有索引条件的update语句，必须锁定更多的数据，降低了数据库的性能。insertselect 语句同样也需要锁定大量的数据，对数据库的性能有所损耗。获取更详细的信息可以参考官方文档Statement-Based的优点和缺点4.2、Row-Based优点和缺点分析优点所有的改变都会被复制，这是最安全的复制方式对于 update、insertselect等语句锁定更少的行此种方式和大多数的数据库系统一样，所以了解其他的系统的人员可以很容易的转到mysql缺点使用不方便，我们不能通过bin-log日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到
16、什么数据改变了因为记录的是数据，所以说bin-log日志文件占用的存储空间要比Statement-based大。对于数据量大的操作其花费的时间有更长获取更详细的信息可以参考官方文档Row-Based的优点和缺点bin-log日志文件默认的格式为Statement-Based，如果想改变其格式在开启服务的时候使用binlog-format选项，其具体命令如下mysqld_safe user=msyql binlog-format=格式 &1四、主服务器流程分析4.1、主服务器线程 Binlog dump threadBinlog dump 线程是当有从服务器连接的时候由主服务器创建，其大致工作过
17、程经历如下几个阶段：首先bin-log日志文件加锁，然后读取更新的操作，读取完毕以后将锁释放掉，最后将读取的记录发送给从服务器。我们可以使用如下的命令来查看该线程的信息mysql SHOW PROCESSLISTG1以我的系统为例，因为我这系统中是一台主服务器和两台从服务器，所以会列出两条Binlog dump线程的信息* 1. row * Id: 2 User: repuser Host: 192.168.144.131:41544 db: NULLCommand: Binlog Dump Time: 54 State: Master has sent all binlog to slave
18、; waiting for binlog to be updated Info: NULL* 2. row * Id: 3 User: repuser Host: 192.168.144.132:40888 db: NULLCommand: Binlog Dump Time: 31 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL上述字段中的state字段会有以下几种状态：1. Sending binlog event to slave表示Binlog dump 线程已
19、经读取完binlog日志中更新的event，现在正在发送给从服务器2. Finished reading one binlog; switching to next binlog表示Binlog dump 线程已经读取完一个binlog日志，现在正在打开下一个binlog日志读取来发送给从服务器3. Master has sent all binlog to slave; waiting for binlog to be updated这就是上面我们看到的state的值，表示Binlog dump 线程已经读取完所有的binlog日志文件，并且将其发送给了从服务器。现在处于空闲状态，正在等待读
20、取有新的操作的binlog日志文件4. Waiting to finalize termination这个状态持续的很短暂，我们几乎看不到。当线程停止的时候显示此状态上述几个状态就是一次主从复制过程中Binlog dump 线程所经历的状态，如果我们是在测试的环境中，上述1、2、4状态我们几乎是看不到的，因为它执行的很快。在主从系统中主服务器上的一个主要的文件就是bin-log日志，该线程操作的文件也是此日志文件，因此这是我们需要在配置文件f 中打开bin-log日志的原因，使用此文件来记录我们的更新操作。mysqldlog-bin = mysql-binserver-id = 1123还有一
21、点需要注意，在上面已经说过，但是在这里觉得有必要再重复一遍，就是有多少个从服务器连接主服务器上就有多少个Binlog dump 线程。bin-log日志文件管理对于bin-log日志文件，其默认的名称为 mysql-bin.xxxxxx。而且还有一个索引文件mysql-bin.index，其中记录了当前所有的bin-log日志文件。对于新的主服务器只有一个bin-log日志文件 mysql-bin.000001。此时所有的操作都有这个文件来记录，如果我们想更换bin-log日志文件，可以使用如下命令Mysqlflush logs;12此时会创建一个mysql-bin.000002文件来记录以后
22、的操作。除了使用上述命令以外，当bin-log日志文件达到其最大值的时候也会产生新的bin-log日志文件其文件最大值和文件名包括索引文件的名称可以使用 max_binlog_size、log-bin和log-bin-index 选项来改变，具体命令如下mysqld_safe user=msyql max_binlog_size=文件长度 log-bin=新的日志文件名称 log-bin-index=新索引文件名 &1对于主服务器来说，总起来一句话：主服务器针对于每一个从服务器都创建一个Binlog dump线程，用来读取bin-log日志中更新的操作将其发送给从服务器，发送完毕以后继续等待b
23、in-log日志是否有更新。五、从服务器流程分析在主服务器探究这篇文章中我们提到过，在一次主从复制过程中需要用到三个线程：Binlog dump 线程、Slave I/O 线程和Slave SQL线程，其中Binlog dump 线程在主服务器上面，剩下的两个线程是在从服务器上面工作的。这两个线程在从服务器上面的工作流程如下图所示：对于这两个线程随着从服务器开启slave而产生mysql START SLVAE;然后使用Mysql SHOW SLAVE STATUSG1查看这两个线程情况Master_Log_File: mysql-bin.000003Read_Master_Log_Pos:
24、1264Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 878Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: Yes 上面结果中的 Slave_IO_Running:Yes和Slave_SQL_Running:Yes表示这两个线程正在运行。然后我们在从服务器上面使用命令mysql SHOW PROCESSLIATG1显示如下结果（记为 结果一）* 1. row * Id: 22 User: system user Hos
25、t: db: NULLCommand: Connect Time: 4 State: Waiting for master to send event Info: NULL* 2. row * Id: 23 User: system user Host: db: NULLCommand: Connect Time: 4 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL从State信息可以看出Id 22是I/O线程，正在等待主服务器发送更新的内容；Id 23是
26、Slave SQL线程，已经读取了relay log 文件中所有更新的内容，正在等待I/O线程更新该文件。使用命令停止slave机制mysql STOP SLVAE;1然后我们再次查看会发现结果如下Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 1264Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 878Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: NoSlave_SQL_Running: No说明这
27、两个线程已经停止了运行。此时再次使用 SHOW PROCESSLISTG命令，则没有结果显示5.1、Slave I/O线程Slave I/O 线程去连接主服务器的Binlog dump 线程并要求其发送binlog日志中记录的更新的操作，然后它将Binlog dump 线程发送的数据拷贝到从服务器上（也就是本地）的文件relay log中。当然要查看此线程是否运行，除了上面介绍的方法，还可以使用mysql SHOW SLAVE LIKE Slave_running;1这时如果出现下面的结果说明该线程正在运行+-+-+| Variable_name | Value |+-+-+| Slave_r
28、unning | ON |+-+-+在上述结果一中我们可以看到1.row即是Slave I/O线程的信息，其State: Waiting for master to send event 表示正在等待主服务器发送内容。当然State不止这一个值，它还有其它的值，下面列出了State的所有的值1. Waiting for master update在连接到主服务器之前的初始状态2. Connecting to master该线程正在连接主服务器，当然如果我们的网络环境优异的话，此状态我们几乎是看不到的3. Checking master version这个状态发生的时间也非常短暂，该状态在该线程
29、和主服务器建立连接之后发生。4. Registering slave on master在主服务器上面注册从服务器，每当有新的从服务器连接进来以后都要在主服务器上面进行注册5. Requesting binlog dump向主服务器请求binlog日志的拷贝6. Waiting to nnect after a failed binlog dump request如果5中失败，则该线程进入睡眠状态，此时State就是这个值，等待着去定期重新连接主服务器，那这个周期的大小可以通过CHANGE MASTER TO 来指定7. Reconnecting after a failed binlog d
30、ump request去重新连接主服务器8. Waiting for master to send event此值就是我们上述结果所显示的，正常情况下我们查看的时候一般都是这个值。其具体表示是这个线程已经和主服务器建立了连接，正在等待主服务器上的binlog 有更新，如果主服务器的Binlog dump线程一直是空闲的状态的话，那此线程会等待很长一段时间。当然也不是一直等待下去，如果时间达到了slave_net_timeout规定的时间，会发生等待超时的情况，在这种情况下I/O线程会重新去连接主服务器9. Queueing master event to the relay log该线程已经读取了Binlog dump
