Accessing Amazon RDS Instance from Remote phpMyAdmin

One of the great services offered by Amazon Web Services (AWS) stack is Amazon Relational Database Service (RDS). This is a properly managed database server that you can run alongside your existing application server instances allowing the load to be spread.

This all works well but management can be a little tedious especially if you have been used to managing your existing MySQL server through phpMyAdmin. However, do not despair as it is possible to manage RDS from phpMyAdmin running on a remote server and this post shows you how.

This article assumes that you already have both RDS and a server with phpMyAdmin installed running on CentOS. However, I have does this on Ubuntu too and the only real difference is where the config files are held.

Configuring RDS to allow access

The first thing to do is to establish the IP address of the server that is running phpMyAdmin. If server is running on Amazons EC2 and is in the same Amazon Region then you can use the internal IP address otherwise use the external IP address. To get the IP address for an EC2 server login to your Amazon console and either choose the “Private IP” is your server is in the same region as your RDS instance or take the Elastic IP. If you server is not hosted with Amazon you can ping the server to find the IP you need.

Next you have to allow access to the RDS instance from your server IP. To do this go back to the Amazon console and to your RDS Dashboard. Select “Security Groups” from the left hand menu and, if you haven’t already create a new security group by clicking “Create DB Security Group”. Add your ip address followed by “/32” and then click Add.

Now you need to associate the security group you create with your RDS instance. On the instance page select it and click Modify. On the next page select your Security Group and tick the Apply Immediately box. Continue and finally reboot the instance for it to take effect.

Setting up phpMyAdmin

Something that is not immediately obvious is that phpMyAdmin has the ability to support multiple servers. In fact there is no limit to the number of servers that you can access from it. When you add a new server it simply appears in a drop down box on the login page. You just select the server you want and enter in the correct credentials.

The key to multiple server support is simply to add another server section to the phpMyAdmin config file. On CentOS this can be found at:

/usr/share/phpmyadmin/config.inc.php

After the existing server section add a further one. The “$i++;” on line one is very important as without this it will overwrite you existing server details. Line three has your instance endpoint which you can find on your RDS dashboard.

$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'abc-rds-eu-1.czxcz7sdshsdf.eu-west-1.rds.amazonaws.com';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = '';
$cfg['Servers'][$i]['controlpass'] = '';
/* Advanced phpMyAdmin features */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
$cfg['Servers'][$i]['recent'] = 'pma_recent';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';

Save the file and when you go to the phpMyAdmin login page you should now see the Server Choice drop down on the login page.

Creating the phpMyAdmin Database

If when you login you find that you are getting error messages this is almost certainly because the phpMyAdmin database hasn’t been created. There is a setup SQL script provided that quickly and easily allows you to create and populate this database. The script can be found at:

/usr/share/phpMyAdmin/examples/create_tables.sql

Run the contents of this file via phpMyAdmin that you have setup and after that all should work as expected.