Monitoring and Managing Amazon RDS Databases using MySQL Workbench


Last couple of week I was looking for an easier approch to manage the Databases created on Amazon RDS instances. I had to run through a tedious set of steps in carrying out routine stuff like introducing new updates, taking daily backups and moving it to an Amazon S3 bucket, etc… Before getting into touch with MySQL Workbench had to run through several intermediate hops (Connect to one of the EC2 instance via SSH and connect to the RDS instance from there, ;-( ) even to run a small query on the tables.

Let me eleborate the step I followed in setting up the connect with the DB created on a Amazon RDS instance.

Step 1: Launch MySQL Workbench

Launch MySQL Workbench and select New Server Instance found under Server Administration section form the Welcome screen as shown in Figure 1.

MySQl Workbench Welcome Screen

Figure 1: MySQl Workbench Welcome Screen

Step 2: Specify Host Machine of the Database server

Next form the Create New Server Instance Profile wizard, provide the Public DNS of the Amazon EC2 instance under Remote Host as shown in Figure 2 (this EC2 instance will be as the intermediate point to access DB that resides inside the Amazon RDS instance). To find the Public DNS specific to the EC2 instance can be done as illustrated in Figure 3. Click Next button to procced.
Specify Host Machine

Figure 2: Specify Host Machine of the Database screen

Amazon EC2 Instance

Figure 3: Amazon EC2 admin console screen

Step 3: Creating the DB connection

Fill in the values as described below which illustrated in Figure 4.

  • Connection Name – Provide a name to identify your connection
  • Connection Method – Select Standard TCP/IP over SSH from the drop down list instead of Standard(TCP/IP). Then on the appearing two tabbed pane, fill the following values found under Parameters tab.
    • SSH Hostname – Provide the Public DNS of the Amazon EC2 instace (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
    • SSH Username – Provide the user of the Amazon EC2 instance (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
    • SSH Key File – Provide the Private Key (xxx.pem) used to connect Amazon EC2 instace via SSH
    • MySQL Hostname – Provide the Endpoint of the DB instance created in Amazon RDS(refer Figure 5).
    • Username – Provide the Master Username of the DB instance created in Amazon RDS(refer Figure 5).
    • Password – Click on Store in Keychain.. button and type the password provided while creating the DB instance in Amazon RDS.

Click Next button to procced.

Database Connection

Figure 4: Database Connection screen

Amazon RDS DB Instance

Figure 5: Amazon RDS DB Instance

Step 4: Testing the Database Connection

In this step it validates the parameters provided in the previous step and upon success it returns Database connection tested successfully(refer Figure 6). Upon this message the Next button get activate to procceds to Management and OS.

Test DB Connection

Figure 6: Testing the Database Connection

Step 5: Management and OS

Select SSH login based management option and select the appropriate parameters for Operating System, MySQL Installation Type (refer Figure 7).

Management and OS

Figure 7: Specify remote management type and target operating system

Step 6: Setting up remote SSH Configuration

Provide the following parameters

  • Host Name – Provide the Public DNS of the Amazon EC2 instace (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
  • User Name – Provide the user of the Amazon EC2 instance (refer Figure 3) which will be used as the intermediate server instance used to create the connection with the DB instance.
  • Select Authenticate Using SSH Key and provide the SSH Private Key Path – Provide the Private Key (xxx.pem) used to connect Amazon EC2 instace via SSH

Remote SSH Configuration

Figure 8: Remote SSH Configuration

Step 7: Test Host Machine Settings

In this step it validates the parameters provided in the previous step and upon success it returns Testing host machine setting is done(refer Figure 9). Upon this message the Next button get activate and clicking Next buttin it popup Review Settings dialog box (refer Figure 10).

Test Host Machine Settings

Figure 9: Test Host Machine Settings

Review Settings

Figure 10: Review Settings

Step 8: Creating the Instance Profile

Provide a name for the Profile got created.

Creating the Instance Profile

Figure 11: Creating the Instance Profile

Step 9: Done

Success

Figure 12: Workbench Home screen with the new profile

19 Comments

  1. timrpeterson said,

    July 23, 2012 at 12:54 pm

    Thanks for this awesome tutorial. I was up and running with my RDS database in just a few minutes!

  2. August 15, 2012 at 11:10 am

    So, if I’m reading this correctly, one has to launch and EC2 instance, and an RDS database. Correct? I find that confusing. Why should it be necessary to go through an EC2 instance? It must have taken a lot of work to figure this out.

  3. jorge said,

    September 27, 2012 at 6:58 pm

    i have this error :

    Could not connect: Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

    what should i do?
    thanks

  4. sudath said,

    October 26, 2012 at 9:11 am

    This is really helpful. awesome thanks

  5. Ian MacGregor said,

    November 19, 2012 at 2:38 am

    On testing host machine settings it passes Connect to host machine successfully, then fails on the last two checks with /etc/init.d/mysql start is invalid. That’s because mysql server is not on ec2, but if I install it there it will use that instead of the rds. So how do you setup mysql on ec2 to pass everything along to rds?

  6. Ezhilan said,

    November 21, 2012 at 7:28 pm

    hi, I need to deploy various applications is cloud , but i need to make sure that certain DB is accessed only by its own application which is built for that. If another application tries to access the same DB, the DB should reject that request as the request is not from mother application. Can you please give me some idea about this. Thanks in advance.
    Regards,
    Ezhil From INDIA

  7. AdrianT said,

    January 1, 2013 at 2:00 pm

    Saved me hours of work to connect to RDS! Thanks!

  8. mikeblevins said,

    January 2, 2013 at 6:34 pm

    Thanks so much for this, worked like a charm

  9. January 17, 2013 at 4:32 am

    Thanks for your time for writing “Monitoring and Managing Amazon
    RDS Databases using MySQL Workbench « tHOUGHTS & iDEAS”.
    I actuallywill surely end up being coming back for much more reading through and commenting soon.

    I am grateful, Jillian

  10. January 23, 2013 at 6:11 am

    This really is the 4th blog, of urs I actually went through.
    And yet I actually love this specific 1, “Monitoring and Managing Amazon RDS
    Databases using MySQL Workbench « tHOUGHTS & iDEAS”
    the most. Regards -Elmo

  11. terryhoeman said,

    January 23, 2013 at 11:23 am

    I’d just like to add that you also need to open all the ports i.e. MYSQL, HTTP and SSH on the EC2 instance otherwise it wont let you connect.

  12. Jamel Hamas said,

    September 16, 2013 at 12:32 pm

    @Ian MacGregor
    I have the same problem /etc/init.d/mysql start is invalid,
    Have you found solution ?!
    thanks

  13. Savvas said,

    October 2, 2013 at 2:09 am

    There’s something clearly flawed with this. The description of this method details that it will be connecting to the DB In a private subnet though a bastion host, however it actually just connects to the bastion host directly. This is why people are getting failures on the last two tests because those two file’s don’t exist on the bastion host they are being checked on. I don’t know how this has worked for anyone using the steps detailed here.

  14. October 2, 2013 at 4:02 am

    […] the VPC and I would like to connect to it using MySQL workbench. I have followed the steps detailed here, however in “Step 6: Setting up remote SSH Configuration”, it asks me to “Provide […]

  15. jasonmogera said,

    July 9, 2014 at 1:00 pm

    I am trying to follow your tutorial, but trying to determine the username to put for the EC2 instance I have created. I have the public dns, but not sure where to acquire the username.

  16. Tirtha Rahaman said,

    August 3, 2014 at 2:08 pm

    Hi Jason,

    For Amazon Linux, the user name is ec2-user. For RHEL5, the user name is either root or ec2-user. For Ubuntu, the user name is ubuntu. For Fedora, the user name is either fedora or ec2-user. For SUSE Linux, the user name is root. Otherwise, if ec2-user and root don’t work, check with your AMI provider.

  17. September 13, 2014 at 8:08 am

    thanks…really help me out.

  18. November 25, 2014 at 4:48 pm

    […] Monitoring and Managing Amazon RDS Databases using MySQL … – May 17, 2012 · Last couple of week I was looking for an easier approch to manage the Databases created on Amazon RDS instances. I had to run through a tedious set of …… […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,050 other followers

%d bloggers like this: