Steps to be performed to import production DB to a stage server instance

1 Step 1 - Back up your QMetry Production database
2 Step 2 - Drop your existing Staging database and create a new empty database.
3 Step 3 - Restore the Production DB backup file on Stage
4 Step 4 - Follow the below steps after the DB Import is completed.

PRE-REQUISITE: Make sure your QMetry Production instance and QMetry Staging instances are both on the same version.

Step 1 - Back up your QMetry Production database

Take the backup of your existing “qmetry” database. You can replace the username, password, location of the backup file (currently default /opt).

Syntax : docker exec qmetry_rds_1 sh -c 'exec mysqldump -uroot -p<password> --triggers --routines --hex-blob --single-transaction --lock-tables --no-create-db qmetry' > /opt/qmetry_backup_<file_name>.sql
For example:

docker exec qmetry_rds_1 sh -c 'exec mysqldump -uroot -pqmetry --triggers --routines --hex-blob --single-transaction --lock-tables --no-create-db qmetry' > /opt/prod_db_backup.sql

Step 2 - Drop your existing Staging database and create a new empty database.

CAUTION - Make sure you are logged to your staging database server (not production) before proceeding.

Run the following commands one by one. Enter MySQL credentials when prompted.

sudo su
docker exec -it qmetry_rds_1 bash
mysql -uroot -p -A
DROP database qmetry;
DROP database qmetrycdc;
CREATE DATABASE qmetry CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
exit (to exit mysql)
exit (to exit mysql docker)

Step 3 - Restore the Production DB backup file on Stage

Copy the prod_db_backup.sql file to /opt directory of your stage server.
Go to the directory where prod_db_backup.sql file is located (cd /opt) and run the below command.

cat /opt/prod_db_backup.sql | docker exec -i qmetry_rds_1 mysql -uroot -p 'qmetry' (Enter password when prompted)

Wait for the restore to complete.

Step 4 - Follow the below steps after the DB Import is completed.

When you import the production DB to your stage server instance, it is required to perform the following mandatory steps to avoid the unnecessary issues with custom report stops working/keeps loading, post-import.

Make sure you run the following queries and perform the changes in the UI when you import the production DB to your stage instance :

Execute the following queries on QMetry Stage App DB –
- Query to de-activate all users other than QMetry admin.
- Activate only the required number of users (as per your stage license) after logging in later.
- Update Jira URLs to point to Stage Jira instead of Production –
- Report Configuration Queries – (Run on QMetry App DB).
Update QMetry Base URL, Reports Database, and Sync Configurations from the UI.
- Login as admin.
- Go to Profile and Update the Base URL as Staging Server URL.
- Go to Customization > Insight configuration and update the host IPs from Production Reports to Stage Reports under "Database Configuration" as well as in the "Sync Data" tab.
Restart QMetry services using the following commands
- Type exit two times to quit MySQL and docker.
Reconfigure LDAP/SAML from QMetry admin login > Integration > LDAP/SAML as per staging environment settings.
Jira integrated QMetry projects need to be re-synced again.

For any questions or concerns, reach out to QMetry Support by creating a ticket on the Support Portal.