(mysql.info.gz) Backing up
(mysql.info.gz) Adding and removing
15.9 Backing Up and Recovering an `InnoDB' Database
The key to safe database management is taking 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) additional tool whose annual license fee is 390
euros per computer where the MySQL server is run. See the `InnoDB Hot
Backup' home page (http://www.innodb.com/order.html) for detailed
information and screenshots.
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:
1. Shut down your MySQL server and make sure that it shuts down
2. Copy all your data files (`ibdata' files, `.ibd' files) into a
3. Copy all your `ib_logfile's to a safe place.
4. Copy your `my.cnf' configuration file or files to a safe place.
5. Copy all the `.frm' files for your `InnoDB' tables to a safe place.
Replication works with `InnoDB' type tables, so you can use MySQL
replication capabilities to keep a copy of your database at database
sites requiring high availability.
In addition to taking binary backups as just described, you should also
regularly take 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, since the format is
simpler, the chance for serious data corruption is smaller.
`mysqldump' also has a `--single-transaction' option that you can use
to take a consistent snapshot without locking out other clients.
To be able to recover your `InnoDB' database to the present from the
binary backup described above, 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 point-in-time recovery:
mysqlbinlog YOURHOSTNAME-bin.123 | mysql
To recover from a crash of your MySQL server process, the only thing
you have to do is to restart it. `InnoDB' will automatically check the
logs and perform a roll-forward of the database to the present.
`InnoDB' will automatically roll back uncommitted transactions that were
present at the time of the crash. During recovery, `mysqld' will
display 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.
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 `innodb_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. It may
eliminate errors that appeared to be database page corruption.
* Forcing recovery Forcing Recovery
* InnoDB checkpoints Checkpoints
(mysql.info.gz) Adding and removing
automatically generated byinfo2html