Moving the MI Database to a Remote DB Server (v6.2+)

This article provides a step-by-step guide on how to move the Metric Insights default and dataset databases from the application server to a remote database (DB) server.

PREREQUISITES

There are a few things you must have ready before migrating the Metric Insights DB to a new remote DB server:

  • MySQL 8+ with root username and password
  • Remote DB server requirements:
    • 32-64 GB RAM
    • 1 TB allocated to /var
  • Root username and password for host
  • Open port 3306 for SQL connections
  • MySQL parameters that are appropriate for the remote DB server based on those specs (Refer to Fine-Tuning MySQL Parameters (v6.2+) for more information)

Once you have the information above, you are ready to proceed with moving the DB.

1. Make a Backup of the MI App

  1. SSH to the app server
  2. Get inside the MI web container:
    • Simple Installation: sudo mi-console
    • Orchestrated Environments:
      • Docker Swarm: docker exec -it <web container ID> bash
      • Kubernetes: kubectl exec -n <namespace> -it <pod> -- bash
      • OpenShift:
        • Single-container pod: oc exec -it <pod> bash
        • Multi-container pod: oc exec -it <pod> -с <container> bash
  3. Run mi-app-backup -v to create a backup

2. Stop Cron Service on the Application Server

Run service cron stop from the web container.

3. Move DBs from App Server to the new Remote DB Server

PREREQUISITE:

Follow the instructions to update the MySQL parameters.

Note: We recommend to set the timezone on the new DB server equal to MI application timezone.

If you are moving the database to Amazon RDS, you must set the following first before moving the DB:

log_bin_trust_function_creators = 1

gtid_mode = OFF

gtid-mode=OFF

  1. mi-db-move -v -t default <remote db host> <remote db root user> <remote db root password>
  2. mi-db-move -v -t dataset <remote db host> <remote db root user> <remote db root password>

For additional migration options please see mi-db-move options.

4. Grant DB rights on, and Point App to, the new Database

In order to configure a DB that is located on a remote server, you need to find out whether or not its timezone is the same as MI app's timezone:

  1. Access the web container
  2. Access MySQL: mysql
  3. Find out the database timezone: select @@time_zone;
  4. Find out the MI app timezone: select @@system_time_zone;

Once you have found out whether or not the remote DB server has the same timezone as MI app, proceed configuring the new DB according to your MI app version. See the corresponding section for:

Prior to MI v6.2.5

For any MI version prior to 6.2.5:

If your database was initially installed with MySQL on localhost and has been moved to a remote database server, please do the following on your localhost machine:

  1. Access /opt/mi/config/deployment/credentials/web.env  and update the MYSQL_ROOT_USER, MYSQL_ROOT_PASSWORD,  MYSQL_DATASET_ROOT_USER parameters with correct DB username/password values
  2. Access /opt/mi/config/deployment/credentials/mysql.env and update the MYSQL_ROOT_PASSWORD parameter with correct DB user password value
  3. Re-run the installer:
    • If the remote DB server has the same timezone as MI app: sudo ./installer.py -v --components web,dataprocessor,seed --db-hostname <remote DB host> --db-user <remote DB root username> --db-password <remote DB root user password> --timezone <MI app timezone> --bind-address 0.0.0.0
    • If the remote DB server has a different timezone than MI app: sudo ./installer.py -v --components web,dataprocessor,seed --db-hostname <remote DB host> --db-user <remote DB root username> --db-password <remote DB root user password> --timezone <MI app timezone> --dp-mysql-option '&serverTimezone=<DB server timezone>' --bind-address 0.0.0.0 --dp-options '\-Duser.timezone=<MI app timezone>'
  1. Re-run the MI installer on app server. Example:

sudo ./installer.py --timezone PST8PDT --bind-address  0.0.0.0 --dp-hostname dataprocessor --seed-hostname seed  --db-hostname <remote DB host>  --db-user <remote DB root username> --db-password <remote DB root user password> --components  web,data-analyzer,dataprocessor,seed,monitoring -vv

  • List of the supported timezones:
    • ./installer.py --timezone-list
MI v6.2.5+
  1. Run the installer:
    • If the remote DB server has the same timezone as MI app: sudo ./installer.py --timezone <MI app timezone> --bind-address  0.0.0.0 --dp-hostname dataprocessor --seed-hostname seed  --db-hostname <remote DB host> --db-user <remote DB root username> --db-password <remote DB root password> --components  web,data-analyzer,dataprocessor,seed,monitoring -vv
    • If the remote DB server has a different timezone than MI app: sudo ./installer.py --timezone <MI app timezone> --bind-address 0.0.0.0 --dp-hostname dataprocessor --seed-hostname seed --db-hostname <remote DB host> --db-user <remote DB root username> --db-password <remote DB root password>  --mysql_timezone <MySQL timezone> --components web,data-analyzer,dataprocessor,seed,monitoring -vv

Note: If the MI installer notices that the username and password on the remote DB are different from the ones listed in /opt/mi/config/deployment/credentials/web.env  and /opt/mi/config/deployment/credentials/mysql.env, you will be asked to re-write the username and password for your DB. Please contact your DB administrator to ensure that the credentials are correct.


You can also check the credentials manually:

  1. Access the web container
  2. cat config/insight.conf
  3. Check the remote DB hostname
    • Can be found under [DEFAULT] parameter section > host parameter
  4. Access MySQL by running: mysql -h<host name of the RDS> -u<Remote DB root username> -p<Remote DB password>

If you can access the remote DB with these credentials, then you can let the installer re-write the old ones; otherwise, contact your DB administrator.

5. Start Cron Service on the Application Server

Run service cron start from the web container.

6. Confirm the Database Move is Successful

Check the following:

  • Homepage loads properly
  • Status Monitor page shows the correct server time
  • Dashboard DB data source profile shows host is set to remote DB; use test connection to confirm
  • Data Collection Triggers are running without errors
  • Elements sourced against Existing Metrics/Reports validate without error 

mi-db-move options 

# mi-db-move -h

USAGE: 

mi-db-move <DB_HOST> <ADMIN_USERNAME> <ADMIN_PASSWORD>
mi-db-move [-hvnxlN -f FILE -p PORT] <DB_HOST> <ADMIN_USERNAME> <ADMIN_PASSWORD>

 Move the MI instance database configured in /opt/mi/config/insight.conf to another database server.

POSITIONAL ARGUMENTS (required)
DB_HOST Address of the destination DB host for moving MI database.
ADMIN_USERNAME Username that has admin rights (GRANT) in DB-engine on DB_HOST.
ADMIN_PASSWORD Password for ADMIN_USERNAME.
OPTIONAL ARGUMENTS (not required)
-h This screen.
-v Be verbose.
-n Dry run.
-f FILE Backup file to create (or reuse).
-x Prune unnecessary log files from database in DB dump.
-p PORT Destination DB port (default: 3306).
-t TARGET_DB Select target database group to move. Possible choices: default,dataset. Default target includes:
1. Default application's database.
2. Reports databases.
Dataset target includes only dataset database. This option must be set by default. The movement will be impossible without this option.

EXAMPLES:

mi-db-move -t default 192.168.35.3 root password

This command moves MI instance application's database on host by address 192.168.35.3 and reconfigures /opt/mi/config/insight.conf to look at this new DB host.    

mi-db-move -v -t dataset db1.local.com root password

This command moves MI instance dataset database on host by address db1.local.com and reconfigures /opt/mi/config/insight.conf dataset sections to look at this new DB host. Also is enabled verbose of execution.    

mi-db-move -n -t default db2.local.com root password

This command does anything without applying changes, dry run mode.