Warning: potential data loss

It is always dangerous to import or restore a database backup. One small mistake, one command in the wrong SSH terminal and you will loose live data without a way to recover recent changes.

Restore a dump from Live on Staging account

When you want to import a database dump of your live/production environment to use on the staging environment you could encounter an error like this:

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

This is because there can be references to the live account name or database name (which also includes the account name). This can be fixed by using a script we provide

/srv/magehost/TOOLS/mysqldump_5.7_fix.sh  <file.sql.gz>

Restore using MySQL Client

This works regardless of your Magento or Shopware configuration but may be a little more complicated.

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

Create database:

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

Example:

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

Restore 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

 
See also:    Create a database backup    Manage the MySQL database