Steps to be performed to import production DB to a stage server instance
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 qmetryapp_db sh -c 'exec mysqldump -uroot -pqmetry --triggers --routines --hex-blob --single-transaction --lock-tables --no-create-db qmetry' > /opt/prod_db_backup.sqlStep 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 qmetryapp_db 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 qmetryapp_db 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 user set isActive = 0 where isSysAdmin = 0 and clientID = 1;Update Jira URLs to point to Stage Jira instead of Production –
update ext_configuration set sharedSecret = "", addonKey = null, isActive = 0, isUninstalled = 1 where clientId =1; update ext_configuration set baseUrl = 'https://<your-test-server-url>.qmetry.com' where baseUrl = 'https://<production-server-url>.qmetry.com' and clientId = 1;Report Configuration Queries – (Run on QMetry App DB).
PURGE BINARY LOGS BEFORE NOW(); drop database qmetrycdc;
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.
sudo su docker restart qmetryapp_db qmetry_app_1
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.