Use the mysqlbinlog tool to recover data based on location or time point.

Use the mysqlbinlog tool to recover 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.

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.

The format of the binary log file includes the line mode, statement mode, and hybrid mode (that is, the server determines under what circumstances to record what types of logs ), in statement-based logs, event information includes executed statements, and in row-based logs, event information includes row changes. Both types of event information in mixed mode logs are recorded. The-v (-- verbose) option of mysqlbinlog tool can be used to view the specific SQL statement executed by the event that records the row change information, this option will repeat the row event to form a pseudo SQL statement that has been commented out. If you want to see more detailed information, you can

To give this option twice, such as-vv, which can contain comments of some data types and metadata, such

Mysqlbinlog-v VMS00781-bin.000001
VMS00781-bin.000001 of mysqlbinlog-vv

In addition, mysqlbinlog and binary log files can be read from the remote server using the -- read-from-remote-server option. In this case, some extra connection parameters, such as -- host, -- password, -- port, -- user, -- socket, -- protocol, and so on. These parameters are valid only after -- read-from-remote-server is specified.

Whether it is a local binary log file or a binary log file on a remote server, whether it is a binary log file in line, statement, or hybrid mode, after being parsed by the mysqlbinlog tool, you can directly apply it to MySQL Server for restoration based on the time point, location, or database.
For example:
Mysqlbinlog VMS00781-bin.000001 | mysql-uusername-p

You can also write binary logs to the. SQL file and then execute these files on the mysql client. For example:
Mysqlbinlog VMS00781-bin.000001>/tmp/VMS00781-bin.000001. SQL
Mysql> source/tmp/VMS00781-bin.000001. SQL

Here are several key parameters:
-- Database = db_name,-d db_name
This parameter enables mysqlbinlog to only output the specified db_name from the local binary log and be selected by the use command as the log event generated by the default database. The behavior is similar to the -- binlog-do-db command of mysqld. If this parameter is specified multiple times, only the last specified content is valid. The specific impact of parameters depends on the binary log format. This parameter can be consistent only when the row mode log format is used. The database parameter may behave differently due to cross-database updates in the statement-or mixed-mode binary log format, so data consistency cannot be guaranteed.
Mysqlbinlog VMS00781-bin.000001-d testDB | mysql-uusername-p

-- Force-read,-f
When this parameter is used, the mysqlbinlog tool prints a warning message when it reads an unrecognized log event. If this parameter is not set, the mysqlbinlog stops.
Mysqlbinlog VMS00781-bin.000001-d testDB-f | mysql-uusername-p

-- No-defaults
Prevent mysqlbinlog tool from reading parameters from any configuration file, except. mylogin. cnf (for Secure Password saving)
Mysqlbinlog VMS00781-bin.000001-d testDB-f -- no-defaults | mysql-uusername-p

-- Start-datetime = datetime
-- Stop-datetime = datetime
The preceding set of parameters are used to specify the recovery start time and end time. They can be provided together or separately or mixed with -- start-position and -- stop-position.
Mysqlbinlog VMS00781-bin.000001-d testDB-f -- no-defaults -- start-datetime = datetime -- stop-position = NNNNNN | mysql-uusername-p

-- Start-position = N,-j N
-- Stop-position = N
The preceding set of parameters are used to specify the recovery start and end positions. They can be provided together or separately or mixed with -- start-datetime and -- stop-datetime.
Mysqlbinlog VMS00781-bin.000001-d testDB-f -- no-defaults -- start-position = NNNNNN -- stop-datetime = datetime | mysql-uusername-p

There are usually more than one binary log file to be restored. If there are multiple binary log files to be restored, what should I pay attention?
First, you can select the method for directly redirecting to the mysql client or import the. SQL file first and then execute the. SQL file to apply the binary log files one by one. However, there is a hidden risk. If temporary tables are recorded in binary logs, when the next binary log is applied in the new connection, the temporary table will be lost, causing an error. Therefore, the safe way is to execute multiple binary files at the same time.
For example:
Mysqlbinlog VMS00781-bin.000001 VMS00781-bin.000002 VMS00781-bin.000003 -- start-position = nnnnnnnn -- stop-datetime = datetime | mysql-uusername-p

Or mysqlbinlog VMS00781-bin.00000 [1-3] -- start-position = NNNNNN -- stop-datetime = datetime | mysql-uusername-p

When multiple binary log files are executed simultaneously, -- start-position and -- stop-position are only applied to the first listed binary log files and the last listed binary log files.

Of course, you can export the output of multiple binary log files to the same. SQL file and then execute the. SQL file (applicable to scenarios with a small amount of logs)