Mysql binary log File Recovery database _mysql

The function of binary log files

The MySQL binary log file is used to record all users ' operations on the database, that is, the SQL statements that record user operations on the database. If you have this file, when the database is an accident, you can see through this file to the user in this file recorded in the time period of the user to do the operation, and database backup with the use, you can reproduce the user operation, so that the database recovery.

The drawbacks of binary log files

When the binary log file is turned on, all records to the database operation are logged to this file.

So, after a long time opening, the log file becomes large and takes up disk space.

Restoring a database using binary log files

Open log File

MySQL default is not to open the function of the log file, we need to manually open. The operation is as follows:

1, open MySQL installation directory \my.ini (Windows system is My.ini file, Linux is my.cnf file).

2, find [mysqld] This label, the following line in this tag, add the statement: Log-bin=binary-log. In the above statement, the Log-bin description is to open the binary log file, Binary-log is the name of the binary log file.

(I set the test time: Log-bin=d:/mysql_log/logbin_ouyang.log)

3, restart the MySQL service. You can see the "Binary-log. Number" file under the MySQL installation directory \data folder,

such as binary-log.000001. After each restart of the MySQL service, the binary log file is regenerated,

The number number in the file name is incremented sequentially.

(The file name given in your last step, plus a serial number and a total file index file, as shown in Figure 1:)

Restore Database

Restoring a database with a log file requires a database backup (as long as it is backed up after the binary log file feature is turned on). When you restore a database, you can recover it either through time or through an operation point.

1, through time recovery methods are as follows:

If I turned on the binary log file feature at 2011-04-02 00:00:00, I backed up the database file. And in 2011-04-02 12:00:00 the database is abnormal and needs to be restored to 10:00:00 2011-04-02. The operation is as follows:

L Use Backup to restore the database to the state of 2011-04-02 00:00:00.

L ENTER the following command at 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 the actions that the user has done from the binary log to the start of the record until 2011-04-02 10:00:00. Corresponding to the stop-date, there is also the Start-date property, which allows you to set the start time of the record execution. You can also set both properties.

2, through the operation point recovery method is as follows:

On the command line, enter Mysqlbinlog D:\binary-log.000003 > D:\log.txt, and then open log.txt after execution,

Look inside the statement, you can find: before each operation, there will be a unique number, the following red Word display:

/*!*/;

# at 450/* Number 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 becomes larger as the number of operands increases. As with time is a mark, the statements recovered through the operation Point are as follows:

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

In this way, the system automatically executes the lowest numbered statement in the binary log to the numbered 450 statement.

Corresponding to the stop-position, there is the Start-position property, which can set the start number of the execution record.

Attach: (You should see)

1.0 Mysqlbinlog is a log operation tool with MySQL!

2.0 If the log file can be opened directly may be garbled, so we will generally:

D:\mysql_log>mysqlbinlog logbin_ouyang.000003 >log_3.txt copy down and open with Notepad!

You can clearly see the number of the operation or (line number)

3.0 If there is a wrong action! If you can! You should stop the database immediately! And then restore the data!

4.0 If you just remove a statement, you can choose to restore two times, as shown in the figure:

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 the could not read entry at the offset 1478:error in log format or read Error occurs. A similar mistake!

Please check that your number is correct! Note that this number is not the line number that the editor shows. Oh, # at 450/

Often delete, update wrong! All give yourself a document! Of course, I also hope you will never use this document! Have a nice weekend! (Typesetting some disorderly ah!) Forgive me! ）