Tutorials for Magento and Joomla! - Yireo

Magento backup through phpMyAdmin

Creating a database backup is a vital part of system administration, but if you have a backup you should also be familiar with the procedure on how to restore this backup. With Magento, the backup involves a copy of all the files and a dump of the MySQL database. But will you be able to restore the database?

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.