15

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

9

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
8

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
    
1
  • 3
    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
5

First of all, it should be noted that the binlog files contain changes of a database for a time interval, but they do not include ALL data, and it makes no sense to restore only the binlog files. As a rule, to restore a database you need a full backup, and all binlog files that were created after a full backup. To restore data from binlog files, you need to perform a full backup correctly.

mysqldump -u root -ppassword --flush-logs --delete-master-logs  --all-databases > full-backup.sql

There are two important parameters in the command:

--flush-logs - this parameter starts writing to a new binlog file.

--delete-master-logs - removes old binlog files

Thus, in the folder with binary logs there will be only binlog files created after a full backup.

To restore data, firstly you have to restore a full backup

mysql -u root -ppassword  < full-backup.sql

And then restore all the binlog files via mysqlbinlog utility, specifying the paths to the binlog files (the order is important).

mysqlbinlog  mysql_bin.000023 mysql_bin.000024 mysql_bin.000025 | mysql -u root -ppassword

--

Note, if you use master\slave replication, then you should not use --delete-master-logs when performing a full backup on the master server, as this can break replication

But in this case, you have to remember which particular binlog file was created the --flush-log parameter, and for the restore process use it and all the binlog files that were created later.

More details about how to backup and restore incremental backups can be found at this article

1
  • 1
    I'm super grateful for this answer as I had totally missed the fact that the binlog itself is useless without knowing the 'starting point'. If you're not doing your backups in a way that flushes the logs, there's no point keeping them. Jun 10 '21 at 6:47
3

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.