Restore a Database Backup

Warning: Potential Data Loss

Importing or restoring a database backup can lead to potential data loss, as small mistakes can result in loss of important information without the ability to recover recent changes.

Restoring Dump from Live on Staging Account

When you want to import a dump of your live/production environment into the staging environment, you may encounter an error like this:

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

This is due to references in your database name or live account name. This can be resolved by using a script we provide:

~/bin/fix_db_export.sh <file.sql.gz>

Restoring Using MySQL Client

This works regardless of whether you're working with Magento or Shopware, but it may require additional steps.

In the examples below, replace <username_dbname> by your SSH username followed by an underscore and the name of your application. For example, coolsitest_magento or blogrc_wordpress could be used.

Create database:

mysql '' --execute='CREATE DATABASE <username_dbname>;'

Example:

zcat ~/backup/db_backup.sql.gz | mysql <username_dbname>

Restoring Database Dump Using N98-MageRun

Magento 1.x

This requires a working Magento configuration file ~/httpdocs/app/etc/local.xml:

n98-magerun db:import --compression=gzip ~/backup/db_backup.sql.gz

Magento 2.x

This requires a working Magento configuration file ~/magento2/app/etc/env.php:

n98-magerun2 db:import --compression=gzip ~/backup/db_backup.sql.gz