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

38 comments

  • Comment Link Agustin Wednesday, 14 March 2018 23:43 posted by Agustin

    Hi there this is somewhat of off topic but I was wondering if blogs use WYSIWYG editors or if you have to manually code
    with HTML. I'm starting a blog soon but have
    no coding knowledge so I wanted to get advice from someone with experience.
    Any help would be greatly appreciated!

  • Comment Link Jeremy Wednesday, 14 March 2018 22:20 posted by Jeremy

    Hi, i feel that i noticed you visited my weblog thus i came to return the prefer?.I'm trying to find things
    to improve my website!I guess its adequate to make use of a few of your ideas!!

  • Comment Link Michel Thursday, 08 March 2018 19:42 posted by Michel

    Very nice post. I just stumbled upon your weblog and wished to say that I have truly enjoyed browsing your blog posts.
    In any case I'll be subscribing to your feed and I hope you write
    again very soon!

  • Comment Link Bertie Wednesday, 07 March 2018 21:29 posted by Bertie

    I was very happy to discover this great site. I wanted to thank you for
    ones time just for this fantastic read!! I definitely really liked every little bit of
    it and I have you saved as a favorite to look at new information on your site.

  • Comment Link Lucy Saturday, 03 March 2018 02:43 posted by Lucy

    Hi there, I enjoy reading all of your post. I wanted to write a little comment to support you.

  • Comment Link Samara Friday, 23 February 2018 02:17 posted by Samara

    About time I get to that 2x4 Christmas tree tutorial!

  • Comment Link Edison Tuesday, 20 February 2018 07:08 posted by Edison

    Tow Truck & Wrecker Towing Service Jersey Village TX.

  • Comment Link Rosalina Tuesday, 20 February 2018 05:38 posted by Rosalina

    Apply lubricant for storage doors to the spring.

  • Comment Link Randall Sunday, 18 February 2018 22:15 posted by Randall

    Contact our yacht brokers for help.

  • Comment Link Hattie Sunday, 18 February 2018 17:06 posted by Hattie

    Award winning landscape design, set up & upkeep.

Leave a comment

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