数据回档常常是使用全量备份+binlog增量实现的。
而数据量很大的情况下,增量恢复binlog一直是一个苦恼的问题,因为恢复binlog速度十分慢,并且容易出错。
恢复binlog文件一般有两种方法:
〇 先解析成sql文件,再导入MySQL
〇 直接管道到MySQL中
关于这种方式的更多info,可以参考:
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呢?
〇 处理思路:
1)重新初始化一个实例, 恢复全量备份文件。
2)找到第一个binlog文件的position,和剩下所有的binlog。
3)将binlog伪装成relaylog,通过sql thread增量恢复。
这里只介绍核心部分,即伪装成relaylog的过程。
① 将relay log info的repository改到file中,并生成这个文件。
通过change命令,是为了告诉MySQL自己为一个slave实例,因为无需用到IO_Thread,故host,password,user等可以随意填写。
并且通过该步骤,生成relay.info文件。
② 关闭实例,将需要增量的binlog文件伪装成relaylog。
通过cp命令将binlog移动到$relaylogdir里,该变量取决于实例的选项参数,默认放在datadir下。
再将binlog批量改名成relaylog,并且给予对应的权限,否则会报错OS error code 13: Permission denied。
③ 修改relay.info文件和relay-log.index文件
将relay.info的第二三行改成需要执行的第一个binlog(现在是relaylog)的文件名和position:
第二三行对应Relay_log_name和Relay_log_pos,等同于:
mysqlbinlog mysql-relay . 000003 --start- position =1276895 | mysql -u -p -S
修改该文件是为了告诉SQL_Thread从哪一个文件和哪一个position开 始执行事务
再修改relay-log.index,清空原有信息,添加以下信息,为的是告诉SQL_Thread还有哪些 relaylog是需要执行的。
④ 启动实例,开启SQL_Thread:
只需要开启SQL_Thread即可
⑤ 检查复制状态:
该测试使用的版本为 :MySQL 5.7.16
效果:恢复全备文件+binlog恢复到故障前的最后一个position。
其他场景也适用,比如在某一时刻执行了错误的sql,如truncate等操作,同样也可以通过该办法。
只需要将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 。
除了更准确的能够恢复错误之外,还有一个最大的好处是加快了binlog 增量的速度。
补充一个额外的测试数据
对于 同一组binlog文件增量 :
通过mysqlbinlog解析+导入的时间为69min。
而通过SQL_Thread的执行时间为41min。
并且 在需要增量的 binlog文件越大的情况下,效果越明显。
〇 优点:
可以断点恢复,人为控制进度,比如stop slave或者遇到错误时,可以断点恢复。
性能好, 在大量binlog的情况下,可以 加快恢复速度。
在某些版本可以利用多线程复制来加快增量速度,时恢复更快。
〇 缺点:
需要关闭mysqld。
手动执行过程较mysqlbinlog方式更为复杂。
〇 总结:
mysqlbinlog --start-position 与 通过修改relay.info的第三行等效:
用途都是指定开始执行的第一个position。
mysqlbinlog --stop-position 与 通过在启动SQL_Thread时指定UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos等效:
用途都是指定结束执行的最后一个position。
作者微信公众号(持续更新)
![](//img.blog.itpub.net/blog/attachment/201805/23/29773961_1527061402upuu.png?x-oss-process=style/bb)
而数据量很大的情况下,增量恢复binlog一直是一个苦恼的问题,因为恢复binlog速度十分慢,并且容易出错。
恢复binlog文件一般有两种方法:
〇 先解析成sql文件,再导入MySQL
- mysqlbinlog mysql-bin.000001 --start-position=n > /data/add.sql
- mysqlbinlog mysql-bin.000002 ... mysql-bin.n >> /data/add.sql
- mysql -u -p -S < /data/add.sql
〇 直接管道到MySQL中
- mysqlbinlog mysql-bin.000001 --start-position=n | mysql -u -p -S
- mysqlbinlog mysql-bin.000002 ... mysql-bin.n | mysql -u -p -S
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呢?
〇 处理思路:
1)重新初始化一个实例, 恢复全量备份文件。
2)找到第一个binlog文件的position,和剩下所有的binlog。
3)将binlog伪装成relaylog,通过sql thread增量恢复。
这里只介绍核心部分,即伪装成relaylog的过程。
① 将relay log info的repository改到file中,并生成这个文件。
- SET GLOBAL relay_log_info_repository='FILE';
- CHANGE MASTER TO master_host='1',master_password='1',master_user='1',master_log_file='1',master_log_pos=4;
并且通过该步骤,生成relay.info文件。
② 关闭实例,将需要增量的binlog文件伪装成relaylog。
- cp 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
- cd $relaylogdir
- rename mysql-bin. mysql-relay. mysql-bin.0000*
- chown mysql:mysql -R .
再将binlog批量改名成relaylog,并且给予对应的权限,否则会报错OS error code 13: Permission denied。
③ 修改relay.info文件和relay-log.index文件
将relay.info的第二三行改成需要执行的第一个binlog(现在是relaylog)的文件名和position:
- /data/mysql57/relaylog/mysql-relay.000003
- 1276895
mysqlbinlog mysql-relay . 000003 --start- position =1276895 | mysql -u -p -S
修改该文件是为了告诉SQL_Thread从哪一个文件和哪一个position开 始执行事务
再修改relay-log.index,清空原有信息,添加以下信息,为的是告诉SQL_Thread还有哪些 relaylog是需要执行的。
- /data/mysql57/relaylog/mysql-relay.000003
- /data/mysql57/relaylog/mysql-relay.000004
- /data/mysql57/relaylog/mysql-relay.000005
- /data/mysql57/relaylog/mysql-relay.000006
- /data/mysql57/relaylog/mysql-relay.000007
- /data/mysql57/relaylog/mysql-relay.000008
- /data/mysql57/relaylog/mysql-relay.000009
- /data/mysql57/relaylog/mysql-relay.000010
④ 启动实例,开启SQL_Thread:
- START SLAVE sql_thread ;
⑤ 检查复制状态:
- mysql> SHOW SLAVE STATUS\G
- *************************** 1. row ***************************
- Slave_IO_State:
- Master_Host: 1
- Master_User: 1
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: 1
- Read_Master_Log_Pos: 4
- Relay_Log_File: mysql-relay.000003 -- 已经执行到的日志名
- Relay_Log_Pos: 11529982 -- 已经执行到日志的位置
- Relay_Master_Log_File: 1
- Slave_IO_Running: No
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 11529982
- Relay_Log_Space: 5347038913
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 274354 -- 若变为0,则表示已经增量完毕
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 0
- Master_UUID:
- Master_Info_File: /data/mysql57/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Reading event from the relay log
- Master_Retry_Count: 86400
- ………………………………
该测试使用的版本为 :MySQL 5.7.16
效果:恢复全备文件+binlog恢复到故障前的最后一个position。
其他场景也适用,比如在某一时刻执行了错误的sql,如truncate等操作,同样也可以通过该办法。
只需要将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 。
除了更准确的能够恢复错误之外,还有一个最大的好处是加快了binlog 增量的速度。
补充一个额外的测试数据
对于 同一组binlog文件增量 :
通过mysqlbinlog解析+导入的时间为69min。
而通过SQL_Thread的执行时间为41min。
并且 在需要增量的 binlog文件越大的情况下,效果越明显。
〇 优点:
可以断点恢复,人为控制进度,比如stop slave或者遇到错误时,可以断点恢复。
性能好, 在大量binlog的情况下,可以 加快恢复速度。
在某些版本可以利用多线程复制来加快增量速度,时恢复更快。
〇 缺点:
需要关闭mysqld。
手动执行过程较mysqlbinlog方式更为复杂。
〇 总结:
mysqlbinlog --start-position 与 通过修改relay.info的第三行等效:
用途都是指定开始执行的第一个position。
mysqlbinlog --stop-position 与 通过在启动SQL_Thread时指定UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos等效:
用途都是指定结束执行的最后一个position。
作者微信公众号(持续更新)
![](http://img.blog.itpub.net/blog/attachment/201805/23/29773961_1527061402upuu.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29773961/viewspace-2143726/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29773961/viewspace-2143726/