(mysql.info.gz) Backup strategy example

(mysql.info.gz) Disaster Prevention
 
 5.7.2 Example Backup and Recovery Strategy
 ------------------------------------------
 
 This section discusses a procedure for performing backups that allows
 you to recover data after several types of crashes:
 
    * Operating system crash
 
    * Power failure
 
    * Filesystem crash
 
    * Hardware problem (hard drive, motherboard, and so forth)
 
 The following instructions assume a minimum version of MySQL 4.1.8,
 because some `mysqldump' options used here are not available in earlier
 versions.
 
 The example commands do not include options such as `--user' and
 `--password' for the `mysqldump' and `mysql' programs.  You should
 include such options as necessary so that the MySQL server allows you
 to connect to it.
 
 We'll consider data is stored into the `InnoDB' storage engine of
 MySQL, which has support for transactions and automatic crash recovery.
 We'll always assume the MySQL server is under load at the time of
 crash. If it were not, no recovery would ever be needed.
 
 For the cases of operating system crashes or power failures, we can
 assume the MySQL disk data is available after a restart. The `InnoDB'
 data files will not contain consistent data due to the crash, but
 `InnoDB' reads its logs and finds in them the list of pending committed
 and non-committed transactions that have not been flushed to the data
 files.  It automatically rolls back those that were not committed, and
 flushes to the data files those that were committed.  Information about
 this recovery process is conveyed to the user through the MySQL error
 log. The following is an example log excerpt:
 
      InnoDB: Database was not shut down normally.
      InnoDB: Starting recovery from log files...
      InnoDB: Starting log scan based on checkpoint at
      InnoDB: log sequence number 0 13674004
      InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
      InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
      InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
      InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
      ...
      InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
      InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
      InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
      InnoDB: 1 uncommitted transaction(s) which must be rolled back
      InnoDB: Starting rollback of uncommitted transactions
      InnoDB: Rolling back trx no 16745
      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 filesystem crashes or hardware problems, we can assume
 the MySQL disk data is _not_ available after a restart. This means that
 MySQL will fail to start successfully because some blocks of disk data
 are no longer readable.  In this case, it's necessary to reformat the
 disk, install a new one, or otherwise correct the underlying problem.
 Then it's necessary to recover our MySQL data from backups--which means
 that we must already have made backups. To make sure that is the case,
 let's step back in time and design a backup policy.
(mysql.info.gz) Backup strategy example

Menu
