Shulou(Shulou.com)05/31 Report--

This article introduces you how to restore binlog through MySQL relaylog + SQL_Thread increment, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Data rollback is often implemented using full backup + binlog increments.

In the case of a large amount of data, incremental recovery of binlog has always been a distressing problem, because the recovery of binlog is very slow and error-prone.

There are generally two ways to recover binlog files:

0 is parsed into a sql file, and then imported into 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 关于这种方式的更多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中，并生成这个文件。 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; 通过change命令，是为了告诉MySQL自己为一个slave实例，因为无需用到IO_Thread，故host，password，user等可以随意填写。 并且通过该步骤，生成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 . 通过cp命令将binlog移动到$relaylogdir里，该变量取决于实例的选项参数，默认放在datadir下。 再将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 第二三行对应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是需要执行的。 /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 ; 只需要开启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-- the name of the log that has been executed

Relay_Log_Pos: 11529982-- the location where the log has been executed

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-- if it becomes 0, the increment has been completed

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

...

The version used for this test is: MySQL 5.7.16

Effect: restore full file + binlog restore to the last position before the failure.

Other scenarios can also be used, such as performing the wrong sql, such as truncate, at a certain time.

Just add a UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos after the START SLAVE sql_thread.

This option is used to control the last position to which SQL_Thread executes, similar to mysqlbinlog mysql-bin.n-- stop-position=$log_pos.

In addition to being able to recover errors more accurately, one of the biggest benefits is that it speeds up the increment of binlog.

Add an additional test data

For the same set of binlog file increments:

The time of parsing + import through mysqlbinlog is 69min.

The execution time through SQL_Thread is 41min.

And the larger the incremental binlog file is required, the more obvious the effect is.

0 advantages:

Breakpoint recovery can be done, progress can be controlled artificially, such as stop slave, or breakpoint recovery can be made when errors are encountered.

Good performance, in the case of a large number of binlog, can speed up the recovery speed.

In some versions, multithreaded replication can be used to speed up increments and recover faster.

0 disadvantages:

Mysqld needs to be closed.

The manual execution process is more complex than the mysqlbinlog method.

0 Summary:

Mysqlbinlog-- start-position is equivalent to the third line by modifying relay.info:

The purpose is to specify the first position to start execution.

Mysqlbinlog-- stop-position is equivalent to specifying UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos when starting SQL_Thread:

The purpose is to specify the last position that ends execution.

On how to restore binlog through MySQL relaylog + SQL_Thread increment to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.