Mysql binary log file Restore database, mysql binary log file

Mysql binary log file Restore database, mysql binary log file

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 !)

Mysql binary logs restore data of one of the databases

Create a temporary database and import the backup data to restore a separate database,
Mysqlbinlog has a -- databases parameter that can be specified to parse the SQL related to the specified database. You can use this SQL to restore data.

Can I create multiple binary log files so that each database corresponds to its own binary log files ????
This function is not available in mysql. mysqlbinlog records the changes of the entire mysql instance and cannot record logs for individual databases.

How does mysql restore data through binary log files?

Yes. Find the table creation point and find the table Deletion Point!