Moving the MI database to a remote DB server

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
  • 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 this article 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 

4. Move local DB on the application server to the remote DB server

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

For example, moving EC2 local db to RDS.

NOTE: For additional migration options please see mi-db-move -h (Step 9 below)

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

 

5. (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>

6. 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.

 

7. Set the timezone on the remote database server

8. Start cron service on the application server

9. 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.

 NOTES:

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).

0 Comments

Add your comment

E-Mail me when someone replies to this comment