Accessing Amazon RDS Instance from Remote phpMyAdmin

imagesOne 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.

EC2_Management_Console-16

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.

RDS_Management_Console-8

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.

RDS_Management_Console-3

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.

phpMyAdmin

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.

  • Thanks, you are the man. As a note, didn’t have to create the tables via the script. Also had to add my EC2 Instances IP (not elastic but it’s Private IP) to the security group that my RDS instance is under (same as EC2) – and that gave me access to phpMyadmin and SSH access to the RDS instance. Thanks again.

  • Pingback: » Accessing Amazon RDS Instance from Remote phpMyAdmin | Rinzler()

  • Prasad Saxena

    I have a beanstalk environment and I am not able to setup the connection between rds and phpmyadmin installed on server. I always get a 403 error.
    Can you help me ?

    • Hi Prasad

      I don’t think that is a problem with RDS. Think is it more likely to be setup. Try the following:

      1. Edit the config file, probably in /etc/httpd/conf.d/phpMyAdmin.conf

      2. Look for the following line

      Allow From ::1

      3. Change to:

      Allow From All

      4. restart apache:

      sudo service httpd restart

  • Thalia

    hi,
    I am using ubuntu server 14.04 lts on EC2 instance… and in phpmyadmin I cant see config.inc.php… but I see a file config.sample.inc.php… How am I going to deal with this? I want to connect phpmyadmin with rds instance and the to connect my database with my website…

    • Hi Thalia,

      In order to get phpMyAdmin working you need to copy config.sample.inc.php to config.inc.php and edit it to put in your server details. Then you can follow the instructions here to hook up with RDS. Have a look at the following link for more details:

      http://docs.phpmyadmin.net/en/latest/setup.html

      • Thalia

        hi my friend and thanks for your answer…
        I have already done this procedure with the “copy config.sample.inc.php to config.inc.php and edit it to put in your server details”…. but nothing changed..! I went to that file /var/www/html/phpmyadmin and I did all you said…! I have followed also other instruction from other blogs where they were suggesting to go to /etc/phpmyadmin and do the modification to the file config.inc.php… but still nothing happened…
        really I am stuck… and tired..! maybe there is something small I dont see…
        I dont know…