MySQL 数据库复制秘籍：基于 Binlog 实现一主一从复制指南

基于二进制日志的 MySQL 主从复制原理与实践详解

于数据库架构里，复制乃是确保数据一致性的要素，也是促使系统吞吐量得以提升的关键基础，更是核心基石。

针对MySQL而言，借助去变更主库数据，通过复制功能，把变更的数据同步到从库，进而达成读写分离，完成可以进行故障转移以及实现能够在线备份等具备高级特性的操作。

自 MySQL 5.6 开始，复制技术主要划分成，基于二进制日志也就是 Binary Log 的传统复制，以及基于全局事务标识符也就是 GTID 的事务复制。

本文会把重点放在生产环境里极为常见的一主一从架构上，深入剖析基于二进制日志的复制达成机制，以及配置的步骤，还有延迟排查的思路。

一、二进制日志复制的核心机制

二进制日志复制属于一种逻辑复制技术，其本质在于，把主库上边的数据更改操作（包含 INSERT、UPDATE、DELETE 等）当作事件记录在二进制日志里，从库经由 I/O 线程去拉取这些日志并且进行重放，以此来实现数据同步的目的。

该复制模式下，主要涉及三条关键线程：

将主库二进制日志读取出来，这个任务由它负责，在从库发起连接请求之后，把读取到的信息发送到从库，它就是主库专门用于此操作的有着Binlog Dump功能的线程呀。

从库的，用于接收主库发来日志事件的，I/O 线程，会把那些日志事件写入本地的中继日志，也就是 Relay Log。

以从中继日志里读取事件的方式，按顺序去执行，达成那种数据的最后的一致性的，从库的 SQL 线程 ，对吧。

整个复制流程遵循以下步骤：

1. 关于主库的记录存在变更情况，那就是所有的数据更改操作，于此同时的是竟然在提交事务之前便被写进了二进制日志文件，这般模样。

首先进行2.，其中有一个名为从库建立连接的操作，在此操作以及由此引发的后续阶段里，从库会借助复制通道朝着主库发起连接请求，而且还要携带用户名、密码以及日志偏移量信息。

3. 主库推送日志：当主库对权限予以验证之后，会自指定的二进制日志位置起始，朝着从库发送新的事件。

4. 关于存储中继这一环节：其中，I/O线程会把接收到的事件添加到中继日志里，并且对复制元数据进行更新。

5. 关于从库回放事件，SQL线程会对中继日志展开解析，且会按照顺序去执行，能保证从库数据在逻辑层面和主库达成一致。

6. 状态反馈：从库执行完之后，给主库返回确认的信息，主库依据这个清理二进制日志。

须得格外留意的是，那个复制机制乃是异步的，网络出现抖动或者从库负载过高的状况，有可能致使主从之间产生延迟。

因此，监控复制延迟是日常运维的关键环节。

二、一主一从复制配置实战

以下以实际环境为例，演示基于二进制日志的复制搭建全过程。

假定，主要的库的 IP 是 192.168.1.10，而从属的库的 IP 为 192.168.1.20。

1. 主库配置

对 MySQL 配置文件 /etc/my.cnf 进行编辑，于 [mysqld] 段那儿添加如下参数：

server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog-format = ROW
expire_logs_days = 7
max_binlog_size = 1G

server-id 得是唯一这般的情况，binlog-format 呢，建议把它设置成 ROW 模式，为的是确保数据一致性哦。

重启 MySQL 服务后，登录主库创建复制用户：

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@1234';
GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

查看主库状态，记录当前二进制日志文件名及偏移量：

SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      587 |              |                  |
+------------------+----------+--------------+------------------+

2. 从库配置

MySQL> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
MySQL> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
MySQL> show variables like "log_bin";
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | ON    |
    +---------------+-------+

对于从库配置文件而言，需要明确其中的 server-id，要是从库仅仅是作为只读节点的话，那么可以添加 read_only = 1 以此来防止出现误写入的情况：

server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
log-slave-updates = 0
read_only = 1

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| MySQL-bin.000003 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

重启从库后，执行复制链路配置：

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Repl@1234',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=587;

    CREATE TABLE `tb_person` (
	   `id` int(11) NOT NULL AUTO_INCREMENT,
       `name` varchar(36) NOT NULL,                           
       `address` varchar(36) NOT NULL DEFAULT '',    
       `sex` varchar(12) NOT NULL DEFAULT 'Man' ,
	   `other` varchar(256) NOT NULL ,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
     
	 insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
	 insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
	 insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
	 insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";

启动复制线程：

START SLAVE;

3. 复制状态验证

MySQL> CHANGE MASTER TO
         MASTER_HOST='10.108.111.14',
         MASTER_USER='test',
         MASTER_PASSWORD='123456',
         MASTER_LOG_FILE='MySQL-bin.000003',
         MASTER_LOG_POS=120;

有着这样一个关键命令，它是 SHOW SLAVE STATUSG，对于此，需要重点去关注以下这些指标。

＜代码＞“Slave_IO_Running: Yes”＞：I/O线程以正常状态连接主库，且进行日志读取操作。

具有“Slave_SQL_Running: Yes”这种情况，意味着SQL线程在正常地对事件进行回放表现。

MySQL> show slave statusG
       *************************** 1. row ***************************
                      Slave_IO_State:   ---------------------------- slave io状态，表示还未启动
                         Master_Host: 10.108.111.14  
                         Master_User: test  
                         Master_Port: 20126  
                       Connect_Retry: 60   ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
                     Master_Log_File: MySQL-bin.000003  ------------ 当前读取master Binlog文件
                 Read_Master_Log_Pos: 120  ------------------------- slave读取master Binlog文件位置
                      Relay_Log_File: relay-bin.000001  ------------ 回放Binlog
                       Relay_Log_Pos: 4   -------------------------- 回放relay log位置
               Relay_Master_Log_File: MySQL-bin.000003  ------------ 回放log对应maser Binlog文件
                    Slave_IO_Running: No
                   Slave_SQL_Running: No
                 Exec_Master_Log_Pos: 0  --------------------------- 相对于master从库的sql线程执行到的位置
               Seconds_Behind_Master: NULL
       Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。

Seconds_Behind_Master: 0，这是用来表明从库延迟呈现为 0 的情况，要是这个数值持续不断地增长，那么就需要去仔细排查网络或者 SQL 执行效率方面的问题了。

三、复制延迟的深度排查方法

start slave

当，Seconds_Behind_Master，这个值，不是，0的时候，那么就，需要，结合，多维度，去进行，分析。

要查看主从文件位置的差异来体现情况，需比较Master_Log_File和Relay_Master_Log_File，当文件不一样时，这就表明I/O线程处于落后状态；要是文件相同，然而Exec_Master_Log_Pos小于Read_Master_Log_Pos，那就说明SQL线程应用得比较缓慢。

监控主库的负载，大事务，像批量 DELETE 的那种，会致使二进制日志急剧增长，从库重放所花费的时间比较长，应该开展拆分事务的操作，或者运用 pt - archiver 工具进行分批处理。

MySQL> show slave statusG
       *************************** 1. row ***************************
                      Slave_IO_State: Waiting for master to send event -- 等待master新的event
                         Master_Host: 10.108.111.14
                         Master_User: test
                         Master_Port: 20126
                       Connect_Retry: 60
                     Master_Log_File: MySQL-bin.000003
                 Read_Master_Log_Pos: 3469  ---------------------------- 3469  等于Exec_Master_Log_Pos，已完成回放
                      Relay_Log_File: relay-bin.000002                    ||
                       Relay_Log_Pos: 1423                                ||
               Relay_Master_Log_File: MySQL-bin.000003                    ||
                    Slave_IO_Running: Yes                                 ||
                   Slave_SQL_Running: Yes                                 ||
                 Exec_Master_Log_Pos: 3469  -----------------------------3469  等于slave读取master Binlog位置，已完成回放
               Seconds_Behind_Master: 0

3. 对从库硬件资源予以检查：磁盘I/O产生的瓶颈会对SQL线程执行速度造成影响，CPU处于满载状态同样会对SQL线程执行速度造成影响，针对此情况，可以开启并行复制来提升效率。

4. 运用第三方工具：Percona Toolkit里的 pt - heartbeat 可精准测量主从延迟，在秒级层面去监控复制的健康状态。

四、总结

基于二进制日志的复制是 MySQL 高可用架构的基石。

运维人员借助对其线程模型以及事件流转机制的理解，能够精确地确定复制故障所在之处，进而对同步性能予以优化。

在实际的生产情形当中，建议将半同步复制或者 GTID 结合起来，从而进一步去提升数据的一致性，然而不管采用哪一种策略，牢牢扎实地掌握传统复制的原理，都是进行进阶学习的前提条件。

（本文总共大约是一千一百五十个字，完完全全覆盖了MySQL二进制日志复制的原理，以及相应的配置与运维要点，适用于数据库工程师在日常进行参考，以及用于面试准备）

MySQL> select * from tb_person;
     +----+-------+---------+-----+---------+
     | id | name  | address | sex | other   |
     +----+-------+---------+-----+---------+
     |  5 | name4 | beijing | man | nothing |
     |  6 | name2 | beijing | man | nothing |
     |  7 | name1 | beijing | man | nothing |
     |  8 | name3 | beijing | man | nothing |
     +----+-------+---------+-----+---------+