using the Mysqlbinlog tool for location-based or point-in-time recovery
MySQL backups typically take full backups plus log backups, such as performing a full backup every day, and a binary log backup every hour. This allows you to recover data to any location or time prior to the last binary log backup using full backup and log backups after a MySQL server failure. There are a variety of tools for full preparation and log preparation, each with its own characteristics, not described here. This article explains how to apply a backup binary log to restore data to a specified location or point in time after a full backup has been resumed.
Here's a very important tool,--mysqlbinlog, that is designed to look at binary logs. We explain the problem with some Lezilai:
First look at how you can see in MySQL server what binary log files and which events are included in the file.
Empty all binary logs on MySQL server first
mysql> Reset Master;
Query OK, 0 rows Affected (0.00 sec)
Viewing binary logs on MySQL server
Mysql> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| vms00781-bin.000001 | 120 |
+---------------------+-----------+
Viewing events in the binary log
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 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
Perform some DML operations
mysql> Delete from AB limit 2;
Query OK, 2 rows affected (1.01 sec)
Start a new log file again
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
Perform 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)
Viewing binary logs on MySQL server
Mysql> show binary logs;
+---------------------+-----------+
| Log_name | File_size |
+---------------------+-----------+
| vms00781-bin.000001 | 372 |
| vms00781-bin.000002 | 515 |
+---------------------+-----------+
Viewing events in the binary log
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 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
The default displays the events in the first binary log file that can be found, including the starting position of the event, the end position, the event type, the information, and so on. You can see that the first event describes the event for the format, the second is the query event, the transaction starts, the third is the table mapping event, the fourth is the delete operation for us, the fifth is the XID time is the action of the autocommit transaction, and the sixth is the log rotation event, and we execute the flush Logs the new log file is started.
To view the events in the 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 |
+---------------------+-----+-------------+-----------+-------------+---------------------------------------+
The command also contains additional 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 + limit 2, 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 equivalent to show MASTER LOGS
PURGE binary logs is used for binary logs such as:
PURGE BINARY LOGS to ' mysql-bin.010 ';
PURGE BINARY LOGS before ' 2008-04-02 22:46:26 ';
Reset MASTER and Reset SLAVE
The former empties all binary logs listed in the index file, resets the index file to null, and creates a new binary log file, typically used when master first starts. The latter causes slave to forget its copy location in the master binary log file, it deletes Master.info, Relay-log.info, and all the trunk log files and starts a new trunk log file so that a clean copy can begin. The slave replication thread must be closed before using the reset slave.
This way you can see the binary log files and the events in the files that exist on the server, but want to see what is in the file and should be able to recover the scene with the help of the Mysqlbinlog tool.
View:
Shell> mysqlbinlog [options] log_file ...
Like what:
Mysqlbinlog [Options] vms00781-bin.000001
The output is slightly different depending on the format of the log file and the options used by the Mysqlbinlog tool. The meaning of the specific content in the binary log file and the available options for Mysqlbinlog refer to the relevant manual. Here are some examples of situations that require special attention.
The format of the binary log file contains the row pattern, the statement pattern, and the mixed mode (that is, the server determines what type of log is logged), the event information in the statement-based log contains the executed statements, and so on, the event information in the row-based log contains the change information of the row. Both types of event information are logged in a mixed-mode log. To make it easier to see the events that recorded the row change information, the SQL statement that was executed at that time can use the-V (--verbose) option of the Mysqlbinlog tool, which will re-construct the row event into a pseudo-SQL statement that is commented out, if you want to see more detailed information
To give this option two times, such as-VV, which can contain comments about data types and meta-information, such as
Mysqlbinlog-v vms00781-bin.000001
MYSQLBINLOG-VV vms00781-bin.000001
In addition Mysqlbinlog and can read the binary log file from the remote server through the--read-from-remote-server option, this requires some connection parameters, such as--host,--password,--port,--user,-- Socket,--protocol, these parameters are valid only after the--read-from-remote-server has been specified.
Whether it is a local binary log file or a binary log file on a remote server, whether it is a row mode, a statement mode, or a mixed-mode binary log file, the Mysqlbinlog tool can be applied directly to MySQL server for Point-in-time, location, or database-based recovery.
Like what:
Mysqlbinlog vms00781-bin.000001 | Mysql-uusername-p
or write the binary log to the. sql file first, and then execute the files on the MySQL client, such as:
Mysqlbinlog vms00781-bin.000001 >/tmp/vms00781-bin.000001.sql
Mysql>source/tmp/vms00781-bin.000001.sql
Here are a few of the more critical parameters:
--database=db_name,-D db_name
This parameter causes Mysqlbinlog to output only the log events that are generated when the specified db_name is selected as the default database by the use command from the local binary log. Behaves like a mysqld--binlog-do-db command. If the parameter is specified more than once, only the last specified content is valid. The specific effect of the parameter depends on the binary log format, which guarantees consistency only when using the log format of the row pattern. A statement-based or mixed-mode binary log format cannot guarantee data consistency because of possible cross-library updates that cause--database parameters to behave differently.
Mysqlbinlog vms00781-bin.000001-d TestDB | Mysql-uusername-p
--force-read,-F
When this parameter is used, the Mysqlbinlog tool prints out warning when it reads an unrecognized log event, ignores the event and resumes execution, Mysqlbinlog stops without this parameter.
Mysqlbinlog vms00781-bin.000001-d testdb-f | Mysql-uusername-p
--no-defaults
Prevents the Mysqlbinlog tool from reading parameters from any profile, except for. MYLOGIN.CNF (for safe password saving)
Mysqlbinlog vms00781-bin.000001-d testdb-f--no-defaults| Mysql-uusername-p
--start-datetime=datetime
--stop-datetime=datetime
The upper set of parameters is used to specify the recovery start point and end point, which can be given together or separately, or mixed with--start-position,--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 upper set of parameters is used to specify the start and end position of the recovery, either together or separately or with--start-datetime,--stop-datetime
Mysqlbinlog vms00781-bin.000001-d testdb-f--no-defaults--start-position=nnnnnn--stop-datetime=datetime | Mysql-uusername-p
There is usually more than one binary log file that needs to be restored, so what should I pay attention to if more than one binary log file needs to be restored?
First, you can select the methods described above to redirect directly to the MySQL client or pilot into the. sql file and then execute the. sql file in a way that applies the binary log file. However, there is a hidden danger, and if the use of temporary tables is recorded in the binary log, then when the previous log is applied, the temporary table will be lost when the next binary log is applied in the new connection, causing an error. So, the safe way is that multiple binaries are executed at the same time.
Such as:
Mysqlbinlog vms00781-bin.000001 vms00781-bin.000002 vms00781-bin.000003--start-position=nnnnnn--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 concurrently,--start-position and--stop-position apply only to the first listed binary log file and the last listed binary log file
Of course, the output of multiple binary log files can be directed to the same. sql file at the end of the execution of the. sql file (for situations where the log volume is not too much)
