Restore a DB snapshot from Staging or Production
This article shows how to restore a DB snapshot from Staging or Production on Adobe Commerce on Cloud Pro infrastructure.
Affected products and versions
- Adobe Commerce on cloud infrastructure, all supported versions
Choose the most appropriate for your case:
Method 1: Transfer the database dump to your local machine and import it meth2
The steps are:
-
Using SFTP, navigate to the location where the database snapshot has been placed, usually on the first server/node of your cluster (For example:
/mnt/recovery-<recovery_id>
). NOTE: If your project is Azure-based, i.e., your project URL looks something like https://us-a1.magento.cloud/projects/<cluster_id>, then the snapshot will be placed in/mnt/shared/<cluster ID>/all-databases.sql.gz
or/mnt/shared/<cluster ID_stg>/all-databases.sql.gz
instead.NOTE: The format of the snapshot on Azure projects will be different and contains other databases that cannot be imported. Before importing the snapshot, you will have to take additional steps to extract the appropriate database before importing the dump.
For Production:
code language-sql cd /mnt/shared/<cluster ID/ gunzip all-databases.sql.gz head -n 17 all-databases.sql > <cluster ID>.sql sed -n '/^-- Current Database: `<cluster ID>`/,/^-- Current Database: `/p' all-databases.sql >> <cluster ID>.sql gzip <cluster ID>.sql zcat <cluster ID>.sql.gz | \ sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | \ mysql -h 127.0.0.1 \ -u $DB_USER \ --password=$MYSQL_PWD $DB_NAME \ --init-command="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_TIME_ZONE=@@TIME_ZONE ;SET TIME_ZONE='+00:00' ;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;"
For Staging:
code language-sql cd /mnt/shared/<cluster ID/ | cd /mnt/shared/<cluster ID_stg> gunzip all-databases.sql.gz head -n 17 all-databases.sql > <cluster ID_stg>.sql sed -n '/^-- Current Database: `wyf2o4zlrljjs`/,/^-- Current Database: `/p' all-databases.sql >> <cluster ID_stg>.sql gzip <cluster ID_stg>.sql zcat <cluster ID_stg>.sql.gz | \ sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | \ mysql -h 127.0.0.1 \ -u $DB_USER \ --password=$MYSQL_PWD $DB_NAME \ --init-command="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_TIME_ZONE=@@TIME_ZONE ;SET TIME_ZONE='+00:00' ;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;"
-
Copy the database dump file (For example:
<cluster ID>.sql.gz
for Production or<cluster ID_stg>.sql.gz
for Staging) to your local computer. -
Make sure you have set up the SSH tunnel to connect to the database remotely: SSH and sFTP: SSH tunneling in our developer documentation.
-
Connect to the database.
code language-sql mysql -h <db-host> -P <db-port> -p -u <db-user> <db-name>
-
Drop the database; at the MariaDB prompt, enter:
(For Production)
code language-sql drop database <cluster ID>;
(For Staging)
code language-sql drop database <cluster ID_stg>;
-
Enter the following command to import the snapshot:
(For Production)
code language-sql zcat <cluster ID>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>
(For Staging)
code language-sql zcat <cluster ID_stg>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>
Method 2: Import the database dump directly from the server meth3
The steps are:
-
Navigate to the location where the database snapshot has been placed, usually on the first server/node of your cluster (For example:
/mnt/recovery-<recovery_id>
). -
To drop and re-create the cloud database, first connect to the database:
code language-sql mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>
-
Drop the database; at the MariaDB prompt, enter:
(For Production)
code language-sql drop database <cluster ID>;
(For Staging)
code language-sql drop database <cluster ID_stg>;
-
Enter the following command to import the snapshot:
(For importing the database backup from Production)
code language-sql zcat <cluster ID>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
(For importing the database backup from Staging)
code language-sql zcat <cluster ID_stg>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
(For importing a database backup from any other environment)
code language-sql zcat <database-backup-name>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
(For importing a database backup from any other environment)
code language-sql zcat <database-backup-name>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
Related reading
In our developer documentation: