The key to safe database management is making regular backups. Depending on your data volume, number of MySQL servers, and database workload, you can use these techniques, alone or in combination: hot backup with MySQL Enterprise Backup; cold backup by copying files while the MySQL server is shut down; physical backup for fast operation (especially for restore); logical backup with mysqldump for smaller data volumes or to record the structure of schema objects.
The mysqlbackup command, part of the MySQL
Enterprise Backup component, lets you back up a running MySQL
MyISAM tables, with minimal disruption
to operations while producing a consistent snapshot of the database.
When mysqlbackup is copying
InnoDB tables, reads and writes to both
MyISAM tables can
continue. During the copying of
reads (but not writes) to those tables are permitted. MySQL
Enterprise Backup can also create compressed backup files, and back
up subsets of tables and databases. In conjunction with MySQL’s
binary log, users can perform point-in-time recovery. MySQL
Enterprise Backup is part of the MySQL Enterprise subscription. For
more details, see MySQL Enterprise Backup Overview.
If you can shut down your MySQL server, you can make a binary backup
that consists of all files used by
manage its tables. Use the following procedure:
Do a slow shutdown of the MySQL server and make sure that it stops without errors.
InnoDB data files
ibdata files and
files) into a safe place.
Copy all the
.frm files for
InnoDB tables to a safe place.
InnoDB log files
ib_logfile files) to a safe place.
my.cnf configuration file or
files to a safe place.
In addition to making binary backups as just described, regularly
make dumps of your tables with mysqldump. 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 1.3.1, “Establishing a Backup Policy”.
Replication works with
so you can use MySQL replication capabilities to keep a copy of your
database at database sites requiring high availability.
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, even before taking the
backup. 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 1.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
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 becomes corrupted or disk failure occurs, you must perform the recovery using a backup. In the case of corruption, 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
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. If MySQL still has trouble starting because of
InnoDB consistency problems, see
Forcing InnoDB Recovery for steps to start the
instance in a diagnostic mode where you can dump the data.