Use mysqlbinlog to restore data based on location or time point

Use mysqlbinlog to restore data based on location or time point

MySQL backup generally uses full backup and log backup. For example, a full backup is performed every day and a binary log backup is performed every hour. In this way, you can use full backup and log backup to recover data to any location or time before the last binary log backup. The tools used for full backup and log backup have their own characteristics and are not described here. This article mainly explains how to apply the backup binary log to restore data to a specified location or time point after a full backup is returned.

-------------------------------------- Split line --------------------------------------

MySQL data recovery experiment with mysqldump and mysqlbinlog

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

Here is a very important tool-mysqlbinlog, which is used to view binary logs. Let's explain the problem with some columns:

First, let's take a look at how to directly check which binary log files and what events contained in the files in MySQL Server.

First clear all binary logs on MySQL Server
Mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

View binary logs on MySQL Server
Mysql> show binary logs;
+ --------------------- + ----------- +
| Log_name | File_size |
+ --------------------- + ----------- +
| VMS00781-bin.000001 | 120 |
+ --------------------- + ----------- +

View events in binary logs

Mysql> show binlog events;
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +

Execute some DML operations

Mysql> delete from AB limit 2;
Query OK, 2 rows affected (1.01 sec)

Start a new Log File

Mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

Execute some DML operations

Mysql> delete from AB limit 1;
Query OK, 1 row affected (0.00 sec)
Mysql> delete from AB limit 2;
Query OK, 2 rows affected (0.01 sec)

View binary logs on MySQL Server
Mysql> show binary logs;
+ --------------------- + ----------- +
| Log_name | File_size |
+ --------------------- + ----------- +
| VMS00781-bin.000001 | 372 |
| VMS00781-bin.000002 | 515 |
+ --------------------- + ----------- +

View events in binary logs
Mysql> show binlog events;
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| VMS00781-bin.000001 | 120 | Query | 36 | 192 | BEGIN |
| VMS00781-bin.000001 | 192 | Table_map | 36 | 238 | table_id: 204 (test. AB) |
| VMS00781-bin.000001 | 238 | Delete_rows | 36 | 291 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000001 | 291 | Xid | 36 | 322 | COMMIT/* xid = 289981 */|
| VMS00781-bin.000001 | 322 | Rotate | 36 | 372 | VMS00781-bin.000002; pos = 4 |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
By default, events in the first binary log file are displayed, including the start position, end position, event type, and information of the event. We can see that the first event is the format description event, the second is the query event, the transaction starts, the third is the table ing event, and the fourth is the deletion operation we perform, the fifth is that the Xid time is the automatic transaction commit action, and the sixth is the log rotation event, which is caused by the execution of flush logs to enable the new log file.

View events in a specified binary log
Mysql> show binlog events in 'vms00781-bin.000002 ';
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
| VMS00781-bin.000002 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| VMS00781-bin.000002 | 120 | Query | 36 | 192 | BEGIN |
| VMS00781-bin.000002 | 192 | Table_map | 36 | 238 | table_id: 204 (test. AB) |
| VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT/* xid = 290004 */|
| VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN |
| VMS00781-bin.000002 | 385 | Table_map | 36 | 431 | table_id: 204 (test. AB) |
| VMS00781-bin.000002 | 431 | Delete_rows | 36 | 484 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 484 | Xid | 36 | 515 | COMMIT/* xid = 290005 */|
| VMS00781-bin.000002 | 515 | Query | 36 | 593 | flush slow logs |
| VMS00781-bin.000002 | 593 | Query | 36 | 671 | flush slow logs |
+ --------------------- + ----- + ------------- + ----------- + ------------- + ----------------------------------------- +
This command also contains other options for flexible viewing
Show binlog events [IN 'Log _ name'] [FROM pos] [LIMIT [offset,] row_count]
Mysql> show binlog events in 'vms00781-bin.000002 'from 120 limit 2 and 3;
+ --------------------- + ----- + ------------- + ----------- + ------------- + --------------------------------- +
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+ --------------------- + ----- + ------------- + ----------- + ------------- + --------------------------------- +
| VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT/* xid = 290004 */|
| VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN |
+ --------------------- + ----- + ------------- + ----------- + ------------- + --------------------------------- +

Show binary logs is equivalent to SHOW MASTER LOGS
Purge binary logs is used for binary logs, such:
Purge binary logs to 'mysql-bin.010 ';
Purge binary logs before '2017-04-02 22:46:26 ';

Reset master and RESET SLAVE
The former clears all binary logs listed in the index file, resets the index file to null, and creates a new binary log file, which is generally used when the MASTER is started for the first time. The latter causes SLAVE to forget its replication location in the MASTER binary log file, which deletes the master.info, relay-log.info, and all relay log files and starts a new relay log file, in order to start a clean copy. Before using reset slave, you must disable the SLAVE replication thread.

The preceding method allows you to view the binary log files and file events on the server. However, you must use the mysqlbinlog tool to view the specific content of the files and restore the files.
View:
Shell> mysqlbinlog [options] log_file...
For example:
Mysqlbinlog [options] VMS00781-bin.000001
The output content varies slightly depending on the log file format and the options used by the mysqlbinlog tool. For more information about the meanings of binary log files and the available options of mysqlbinlog, see the relevant manual. Here we will explain some situations that require special attention.

For more details, please continue to read the highlights on the next page:

1 2 Next Page