【补充篇】MySQL的主从复制

  复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到复制服务器（也叫从服务器）上，然后在从服务器上对这些日志重新执行（也叫重做），从而使得从服务器和主服务器的数据保持同步。
  MySQL 支持的主从形式有：一主一从、一主多从（高性能）、多主一从（从5.7开始支持），双主复制，级联复制等，但本文旨在讲解原理和让读者认识主从复制，所以只讲解一主一从模式。

   MySQL 主从复制默认是异步的模式，换句话说，主从服务器之间在同一时间存在一定的数据差距。虽然现在的MySQL支持半同步模式和全同步模式，同样的，我们本文只讲解基础的异步模式。半同步和同步在后面学习高性能、高并发时讲解。

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

• 宕机处理：如果主服务器出现问题，可以快速切换到从服务器提供服务
• 读写分离：主服务器写，从服务器读，降低主服务器的访问压力
• 数据备份：在从服务器备份，减少备份对主服务的影响

1、原理及流程

关于主从复制的流程，分以下三步：

（1）把主库的数据写入二进制日志
（2）把主库的二进制日志读进从库
（3）从库执行读取的二进制日志中的SQL语句

  这样说可能还是不太明白，放一个“烂大街”的流程图看看：

  

2、一主一从复制演示

   MySQL 的复制至少需要两个 MySQL 服务，这些 MySQL 服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。进行主从复制之前需要对两个服务器进行配置，具体如下：

1. 环境配置

1.主机配置:

   （1）在主服务器上，设置一个复制使用的账户，并授予 REPLICATION SLAVE 权限。(这里是一对一，所以IP是从服务器的ip，否则你可以设置所有ip访问）

GRANT REPLICATION SLAVE ON . TO ‘repl’@‘192.168.1.101’ IDENTIFIED BY ‘1234test’;

   （2）修改主数据库服务器的配置文件 my.cnf，开启 BINLOG，并设置 server-id 的值(最好是使用主服务器的ip最后一段)。这两个参数的修改需要重新启动数据库服务才可以生效。

My.cnf 中修改：
  
[mysqld]
log-bin = /home/mysql/log/mysql-bin.log
server-id = 101

   （3）查看主服务器上当前的二进制日志名和偏移量值。这个操作的目的是为了在从数据库启动以后，从这个点开始进行数据的恢复。

show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000039 | 102 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

2.从机配置:

   （1）修改从数据库的配置文件 my.cnf，增加 server-id 参数。注意 server-id 的值必须是唯一的，不能和主数据库的配置相同，如果有多个从数据库服务器，每个从数据库服务器必须有自己唯一的 server-id 值。

my.cnf 中修改：
[mysqld]
server-id = 100

   （2）查看是否有有以前配置的从库信息

show slave status;

   （3）如果没有，则忽略这一步，否则，关闭之前的从库

stop slave;

   （4）初始化重置从库的状态(MASTER_LOG_FILE,MASTER_LOG_POS两个参数就是之前查看的主库的二进制日志名和偏移量)

CHANGE MASTER TO
   MASTER_HOST=‘master_host_name’,
   MASTER_POST=‘master_port’,
   MASTER_USER=‘replication_user_name’,
   MASTER_PASSWORD=‘replication_password’,
   MASTER_LOG_FILE=‘recorded_log_file_name’,
   MASTER_LOG_POS=recorded_log_position;

例如：
CHANGE MASTER TO
   MASTER_HOST=‘192.168.1.100’,
   MASTER_PORT=3306,
   MASTER_USER=‘repl’,
   MASTER_PASSWORD=‘1234test’,
   MASTER_LOG_FILE=‘mysql-bin.000039’,
   MASTER_LOG_POS=102;

   （5）在从服务器上，启动slave线程

start slave;

   （6）查看从库是否开启成功

show slave status;
  
只有当slave_io_Running、Slave_SQL_Running两个参数值都为Yes时，说明从库开启成功

3.测试:

   在主数据库上执行一个更新操作，观察是否在从数据库上同步。

-- 主库
use test;
create table repl_test (id int);
insert into repl_test values(1),(2),(3),(4),(5);

--从库
use test;
select * from repl_test;

  若从库看到的内容和主库的一致，则复制服务配置成功完成。

注意：
 1.这里讲解的是异步复制，所以实时性要求高的数据仍然需要从主数据库获得
 2.主从复制是从搭建之后才开始的，所以之前的数据必须在搭建之前手动取同步，不然数据不一致
 3.确保主从服务器上安装了相同版本的数据库

2. 更多参数讲解

  前面介绍安装配置信息是主从复制最基本的配置项，但还有一些配置项是可选的，但实际应用中也很有用，下面就介绍以下几个参数。

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

master-connect-retry
 master-connect-retry 这个参数用来设置在和主服务器的连接丢失的时候，重试的时间间隔，默认是 60 秒，即每 60 秒重试一次。

read-only
 read-only 该参数用来设置从服务器只能接受超级用户的更新操作，从而限制应用程序错误的对从服务器的更新操作。比如创建了一个普通用户，在默认情况下，该用户是可以更新从数据库中的数据的，但是使用 read-only 选项启动从数据库以后，该用户对从数据库的更新会提示错误。需要说的是它不会影响slave同步复制的功能。

set global read_only=1;
 
或者直接在my.cnf的[mysqld]下输入read-only=1，然后重启服务；

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

slave-skip-errors
  在复制过程中，由于各种原因，从服务器可能会遇到执行 BINLOG 中的 SQL 出错的情况（比如主键冲突），默认情况下，从服务器将会停止复制进程，不再进行同步，等待用户介入处理。这种问题如果不能及时发现，将会对应用或者备份产生影响。此参数的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，这样当复制过程中遇到定义中的错误号时，便可以自动跳过，直接执行后面的 SQL 语句，以此来最大限度地减少人工干预。此参数可以定义多个错误号，或者通过定义成 all 跳过全部的错误。具体语法如下：

修改mysql的配置文件
slave-skip-errors=[err_code1,err_code2,… | all]

跳过指定的三个错误
slave-skip-errors=1062,1053,1146

  默认是不启用这个参数的，尤其是数据库主要是作为主数据库的备份，那么就更不应该使用这个启动参数，设置不当，很可能造成主从数据库的数据不同步。仅当从数据库是为了分担主数据库的查询压力，且对数据的完整性要求不是很严格，那么这个选项的确可以减轻数据库管理员维护从数据库的工作量。

3、主从切换

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

（1）首先要确保所有的从数据库都已经执行了 relay log（加载进中级日志的SQL） 中的全部更新，在每个从服务器上，执行 STOP SLAVE IO_THREAD，然后检查 SHOW PROCESSLIST 的输出，直到看到状态是 Has read all relay log，表示更新都执行完毕。
（2）在从数据库 S1 上，执行 STOP SLAVE 停止从服务，然后 RESET MASTER 重置成主数据库。
（3）在 S2 上，执行 STOP SLAVE 停止从服务，然后执行 CHANGE MASTER TO MASTER_HOST= 'S1’重新设置主数据库，然后再执行 START SLAVE 启动复制。
（4）通知所有的客户端将应用指向 S1，这样客户端发送的所有的更新语法写入到 S1 的二进制日志。
（5）删除新的主数据库服务器上的 master.info 和 relay-log.info 文件，否则下次重启的时候还会按照从服务器启动。
（6）最后，如果 M 服务器可以修复，则可以按照 S2 的方法配置成 S1 的从服务器。

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