13.2.7 Backing Up and Recovering an InnoDB Database

InnoDB Hot Backup enables you to back up a
running MySQL database, including
InnoDB and
MyISAM tables, with minimal
disruption to operations while producing a consistent snapshot of
the database. When InnoDB Hot Backup is copying
InnoDB tables, reads and writes to
both InnoDB and
MyISAM tables can continue. During
the copying of MyISAM tables, reads
(but not writes) to those tables are permitted. In addition,
InnoDB Hot Backup supports creating compressed
backup files, and performing backups of subsets of
InnoDB tables. In conjunction with
MySQL’s binary log, users can perform point-in-time recovery.
InnoDB Hot Backup is commercially licensed by
Innobase Oy. For a more complete description of InnoDB
Hot Backup, see
http://www.innodb.com/products/hot-backup/features/
or download the documentation from
http://www.innodb.com/doc/hot_backup/manual.html.
You can order trial, term, and perpetual licenses from Innobase at
http://www.innodb.com/wp/products/hot-backup/order/.

If you are able to shut down your MySQL server, you can make a
binary backup that consists of all files used by
InnoDB to manage its tables. Use the
following procedure:

Shut down the MySQL server and make sure that it stops without
errors.

In addition to making binary backups as just described, you should
also regularly make dumps of your tables with
mysqldump. The reason for this is that a binary
file might be corrupted without you noticing it. Dumped tables are
stored into text files that are human-readable, so spotting table
corruption becomes easier. Also, because the format is simpler,
the chance for serious data corruption is smaller.
mysqldump also has a
--single-transaction option for
making a consistent snapshot without locking out other clients.
See Section 6.3.1, “Establishing a Backup Policy”.

Replication works with InnoDB tables,
so you can use MySQL replication capabilities to keep a copy of
your database at database sites requiring high availability.

To be able to recover your InnoDB
database to the present from the time at which the binary backup
was made, you must run your MySQL server with binary logging
turned on. To achieve point-in-time recovery after restoring a
backup, you can apply changes from the binary log that occurred
after the backup was made. See
Section 6.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.

To recover from a crash of your MySQL server, the only requirement
is to restart it. InnoDB
automatically checks the logs and performs a roll-forward of the
database to the present. InnoDB
automatically rolls back uncommitted transactions that were
present at the time of the crash. During recovery,
mysqld displays output something like this:

If your database becomes corrupted or disk failure occurs, you
must perform the recovery using a backup. In the case of
corruption, you should first find a backup that is not corrupted.
After restoring the base backup, do a point-in-time recovery from
the binary log files using mysqlbinlog and
mysql to restore the changes that occurred
after the backup was made.

In some cases of database corruption it is enough just to dump,
drop, and re-create one or a few corrupt tables. You can use the
CHECK TABLE SQL statement to check
whether a table is corrupt, although CHECK
TABLE naturally cannot detect every possible kind of
corruption. You can use the Tablespace Monitor to check the
integrity of the file space management inside the tablespace
files.

In some cases, apparent database page corruption is actually due
to the operating system corrupting its own file cache, and the
data on disk may be okay. It is best first to try restarting your
computer. Doing so may eliminate errors that appeared to be
database page corruption.