Amazon RDS – The Beginner’s Guide
On the eve of Microsoft’s announcement of the public release of SQL Azure Database, Amazon decided to release its own relational database service (RDS). This is something they seem to have resisted offering for a very long time. Maybe it was in the works, or maybe this was a preemptive action against their competition. Whatever it may be, such a healthy competition can do much good for the Cloud marketplace.
RDS brings with it the promise of MySQL on the Cloud. Having preferred MySQL for quite some time, we were anxious to get an AWS account and see what the hype is all about. Imagine our confusion when we signed up for Amazon RDS and all the AWS Management Console showed was the EC2 dashboard. There was no obvious way to get started using RDS.
Command Line Interface Tools (CLI)
At the time of this article, there is not yet a graphical user interface for RDS. The only way to use RDS is through the command-line tools. Setting up the tools, however, can be quite difficult: There are no installers per se; you have to download the zipped package to your instance, extract it, and set the environment, manually. Here’s how we did it on a Windows XP box:
Prerequisites: The command-line tools are written in Java. So you need either JDK or JRE installed on your system.
Environment: There are a couple of environment variables that need to be set, manually:
- The JAVA_HOME variable, which contain the path of the Java runtime installed on the system.
- The AWS_RDS_HOME variable, which contains the path to the directory containing the CLI tools.
Based on the above, below is what we entered from the command-line:
C:\>set JAVA_HOME=E:\Java\jre6 C:\>set AWS_RDS_HOME=C:\Amazon RDS\CLI
Credential File: The zipped package containing the CLI tools also had a file named credential-file-path.template. We copied our AWS Access and Secret Keys to the place holders in the file, and then set another environment variable:
C:\>set AWS_CREDENTIAL_FILE=C:\Amazon RDS\CLI\credential-file-path.template
Path: Finally, we had to add the path of the CLI tools to the PATH variable:
C:\>set PATH=%PATH%;C:\Amazon RDS\CLI
Creating a Database Instance
We next focused on creating an Extra Large database instance, with an allocated storage of 5GB:
C:\>rds-create-db-instance --engine MySQL5.1 / --master-username root --master-user-password mypass --db-name WebyogTestData --db-instance-identifier webyogtestinstance / --allocated-storage 5 --db-instance-class db.m1.xlarge –-header DBINSTANCE DBInstanceId Class Engine Storage Master Username Status Backup Retention DBINSTANCE webyogtestinstance db.m1.xlarge mysql5.1 5 root creating 1 SECGROUP Name Status SECGROUP default active PARAMGRP Group Name Apply Status PARAMGRP default.mysql5.1 in-sync
The rds-decribe-db-instances command displays all of the running instances:
C:\>rds-describe-db-instances DBINSTANCE webyogtestinstance 2009-11-06T08:40:52.571Z db.m1.xlarge mysql5.1 5 root available webyogtestinstance.clc2ed76md1v.us-east-1.rds.amazonaws.com 3306 us-east-1d 1 SECGROUP default active PARAMGRP default.mysql5.1 in-sync
That was it. Our database was up and running.
Connecting SQLyog/MONyog to Amazon RDS
Here’s the best part about Amazon RDS: It has native MySQL 5.1 support. This means that you can use our favorite MySQL GUI tool to connect to an Amazon RDS database instance.
- SQLyog settings for RDS
We entered the master username and password, the same one given when we created the DB instance. For the host address, we used webyogtestinstance.clc2ed76md1v.us-east-1.rds.amazonaws.com It’s shown in the output of the rds-describe-db-instances command.
We then clicked on the Test Connection button. It connected successfully, as you can see below. Notice that SQLyog reports as having connected to MySQL 5.1.38-log.
- Success connecting to Amazon RDS
Setting up Monyog was as simple at this point. Below are screenshots of how the Amazon RDS instance looks from Monyog.
- MONyog displaying Amazon RDS DB stats
As you can see here, all of the usual monitoring tools from Monyog work just find when interfaced with a database instance from Amazon RDS. While it might be a bit of trouble to get things started with Amazon RDS, once you do, you can retreat back to Monyog and relax as you control your databases.
- MONyog displaying the InnoDB Cache stats
Maintenance
Periodically, the Amazon RDS system performs some maintenance of database instances. This ensures that your server is running smoothly. This also translates into a 4-hour down time period on a weekly basis. Within a weekly 4-hour user-definable window (i.e., the maintenance window), Amazon’s management system may start back-ups or roll out patches for the MySQL server. This may, however, result in a certain amount of downtime of your database instance. So, be prepared for that.
Conclusion
Amazon RDS is just a MySQL 5.1 instance running on an Amazon EC2 platform, but with all of the advantages of EC2. You can scale your server to use up to 68 GB of memory, 26 ECUs, and 1 TB of persistent storage. However, Amazon RDS doesn’t support replication, yet.
The release of Amazon RDS indicates a pattern. In 2008 when it became clear that Microsoft would announce a Windows-based Cloud, Amazon jumped in and announced support for Windows-based EC2 instances. And now, Amazon has done it again, this time with RDS in response to Microsoft’s SQL Azure. Competition with Microsoft seems to drive Amazon much more than requests from its users.
For us at Webyog this is an exciting development. We believe that our products (SQLyog and MONyog) are very fit for the Cloud. Our products are much more fit than the console-based tools that most advanced users still seem to use. We will now start checking our programs in detail with this. So far we’ve found no problems.
Want to Know More?
22 Comments
Add yours+ Leave a Comment
You must be logged in to post a comment.
[…] http://www.webyog.com/blog/2009/11/06/amazon-rds-the-beginners-guide/ […]
this is pretty exciting, im gonna go check it out myself. thx for the great article!
Hey Josh,
Glad that you liked the article. Do come back and check this blog later… We’re going to be doing a series of posts on Amazon RDS.
“Periodically, the Amazon RDS system performs some maintenance of the database instance. This ensures that your server is running smoothly. This also translates into a 4-hour down time period on a weekly basis.”
What? That’s a deal breaker for me. I haven’t read anything like this in the docs yet. Can you point me to your reference?
Thanks!
Hi Grig,
Funnily enough, the “Getting Started Guide” doesn’t mention anything about maintenance. However, try the “Developer’s Guide” (page 8): There’s a short and precise paragraph on maintenance. Also, the “Command Line Interface Reference” includes several allusions to “maintenance windows” and how to change the default window.
Grig,
I’ve made a mistake in my blog post with the maintenance window issue. It does NOT necessarily mean that you’ll have a 4-hour downtime period every week. I’ve made a correction to the post as well…
Hmmm.. I got an error connecting on my first DB instance.
There’s a need to authorize access to the db instance unless is not the first DB instance in RDS you got. 🙂
Hey Rodney,
You are absolutely correct. You have to set up access permissions for different users, and host IPs. In my opinion, these stringent security measures are very much needed. More so because the only way you can access your DB instance is through the Internet.
[…] on the server. For example, we created an extra large instance just of the purpose of the previous blog post. We only connected for a few minutes to test it but the server was idle for 99% of the time. At the […]
[…] are that your applications will work seamlessly with Amazon RDS as well. For instance, in one of my previous posts, I had described how to set up SQLyog and MONyog for an Amazon RDS DB instance. SQL Azure features […]
how i create table & insert data in Amazon RDS
@Vivek:
You could try SQLyog for that. Once you have your Amazon RDS instance set up (and don’t forget security settings for MySQL, and firewall settings available through the AWS interface) just put in the details into the connection settings dialog in SQLyog (screen-shots’ are in the blog post), and you should be good to go!
The right-pane in SQLyog shows you all available databases on that instance, and creating/delete new databases is a breeze through SQLyog’s GUI!
How did you install MONyog on an RDS instance? Seems fairly unlikely.
MONyog was not installed on RDS. MONyog connects from a remote machine. It works for MySQL on RDS (but system counters – CPU, Memory etc – are not available as reading the information over SSH is not supported by Amazon RDS).
Still don’t follow – MONyog looks to be a service you install locally. How was it setup in the example? It simply says, “Setting up MONyog was as simple.”
You are making a wrong assumption! This “MONyog looks to be a service you install locally” is wrong. MONyog will connect from any computer to MySQL on any other computer. Just like most other MySQL clients do.
Set up MONyog
* install it on an computer (Windows or Linux – for isntance the one you are sitting with right now).
* register the MySQL server on the RDS (use SSH tunnel and ensure that port 22 is open in the RDS)
* connect to MONyog from a Internet browser. MONyog exposes its information as a HTTP service.
The machine where MySQL runs, where MONyog runs and where the Browser opens may be 1,2 or 3 different machines. Please simply just try aand and do not make assumptions like ” .. looks to be .. ”
If you want to continue this discussion/have some problems/need help please discuss in our Forums ( http://www.webyog.com/forums/ ) and not here.
[…] It requires no agents on the server & hence can monitor MySQL instances running on cloud. Here is a post from my colleague on monitoring Amazon RDS with MONyog. According to our internal benchmark reports […]
hey. Thanks for the valuable information. When i was new with Amazon RDS, it was hard to change the my.ini parameters of mysql, as they don’t allow direct access to it. I found easy way to configure my.ini , may help some guys. http://amitech.co/amitech-lab/item/how-to-configurechange-myini-parameters-of-mysql-in-amazon-rds
Thank you so much!
Hey am new to amazon RDS. I have created a database on Amazon RDS. Please help to on how to access the tables and also modify them using java. I am working in eclipse.
there are plenty of samples available on simpleDB and dyanamoDB, but not on RDS for the above process in eclipse.
Hi,
Im new to RDS & we had our oracle deployed on RDS.
Now I want to know if the Schema on RDS can imported to the Local Server at our office?
We cannot help with Oracle RDBMS. We support our own programs (SQLyog and MONyog) only.