5.9.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:
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.
