11

How can I restore my files and photos from binary files?

7

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
4

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
    
  • 2
    The first example of restoring more than one binary log is specifically unsafe, as noted in the documentation. – Reuben Dec 18 '15 at 0:10
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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