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 backup techniques, alone or in combination: hot backup with MySQL Enterprise Backup; cold backup by copying files while the MySQL server is shut down; logical backup with mysqldump for smaller data volumes or to record the structure of schema objects. Hot and cold backups are physical backups that copy actual data files, which can be used directly by the mysqld server for faster restore.
The mysqlbackup command, part of the MySQL
Enterprise Backup component, lets you back up a running MySQL
InnoDB tables, with minimal
disruption to operations while producing a consistent snapshot of
the database. When mysqlbackup is copying
InnoDB tables, reads and writes to
InnoDB can continue. MySQL Enterprise Backup
can also create compressed backup files, and back up subsets of
tables and databases. In conjunction with the MySQL 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 the MySQL server, you can make a physical
backup that consists of all files used by
InnoDB to manage its tables. Use the following
Perform a slow shutdown of the MySQL server and make sure that it stops without errors.
InnoDBdata files (
.ibdfiles) into a safe place.
Copy all the
InnoDBtables to a safe place.
InnoDBlog files (
ib_logfilefiles) to a safe place.
my.cnfconfiguration file or files to a safe place.
In addition to physical backups, it is recommended that you
regularly create logical backups by dumping your tables using
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
option for making a consistent snapshot without locking out other
clients. See Section 1.3.1, “Establishing a Backup Policy”.