MySQL数据库：主从复制Replication

一、主从复制架构的搭建：

1、MySQL5.6 数据库主从（Master/Slave）同步安装与配置详解请参考： http://blog.csdn.net/xlgen157387/article/details/51331244#comments

2、使用mysqlreplicate命令快速搭建 Mysql 主从复制： http://blog.csdn.net/xlgen157387/article/details/52452394

二、MySQL主从复制过程及原理：

1、MySQL主从复制的原理：

Slave从Master获取binlog二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成：

• （1）Slave SQL thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于slave端
• （2）Slave I/O thread线程：读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中，位于slave端：
• （3）Binlog dump thread线程(也称为IO线程)：将bin-log二进制日志中的内容发送到slave服务器，位于master端

注意：如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程；要实现MySQL的主从复制，首先必须打开master端的binlog (mysql-bin.xxxxxx)日志功能，否则无法实现mysql的主从复制。

2、主从复制流程：

（1）master服务器在执行SQL语句之后，记录在二进制bin-log文件中；

（2）slave端的IO线程连接上master端，并请求从指定bin log日志文件的指定pos节点位置（或者从最开始的日志）开始复制之后的日志内容。

（3）master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。

（4）slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relay log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中（该文件存slave端），以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步；

（5）slave端的SQL线程在检测到relay log文件中新增内容后，就马上解析该relay log文件中的内容，然后还原成在master端真实执行的那些SQL语句，再按顺序依次执行这些SQL语句，从而到达master端和slave端的数据一致性；

3、主从复制的好处：

（1）读写分离，通过动态增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上执行读功能。

（2）提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。

（3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

三、MySQL支持的复制类型及其优缺点：

bin-log日志文件有两种格式，一种是Statement-Based（基于语句的复制），另一种是Row-Based（基于行的复制）。默认格式为Statement-Based，如果想改变其格式在开启服务的时候使用 -binlog-format 选项，其具体命令如下：

mysqld_safe –user=msyql –binlog-format=格式 &

1、基于语句的复制（Statement-Based）：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。

（1）优点：

• ① 因为记录的SQL语句，所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
• ② binlog日志文件记录了所有的改变数据库的语句，所以此文件可以作为数据库的审核依据。

（2）缺点：

• ① 不安全，不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如：对于 delete 或者 update 语句，如果使用了 limit 但是并没有 order by ，这就属于非确定性的语句，就不会被记录。
• ② 对于没有索引条件的update，insert……select 语句，必须锁定更多的数据，降低了数据库的性能。

2、基于行的复制（Row-Based）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从mysql5.0开始支持；

（1）优点：

• ① 所有的改变都会被复制，这是最安全的复制方式；
• ② 对于 update、insert……select等语句锁定更少的行；

（2）缺点：

• ① 不能通过binlog日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到什么数据改变。
• ② 因为记录的是数据，所以说binlog日志文件占用的存储空间要比Statement-based大。
• ③ 对于数据量大的操作其花费的时间有更长。

3、混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

四、主服务器流程分析：

1、主服务器线程 Binlog dump thread：

Binlog dump 线程是当有从服务器连接的时候由主服务器创建，其大致工作过程经历如下几个阶段：

这里写图片描述

首先bin-log文件加锁，然后读取更新的操作，读取完毕后将锁释放，最后将读取的记录发送给从服务器。

我们可以使用如下的命令来查看该线程的信息

mysql> SHOW PROCESSLIST

以我的系统为例，因为我这系统中是一台主服务器和两台从服务器，所以会列出两条Binlog dump线程的信息：

*************************** 1. row *************************** Id: 2 User: repuser Host: 192.168.144.131:41544 db: NULL Command: Binlog Dump Time: 54 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 3 User: repuser Host: 192.168.144.132:40888 db: NULL Command: 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 线程已经读取完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日志文件，并且将其发送给了从服务器。现在处于空闲状态，正在等待读取有新的操作的binlog日志文件 4. Waiting to finalize termination 这个状态持续的很短暂，我们几乎看不到。当线程停止的时候显示此状态

上述几个状态就是一次主从复制过程中Binlog dump 线程所经历的状态，如果我们是在测试的环境中，上述1、2、4状态我们几乎是看不到的，因为它执行的很快。

在主从系统中主服务器上的一个主要的文件就是bin-log日志，该线程操作的文件也是此日志文件，因此这是我们需要在配置文件my.cnf 中打开bin-log日志的原因，使用此文件来记录我们的更新操作。

[mysqld] log-bin = mysql-bin server-id = 1

还有一点需要注意，在上面已经说过，但是在这里觉得有必要再重复一遍，就是有多少个从服务器连接主服务器上就有多少个Binlog dump 线程。

2、bin-log日志文件管理：

对于bin-log日志文件，其默认的名称为 mysql-bin.xxxxxx。而且还有一个索引文件mysql-bin.index，其中记录了当前所有的bin-log日志文件。

对于新的主服务器只有一个bin-log日志文件 mysql-bin.000001。此时所有的操作都有这个文件来记录，如果我们想更换bin-log日志文件，可以使用如下命令：

Mysql>flush logs;

此时会创建一个mysql-bin.000002文件来记录以后的操作。除了使用上述命令以外，当bin-log日志文件达到其最大值的时候也会产生新的bin-log日志文件。

其文件最大值和文件名包括索引文件的名称可以使用 –max_binlog_size、–log-bin和—log-bin-index 选项来改变，具体命令如下

mysqld_safe –user=msyql –max_binlog_size=文件长度 –log-bin=新的日志文件名称 –log-bin-index=新索引文件名 &

对于主服务器来说，总起来一句话：主服务器针对于每一个从服务器都创建一个Binlog dump线程，用来读取bin-log日志中更新的操作将其发送给从服务器，发送完毕以后继续等待bin-log日志是否有更新。

五、从服务器流程分析：

在主服务器探究这篇文章中我们提到过，在一次主从复制过程中需要用到三个线程：Binlog dump 线程、Slave I/O 线程和Slave SQL线程，其中Binlog dump 线程在主服务器上面，剩下的两个线程是在从服务器上面工作的。

这两个线程在从服务器上面的工作流程如下图所示：

这里写图片描述

对于这两个线程随着从服务器开启slave而产生

mysql> START SLVAE;

然后使用：

Mysql> SHOW SLAVE STATUS

查看这两个线程的情况：

…… Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1264 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 878 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ……

上面结果中的 Slave_IO_Running:Yes 和 Slave_SQL_Running:Yes表示这两个线程正在运行。

然后我们在从服务器上面使用命令：

mysql> SHOW PROCESSLIAT

显示如下结果（记为 结果一）

*************************** 1. row *************************** Id: 22 User: system user Host: db: NULL Command: Connect Time: 4 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 23 User: system user Host: db: NULL Command: 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是Slave SQL线程，已经读取了relay log 文件中所有更新的内容，正在等待I/O线程更新该文件。

使用命令停止slave机制：

mysql> STOP SLVAE;

然后我们再次查看会发现结果如下：

…… Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1264 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 878 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: No ……

说明这两个线程已经停止了运行。此时再次使用 SHOW PROCESSLIST 命令，则没有结果显示。

1、Slave I/O线程：

Slave I/O 线程去连接主服务器的Binlog dump 线程并要求其发送binlog日志中记录的更新的操作，然后它将Binlog dump 线程发送的数据拷贝到从服务器上（也就是本地）的文件 relay log 文件中。

当然要查看此线程是否运行，除了上面介绍的方法，还可以使用

mysql> SHOW SLAVE LIKE ‘Slave_running’;

这时如果出现下面的结果说明该线程正在运行

+—————–+——————-+ | Variable_name | Value | +—————–+——————-+ | Slave_running | 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 这个状态发生的时间也非常短暂，该状态在该线程和主服务器建立连接之后发生。 4. Registering slave on master 在主服务器上面注册从服务器，每当有新的从服务器连接进来以后都要在主服务器上面进行注册 5. Requesting binlog dump 向主服务器请求binlog日志的拷贝 6. Waiting to reconnect after a failed binlog dump request 如果5中失败，则该线程进入睡眠状态，此时State就是这个值，等待着去定期重新连接主服务器，那这个周期的大小可以通过CHANGE MASTER TO 来指定 7. Reconnecting after a failed binlog dump 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线程发送的一个更新事件并且正在将其拷贝到relay log文件中 10. Waiting to reconnect after a failed master event read 当该线程读取Binlog dump 线程发送的更新事件失败时，该线程进入睡眠状态等待去重新连接主服务器，这个等待的时间默认是60秒，当然这个值也可以通过CHANGE MASTER TO来设置 11. Reconnecting after a failed master event read 该线程去重新连接主服务器，当连接成功以后，那这个State的值会改变为 Waiting for master to send event 12. Waiting for the Slave SQL thread to free enough relay log space relay log space的大小是通过relay_log_space_limit来设定的，随着relay logs变得越来越大所有的大小合起来会超过这个设定值。这时该线程会等待SQL线程释放足够的空间删除一些relay log文件 13. Waiting for slave mutex on exit 当线程停止的时候会短暂的出现该情况

2、Slave SQL线程：

Slave SQL线程是在从服务器上面创建的，主要负责读取由Slave I/O写的 relay log 文件并执行其中的事件。

在上述结果一中2.row即是Slave SQL线程的信息，同样有一个State表示该线程的当前状态。

下面也列出了State所有可能出现的情况。

1. Waiting for the next event in relay log 该状态是读取relay log之前的初始状态 2. Reading event from the relay log 该状态表示此线程已经在relay log中读取了一个事件准备执行 3. Making temp file 该状态表示此线程正在执行LOAD_DATA_INFILE并且正在创建一个临时文件来保存从服务器将要读取的数据 4. Slave has read all relay log; waiting for the slave I/O thread to update it 该线程已经处理完了relay log中的所有事件，现在正在等待slave I/O线程更新relay log文件 5. Waiting for slave mutex on exit 当线程停止的时候会短暂的出现该情况

上面是对从服务器上的两个线程的简单的介绍，在运行过程中我们会发现这两个线程都离不开的文件就是relay log文件，下面我们简单介绍一下relay log文件。

六、如何提高Mysql主从复制的效率：

对于提升MySQL的主从复制的复制效率，binlog的大小是非常重要的因素，因为它涉及了I/O和网络传输，所以我们主要看一下master/slave这两端可以如何优化：

1、master 端：

master端有2个参数可以控制：

Binlog_Do_DB : 设定哪些数据库需要记录Binlog Binlog_Ignore_DB : 设定哪些数据库不要记录Binlog

这两项很重要，指定必要数据库，忽略不需要复制的数据库，可以减少binlog的大小，提高了I/O效率，加快网络传输。

但这两项也需要谨慎使用，因为可能会有主从数据不一致和复制出错的风险，。因为MySQL判断是否须要复制某个Event，不是根据产生该Event的语句所在的数据库，而是根据执行时所在的默认数据库，也就是登录时指定的数据库，或运行“USE DATABASE”中所指定的数据库。如果执行语句中明确指定了数据库名称，而这个数据库是被指定不记录Binlog的，那么这个语句在slave中执行时就会出错。

例如：

garbage 库是被指定不记录日志的 product 库是指定要记录日志的

执行下面的语句：

use product; delete from garbage.junk;

delete语句会被发送给slave，但slave中没有garbage库，所以执行时报错，复制失败。

2、slave端：

slave端有6个参数可以控制：

Replicate_Do_DB : 设定须要复制的数据库，多个DB用逗号分隔 Replicate_Ignore_DB : 设定可以忽略的数据库 Replicate_Do_Table : 设定须要复制的Table Replicate_Ignore_Table : 设定可以忽略的Table Replicate_Wild_Do_Table : 功能同Replicate_Do_Table，但可以带通配符来进行设置 Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table，可带通配符设置

slave端的配置优化效果要明显小于master端的，因为master端日志都写完了，日志也传过来了。

但这几个参数可以帮助我们减少日志的应用量，因为设置了过滤，实际写入的sql数量变少了，slave端的复制也就加快了

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100060.html原文链接：https://javaforall.cn