(mysql.info) backup-strategy-example

(mysql.info) point-in-time-recovery

 
 5.10.2 Example Backup and Recovery Strategy
 -------------------------------------------

 
* backup-policy                Backup Policy
* backup-recovery              Using Backups for Recovery
* backup-strategy-summary      Backup Strategy Summary
 
 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 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 assume that data is stored in the `InnoDB' storage engine, which has
 support for transactions and automatic crash recovery. We also assume
 that the MySQL server is under load at the time of the crash. If it
 were not, no recovery would ever be needed.
 
 For cases of operating system crashes or power failures, we can assume
 that MySQL's disk data is available after a restart. The `InnoDB' data
 files might 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. `InnoDB' automatically rolls back those transactions that were
 not committed, and flushes to its 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
 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 we must already have made backups. To make sure that is the
 case, we should design a backup policy.

Info Catalog

(mysql.info) backup

(mysql.info) disaster-prevention

(mysql.info) point-in-time-recovery

automatically generated byinfo2html

(mysql.info) backup-strategy-example

Menu