How can I restore my files and photos from binary files?
Do you know from which binlog files you need to restore and how many binlog files there are?
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:
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
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