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.
Using MySQL Enterprise Backup is the
recommended method for backing up
InnoDB does not support databases that are
restored using third-party backup tools.
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 tables 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.
Logical Backups Using mysqldump
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”.
Replication works with
so you can use MySQL replication capabilities to keep a copy of
your database at database sites requiring high availability. See
InnoDB and MySQL Replication.