Magento is an Open Source eCommerce Platform that helps in your online business and store growth. Backup of Magento eCommerce software database and website is one of the vital steps, webmasters should always perform. Usually, backing up the website data is 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 business owners, perform backup tasks at night when it is quiet.
Most web hosting service providers offer backup service on daily basis for a little fee. Though it’s better and worth the fee, 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 over 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 the Magento database on your own.
Backup Magento MySQL Database using phpMyAdmin
If your web host has installed the phpMyAdmin in your cPanel, you can backup your Magento MySQL database with phpMyAdmin using the interface. Follow the steps given below:
- log in to your cPanel and click on phpMyAdmin
- Now, Select the database you want to backup from the left panel
- Simply click on the “Export” tab on the top
- 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.
- 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
Where:
- Hostname: is the database server hostname.
- Username is the user with full privileges to the database.
- Database name: is the full name of the database which your Magento store is running.
- 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 database name.
How to Restore a Magento Database from a Backup File
Usually, the Magento store database uses foreign key constraints to make sure the database’s 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 back up your Magento database with the built-in backup function which is available in the Magento Admin, it inserts a 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 the 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 the Magento database from a backup file and restoring 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;
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.
