Use mysqlbinlog Tools based on timely restoration of position or point



MySQL Backup generally takes the form of full backup plus log backup . Let's run a full backup , Every day . Run a binary log backup every hour .

In this way MySQL Server After failure, it can use full backup and log backup to recover data to any location or time before the last binary log backup .

There are various tools for full backup and log backup , Each has its own characteristics , There is no description here . This article mainly explains that after replying to full backup , How to use the backup binary log to recover the data to the specified location or time point .

Here's a very important tool ——mysqlbinlog, Dedicated to viewing binary logs . Let's use some examples to illustrate the problem :

Let's first see how to MySQL Server View the binary log files and events in the file directly in .

Empty first MySQL Server All binary logs on

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)



see MySQL Server Binary log on

mysql> show binary logs;

+---------------------+-----------+

| Log_name            | File_size |

+---------------------+-----------+

| VMS00781-bin.000001 |       120 |

+---------------------+-----------+

Look at the 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 |

+---------------------+-----+-------------+-----------+-------------+---------------------------------------+

Run some DML operation

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)

Run some DML operation

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)



see MySQL Server Binary log on

mysql> show binary logs;

+---------------------+-----------+

| Log_name            | File_size |

+---------------------+-----------+

| VMS00781-bin.000001 |       372 |

| VMS00781-bin.000002 |       515 |

+---------------------+-----------+



Look at the 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             |

+---------------------+-----+-------------+-----------+-------------+---------------------------------------+

By default, the events in the first binary log file that can be found are displayed , Including where the event started 、 End position 、 Event type 、 Information, etc . Be able to see , The first event is the descriptive event . The second is query Events , Business begins ; The third is the table mapping event , The fourth is the delete operation we run . The fifth is Xid Time is the act of committing a transaction , The sixth is log rotation events , We run it flush logs Caused by opening a new log file .



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 includes 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,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 It's 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 is empty index All binary logs listed in the file , Reset index The file is empty . And create a new binary log file , Commonly used in MASTER First boot . The latter makes SLAVE Forget it's in MASTER Copy location in binary log file , It will delete master.info、relay-log.info And all relay log files and start a new relay log file . In order to start a clean copy .

In the use of RESET SLAVE You need to close it before you start SLAVE Replication thread .





You can see server The binary log file and the events in the file , But if you want to see the details in the file and restore the scene, you have to use mysqlbinlog This tool .

see :

shell> mysqlbinlog [options] log_file ...

example :

mysqlbinlog [options] VMS00781-bin.000001

The output content depends on the format of the log file and mysqlbinlog The options used by the tools are different and slightly different . The meaning of the details in the binary log file and mysqlbinlog For the available options, please refer to the relevant manual . Here are some situations that need special attention .

The format of binary log file includes line mode 、 Sentence mode and mixed mode ( That is to say server Decide what kind of log to record under what circumstances ). The event information in the statement based log includes the running statement, etc , The event information in the line based log includes the change information of the line . Both types of event information are recorded in the mixed mode log . In order to easily see what kind of events recorded row change information were running at that time SQL Statement can use mysqlbinlog The tool -v(--verbose) Options , This option reconstructs the row event into a pseudo SQL sentence , Suppose you want to see more specific information

To give the option twice, such as -vv, This can include some data types and meta information , Such as

mysqlbinlog -v VMS00781-bin.000001

mysqlbinlog -vv VMS00781-bin.000001



in addition mysqlbinlog And be able to pass --read-from-remote-server Option to read the binary log file from the remote server , At this time, you need some external connection parameters . Such as --host,--password ,--port,--user,--socket.--protocol etc. . These parameters are available only when specified --read-from-remote-server After it works .

Whether it's local binary log files or remote server Binary log file on , Whether it's line mode 、 Statement mode or mixed mode binary log file , By mysqlbinlog The tools can be directly applied to MySQL Server Based on the point in time 、 Location or database recovery .

example :

mysqlbinlog  VMS00781-bin.000001 | mysql -uusername -p



Or write the binary log to .sql In the document . And then in mysqlclient Run these files , example :

mysqlbinlog  VMS00781-bin.000001 > /tmp/VMS00781-bin.000001.sql

mysql>source /tmp/VMS00781-bin.000001.sql



Here are some key parameters :

--database=db_name, -d db_name

This parameter makes mysqlbinlog Output only the specified... From the local binary log db_name By use Log events generated when the command is selected as the default database . Behavior similar to mysqld Of --binlog-do-db command . If the parameter is specified more than once, only the last specified content is valid .

The detailed impact of the parameters depends on the binary log format , This parameter can guarantee consistency only when the log format of row mode is used . In the binary log format based on statement or mixed mode, there may be cross database update --database Parameters show different behaviors , So there's no guarantee of data consistency .

mysqlbinlog  VMS00781-bin.000001  -d testDB | mysql -uusername -p



--force-read, -f

After using this parameter mysqlbinlog When the tool reads unrecognized log events, it prints out warning, Ignore events and continue to run . Without this parameter mysqlbinlog Will stop .

mysqlbinlog  VMS00781-bin.000001  -d testDB -f | mysql -uusername -p



--no-defaults

prevent mysqlbinlog The tool reads parameters from whatever configuration file , .mylogin.cnf With the exception of ( For safe storage password)

mysqlbinlog  VMS00781-bin.000001  -d testDB -f --no-defaults| mysql -uusername -p



--start-datetime=datetime

--stop-datetime=datetime

The above set of parameters is used to specify the start and end time of recovery . Can be given together or separately , It can also be used with --start-position,--stop-position A mixture of

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 positions of the recovery , It can be given together or separately or with --start-datetime.--stop-datetime A mixture of

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 to restore , So if there are multiple binary log files that need to be restored , What to pay attention to ?

First . You can choose to redirect directly to mysqlclient Or import to first .sql File and run .sql The binary log file is applied one by one .

But there is a hidden danger . And , Suppose the binary log records the use of temporary tables , So when the last log is applied , When the next binary log is applied to a new connection, the temporary table is lost , Cause a mistake . therefore . The safe way is to run multiple binaries 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 run at the same time .--start-position and --stop-position Only apply to the first listed binary log file and the last listed binary log file, respectively



Of course, it is also possible to make multiple binary output log files the same .sql Last , Run the file .sql file ( It works with a small number of logs )

Copyright notice : This article is an original blog article , Blog , Without consent , Shall not be reproduced .

Use mysqlbinlog Tools based on the timely recovery of data location or point of more related articles

  1. utilize IDM Download tool ESA Upper Sentinel data

    Because of the closure of the U.S. government ,NASA,USGS,NOAA And other non core departments of the organization are also on holiday , Not available for the time being Sentinel Data download , And directly from ESA It's slower to download data than a snail , Fortunately, I found IDM Tools . Use a browser or wget work ...

  2. Data misoperation , Delete the library and run away ? Teach you how to use ApexSQLLog Tools from SQLServer Log recovery data !

    A few days ago, my colleagues accidentally misoperated , take SQLServer A status field of a table in the library is changed into a unified status , Because it is update So the original relevant state cannot be determined . When this happens, my little friend's back A cool , I think it's probably ...

  3. ORACLE11g There's no control over how the file goes through rman The detailed actual process of data backup and recovery

    1. The grim reality that vice presidents need to recover The head of the Information Department of the group headquarters called me and said that in order to find the records of a year ago , So it's a year ago 2015 year 5 month 1 Day of data recovery . and 2016 At the beginning of the year, because of the migration , So some files may be lost , There is nothing but rma ...

  4. from WEB SERVICE Return a large amount of data on DATASET

    Some time ago when I was working on a project , I'm going to go through WEB SERVICE A large amount of data is returned from the server DATASET, Of course , In addition to being displayed on the page , It is possible to use this data for other operations on the client side . I checked the articles on the website , Asked a question ...

  5. xtrbackup Backup , And data recovery

    Simulate scheduled task backup data on Sunday , The data changed on Monday , Monday crontab Scheduled task incremental backup , The data changed on Tuesday , Tuesday crontabl Incremental backup , Then someone deleted the library , We do data recovery simulation crontab The scheduled tasks in are available on Sunday [ro ...

  6. mysqlbinlog The tool explains in detail based on log recovery

    If a large number of binary logs are generated every day , If these logs are not cleaned up for a long time , It's going to be a huge waste of disk space , So regular log cleaning is DBA maintain mysql It's an important part of our work 1)RESET MASTER Look in the folder where the logs are stored ...

  7. MYSQL log enable , Check the log , utilize mysqlbinlog Tool recovery MySQL database 【 Reprint 】

    from MYSQL log enable , Check the log , utilize mysqlbinlog Tool recovery MySQL database - _ quiet - Blog Garden http://www.cnblogs.com/xionghui/archive/2012/ ...

  8. [ turn ] Use MYSQLBINLOG To recover data

      Use MYSQLBINLOG To recover data 2009-04-05 12:47:05 label :mysql mysqlbinlog recovery database data Original works , Allowed to reprint , When reprinting, please make sure to indicate the article in the form of hyperlink ...

  9. Use mysqlbinlog Tools for location or point in time based data recovery

    Use mysqlbinlog Tools for location-based or point in time recovery MySQL Generally, full backup plus log backup is adopted for backup , For example, perform a full backup once a day , Perform a binary log backup every hour . In this way MySQL Server Failure can make ...

Random recommendation

  1. build Cordova development environment

    Cordova What is it? Apache Cordova It's a set of equipment API, Allow developers of mobile applications to use JavaScript To access the functions of local devices , Like a camera . Accelerometer . It can work with UI frame ( Such as jQuery Mobile ...

  2. Chapter four ngrok Use

    website ( download ):http://www.ngrok.cc/ register :http://www.ngrok.cc/login Management backstage :http://www.ngrok.cc/user.html Create a tunnel , Create a tunnel ...

  3. MySQL_update Same table

    update tb1 inner join(select type, count(*) as cntfrom tb1 group by type)as der using(type)set tb1.c ...

  4. map.entry<k,v> Minor usage ( turn )

    Have you ever been from Map Get the keyword and then get the corresponding value to feel tired ? Use Map.Entry class , You can get all the information at the same time . The standard Map The access method is as follows : Set keys = map.keySet( ...

  5. Leetcode_136_Single Number

    This article is a summary of my study , Welcome to reprint, but please indicate the source :http://blog.csdn.net/pistolove/article/details/42713315 Given an array of inte ...

  6. Word Skill set

    1. How to be in Word Fast input superscript and subscript in Subscript ~~ Press and hold at the same time :Ctrl and = key : Superscript ~~ Press and hold at the same time :Ctrl and Shift and + key : 2. How to delete Word Blank pages generated in Set up The paragraph Medium     Fixed value   ...

  7. hello2

    String username = request.getParameter("username");// Get parameter value if (username != null && ...

  8. li direct 1px The reason for pixels

    1. Because of the blank node ( Because of Enter cause ),li The problem can be solved without a line break . Internet Explorer The blank nodes generated between nodes are ignored , Other browsers won't ignore ( By detecting the node type , Filter child nodes ) 2. Perfect solution ...

  9. Submatrix Sum

    Given an integer matrix, find a submatrix where the sum of numbers is zero. Your code should return ...

  10. 6.capacity scheduler

      1. precondition To use yarn Of capcitiy Scheduler , Must be turned on yarn Of ACLs, Otherwise, the queue ACLs Settings don't work Turn on yarn ACLs: # hadoop: core-site.xml hadoop ...