14.2.8. Backing Up and Recovering an
The key to safe database management is making regular backups.
InnoDB Hot Backup is an online backup tool you
can use to backup your
InnoDB database while it
is running. InnoDB Hot Backup does not require
you to shut down your database and it does not set any locks or
disturb your normal database processing. InnoDB Hot
Backup is a non-free (commercial) add-on tool with an
annual license fee of €390 per computer on which the MySQL
server is run. See the
Backup home page for detailed information and
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
Shut down your MySQL server and make sure that it shuts down
Copy all your data files (
.ibd files) into a safe place.
Copy all your
ib_logfile files to a safe
my.cnf configuration file or
files to a safe place.
Copy all the
.frm files for your
InnoDB tables to a safe place.
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.
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 that you can use to
make a consistent snapshot without locking out other clients.
To be able to recover your
InnoDB database to
the present from the binary backup just described, you have to run
your MySQL server with binary logging turned on. Then you can
apply the binary log to the backup database to achieve
yourhostname-bin.123 | mysql
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
InnoDB automatically rolls back
uncommitted transactions that were present at the time of the
crash. During recovery, mysqld displays output
something like this:
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
mysqld: ready for connections
If your database gets corrupted or your disk fails, you have to do
the recovery from a backup. In the case of corruption, you should
first find a backup that is not corrupted. After restoring the
base backup, do the recovery from the binary log files using
mysqlbinlog and mysql to
restore the changes performed 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
naturally cannot detect every possible kind of corruption. You can
innodb_tablespace_monitor to check the
integrity of the file space management inside the tablespace
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.