【MySQL】通过SQL_Thread快速恢复binlog

谁借莪１个温暖的怀抱￠ 2022-12-11 15:24 207阅读 0赞

> 将数据库回档至指定时间点或位置，常常是使用全量备份+binlog增量实现的。  
> 而数据量很大的情况下，增量恢复binlog一直是一个苦恼的问题。
> 
> 因为恢复binlog速度十分慢，并且容易出错。  
>   
> 
> 
> p.s. 以下所有框可左右滑动
> 
> 建议横屏阅读

### 常见binlog增量恢复方式 ###

**先解析成sql文件，再导入MySQL**

1mysqlbinlog mysql-bin.000001 --start-position=n > /data/add.sql
    2mysqlbinlog mysql-bin.000002 ... mysql-bin.n >> /data/add.sql
    3mysql -u -p -S < /data/add.sql

**直接管道到MySQL中**

1mysqlbinlog mysql-bin.000001 --start-position=n | mysql -u -p -S
    2mysqlbinlog mysql-bin.000002 ... mysql-bin.n | mysql -u -p -S

**直接管道进去的方式，并不一定安全，手册上也有指明：**

1If you have more than one binary log to execute on the MySQL server,
    2the safe method is to process them all using a single connection to the server.

**关于这种方式的更多内容，可以参考：**  
*https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html*

**然而这两种方式原理都是一样的，通过`mysqlbinlog`解析成sql并导入到MySQL中。**

*  **优点：**
 *   *  操作方便，逻辑简单。
     *  无需关闭`mysqld`。
 *  **缺点：**
 *   *  遇到`ERROR`难以定位位置，难以“断点恢复”。
     *  特殊字符或字符集的问题。
     *  `max_allowed_packet`问题。
     *  恢复速度慢。

--------------------

> 因为relaylog和binlog本质实际上是一个东西  
> 所以是否可以利用MySQL自身的sql\_thread来增量binlog呢？

### 通过sql\_thread恢复 ###

*  **处理思路：**  
    1）重新初始化一个实例，恢复全量备份文件。  
    2）找到第一个`binlog`文件的`position`，和剩下所有的`binlog`。  
    3）将`binlog`伪装成`relaylog`，通过`sql thread`增量恢复。

**这里只介绍核心部分，即伪装成relaylog的过程。**

**① 将relay log info的repository改到file中，并生成这个文件。（`relay_log_info_repositor`写到配置文件中）**

1SET GLOBAL relay_log_info_repository='FILE';
    2CHANGE MASTER TO master_host='1', master_password='1', master_user='1', master_log_file='1', master_log_pos=4;

通过change命令，是为了告诉MySQL自己为一个slave实例，因为无需用到`IO_Thread`，故`host`，`password`，`user`等可以随意填写。  
并且通过该步骤，生成`relay.info`文件。

**② 关闭实例，将需要增量的binlog文件伪装成relaylog。**

1cp mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 $relaylogdir
    2cd $relaylogdir
    3rename mysql-bin. mysql-relay. mysql-bin.0000*
    4chown mysql:mysql -R .

通过cp命令将`binlog`移动到`$relaylogdir`里，该变量取决于实例的选项参数，默认放在`datadir`下。  
再将`binlog`批量改名成`relaylog`，并且给予对应的权限，否则会报错`OS error code  13:  Permission denied`。

**③ 修改relay.info文件和relay-log.index文件**  
将`relay.info`的第二三行改成需要执行的第一个`binlog`（现在是`relaylog`）的文件名和`position`：

1/data/mysql57/relaylog/mysql-relay.000003
    21276895

第二三行对应`Relay_log_name`和`Relay_log_pos`，等同于：

1mysqlbinlog mysql-relay.000003 --start-position=1276895 | mysql -u -p -S

修改该文件是为了告诉`SQL_Thread`从哪一个`file`和哪一个`position`开始执行`events`。

再修改`relay-log.index`，清空原有信息，添加以下信息，为的是告诉`SQL_Thread`还有哪些`relaylog`是需要执行的。

1/data/mysql57/relaylog/mysql-relay.000003
    2/data/mysql57/relaylog/mysql-relay.000004
    3/data/mysql57/relaylog/mysql-relay.000005
    4/data/mysql57/relaylog/mysql-relay.000006
    5/data/mysql57/relaylog/mysql-relay.000007
    6/data/mysql57/relaylog/mysql-relay.000008
    7/data/mysql57/relaylog/mysql-relay.000009
    8/data/mysql57/relaylog/mysql-relay.000010

**④ 启动实例，开启`SQL_Thread`：**

1START SLAVE sql_thread ;

**⑤ 检查复制状态：**

1mysql> SHOW SLAVE STATUS\G
     2*************************** 1. row ***************************
     3Slave_IO_State:
     4Master_Host: 1
     5Master_User: 1
     6Master_Port: 3306
     7Connect_Retry: 60
     8Master_Log_File: 1
     9Read_Master_Log_Pos: 4
    10Relay_Log_File: mysql-relay.000003    -- 已经执行到的日志名
    11Relay_Log_Pos: 11529982        -- 已经执行到日志的位置
    12Relay_Master_Log_File: 1
    13Slave_IO_Running: No
    14Slave_SQL_Running: Yes
    15Replicate_Do_DB:
    16Replicate_Ignore_DB:
    17Replicate_Do_Table:
    18Replicate_Ignore_Table:
    19Replicate_Wild_Do_Table:
    20Replicate_Wild_Ignore_Table:
    21Last_Errno: 0
    22Last_Error:
    23Skip_Counter: 0
    24Exec_Master_Log_Pos: 11529982
    25Relay_Log_Space: 5347038913
    26Until_Condition: None
    27Until_Log_File:
    28Until_Log_Pos: 0
    29Master_SSL_Allowed: No
    30Master_SSL_CA_File:
    31Master_SSL_CA_Path:
    32Master_SSL_Cert:
    33Master_SSL_Cipher:
    34Master_SSL_Key:
    35Seconds_Behind_Master: 274354        -- 若变为0，则表示已经增量完毕
    36Master_SSL_Verify_Server_Cert: No
    37Last_IO_Errno: 0
    38Last_IO_Error:
    39Last_SQL_Errno: 0
    40Last_SQL_Error:
    41Replicate_Ignore_Server_Ids:
    42Master_Server_Id: 0
    43Master_UUID:
    44Master_Info_File: /data/mysql57/master.info
    45SQL_Delay: 0
    46SQL_Remaining_Delay: NULL
    47Slave_SQL_Running_State: Reading event from the relay log
    48Master_Retry_Count: 86400
    49………………………………

至此，已经可以通过`sql_thread`来增量恢复`binlog`了。  
当然，上述过程只针对于指定`--start-position`的方式来恢复，比如单点MySQL实例在`innodb_force_recovery=6`还无法启动的情况下，就需要通过最近一次可用的全量备份+剩下的binlog恢复。

**该测试使用的版本为：`MySQL 5.7.16`**

**效果**：  
快速恢复到指定位置点，即通过全备文件+`binlog`恢复到故障前的最后一个`position`。

### 针对--stop-position ###

比如在某一时刻执行了错误的sql，如truncate等操作，同样也可以通过该办法。  
但与指定`--start-position`的方法有些许不同：  
只需要将`START SLAVE sql_thread`后添加一个`UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos`即可。  
该选项用于控制`SQL_Thread`执行到的最后的position，类似于`mysqlbinlog mysql-bin.n --stop-position=$log_pos` 。

当然，这种数据回档操作，也可以考虑带有`flashback`功能的工具。

### 性能对比 ###

对于同一组binlog文件增量：  
通过`mysqlbinlog`解析+导入的时间为`69min`。  
而通过`SQL_Thread`的执行时间为`41min`。

并且在需要增量的binlog文件越大的情况下，效果越明显。

### 总结 ###

*  **优点**：  
    1）可以断点恢复，人为控制进度，比如stop slave或者遇到错误时，可以知道出错点在哪。  
    2）性能相对较好，在大量binlog的情况下，可以加快恢复速度。  
    3）在某些版本可能可以通过MTS来加快增量速度，使恢复更快。
 *  **缺点**：  
    1）需要关闭mysqld。  
    2）手动执行过程较mysqlbinlog方式更为复杂。

`mysqlbinlog --start-position`与通过修改`relay.info`的第三行等效：  
用途都是指定开始执行的第一个`position`。

`mysqlbinlog --stop-position`与通过在启动`SQL_Thread`时指定`UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos`等效：  
用途都是指定结束执行的最后一个`position`。

全文完。

Enjoy MySQL :)

扫码关注作者微信公众号

![format_png][]

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png 1][]

[format_png]: /images/20221123/2d2ceaeab38d45b0b86414cd4cb855f5.png
[format_png 1]: /images/20221123/fc121028cb20494381f96e08c7b3848e.png