Mysql binary log file restore database _ MySQL

Source: Internet
Author: User
If you like to operate directly on servers or databases, you deserve to add them to your favorites! Otherwise you will suffer. ----- (Of course, I also search for materials online! But I passed the test !) Roles of binary log files

The mysql binary log file is used to record all user operations on the database, that is, the SQL statements that the user operates on the database. If this file exists, you can view the operations performed by the user during the time period recorded in this file in case of a database accident, and then use it with the database backup, to reproduce user operations and restore the database.

Disadvantages of binary log files

After the binary log file is enabled, all database operation records will be recorded in this file,

Therefore, after enabling it for a long time, the log file will become very large and occupy disk space.

Restore a database using a binary log file

Enable log files

By default, mysql does not enable the log file function, which must be manually enabled. The procedure is as follows:

1. open the mysql installation directory \ my. ini (my. ini file in windows and my. cnf file in linux ).

2. locate the [mysqld] label and add the statement log-bin = binary-log under this label. In the preceding statement, log-bin indicates that binary log files must be enabled. binary-log is the name of binary log files.

(Log-bin = d:/mysql_log/logbin_ouyang.log)

3. restart the mysql service. You can see the "binary-log. digital number" file under the mysql installation directory \ data folder,

Like binary-log.000001. The binary log file will be generated every time the mysql service is restarted,

The numbers in the file names increase sequentially.

(The name of the file you gave in the previous step, with a serial number and a total file index file 1 :)

Restore database

To use log files to restore a database, you must have a database backup (as long as it is backed up after the binary log file function is enabled ). You can restore the database by time or by operation points.

1. the time recovery method is as follows:

If I have enabled the binary log file function and backed up the database files at 00:00:00, January 2. The database encountered an exception at 12:00:00 and needs to be restored to 10:00:00. The operation is as follows:

L back up the database to the status at 00:00:00.

L enter the following command in the command line:

Mysqlbinlog -- stop-date = "The time you want to recover" log file directory \ binary-log.000001 | mysql-u root-p (D: \ mysql_log> mysqlbinlog logbin_ouyang.000007 -- stop-date = "2011-10-23 15:05:00" | mysql-uroot-proot)

In this way, the system automatically performs all operations performed by the user from the time the binary log has been recorded to 10:00:00, January 2. The start-date attribute corresponds to the stop-date attribute. you can set the start time of the execution record. You can also set both attributes.

2. the restoration method is as follows:

Enter mysqlbinlog D: \ binary-log.000003> D: \ log.txtin the command line, and then open log.txt,

View the statement in it and you will find that each operation has a unique number, which is displayed in the following red letter:

/*! */; # At 450/* No. 450 */#110402 15:31:50 server id 1 end_log_pos 529 Query thread_id = 2 exec_time = 0 error_code = 0 set timestamp = 1301729510 /*! */; Drop table 'jwc'

This number increases as the number of operations increases. Like time, it is a mark. the Statement restored by the Operation point is as follows:

Mysqlbinlog -- stop-position = "450" mysql installation directory \ data \ binary-log.000001 | mysql-u root-p

In this way, the system will automatically execute the statement with the lowest number in the binary log to the 450 statement.

Corresponding to stop-position, and the start-position attribute, you can set the start number of the execution record.

Appendix: (you should check it out)

1.0 mysqlbinlog is a log operation tool that comes with mysql!

2.0 if you open the log file directly, it may be garbled, so we generally:

D: \ mysql_log> mysqlbinlog logbin_ouyang.000003> Copy log_3.txt and open it in Notepad!

You can clearly see the operation number or (row number)

3.0 if an error occurs! If yes! You should immediately stop the database! Then recover the data!

4.0 if you just remove a statement, you can choose to restore it twice,

D:\mysql_log>mysqlbinlog logbin_ouyang.000009 --stop-position=10625|mysql -uroot     -proot   D:\mysql_log>mysqlbinlog logbin_ouyang.000009 --start-position=10843|mysql -uroo     t –proot

5.0 if a cocould not read entry at offset 1478: Error in log format or read error. Similar Error occurs!

Check whether your ID is correct! Note that this number is not the line number displayed by some editors. # at 450/

Frequent delete and update errors! All give yourself a document! Of course, I also hope that you will never use this document! Have a good weekend! (The layout is messy! Forgive me !)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.