Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to restore some lost data , but I am not having its backup .So I am trying to restore from the binary log .Please explain how to restore from the binary log in mysql .

share|improve this question

3 Answers 3

Do you know from which binlog files you need to restore and how many binlog files there are?

Using mysqlbinlog utility we can view the binlog file content.

If it's a single file you can recover using:

mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pReset123

If it's multiple files then just extract all content to one .sql file and directly restore it:

mysqlbinlog /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

To append second binlog content to allbinlof.sql file use below command:

mysqlbinlog /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql

You can also exclude certain statements using option of mysqlbinlog. Try this below for options:

mysqlbinlog --help
share|improve this answer

Use below steps to restore data from binary logs or use this link to understand restoration process: MySQL Binary Log Restoration

First, restore database from the latest backup

mysql -u username -ppassword database_name < dump.sql

Then do either of:

  • Restore rest of data from binary log.

    If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server.

    mysqlbinlog mysql_bin.000001 | mysql -u root -ppassword database_name
    mysqlbinlog mysql_bin.000002 | mysql -u root -ppassword database_name
    

    or:

    mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_name
    
  • Restore data on basis of time

    mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
                --stop-datetime="2005-04-20 9:59:59" mysql_bin.000001 \
                | mysql -u root -ppassword database_name
    
  • Restore data on basis of position

    mysqlbinlog --start-position=368315 \
                --stop-position=368312 mysql_bin.000001 \
                | mysql -u root -ppassword database_name
    
share|improve this answer

You can use -v option to make .sql file in READABLE format.

For Example

mysqlbinlog -v /var/lib/mysql-bin.000016 > /logs/allbinlog.sql

mysqlbinlog -v /var/lib/mysql-bin.000016 >> /logs/allbinlog.sql
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.