Backup and Restore Magento Database on Dedicated Server

Magento is an Open Source eCommerce Platform which helps in your online business and stores growth. Backup of Magento eCommerce software database and website is one of the vital step, webmasters should always perform. Usually, backing up the website data is a planning to prevent data from worst scenarios. If a disaster takes place, you will need those backups to restore your data and put your Magento store online as soon as possible with minimal data loss. Most of the business owners, perform backup tasks at night, when its bit quiet.

Most of the web hosting service providers offers backup service on daily basis for a little fee. Though its better and worth the fee, but it also comes with cautions. The advantages are that the backups of your Magento store are done on daily basis and you don’t have to worry about it. The disadvantage is that you don’t have any control on the backups. You need to rely on the web host if you wish to restore a backup. It’s better to know, how 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 can backup your Magento MySQL database with phpMyAdmin using the interface. Follow the steps given below:

  1. Login to your cPanel and click on phpMyAdmin
  2. Now, Select the database you want to backup from the left panel
  3. Simply click on the “Export” tab on the top
  4. 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 at the bottom, and save the file on your local machine.

If you don’t have phpMyAdmin installed in your cPanel, but have access to SSH on your Linux dedicated server hosting platform, then using shell access you can use the following command to dump your Magento mysql database.

mysqldump -hHostname -uUsername -pDatabasename > Filename.sql


  1. Hostname: is the database server hostname.
  2. Username: is the user with full privileges to the database.
  3. Databasename: is the full name of the database which your Magento store is running.
  4. Filename:  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 hostname, username and databasename.

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 @[email protected]@CHARACTER_SET_CLIENT;
SET @[email protected]@CHARACTER_SET_RESULTS;
SET @[email protected]@COLLATION_CONNECTION;
SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@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 [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
SET [email protected]_CHARACTER_SET_CLIENT;
SET [email protected]_CHARACTER_SET_RESULTS;
SET [email protected]_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.

Like this post ?

Share on your Social Networking Profile ( Facebook, Twitter & Google+ ) and get a flat 10% Recurring discount on our VPS Hosting and Dedicated Servers.

Email us the shared link at : [email protected] or speak to our live chat operator now, by clicking on the “Live Chat” Scroller on the left-hand side of this page and we will provide you with the discount Coupon right away!

Powered by BetterDocs