Introducing InnoDB

At the moment Magento can only be used in combination with the database MySQL. Within MySQL storage engines are used to store data on the actual file system, and of these storage engines MyISAM and InnoDB are the most known. Magento uses InnoDB, which is more performant than MyISAM and also is able to handle relations between tables much better.

If you have tried deleting the Magento database through phpMyAdmin you may have noticed some peculiarities of InnoDB. One way to empty the MySQL database is to drop the entire database, but this might also drop all the privileges of the related database user.

Deleting or creating Magento tables

Another option is to delete all the tables within the Magento database, but this gives problems within the InnoDB settings: If table A has a relation with table B, you might not be able to delete table A unless table B is removed before that. The problem is that the ordering in which tables are removed needs to conform to the InnoDB relations. Just hitting the Delete-button several times in phpMyAdmin solves this problem.

But when you try to create a new database from an existing MySQL dump, these InnoDB relations might prevent you from actually creating all the new tables. The solution is quite simple: Disable all these InnoDB checks, until the database is fully restored.

Creating a proper backup using phpMyAdmin

Using the webinterface of phpMyAdmin, it is quite easy to create a proper backup of the Magento database. With the right database selected, you just navigate to Export to create a database dump. In most cases the default settings to create a database backup are fine.

The trick is to enable the option Disable foreign key checks. This will add specific SQL-code to the database dump which disables the InnoDB checks when importing the database again.

Changing the MySQL dump manually

But what if you have already created a backup using phpMyAdmin? Importing a regular backup (without this option enabled) will fail. The solution is to open up the SQL-dump with some kind of text editor.

Add the following code to the top of the file:

SET FOREIGN_KEY_CHECKS=0;

This will disable the InnoDB checks before actually using the rest of the SQL-commands to create or restore tables. But after the import you'll want to enable these checks again. So add the following to the bottom of the file:

SET FOREIGN_KEY_CHECKS=1;

It's a small trick but vital when handling database backups of your Magento instance. More SQL-commands that could aid your Magento backups can be found in the Magento wiki-page http://www.magentocommerce.com/wiki/restoring_a_backup_of_a_magento_database.

Recovery testing and database repair

When you have created a MySQL dump, you have made sure to have a backup of your database. But are you 100% sure that you can also recover from this backup? Any backup is worth nothing if the restore procedure is not tested. Simply setup an empty database and try to import the MySQL dump into this database. With such a simple test, you can see whether the dump is made correctly.

It can also be that the dump is actually correct, but the data within the dump are not. For instance, if a table contains entries with duplicate primary keys, your database will keep running fine, but important a dump will fail. In these cases, a Magento Datasebase Repair might solve these issues. It is a good idea anyway to make sure the integrity of your database tables are correct.