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 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:
Shut down your MySQL server and make sure that it shuts down without errors.
Copy all your data files into a safe place.
Copy all your InnoDB log files to a safe place.
Copy your 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 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 InnoDB: Started 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.
If there is database page corruption, you may want to dump your tables from the database with SELECT INTO OUTFILE, and usually most of the data is intact and correct. But the corruption may cause SELECT * FROM tbl_name or InnoDB background operations to crash or assert, or even the InnoDB roll-forward recovery to crash. Starting from MySQL 3.23.44, there is an InnoDB variable that you can use to force the InnoDB storage engine to start up, and you can also prevent background operations from running, so that you will be able to dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:
[mysqld] innodb_force_recovery = 4
Before MySQL 4.0, use this syntax instead:
[mysqld] set-variable = innodb_force_recovery=4
The allowable non-zero values for innodb_force_recovery follow. A larger number includes all precautions of lower numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more dramatic, because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
1 (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page; try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
2 (SRV_FORCE_NO_BACKGROUND)
Prevent the main thread from running. If a crash would occur in the purge operation, this prevents it.
3 (SRV_FORCE_NO_TRX_UNDO)
Do not run transaction rollbacks after recovery.
4 (SRV_FORCE_NO_IBUF_MERGE)
Prevent also insert buffer merge operations. If they would cause a crash, better not do them; do not calculate table statistics.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
Do not look at undo logs when starting the database: InnoDB will treat even incomplete transactions as committed.
6 (SRV_FORCE_NO_LOG_REDO)
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any of these options enabled! As a safety measure, InnoDB prevents users from doing INSERT, UPDATE, or DELETE when innodb_force_recovery is set to a value greater than 0.
Starting from MySQL 3.23.53 and 4.0.4, you are allowed to DROP or CREATE a table even if forced recovery is used. If you know that a certain table is causing a crash in rollback, you can drop it. You can use this also to stop a runaway rollback caused by a failing mass import or ALTER TABLE. You can kill the mysqld process and set innodb_force_recovery to 3 to bring your database up without the rollback. Then DROP the table that is causing the runaway rollback.
InnoDB implements a checkpoint mechanism called a “fuzzy checkpoint.” InnoDB will flush modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would in practice stop processing of user SQL statements for a while.
In crash recovery, InnoDB looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are already present in the disk image of the database. Then InnoDB scans the log files forward from the place of the checkpoint, applying the logged modifications to the database.
InnoDB writes to the log files in a circular fashion. All committed modifications that make the database pages in the buffer pool different from the images on disk must be available in the log files in case InnoDB has to do a recovery. This means that when InnoDB starts to reuse a log file in the circular fashion, it has to make sure that the database page images on disk already contain the modifications logged in the log file InnoDB is going to reuse. In other words, InnoDB has to make a checkpoint and often this involves flushing of modified database pages to disk.
The preceding description explains why making your log files very big may save disk I/O in checkpointing. It can make sense to set the total size of the log files as big as the buffer pool or even bigger. The drawback of big log files is that crash recovery can take longer because there will be more logged information to apply to the database.