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