Restore the MySQL database log files

Restore the MySQL database tutorial log files

If the MySQL server has binary logs enabled, you can use the MySQL tutorial Binlog tool to recover data starting at a specified point in time (for example, from your last backup) until now or another specified point in time. "Mysqlbinlog: Utility for processing binary log files."
To recover data from a binary log, you need to know the path and file name of the current binary log file. You can generally find the path from the option file (that is, my.cnf or My.ini, depending on your system). If it is not included in the options file, it can be given as an option on the command line when the server is started. The option to enable binary logging is –log-bin. To determine the file name of the current binary log file, enter the following MySQL statement:
Show Binlog EVENTS G
You can also enter the following from the command line:
Mysql–user=root-pmy_pwd-e ' show Binlog EVENTS G '
Replace the password my_pwd with the server's root password.
1. Specify recovery time
For MySQL 4.1.4, you can specify the start and end times of the DateTime format through the –start-date and –stop-date options in the Mysqlbinlog statement. For example, suppose that at 10:00 today (today is April 20, 2006), execute the SQL statement to delete a large table. To recover the tables and data, you can restore the previous night's backup and enter:
Mysqlbinlog–stop-date= "2005-04-20 9:59:59″/var/log/mysql/bin.123456
| Mysql-u root-pmypwd
This command restores all data up to the date and time given in datetime format in the –stop-date option. If you do not detect the wrong SQL statement entered after a few hours, you may want to restore the activity that occurred later. Depending on these, you can use the date and time to run the Mysqlbinlog again:
Mysqlbinlog–start-date= "2005-04-20 10:01:00″/var/log/mysql/bin.123456
| Mysql-u root-pmypwd
In this row, the SQL statement that logs in from 10:01 will run. The two rows of the dump file and Mysqlbinlog on the eve of the combined execution can restore all data to one second before 10:00. You should check the log to make sure the time is accurate. The next section describes how to implement.
2. Specify the recovery location
You can also specify the log location by using Mysqlbinlog options –start-position and –stop-position without specifying a date and time. They function the same as the start and end dates, and the difference is given the number of positions from the log. Using log locations is a more accurate method of recovery, especially when many transactions occur concurrently due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find a time range that performs an unexpected transaction, but you should point the results back to a text file for inspection. The action method is:
Mysqlbinlog–start-date= "2005-04-20 9:55:00″–stop-date=" 2005-04-20 10:05:00″
/var/log/mysql/bin.123456 >/tmp/mysql_restore.sql
This command creates a small text file in the/tmp directory and displays the SQL statement when the incorrect SQL statement is executed. You can open the file in a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, you should comment. Mark position with Log_pos plus a number. After restoring the previous backup file using the location number, you should enter the following from the command line:
Mysqlbinlog–stop-position= "368312″/var/log/mysql/bin.123456
| Mysql-u root-pmypwd
Mysqlbinlog–start-position= "368315″/var/log/mysql/bin.123456
| Mysql-u root-pmypwd
The 1th line above reverts to all transactions until the stop position. The next line restores all transactions from the given starting position to the end of the binary log. Because the output of Mysqlbinlog includes the set timestamp statements that precede each SQL statement, the recovered data and the associated MySQL log reflect the original time of the transaction execution.