As outlined in previous blogs, we’ve been playing around in our “lab” with what we can do with Opsview and how it can play with other things. This time around I wanted to take a look at Amazon RDS and see how we would interact with that as a back-end for Opsview, instead of a local or remote MySQL installation.
What is Amazon RDS?
Amazon’s official website says that RDS (Relational Database Service) is “… a web service that makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business.”
In other words, you can deploy a database “in the cloud” via the Amazon web services console, and specify lots of different variables such as “instance class” (How powerful should it be?), automatic backups, Multi-zone deployment, etc (lots of cool things for those wanting to ensure high availability and resiliency, a main worry of cloud punters).
To deploy a new RDS database takes very little. First (assuming you already have an AWS account, etc) you must navigate to the RDS section and click “Launch a DB instance” which will provide a page as below:
In our example we’re using MySQL (as we don’t support Oracle or Microsoft SQL Server). After clicking “Select”, we must enter our “DB instance details” as below in our example:
As we can see above, we’ve chosen “MySQL 5.6.12” and an instance class of db.m1.medium (purely arbitrarily choice at this moment). We’ve also chosen just 10GB of storage, no provisioned IOPS and a few parameters (instance ID, username and password) as below:
- DB instance identifier: opsviewdbseperate
- Master username: opsviewrw
- Master password: opsview123
Next, we need to choose some add some more details on “Additional configuration” such as zone, database port, VPC (must use VPC if we are using Opsview Pro/Enterprise**), etc.
Once this is all done, we will have a fully provisioned, 10GB MySQL 5.6 database running as below:
Before we can start using this DB, we have to allow ourselves access to the database so lets click on “Security groups: default” and then click on the “magnifying glass” which will bring us to a page entitled “Security group details”. Here we need to ensure our CIDR range, or simply choose the security group we wish to apply to allow inbound access. And that should be the configuration on the RDS-side done!
Configuring our RDS DB for Opsview
Next we must SSH into our Opsview server, and migrate the database from it to the RDS DB – following the migration guide here: http://docs.opsview.com/doku.php?id=opsview4.3:dbs-on-diff-server
Firstly, we must stop Opsview to get a “consistent snapshot”:
/etc/init.d/opsview stop /etc/init.d/opsview-web stop
Next, we need to back-up and restore. In the document it states we should mysqldump, transfer to our DB host, and then gunzip into MySQL – but, this isnt possible in RDS as we don’t have shell access to the server underneath it – so we must be a little smarter in how we do this.
Firstly, lets test we can login to the RDS DB from Opsview:
root@opsview-master:/home/ubuntu# mysql -u opsviewrw -p -h opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 141 Server version: 5.6.12 MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Looks good to me! Next, we need to create the databases on the RDS database – very simple to do:
create database opsview; create database odw; create database runtime; create database dashboard;
This creates our 4 databases required. We can now drop out of shell access to the RDS DB and run mysqldump on the Opsview server, to dump the databases and pipe the output into the remote RDS database, as below:
mysqldump -u root -p opsview | mysql -u opsviewrw -popsview123 --host=opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 -C opsview mysqldump -u root -p runtime | mysql -u opsviewrw -popsview123 --host=opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 -C runtime mysqldump -u root -p odw | mysql -u opsviewrw -popsview123 --host=opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 -C odw mysqldump -u root -p dashboard | mysql -u opsviewrw -popsview123 --host=opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 -C dashboard
Next, we must edit “/usr/local/nagios/etc/opsview.conf” on our Opsview master, to point to the new RDS DB:
root@opsview-master:/home/ubuntu# cat /usr/local/nagios/etc/opsview.conf # # This file overrides variables from opsview.defaults # This file will not be overwritten on upgrades # [removed for brevity] $dbhost = "opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com"; $odw_dbhost = "opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com"; $runtime_dbhost = "opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com"; $reports_dbhost = "opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com"; $dashboard_dbhost = "opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com";
Almost there now! Next, we need to set-up access permissions on the RDS database. Again, we cant export it to a file, transfer the file, and import it – so we have to be clever with piping:
/usr/local/nagios/bin/db_mysql -t | mysql -u opsviewrw -popsview123 -h opsviewdbseperate.ctw1cn2efche.eu-west-1.rds.amazonaws.com -P 3306 This will get our credentials and import them into the RDS DB (snapshot of what these credentials look like is below): GRANT ALL ON opsview.* TO opsview@localhost IDENTIFIED BY '508E67C6-DA75-11E2-882D-94E35B00F56E' WITH GRANT OPTION; GRANT ALL ON opsview.* TO opsview@'%' IDENTIFIED BY '508E67C6-DA75-11E2-882D-94E35B00F56E' WITH GRANT OPTION; GRANT SELECT ON opsview.* TO odw@'%' IDENTIFIED BY '508E67C6-DA75-11E2-882D-94E35B00F56E';
Finally, we need to re-generate the Opsview configuration and start the web service again, using the commands:
/usr/local/nagios/bin/rc.opsview gen_config /etc/init.d/opsview-web start
And that’s the configuration done. We can stop the MySQL server running locally (service mysql stop, etc depending on your OS) and then login to Opsview to see it running in all its glory, now upon RDS storage:
I’ve used an example as below to show that historical data is migrated (proving it), and that there is no blip in the data (no massive losses, etc).
Closing thoughts
So that’s a quick and simple method to migrating your Opsview DB to Amazon RDS – to allow you better scalability, reliability and ease of use for your database back-end if you running in the cloud. Next up, I’ll take a look at running Opsview in a VPC and how we set that up.
