content_image/839726.jpg content_image/839727.jpg
## 7.3.2 Using Backups for Recovery 

Now, suppose that we have a catastrophic unexpected exit on Wednesday at 8 a.m. that requires recovery from backups. To recover, first we restore the last full backup we have (the one from Sunday 1 p.m.). The full backup file is just a set of SQL statements, so restoring it is very easy: 

shell> mysql < backup_sunday_1_PM.sql 

At this point, the data is restored to its state as of Sunday 1 p.m.. To restore the changes made since then, we must use the incremental backups; that is, the gbichot2-bin.000007  and gbichot2-bin.000008 binary log files. Fetch the files if necessary from where they were backed up, and then process their contents like this: 

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql 

We now have recovered the data to its state as of Tuesday 1 p.m., but still are missing the changes from that date to the date of the crash. To not lose them, we would have needed to have the MySQL server store its MySQL binary logs into a safe location (RAID disks, SAN, ...) different from the place where it stores its data files, so that these logs were not on the destroyed disk. (That is, we can start the server with a --log-bin  option that specifies a location on a different physical device from the one on which the data directory resides. That way, the logs are safe even if the device containing the directory is lost.) If we had done this, we would have the gbichot2-bin.000009  file (and any subsequent files) at hand, and we could apply them using mysqlbinlog  and mysql  to restore the most recent data changes with no loss up to the moment of the crash: 

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql 

For more information about using mysqlbinlog  to process binary log files, see Section 7.5, “Point-in- Time (Incremental) Recovery” . 

## 7.3.3 Backup Strategy Summary 

In case of an operating system crash or power failure, InnoDB  itself does all the job of recovering data. But to make sure that you can sleep well, observe the following guidelines: 

•Always tun the MySQL server with binary logging enabled (that is the default setting for MySQL 8.0). If you have such safe media, this technique can also be good for disk load balancing (which results in a performance improvement). 

•Make periodic full backups, using the mysqldump  command shown earlier in Section 7.3.1, “Establishing a Backup Policy” , that makes an online, nonblocking backup. 

•Make periodic incremental backups by flushing the logs with FLUSH LOGS  or mysqladmin flush- logs . 

## 7.4 Using mysqldump for Backups 

<img src='content_image/839726.jpg'>

<img src='content_image/839727.jpg'>

## Tip 

Consider using the MySQL Shell dump utilities, which provide parallel dumping with multiple threads, file compression, and progress information display, as well as cloud features such as Oracle Cloud Infrastructure Object Storage streaming, and MySQL Database Service compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or a MySQL Database Service DB System using the MySQL Shell load dump utilities. Installation instructions for MySQL Shell can be found here. 

