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 data.
InnoDB does not support databases that are restored using third-party backup tools.
Hot Backups
The mysqlbackup command, part of the MySQL Enterprise Backup component, lets you back up a running MySQL instance, including 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.
Cold Backups
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 procedure:
- Perform a slow shutdown of the MySQL server and make sure that it stops without errors.
- Copy all
InnoDBdata files (ibdatafiles and.ibdfiles) into a safe place. - Copy all the
.frmfiles forInnoDBtables to a safe place. - Copy all
InnoDBlog files (ib_logfilefiles) to a safe place. - Copy your
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 --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 InnoDB tables, 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.
https://dev.mysql.com/doc/mysql-backup-excerpt/5.5/en/innodb-backup.html
Home