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

This article provides a step-by-step guide on how to move the Metric Insights dashboard database from the application server to a remote database server.

PREREQUISITES

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

  • MySQL 5.5+ with root username and password with root username and password:
  • Root username and password for host
  • Open port 3306 for SQL connections
  • Remote DB server specs
  • MySQL parameters that are appropriate for the remote DB server based on those specs (Refer to Fine Tuning MySQL Parameters for more information)

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

1. Stop Cron Service on the Application Server

2. Create a Backup of Metric Insights prior to Moving the Database 

mi-app-backup -v

3. Move Local DB on the Application Server to the Remote DB Server

Important: 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

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

3.1. Prior to Metric Insights v5.1 

mi-db-move -v <remote host> <remote db admin> <remote db password>

3.2. Metric Insights v5.1+

  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>

4. (Optional) Rename Database on Remote DB Server to Some Other Desired Name 

mi-db-rename -v -d <dashboard db name> -D <old dashboard db name> -U <remote db admin> -P <remote db password>

5. Fine-Tune MySQL Parameters

Once the move is complete on the remote DB server, follow the instructions in this article to update the MySQL parameters.

6. Set the Timezone on the Remote Database Server

7. Start Cron Service on the Application Server

8. Confirm the Database Move is Successful by Checking 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 [-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.

The current machine's MI instance will be updated to point to the new database server unless the you explicitly request no config updates with the -N option.

OPTIONS
-h This screen.
-v Be verbose.
-n Dry run.
-N Don't update local config files to point to new database.
-f FILE Backup file to create (or reuse).
-x Prune unnecessary log files from database in DB dump.
-l Restrict database users to only allow them to connect from localhost.
-p PORT Destination DB port (default: 3306).