Amazon RDS: Remote DB with a difference
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 and lots more cool things for those wanting to ensure high availability and resiliency (a main worry of cloud users).
Deploying a new RDS database takes very little. Firstly, assuming you already have an AWS account, 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 (a purely arbitrary choice). 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 add some more details on “Additional configuration” such as zone, database port, VPC (we must use VPC if we are using Opsview Pro/Enterprise**).
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 database we have to allow ourselves access to the database, so let’s 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”:
Next, we need to back-up and restore. In the document it states we should mysqldump, transfer to our database host, and then gunzip into MySQL – but, this isn’t 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
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.
Looks good to me! Next, we need to create the databases on the RDS database, which is very simple to do:
create database opsview;
create database odw;
create database runtime;
create database dashboard;
This creates our four 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";
We’re almost there now!
Next, we need to set-up access permissions on the RDS database. Again, we can’t 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
<strong>This will get our credentials and import them into the RDS DB (snapshot of what these credentials look like is below):</strong>
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:
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 and that there is no blip in the data.
So that’s a quick and simple method to migrating your Opsview DB to Amazon RDS, which will allow you better scalability, reliability and ease of use for your database back-end if you are running in the cloud.