MySQL Backup and Recovery Issues

There can be numerous ways your MySQL in database server can get corrupted despite the best of software and proficient users. Nevertheless MySQL recovery is not a complicated procedure especially in the presence of reliable backups.

InnoDB, the leading storage engine for MySQL has an automatic built-in recovery system that for large part operates very seamlessly. Whenever the database hosting goes down, InnoDB takes charge and tries to fix issue by running the log file from its last timestamp. In nine out of ten cases it actually succeeds. The not uncommon situations like and operating system crash or power failure provide instances where InnoDB itself takes care of tedious data recovery. However in the eventuality of InnoDB failing in providing automatic repair solution, the overall database will not start.

No less significant is the fact that regular backups of your database should be scheduled. Quite helpful is mysqldump utility for making backups of data as a snapshot in any available point of time. In order to create a “dump file” of MySQL database you can use the following command:

shell> mysqldump –single-transaction –flush-logs –master data=2 \
–all-databases > dump.sql

The output file can be named in any manner – in the above case it is named dump.sql. This dump.sql file consists of the tables, data, structures of all databases entirely backed up into an SQL text file, dump.sql.

In the above command “–single-transaction” option is particularly for InnoDB tables. This option carries out an online backup that allows no locks on tables.
Further to incremental changes have to be saved if you wish to make incremental backups, This process is best achieved by employing binary logs. It is advisable to start MySQL always with the –log-bin option to enable that binary log. In the mysqldump command referenced above there is a “–flush-logs” option which allows the server to flush out its logs. These logs can be scheduled to be flushed incrementally between dump backups to hold entire data changes made since the time of backup.

Let’s suppose you have a case where your database experiences a terrible crash. It is simple to restore If you have regular backups along with binary logs, restoring is not actually difficult. To begin with take restore to the last full backup which you have using the following command:

shell> mysql < dump.sql

This makes for restoration of data to the point it was at your last mysqldump backup. In order to restore various changes arisen since then, employ incremental backups from the binary log files that are clearly listed in the data directory of MySQL server (in our present example, logfilename-bin.000001 and logfilename-bin.000002).
shell> mysqlbinlog logfilename-bin.000001 logfilename-bin.000002 | mysql