DocumentationCommerceCommerce KB

Restore a DB snapshot from Staging or Production

Last update: Thu Jul 04 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

This article shows how to restore a DB snapshot from Staging or Production on Adobe Commerce on Cloud Pro infrastructure.

Affected products and 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:

  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:

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

  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.

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

  5. 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>;

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

  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:

    code language-sql 
    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)

    code language-sql 
    drop database <cluster ID>;

    (For Staging)

    code language-sql 
    drop database <cluster ID_stg>;

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

In our developer documentation:

recommendation-more-help
8bd06ef0-b3d5-4137-b74e-d7b00485808a