Thursday, 20 October 2011

Accessing RDS using HeidiSQL

Whilst looking at family-tree type things yesterday at work (it was for a work-based project, honest!) I remembered that my boss was looking at a way to administer our database on RDS. After reading shed loads of documentation, which said that it couldn't be done or that you needed to jump through loads of hoops I managed it.

Basically what I had to do it connect to it via one of the instances we already have running on AWS.

When we log into RDS we use this command on one of the instances running in order to get access to the mysql commandline:

mysql -u [username] -p[password] --database=[database name] --host=[location of RDS instance]

So for instance when we use puTTY to log into our Development instance we issue the above command to get into the RDS instance - there doesn't seem to be a direct way of accessing it.

HeidiSQL can understand that work-flow so in the Session manager of HeidiSQL create a new Session and call it "RDS" for example.

On the Settings tab select SSH tunnel as the Network Type, enter the host variable from the command detailed above into the Hostname / IP field (i.e. [location of RDS instance]). In our case User is [username] and Password is [password]. We'll keep Port as 3306, keep the Compressed client/server protocol tickbox ticked and enter [database name] in the Databases field.

Next click the SSH tunnel tab. Locate plink.exe on your hard drive or download it (handy link on the tab itself). The SSH host + port is the instance you just copied the command from. So for us it's [some subdomain].[some domain].com, port 22. Username is [not necessarily the same username as above] (the username you use to access the AWS instance). Leave the Password field blank and locate your ppk file (which you converted from your initial pem file using PuTTYgen.exe a while ago). Leave the Local port as 3307.

Click Save and then Open and Bob's your Uncle and Fanny's your Aunt!

Happy browsing of the DB!