InnoDB database: Backup & Recovery

The best way to prevent data loss in case of disaster is to maintain regular backups. You can choose your backup strategy depending upon database workload, business requirement and data volume. Here is some backup strategies for MySQL innodb database, you can choose any one that suits your business need.

Mysqlbackup command: This command is a part of MySQL Enterprise Backup and allows you to backup innodb and myisam tables. It also provides a snapshot of database. You can perform read, write operation while performing backup of innodb tables. Using this command you can also perform online backup of innodb tables.

Cold Backup: To perform cold backup you need to shutdown the MySQL Server. This is used to backup all the files of innodb. Follow below steps to perform backup:
1.First shutdown your MySQL Server.
2.Now copy all the innodb data files such as ibdata and ibd files to some safe location.
3.Copy all the .frm to a safe location.
4.Copy all the innodb log files to a safe location.
5.Copy your configuration file i.e my.cnf to a safe location.

Mysqldump command: Mysqldump is an effective backup tool to backup mysql database. It is used to dump a single database or multiple databases for backup. Mysqldump uses *.sql file to store all SQL statements. Using this command you can perform backup of local database and restore it on a remote database simultaneously.

To backup a single database:
mysqldump -u root -p[password] [database_name] > dumpfilename.sql

To backup multiple databases:
mysqldump -u root -p[password] - - databases [database_name1,database_name2] > dumpfilename.sql

To backup all the databases
mysqldump -u root -p[password] - - all- databases> /tmp/all-databasename.sql

Forcing innodb recovery: Sometimes innodb background operations or Select statement got crashed due to corruption. In this case you can use innodb force recovery. This option will start innodb storage engine and also prevent background processes from running. In the meanwhile you can dump MySQL tables. Here is the syntax of command:
[mysqldd] innodb_force_recovery = 4
By default its value is 0. In case of 4 there is minimal data loss in case of corruption.