Thursday, 28 May 2015 00:00

Recovery of MySQL database from binary logs

Rate this item
(0 votes)

b2ap3_thumbnail_iStock_000018560014XSmall.jpgSometimes databases are lost. Human factor and stuff like that… If you didn’t backup (you better do) or the backup is old, don’t despair, there is still a chance of recovering the information which has been lost.

Starting from 4.1.3 version, MySQL writes all queries that change the data into a binary log. But this operation can be truned off by default. For example, after installation my home page didn’t have binary logs. I have turned it on by uncommenting the line log_bin = /var/log/mysql/mysql-bin.log in configuration file (my.cnf). Although server’s binary log was on from the very beginning and the parameter in the settings looks different: log-bin = mysql-bin. Logging is also on if MySQL was launched with the key --log-bin[=file_name].

The redo log is usually located in the files like mysql-bin.digits. If its full pathname is not set in the settings, then these files will be located in the directory, in which MySQL keeps databases.

Perhaps, you won’t need all log files to recover the database, separate tables or even specific records. Check their modification date.

In order to work with binary logs, you will need a mysqlbinlog tool. It comes along with MySQL server set. This tool processes log files and displays a perfectly usabe SQL code directly on the console. The result can be routed to the file (mysqlbinlog [parameters] [log_files] > file.sql), or directly to MySQL (mysqlbinlog [parameters] | mysql [parameters]) or point to the file to be retrieved in tool parameters. For example:

mysqlbinlog -s -d db_name -r out.sql mysql-bin.000012

In this case a mysql-bin.000012 file will be processed (from the current directory), the output will move to out.sql, and commands will be displayed which relate to the madification of db_name database. By the –s parameter we have resrticted the display of any other service information.

Another example:

mysqlbinlog -s -d db_name -u user_name --start-datetime="2009-01-23 21:10:00" -t mysql-bin.000001 > out.sql

Here, among other things, we confine ourselves to displaying the queries executed by the user_name starting from the data specified. Parameter -t tells the tool that logs after the mysql-bin.000001 file also have to be processed. Please note, that if you will reroute the output directly into MySQL, than the redo log is going to be filled with fresh entries which may lead to circularity. You can avoid that by adding a –D parameter, restricting logging. The restriction will be accessible only if it would be ran from the root.

The remaining parameters can be viewed in the same way as in any other console program:

mysqlbinlog --help

As a matter of fact, the output redirection into the muscle is not recommended. In addition to that, if you use an intermediate SQL file, you should be able to delete a notorious DROP DATABASE, that has led to data loss. That file could be useful in the future as well.

Run the followng command to recover the database from the SQL file:

mysql -u user_name -p < out.sql

Don’t forget to backup:

mysqldump db_name > backup.sql

Or even like this:

mysqldump -u user_name --password -A > backup.sql

Last modified on Thursday, 28 May 2015 16:35
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.