InnoDB Backup and Recovery

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#

Contents