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.
• Method 2: Import the database dump directly from the server.

Method 1: Transfer the database dump to your local machine and import it

The steps are:

1. 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:

   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:

   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;"
   

2. 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.

3. Make sure you have set up the SSH tunnel to connect to the database remotely: SSH and sFTP: SSH tunneling in our developer documentation.

4. Connect to the database.

   mysql -h <db-host> -P <db-port> -p -u <db-user> <db-name>
   

5. Drop the database; at the MariaDB prompt, enter:

   (For Production)

   drop database <cluster ID>;
   

   (For Staging)

   drop database <cluster ID_stg>;
   

6. Enter the following command to import the snapshot:

   (For Production)

   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)

   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

The steps are:

1. 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>).

2. To drop and re-create the cloud database, first connect to the database:

   mysql -h 127.0.0.1 -P <db-port> -p -u <db-user> <db-name>
   

3. Drop the database; at the MariaDB prompt, enter:

   (For Production)

   drop database <cluster ID>;
   

   (For Staging)

   drop database <cluster ID_stg>;
   

4. Enter the following command to import the snapshot:

   (For Production)

   zcat <cluster ID>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
   

   (For Staging)

   zcat <cluster ID_stg>.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h 127.0.0.1 -p -u <db-user> <db-name>
   

Related reading

In our developer documentation:

• Import code: Import the database
• Snapshots and backup management: Dump your database