Establishing a Backup Policy InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections For the cases of file system crashes or hardware problems, we can assume that the MySQL disk data is not  available after a restart. This means that MySQL fails to start successfully because some blocks of disk data are no longer readable. In this case, it is necessary to reformat the disk, install a new one, or otherwise correct the underlying problem. Then it is necessary to recover our MySQL data from backups, which means that backups must already have been made. To make sure that is the case, design and implement a backup policy. To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MySQL with several tools. For example, MySQL Enterprise Backup can perform a physical backup of an entire instance, with optimizations to minimize overhead and avoid disruption when backing up  InnoDB  data files;  mysqldump  provides online logical backup. This discussion uses  mysqldump . Assume that we make a full backup of all our  InnoDB  tables in all databases using the following command on Sunday at 1 p.m., when load is low: shell>  mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql The resulting  .sql  file produced by  mysqldump  contains a set of SQL  INSERT  statements that can be used to reload the dumped tables at a later time. This backup operation acquires a global read lock on all tables at the beginning of the dump (using  FLUSH TABLES WITH READ LOCK ). As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the  FLUSH  statement is issued, the backup operation may stall until those statements finish. After that, the dump becomes lock-free and does not disturb reads and writes on the tables. It was assumed earlier that the tables to back up are  InnoDB  tables, so  --single-transaction  uses a consistent read and guarantees that data seen by  mysqldump  does not change. (Changes made by other clients to  InnoDB  tables are not seen by the  mysqldump  process.) If the backup operation includes nontransactional tables, consistency requires that they do not change during the backup. For example, for the  MyISAM  tables in the  mysql  database, there must be no administrative changes to MySQL accounts during the backup. Full backups are necessary, but it is not always convenient to create them. They produce large backup files and take time to generate. They are not optimal in the sense that each successive full backup includes all data, even that part that has not changed since the previous full backup. It is more efficient to make an initial full backup, and then to make incremental backups. The incremental backups are smaller and take less time to produce. The tradeoff is that, at recovery time, you cannot restore your data just by reloading the full backup. You must also process the incremental backups to recover the incremental changes. To make incremental backups, we need to save the incremental changes. In MySQL, these changes are represented in the binary log, so the MySQL server should always be started with the  --log-bin option to enable that log. With binary logging enabled, the server writes each data change into a file while it updates data. Looking at the data directory of a MySQL server that has been running for some days, we find these MySQL binary log files: -rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001 -rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002 1618 7.3.1 Establishing a Backup Policy