What do you do if you accidentally perform a wrong operation on your database and don’t have a recent backup? This is a common problem many coders face.
I accidentally dropped a database today, but my last backup was from a week ago. The only way to recover was through mysqlbinlog. Here’s the solution:
If your MySQL server has binary logging enabled, you can use the mysqlbinlog tool to restore data starting from a specified point in time (for example, since your last backup) up to the present or another specified point in time.
For information on enabling binary logging, see Section 5.11.3, “The Binary Log”. For detailed information on mysqlbinlog, see Section 8.6 of the MySQL manual, “mysqlbinlog — Utility for Processing Binary Log Files”.
To recover data from the binary log, you need to know the path and filename of the current binary log file.
You can generally find the path from the configuration file (typically my.cnf on Linux, my.ini on Windows, depending on your system). If it is not included in the option file, it can be given as an option on the command line when the server starts.
The option to enable binary logging is –log-bin.
To determine the name of the current binary log file, enter the following MySQL statement:
SHOW BINLOG EVENTS /G;
Or you can enter the following from the command line:
mysql –user=root -pmypasswd -e ‘SHOW BINLOG EVENTS /G’ Replace the password mypasswd with the root password for your MySQL server.
For example, suppose the log filename obtained is:
mysql-bin.000001 1. Specifying a Recovery Time For MySQL 5.1.54, you can specify start and end times in DATETIME format using the –start-date and –stop-date options in the mysqlbinlog statement.
For example, suppose you accidentally executed an SQL statement that dropped a table today at 14:02 (today being March 15, 2012), but you discover there’s no recent backup (of course, this is only a development environment, not a formal production environment; production environments should always have scheduled data backups). To restore the table and data, you can recover the backup for a specified time via mysqlbinlog by entering:
mysqlbinlog –stop-date=”2012-03-15 14:02:00″ /data1/log/mysql/mysql-bin.000001 | mysql -u root -pmypasswd
This command will recover all data up to the date and time given in DATETIME format in the –stop-date option.
If you did not detect the erroneous SQL statement you entered, you might want to recover subsequent database activity that occurred later.
Based on this, you can run mysqlbinlog again with a start date and time:
mysqlbinlog –start-date=”2012-03-15 00:01:00″ /data1/log/mysql/mysql-bin.000001 | mysql -u root -pmypasswd
In this line, SQL statements logged from 00:01 early this morning will run. Combining the dump file from the previous night with these two mysqlbinlog lines can restore all data up to one second before 00:01 this morning.
You should check the logs to ensure the times are exact. The next section describes how to do this.
2. Specifying a Time Range Recovery Use mysqlbinlog –start-date and –stop-date to recover database activity records for a specific time range, as follows:
mysqlbinlog –start-date=”2012-03-09 02:00:00″ –stop-date=”2012-03-15 14: