How to Restore Data from Binary Log?

Step 1 : Restore database from the latest backup

mysql -u username -ppassword database_name < dump.sql

Step 2 : 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

OR

Step 2 (i) : 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

OR

Step 2 (ii): Restore data on basis of position

Mysqlbinlog –start-position=368315 –stop-position=368312 mysql_bin.000001 | mysql -u root -ppassword database_name

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

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


<<Click here to see all posts>>


Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.