MySQL学习笔记-复制

MySQL从3.23版本开始提供复制的功能。复制是指将主数据库的DDL和DML操作通过二进制日志传到复制服务器（也叫从库）上，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保存同步。

     MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他服务器的主库，实现链状的复制。

MySQL复制的优点主要包括以下3个方面：

1.如果主库出现问题，可以快速切换到从库提供服务；

2.可以在从库上执行查询操作，降低主库的访问压力；

3.可以在从库上执行备份，以避免备份期间影响主库的服务。

注意：由于MySQL实现的是异步的复制，所以主从库之间存在一定的差距，在从库上进行的查询操作需要考虑到这些数据的差异，一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询，实时性要求高的数据仍然需要从主数据库获得。

 

一、MySQL复制原理：

MySQL的 Replication 是一个异步的复制过程，从一个MySQL节点（称之为Master）复制到另一个MySQL节点（称之Slave）。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（I/O 线程）在 Master 端。

 

要实现 MySQL 的 Replication ，首先必须打开 Master 端的 Binary Log，因为整个复制过程实际上就是 Slave 从 Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。

 

从这几条Replication原理来看，可以有这些推论：

主从间的数据库不是实时同步，就算网络连接正常，也存在瞬间，主从数据不一致。 

如果主从的网络断开，从会在网络正常后，批量同步。 

如果对从进行修改数据，那么很可能从在执行主的bin-log时出现错误而停止同步，这个是很危险的操作。所以一般情况下，非常小心的修改从上的数据。 

一个衍生的配置是双主，互为主从配置，只要双方的修改不冲突，可以工作良好。 

如果需要多主的话，可以用环形配置，这样任意一个节点的修改都可以同步到所有节点。

下图是MySQL官方给出了使用Replication的场景：

 

 

二、MySQL支持的复制类型：

1.基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，效率比较高。

一旦发现没法精确复制时，会自动选择基于行的复制。

2.基于行的复制：把改变的内容复制过去，而不是把命令在服务器上执行一遍。从MySQL5.0开始支持。

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

 

三、复制解决的问题

MySQL复制技术有以下一些特点：

1.数据分布（Data distribution）

2.负载平衡（load balancing）

3.备份（Backups）

4.高可用性和容错（High availability and failover）

 

四、复制如何工作

整体上来说，复制有3个步骤：

1.master将改变记录到二进制（binary log）中（这些记录叫做二进制日志事件，binary log events）；

2.Slave将master的binary log events拷贝到它的中继日志（relay log）；

3.Slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

 

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

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

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

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

 

五、复制步骤

准备：主（master）:192.168.2.33，从（slave）:192.168.2.37

A.在master端

1. 修改mysql配置文件[mysqld]节点内容：

log-bin = mysql-bin   #复制过程即Slave从Master端获取该日志再执行日志记录的操作

server-id = 1         #1代表master服务 

binlog-do-db = test    #要同步的库backup

binlog-ignore-db = mysql   #不同步的库，初始所有库默认为不同步，但为了明确最好加上

2. 重启mysql服务，使配置生效。

3. 设置一个复制使用的账户，并授予REPLICATION SLAVE权限。这里创建一个复制用户mast。添加要从slave服务器访问master服务器的有权限的帐号

cmd登录mysql后执行（用户名：mast，密码：mast）：

 

GRANT FILE ON *.* TO mast@192.168.2.33 IDENTIFIED BY 'mast';

GRANT REPLICATION SLAVE ON *.* TO mast@192.168.2.33 IDENTIFIED BY 'mast';

4. 查看master状态信息 

mysql> show master status;

 

记录下file和Position信息，slave需根据这两个信息来确定复制位置点

 

B.在slave端

1. 修改mysql配置文件[mysqld]节点内容:

server-id = 2    #2代表slave服务 

replicate-ignore-table = test.tb2  #不同步test库的tb2表

skip-slave-start  #防止复制随着mysql启动而自动启动。即slave端的mysql服务重启后需手动来启动主从复制（slave start），最好加上，slave端数据库服务重启后手动启动slave比较安全

2. 如果slave服务也同时作为另一个mysql服务的master服务，则配置文件[mysqld]节点内容还需要加上log-slave-updates，该参数可参阅手册说明： Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:  A -> B -> C Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

3. 重启mysql服务，使配置生效

4. 登录mysql并停止slave服务 

mysql>stop slave ; 

5. 设置与master服务器相关的配置参数（用到之前记录的master信息）

mysql>change master to master_host='192.168.2.33', master_user='mast', master_password='mast',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=509;

6. 开启slave服务 

mysql>start slave ; 

若在slave未停止的状态下启动slave服务，会产生错误： ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first.

7. 查看slave状态信息 

mysql>show slave status; 

。。。。。。

保证Slave_IO_Running: Yes     Slave_SQL_Running: Yes

8. 测试

     在主库建表并插入数据，看从库是否多了个表和数据。是则同步成功！    

六、主要复制启动选项

1.log-slave-updates:这个参数用来配置从库上的更新操作是否写进二进制日志，默认是打开的。但是，如果这个从数据库同时也要作为其他服务器的主库，搭建一个链式的复制，那么就需要打开这个选项，这样它的从库将获得它的二进制日志以进行同步操作。

2.Master-connect-retry:这个参数用来设置在和主库的连接丢失时重试的时间间隔，默认是60秒，即每60秒重试一次。

3.Read-only:该参数用来设置从库只能接受超级用户的更新操作，从而限制应用程序错误的对从库的更新操作，以确保从数据库的安全性。

4.指定复制的数据库或者表。可以使用replicate-do-db、replicate-do-table、replicate-ignore-db、replicate-ignore-table或者replicate-wild-do-table来指定从主数据库复制到从数据库的数据库或者表。有时用户只需要将关键表备份到从库上，或者只需要将提供查询操作的表复制到从库上，这样就可以通过配置这几个参数来筛选进行同步的数据库和表。

5.Slave-skip-errors:在复制过程中，由于各种原因，从库可能会遇到执行BINLOG中的SQL出错的情况（比如主键冲突），默认情况下，从库将会停止复制过程，不再进行同步，等待用户介入处理。这种问题如果不能及时发现，将会对应用或者备份产生影响。此参数的作用就是用来定义复制过程中从库可以自动跳过的错误号，这样当复制过程中遇到定义中的错误号时，便可以自动跳过，直接执行后面的SQL语句，以此来最大限度地减少人工干预。此参数可以定义多个错误号，或者通过定义成all跳过全部的错误，具体语法如下：--slave-skip-errors=[err_code1,err_code2,...|all]                                                                                        如果从数据库主要是作为主数据库的备份，那么就不应该使用这个启动参数，设                                                                             置不当很可能造成主从数据库的数据不同步。但是，如果从数据库仅仅是为了分担从数据库的查询压力，且对数据的完整性要求不是很严格，那么这个选项的确可以减轻数据 库管理员维护从数据库的工作量。

 

七、日常管理维护

1.查看从库状态：show slave status;

 在显示的信息中，我们主要关心“Slave_IO_Running”和“Slave_SQL_Running”这两个进程状态是否是“”，这两个进程的含义分别如下：

Slave_IO_Running:此进程负责从库（Slave）从主库（Master）上读取BINLOG日志，并写入从库上的中继日志中。

Slave_SQL_Running:此进程负责读取并且执行中继日志中的BINLOG日志。

只要其中有一个进程的状态是no，则表示复制进程停止，错误原因可以从“Last_Error”字段的值中看到。

除了查看上面的信息，用户还可以通过这个命令了解从库的配置情况以及当前和主库的同步情况，包括指向哪个主库、主库的端口、复制使用的用户、当前日志恢复到的位置等，这些都是记录在从库这一端的，主库上并没有相应的信息。

2.主从库同步维护

  在某些繁忙的OLTP系统上，由于主库更新频繁，而从库由于各种原因（比如硬件性能较差）导致更新速度较慢，从而使得主从库之间的数据差距越来越大，最终对某些应用产生影响。在这种情况下，我们就需要定期的进行主从库的数据同步，使得主从数据库差距能够减到最小。常用的方法是：在负载较低时暂时阻塞主数据库的更新，强制主从数据库更新同步。具体操作步骤如下：

1.在主库上，执行以下语句（注意：会阻塞主数据库的所有更新操作）：

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

记录SHOW语句的输出的日志名和偏移量，这些都是从库复制的目的坐标。

2.在从库上，执行下面的语句，其中MASTER_POS_WAIT()函数的参数是前面步骤中得到的复制坐标值：

Select MASTER_POS_WAIT(‘mysql-bin.000039’,’974’);

这个select语句会阻塞直到从库达到指定的日志文件和偏移量后，返回0，如果返回-1，则表示超时退出。查询返回0时，则从库与主库同步。

3.在主库上，执行下面的语句允许主库重新开始处理更新：

UNLOCK TABLES;

3.从库复制出错的处理

当从库更新失败时，首先需要确定是否是从库的表与主库的不同造成的，如果不是表结构不同导致的，则修改从库的表使之与主库的相同，然后重新运行START SLAVE语句。如果不是表结构不同导致的更新失败，则需要确认手动更新是否安全，然后忽视来自主库的更新失败的语句。跳过来自主库的语句的命令为SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n，其中n的取值为1或者2.如果来自主库的更新语句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n值应为1，否则，值应为2。原因是使用AUTO_INCREMENT或LAST_INSERT_ID()的语句需要从二进制日志中读取两个事件。以下例子就是在从库端模拟跳过主库的两个更新语句的效果。

（1）首先，在从库端先停止复制进程，并设置跳过两个语句：

     Stop slave;

     SET GLOBAL SQL_slave_SKIP_COUNTER = 2;

（2）在主库端插入3条记录：略

（3）从库端启动复制进程，检查测试的表，发现首先插入的两条记录被跳过了，只执行了第3条插入语句：

     Start slave;

4.log event entry exceeded max_allowed_packet的处理

如果应用中使用大的BLOG列或者长字符串，那么在从库上恢复时，可能会出现“log event entry exceeded max_allowed_packet”错误，这是因为含有大文本的记录无法通过网络进行传输导致。解决的办法就是在主从库上增加max_allowed_packet参数的大小，这个参数的默认值为1MB，可以按照实际需要进行修改，比如下例中将其增大为16MB：

Show variables like ‘max_allowed_packet’;

Set @@global.max_allowed_packet = 16777216;

同时在my.ini文件中，设置max_allowed_packet = 16MB，保证下次数据库重新启动后参数继续有效。

5.多主复制时的自增长变量冲突问题

在使用多主复制时，为了防止主键冲突发生，需要定制auto_increment_increment和auto_increment_offset的设置，保证多主之间复制到从数据库不会有重复冲突。比如，两个master的情况可以按照以下设置：

Master1上：auto_increment_increment=2，auto_increment_offset=1；（1，3，5，7。。。序列）。

Master2上：auto_increment_increment=2，auto_increment_offset=0；（0，2，3，6。。。序列）。

6.查看从库的复制进度

  很多情况下，我们都想知道从库复制的进度如何。知道这个差距，可以帮助我们判断是否需要手工来做主从的同步工作，也可以帮助我们判断从库上做统计的数据精度如何。这个值可以通过SHOW PROCESSLIST列表中的Slave_SQL_Running线程的Time得到，它记录了从库当前执行的SQL时间戳与系统时间之间的差距，单位是秒。

7.如何提高复制的性能

  。。。。。。

八、切换主从库

假设有一个复制的环境，一个主数据库服务器M，两个从数据库服务器S1、S2同时指向主数据库服务器M。当主数据库M因为某种原因出现故障时，需要将其中的一个从数据库服务器（假设选中S1）切换成主数据库服务器，同时修改另一个从数据库（S2）的配置，使其指向心的主数据库（S1）。此外还需要通知应用修改主数据库的IP地址，如果不能，将出现故障的数据库（M）修复或者重置成新的从数据库。

下面是切换主从库的操作步骤。

（1）首先要确保所有的从数据库都已经执行了relay log中的全部更新，在每个从库上，执行STOP SLAVE IO_THEAD，然后检查SHOW PROCESSLIST的输出，直到看到状态是Has read all relay log，表示更新都执行完毕。

（2）在从数据库S1上，执行STOP SLAVE以停止从服务，然后执行RESET MASTER以重置成主数据库：STOP SLAVE;reset master;

（3）在S2上，执行STOP SLAVE以停止服务，然后执行CHANGE MASTER TO MASTER_HOST=’S1’以重新设置主数据库，再执行START SLAVE以启动复制： STOP ALAVE;CHANGE MASTER TO MASTER_HOST=’192.168.1.191’;start slave;

（4）通知所有的客户端将应用指向S1，这样客户端发送的所有的更新语法写入到S1的二进制日志。

（5）删除新的主数据库服务器上的master.info和relay-log.info文件，否则下次启动时还会按照从库启动。

（6）最后，如果M服务器可以修复，则可以按照S2的方法配置成S1的从库。

     注意：上面步骤是默认S1是打开log-bin选项的，这样重置成主数据库后可以将二进制日志传输到其他从库。其次，S1上没有打开log-slave-updates参数，否则重置生成主数据库后，可能会将已经执行过的二进制日志重复传输给S2，导致S2的同步错误。