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

5 Answers 5


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

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


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

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


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

If you're using binlog with binlog_format=ROW which both is by default as of mysql 8.0,

and haven't done a full backup within the lifespan of available binlog files, i.e. complete mysqldump after the configured time of binlog_expire_logs_seconds before now, to perform the suggested Point-in-Time Recovery,

then you can still try to recover deleted rows with only a binlog file that covers these deleted events and turn them into insert events with this approach in https://github.com/lefred/MyUndelete/issues/5 :

it's able to replace the event type with the original awk script in http://thenoyes.com/littlenoise/?p=307:

BEGIN { regexp = "$^" }

  regexp = "^" substr($0, 1, 5) "[CSiy][ABCD]" substr($0, 8, 4);

$0 ~ regexp {
  n = $0;
  $0 = substr(n, 1, 5)
  $0 = $0 substr("BRhx", index("CSiy", substr(n, 6, 1)), 1)
  $0 = $0 substr("4567", index("ABCD", substr(n, 7, 1)), 1)
  $0 = $0 substr(n, 8) ;

{ print; }
sudo mysqlbinlog --start-position=148418658 --stop-position=484776616 binlog.000117 | awk -f undelete.awk | mysql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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