Mysqlbinlog four ways to recover data

Mysqlbinlog command

Role: Mysqlbinlog is used to parse the Binlog log of MySQL.

[[Email protected] data]# file mysql-bin.000001

Mysql-bin.000001:mysql Replication Log

[Email protected] data]#

Mysqlbinlog logs cannot be viewed directly with cat and need to be converted to normal files with the Mysqlbinlog command to view

Only use the Mysqlbinlog command to convert the Binlog log into a normal file before you can use this normal file for data recovery.

Parameter resolution:

–start-datetime=datetime

Reads from an event in the binary log where the 1th datetime is equal to or later than the DateTime parameter. The datetime value is relative to the local time zone on the machine that is running Mysqlbinlog. The value format should conform to the datetime or timestamp data type. For example:

Shell> mysqlbinlog–start-datetime= "2004-12-25 11:25:56″binlog.000003 this option can help point-to-point recovery.

–stop-datetime=datetime

Stop reading from the binary log for events that are equal to or later than the DateTime parameter in the 1th date time. See the –start-datetime option for a description of the datetime value. This option can help you recover in a timely manner.

–start-position=n

Start reading from the event that the 1th position in the binary log equals the n parameter.

–stop-position=n

Stops reading from the event that the 1th position in the binary log is equal to and greater than the N parameter.

-d Specifies to restore the log of a library in the Binlog log

##########################################

1. Example: Location-based recovery

Where drop tables Test1 This misoperation end_log_pos is 9917, a few below this ID, the ID of the operation before and after it is 9916,9918

We will perform a location recovery operation

To specify a location for stop, recover data from the log file and revert to stop at a specified point in time

Mysqlbinlog--stop-position= ' 9916 '/var/log/mysql-bin.000001 | Mysql-uroot-p

Given start specified position, restore to end of file from start specified position

Mysqlbinlog--start-position= ' 9918 '/var/log/mysql-bin.000001 | Mysql-uroot-p

If the start position point is given, and the stop stop point is specified, it is the end of the start point to the stop position.

##########################################

2, based on point-in-time recovery

Only give stop time, do not give start time, start recovery from the beginning of file, restore to stop time specified stopped

Mysqlbinlog-–stop-datetime= "20014-12-25 11:25:56"/var/log/mysql-bin.000001 | Mysql-uroot-p

The start time is only given, starting at the time specified by start and recovering to the end of the Binlog file.

Mysqlbinlog-–start-datetime= "20014-12-25 11:29:56"/var/log/mysql-bin.000001 | Mysql-uroot-p

If you give the start time and end data, that is the recovery start to the end of the stop point in time data

##########################################

3. Recover data from the specified library-D

-database=db_name,-d db_name

Lists only the entries for the database (local logs only). Just restore the data from the specified database, such as the following, just restore the database Lvnian data, the other library data will not be restored

Mysqlbinlog-d lvnian/var/log/mysql-bin.000001 | Mysql-uroot-p

This article is from the "Struggle Bar" blog, please be sure to keep this source http://lvnian.blog.51cto.com/7155281/1699627

Mysqlbinlog four ways to recover data