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