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. NOTE: 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 and MySQL services on the application server

2. Change 'host' in /opt/mi/config/insights.conf

host=<Remote MySQL server hostname> 

3. Perform the following changes in the '~/.my.cnf' file:

host=<Remote MySQL server hostname>
user=<Remote MySQL server hostname admin user>
password=<Remote MySQL server hostname admin user password>

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

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

For example, moving EC2 local db to RDS.

NOTE: This will also rename the dashboard db to the "hostname/ip address" of the machine.

5. Rename DB on remote DB server back to dashboard or some other desired name

mi-db-rename -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.

Important: If you are moving the database to Amazon RDS, you must set:

log_bin_trust_function_creators = 1

NOTE: You need to restart the MySQL service on the remote server to apply the changes.

7. Set the timezone on the remote database server

8. Restart cron and MySQL services on the application server

9. Confirm the db move is successful by checking the following:

  • Homepage loads properly
  • Status Monitor page shows the correct server time
  • Point the Dashboard DB data source connection to the remote host and confirm test connection is successful
  • 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 [-hvnN -f FILE -U USER -P PASS] newserver adminuser adminpass

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

 NOTES:

  • If -U and -P are not passed in, the destination app user and password will be assumed to be the same as those specified in /etc/mi/insight.conf
  • The current machine's MI instance will be updated to point to the new database server, unless you explicitly request no config updates using the -N option.
  • When renaming the database, only one of -r or -d is necessary.
  • The -r option will default to renaming the database based on the output of $ (hostname -s). If you don't want that, use the -d option.
OPTIONS
-h this screen
-v be verbose
-n dry run
-N don't update local config files to point to new database
-f backup file to create (or reuse)
-r rename app database to adam before copying,see also -d below
-d DB rename app database to DB before copying
-l restrict database users to only allow them to connect from localhost
-U USER destination DB app user
-P PASS destination DB app user's password

0 Comments

Add your comment

E-Mail me when someone replies to this comment