InnoDB Backup and Recovery

InnoDB Backup

Hot Backups

The mysqlbackup command, part of the MySQL Enterprise Backup component

Cold Backups

  1. Perform a slow shutdown of the MySQL server and make sure that it stops without errors.

  2. Copy all InnoDB data files (ibdata files and .ibd files) into a safe place.

  3. Copy all the .frm files for InnoDB tables to a safe place.

  4. Copy all InnoDB log files (ib_logfile files) to a safe place.

  5. Copy your my.cnf configuration 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.

Tips

  • backup-mysql.sh

  • mysql-backup.sh

InnoDB Recovery

Point-in-Time Recovery

Recovery from Data Corruption or Disk Failure

InnoDB Crash Recovery

The InnoDB crash recovery process consists of several steps:

  • Redo log application

  • Roll back of incomplete transactions

  • Change buffer merge

  • Purge

References