Login
Menu

Backup and Restore Magento Database on Dedicated Server

Home / Backup and Restore Magento Database on Dedicated Server

Backup and Restore Magento Database on Dedicated Server

Magento is an Open Source eCommerce Platform that helps in your on-line business and stores growth. Backup of Magento eCommerce software package information and web site is one amongst the very important step, webmasters should perform. Usually, backing up the web site information could be a about to stop information from worst situations. If a disaster takes place, you’ll required those backups to revive your data and place your Magento store on-line as soon as with minimal information loss. Most of the business owners, perform backup tasks at night, once its bit quiet.

Most of the online hosting service provider offers backup service on daily basis for a little fee. Though its higher and value the fee, however it additionally comes with cautions. The benefits are that the backups of your Magento store are done on each day and you don’t have to worry regarding it. The disadvantage is that you simply don’t have any management on the backups. You wish to rely the online host if you want to restore a backup. It’s better to know, the way to backup and restore Magento database by your own.

Backup Magento MySQL Database using phpMyAdmin

If your web host have installed the phpMyAdmin in your cPanel, you’ll backup your Magento MySQL database with phpMyAdmin using the interface. Follow the steps given below:

  1. Log-in to your cPanel using lohin details and click on phpMyAdmin
  2. Now, Choose the database you want to backup present on the left panel
  3. Then from the top click on the “Export”
  4. Now select the options, Add DROP TABLE and Save as file to save the backup file on your local machine in .sql format. Keep the default selection as it is.
  5. Click the Go button present at the bottom, after that save the file on your local machine.

If phpMyAdmin is not installed in your cPanel, but have access to SSH on your Linux dedicated server hosting platform, then using shell access you can use the command given below to dump your Magento mysql database.

mysqldump -hHostname -uUsername -pDatabasename > Filename.sql

Where:

  1. Hostname: it is the database server hostname.
  2. Username: using username user have the full privileges to the database.
  3. Databasename: it is the full name of the database which your Magento store is running.
  4. Filename: it is the name of the file which you think is suitable for the backup file.

Make sure, you keep (-h, -u and -p) at the start of the databasename and hostname and username.

How to Restore a Magento Database from a Backup File ?

Usually, the Magento store database uses a foreign key constraints to make sure the database integrity. For example: if you delete a particular category from your Magento store, you will need to delete all the categories under that main category.

When you backup your Magento database with the built-in backup function which is available in the Magento Admin, it inserts special line in the .sql backup file to avoid the foreign key checking when the backups are restored.

Using other methods like phpMyAdmin, if you perform backups, it will not add such special lines in the .sql file. And when you perform the restoration of database, you will get the following errors:

Cannot add or update a child row: a foreign key constraint fails

To avoid such errors when restoring Magento database from a backup file and restore backup without foreign key checking, you need to add the following lines at the beginning of your .sql backup 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;

In order to “Turn On” the foreign key checks again, add the following lines at the end of the .sql backup file.

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;

By doing the above modifications, you will be able to restore your Magento database successfully from a .sql backup file on your dedicated server.