Magento Backup Using phpMyAdmin

The most common back up solution is phpMyAdmin, and some people prefer it over any built-in method. To export via phpMyAdmin, we:

1. Navigate to the database

2. Switch to the export tab

3. Select all tables and SQL as the export type

4. Under options on the right-hand side select Disable foreign key checks

5. Select save as file at the very bottom of the page

6. If we want to match the compression type of Magento’s output, select gzipped as our compression method

7. Click the Go button to export

This will give us an SQL file, which we can then import at a later date back into an empty database and restore our data.

To restore data from phpMyAdmin there can be some error occure. To avoid such error we can add following code in my sql file.

Add this code at the beginning of our sql file:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;

SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;

SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;

SET NAMES utf8;

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’;

SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;

At the end of the file, add the statements required to turn on constraint checking again:

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;

SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;

SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;

SET SQL_NOTES=@OLD_SQL_NOTES;

With these modifications, you should be able to restore your database from a .sql file backup created with any tool.

If you have similar problems installing the sample database, just add the same statements to the .sql file containing the sample data.

Blog from experience