Using PHPMyAdmin to administer Amazon RDS

Question

I can't get PHPMyAdmin to connect to my Amazon RDS instance. I've granted permissions for my IP address to the DB Security Group which has access to this database I'm trying to access. Here's what I'm working with...

    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $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';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

    $cfg['Servers'][$i]['auth_type'] = 'http';  //is this correct?
    $cfg['Servers'][$i]['user'] = 'MASTER-USER';
    $cfg['Servers'][$i]['password'] = 'MASTER-USER-PASSWORD';
    $cfg['Servers'][$i]['hide_db'] = '(mysql|information_schema|phpmyadmin)';
    /* Server parameters */
    $cfg['Servers'][$i]['host'] = 'MY-DB.us-east-1.rds.amazonaws.com';
    $cfg['Servers'][$i]['connection_type'] = 'socket';
    $cfg['Servers'][$i]['port'] = PORT;

I'm not sure if my configuration is correct.

I'm getting this error:

#2013 - Lost connection to MySQL server at 'reading initial communication packet', system error: 110

Does anyone have any advice?

score 49 · Accepted Answer · answered Feb 20 '17 at 11:16

I found most of the answers in this question lack explanation. To add RDS server in phpMyAdmin installed in EC2, you can first login to your EC2 via SSH. Then, issue the following command to edit the Config file of phpMyAdmin (use vi, nano or any other favorite text editing tool):

sudo vi /etc/phpMyAdmin/config.inc.php # Amazon Linux
sudo vi /etc/phpmyadmin/config.inc.php # Ubuntu Linux

Find the following lines in config.inc.php:

/*
 * End of servers configuration
 */

Append the following lines above the "End of servers configuration" line:

$i++;
$cfg['Servers'][$i]['host'] = 'xxxxx.xxxxxxxxxx.us-east-1.rds.amazonaws.com';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['verbose'] = 'YOUR_SERVER_NAME';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['compress'] = TRUE;

which YOUR_SERVER_NAME is the name being displayed in phpMyAdmin login page's selection box. If you remove this line, the whole RDS hostname will be displayed in the selection box (which is too long, obviously). Remember to save the config.inc.php.

There are several other config settings, which you can find the details in the official documentation.

Note: The other answer suggests auto login with preset username & password in Config file:

$cfg['Servers'][$i]['auth_type']     = 'config';
$cfg['Servers'][$i]['user']          = '__FILL_IN_DETAILS__';
$cfg['Servers'][$i]['password']      = '__FILL_IN_DETAILS__';

which is extremely dangerous if your phpMyAdmin is exposed to public. You don't want to show your database schema to everyone, do you? If you really want to use automatic login, make sure your phpMyAdmin is accessible via specific IPs only.

I had to add my EC2 instance's private IP address to the inbound rules of the security group which my RDS instance was using in order to allow mysql connections to it. Just leaving it here for anyone having connection issues. — Talha Imam, Mar 19 '18 at 07:33
Thanks for the extra information. Yes, adding inbound rule is essential for a working connection. — Raptor, Mar 19 '18 at 10:08

score 22 · Answer 2 · answered Jan 12 '11 at 21:19

In Debian Lenny using the phpmyadmin from the repo add this to /etc/phpmyadmin/config.inc.php :

$i++;
$cfg['Servers'][$i]['host'] = 'xxxxx.xxxxxxxxxx.us-east-1.rds.amazonaws.com';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['compress'] = TRUE;
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'xxxxxxxxxxxx';
$cfg['Servers'][$i]['password'] = 'xxxxxxxxxxxxxxxxxx';

I found that specifying the port caused a log-in error. Also you can use 'auth_type' cookie and not specify user and password. — icc97, Nov 22 '11 at 17:33

score 14 · Answer 3 · edited Jun 20 '20 at 09:12

14

You need to add the RDS instance as an additional server listed on PHPMyAdmin while granting the host PHPMyAdmin access to your RDS instance.

More details from this blog post on How to remotely manage an Amazon RDS instance with PHPMyAdmin:

The one thing I had trouble with was the DB Security Group setup. When you go to add access for an CIDR/IP it provides a recommended value. It took some messing around to determine that this default value isn’t actually what needed to be there. If you’re not able to connect to your instance when it’s all said and done, be sure to double check this value. The IP they provided did not match the IP address that was provided to us by our ISP. Once you’ve created your DB Instance and setup the security group you’re good to go.

I’m going to assume you’ve already got PHPMyAdmin up and running. What you need to do is modify config.inc.php to recognize the new server.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 10 '10 at 15:21

Ben

60,438
111
314
488

18

Please do not just post links as answer. Post answer as text, and if needed, provide the link for further explanation. This way, if the link drops dead, the answer will still be there. – Valentin Rocher Dec 10 '10 at 15:26
If your RDS is in a VPC In regards to the security groups, AWS has a tutorial on connecting from EC2 to RDS within a VPC here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.Scenarios.html In particular you need to edit the VPC security group inbound rule to add the id of the security group that your EC2 instance is in (or if not using EC2 then the IP of the server you want to access the RDS) – Baxny Jul 14 '16 at 13:17
the link is dead. – Raptor Feb 20 '17 at 10:52
1

Link is still dead, answer is useless. This is why we don't use links. – Dave S Jan 16 '19 at 17:39
Link is dead, here is the page from Wayback: https://web.archive.org/web/20160322111825/http://blog.benkuhl.com/2010/12/how-to-remotely-manage-an-amazon-rds-instance-with-phpmyadmin/ – Edward Jan 31 '19 at 06:10

score 10 · Answer 4 · answered Dec 15 '10 at 00:25

10

Try to connect from the mysql command line (http://dev.mysql.com/doc/refman/5.1/en/mysql.html) and see what's this utility returns you. I found it's easier to debug that way.

mysql -hMY-DB.us-east-1.rds.amazonaws.com -uMASTER-USER -pPASSWORD

If that's doesn't work, it means your amazon RDS security aren't configured correctly. (which is the common problem).

answered Dec 15 '10 at 00:25

user539688

109
3

Using incompatible security group will cause this, thank you. – SleighBoy Jun 06 '11 at 21:20
This helped me rule out RDS, hostname, username and password. Unfortunately no matter how I set up my config I still get #2002 – trex005 Apr 17 '14 at 19:59

score 9 · Answer 5 · answered May 12 '16 at 18:21

sudo nano /etc/phpmyadmin/config.inc.php

--ADD LINES BELOW THE PMA CONFIG AREA AND FILL IN DETAILS--
$i++;
$cfg['Servers'][$i]['host']          = '__FILL_IN_DETAILS__';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['socket']        = '';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysql';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'config';
$cfg['Servers'][$i]['user']          = '__FILL_IN_DETAILS__';
$cfg['Servers'][$i]['password']      = '__FILL_IN_DETAILS__';

Save and Exit. Refresh your phpmyadmin page and you'll see a dropdown with the server that you just added

Source: https://github.com/andrewpuch/phpmyadmin_connect_to_rds, https://www.youtube.com/watch?v=Bz-4wTGD2_Q

score 2 · Answer 6 · answered Jul 29 '15 at 00:46

I had the same problem and nothing of the above solved it.

As it turned out the thing was in "Publicly Accessible" option of the RDS instance. When you create your instance this option is "no" by default and there is no place you can change it.

However, after creating the RDS instance you can make its snapshot, then delete the instance and then launch the snapshot. In launch options just set "Publicly Accessible" to "yes" and your phpMyAdmin will finally see you RDS MySql server.

Beware that DNS of the original RDS instance and the snaphot are different so you would have to change your 'host' in config.inc.php

Cheers :)

amazing, i already figured this out but i found no other answers related to this.Thanks mate — MR_AMDEV, Sep 22 '20 at 07:55

score 1 · Answer 7 · answered Feb 05 '18 at 14:29

1

First try this :

mysql -h xxxxx.xxxxxxx.xx-xx-2.rds.amazonaws.com -u root -p // Your RDS server.

If it waits and doesn't prompt for the password then you would need to check security group and add 3306 outbound to your Web Tier.

answered Feb 05 '18 at 14:29

Omkar Jadhav

1,046
3
16
41

score 0 · Answer 8 · answered Apr 27 '18 at 14:53

0

If you can connect from the cli using mysql -h ENDPOINT -u USERNAME -p but not from PHPMyAdmin or your own web scripts, don't forget to tell selinux to let your web server talk to the network :)

sudo setsebool -P httpd_can_network_connect=1

answered Apr 27 '18 at 14:53

Egg

1,782
1
12
28

score 0 · Answer 9 · answered Dec 20 '22 at 10:52

Summarily, possible things you need to check when somehow the phpMyAdmin seems not to connect to the config file at all.

RDS instance must be Publicly Accessible. You can go to the AWS console and modify the RDS instance again to switch it on.
RDS instance must open the firewall (Inbound rules).

Type: MySQL
Protocol: TCP
Port: 3306 (default)
Source: 0.0.0.0/0 --> For all access. Or Your server IP/32 (ex: 54.209.7.206/32) --> For specific access.

'auth_type' in config.inc.php must be 'cookie' (default)
config.inc.php must have the permissions of reading and executing. Command (Centos): sudo chmod -R 555 /etc/phpMyAdmin

score -1 · Answer 10 · answered Dec 11 '15 at 14:05

-1

I use ubuntu shell to access Amazon RDS. first of all go to ubuntu root

sudo -i

to do this use amazon RDS end user URL

mysql -h gelastik.cqtbtepzqfhu.us-west-2.rds.amazonaws.com -u root -p

answered Dec 11 '15 at 14:05

chamodck

1
2

Using PHPMyAdmin to administer Amazon RDS

10 Answers10

Linked