[+/-]
The key to safe database management is making regular backups.
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/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/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 your MySQL server and make sure that it shuts down without errors.
Copy all your data files (ibdata
files
and .ibd
files) into a safe place.
Copy all your ib_logfile
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
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. See Section 6.2.1, “Backup Policy”.
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. 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.3, “Point-in-Time Recovery”.
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:
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 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.
User Comments
Add your own comment.