Like directly on the server or database operations brothers under your worth collecting! Otherwise you "one hundred tear shall not ride jie" (second line you know!)
-----( Course, I also search for information online! Their next test!)
The role of the binary log file
mysql binary log file to record all users of database operations, database operations that records the user's sql statement. If you have this file, when the accident database, you can see through this file to the user documentation of the period of time in this operation made by the user, and database backup and then used in conjunction, can reproduce the user, the database recovery.
The drawbacks of the binary log file
Binary log file is opened, all the records of database operations will be recorded into this file,
So, when a long time after the opening, the log files become very large, taking up disk space.
To restore the database using the binary log file
Open the log file
mysql default is not open log file function, we need to manually open. As follows:
1, open the mysql installation directory \ my.ini (windows system is the my.ini file, linux is the my.cnf file).
2, find the [mysqld] tag, the following line in this tab, add the statement: log-bin = binary-log. The above statement, log-bin description to open the binary log file, binary-log is a binary log file name.
(Test when I set: log-bin = d: / mysql_log / logbin_ouyang.log)
3, restart the mysql service. Installation directory in the mysql \ data folder to see "binary-log. Figures" file,
Such as binary-log.000001. After each restart a mysql service will re-generate the binary log file,
Filename numbered in ascending order.
(Your file name is given on the step, added a file number and an overall index file as shown in Figure 1:)
Restore the database
Use the log file to restore the database must have a database backup (as long as enabled in the binary log file after the backup on the line). Restore the database, you can recover through time can also be restored by operating point.
1, by the time recovery are as follows:
At 2011-04-02 00:00:00 If I open a binary log file time function, and back up the database files. 2011-04-02 12:00:00 in the database and an exception occurs, the need to restore to 2011-04-02 10:00:00. Is as follows:
l back to the database with the backup time 2011-04-02 00:00:00 status.
l at the command line type the following command:
mysqlbinlog - stop-date = "The time you want to restore" the 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 will automatically start from the binary log record as of 2011-04-02 10:00:00, users have done all operations. Correspond with the stop-date, as well as start-date attribute can be set to perform recording start time. Both properties can be set.
2, through the operating point recovery method is as follows:
In the command line, type mysqlbinlog D: \ binary-log.000003> D: \ log.txt, after the implementation of open log.txt,
See which statements can be found: Before each operation, there will be a unique number, as shown in red:
# 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 increased with the larger operand. And time as a mark, to restore the operating point by the following statement:
mysqlbinlog - stop-position = "450" mysql installation directory \ data \ binary-log.000001 | mysql-u root-p
In this way, the system will automatically execute the binary log statements to the lowest numbered statement number 450.
Correspond with the stop-position, as well as start-position attribute, you can set the start of the implementation record number.
Attachment: (you should see)
1.0 mysqlbinlog is a logging operation that comes with mysql tool!
2.0 If you directly open the log file may be garbled, so we generally will:
D: \ mysql_log> mysqlbinlog logbin_ouyang.000003> log_3.txt copy down and then use Notepad to open!
You can clearly see the number of operations or (line number)
3.0 If an error occurs the operation! If you can! You should immediately stop the database! Then restore the data!
4.0 If you just remove a single statement, you can choose to recover twice, as shown:
D: \ mysql_log> mysqlbinlog logbin_ouyang.000009 - stop-position = 10625 | mysql-uroot
D: \ mysql_log> mysqlbinlog logbin_ouyang.000009 - start-position = 10843 | mysql-uroo
5.0 If Could not read entry at offset 1478: Error in log format or read error. Similar mistakes!
Please check your numbers are correct! Note that this number is not certain that the editor displays the line number Oh, # at 450 /
Often delete, update wrong! All to yourself a document! Of course I also hope you never do not have access this document! Weekend! (Layout and some random Ha! Forgive me!)