Configuration Changes required for QMetry App and QMetry Reports when MySQL passwords are changed (applicable only to the on-premise customers)

When MySQL passwords are changed or updated, QMetry App and Reports require specific configuration changes. In this article, we have described the steps for implementing these changes.

Database Backup (recommended)

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_<date>.sql

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/qmetry_backup_2023-03-21.sql

--

QMetry App and QMetry Reports need to be reconfigured as per the steps mentioned below to utilize the updated MySQL Passwords.

We recommend performing the below steps during non-production/off-peak-hours or during the weekends, as the steps contains multiple services restarts for the QMetry App and Reports.
Before making changes, we advise to take a QMetry app database backup.

Steps to Update MySQL Password on QMetry App DB

sudo su -
Go inside the QMetry database docker container:
- docker exec -it qmetry_rds_1 bash
Login to MySQL:
- mysql -uroot -p
  - <enter the password when prompted>
USE mysql;
Query to change the MySQL password:
- ALTER USER 'root'@'localhost' identified by 'NEW_PASSWORD'; (Don’t use the ^ Caret symbol/sign in password)
- ALTER USER 'root'@'%' identified by 'NEW_PASSWORD'; (Don’t use the ^ Caret symbol/sign in password)
  - Example:
    - ALTER USER 'root'@'localhost' identified by 'QMetry@123';
    - ALTER USER 'root'@'%' identified by 'QMetry@123';
\q (to exit mysql)
exit (to exit container)

Changes required after updating the QMetry App DB MySQL password:

Go inside the QMetry App docker container:
- docker exec -it qmetry_app_1 bash
vi dbConfig.properties
- Press “i” to launch the INSERT mode.
- Update the password (the one you changed above) in the variable mysql.password
- Press the “Esc” button and type :wq to save the file with changes.
Execute the below command to run the "qmetry.jar" file, which automatically updates the MySQL database password on the required files/places.
- java -jar qmetry.jar --upgrade
  - Note: Errors (if any) in the INFO messages can be safely ignored.
Restart DB followed by App Containers
- docker restart qmetry_rds_1
- docker restart qmetry_app_1

Steps to Update MySQL Password on Reports DB

sudo su -
Go inside the QMetry Reports database docker container:
- docker exec -it qmetry-reports-db bash
Login to MySQL:
- mysql -uroot -p
  - <enter the password when prompted>
USE mysql;
Query to change the MySQL password:
- ALTER USER 'root'@'localhost' identified by 'NEW_PASSWORD'; (Don’t use the ^ Caret symbol/sign in password)
- ALTER USER 'root'@'%' identified by 'NEW_PASSWORD'; (Don’t use the ^ Caret symbol/sign in password)
  - Example:
    - ALTER USER 'root'@'localhost' identified by 'Game123$';
    - ALTER USER 'root'@'%' identified by 'Game123$';
\q (to exit mysql)
exit (to exit container)

Steps to be performed on Reports App Server after MySQL DB Password change

Note: We have assumed you have changed the MySQL password for both QMetry App DB and QMetry Reports DB. When performing the steps below, make sure to update only the fields that are password-related accordingly and press blank enter to leave the rest of the fields unchanged.

Go inside the QMetry Report app docker container:
- docker exec -it qmetry-reports bash
sh ConfigureSyncApp.sh
- This will run the sync script and the user will be prompted with report-related configurations/questions, simply press enter to leave the fields unchanged. Only update the following prompts:
  - Enter MySQL Database Password (Qmetry): <Enter the updated password> (i.e. QMetry@123)
  - Enter MySQL Database Password (Reports): <Enter the updated password> (i.e. Game123$)
  - Enter Port to Start SyncJob (Reports): 8080
  - Do you want to run force sync now?: y
  - Enter Memory to allocate in GB(s): Enter 3/4th of RAM value (for example, for 16GB RAM on the server - enter 12 as value).

Wait for the background sync to complete.

After the script execution is completed, Login to QMetry as "admin" user, go to Customization > Insight Configuration > “Database Configuration” > Re-type Password > Test > Test & Save.
Switch to the “Sync Data” tab > Test > Test & Save.