phpMyAdmin not working to connect to RDS from EC2 machine, out of ideas

Question

I have a new EC2/RDS setup. I've followed about 5 different guides, but cannot get phpMyAdmin to connect.

First off, it's not a security group issue. I've temporarily set the RDS server accepting all connections. I'm able to connect to it from the EC2 machine using mysql from the command line.

I installed phpMyAdmin from the EPEL repo.

I've quadruple checked my config.inc.php file. I believe it to be set properly.

$i++;
$cfg['Servers'][$i]['host']          = 'xxx.us-west-1.rds.amazonaws.com'; // MySQL hostname or IP address
$cfg['Servers'][$i]['port']          = '3306';      // MySQL port - leave blank for default port
$cfg['Servers'][$i]['socket']        = '';          // Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type']  = 'tcp';       // How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][$i]['extension']     = 'mysqli';    // The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][$i]['compress']      =  FALSE;       // Use compressed protocol for the MySQL connection
                                                    // (requires PHP >= 4.3.0)
$cfg['Servers'][$i]['controluser']   = '';          // MySQL control user settings
                                                    // (this user must have read-only
$cfg['Servers'][$i]['controlpass']   = '';          // access to the "mysql/user"
                                                    // and "mysql/db" tables).
                                                    // The controluser is also
                                                    // used for all relational
                                                    // features (pmadb)
$cfg['Servers'][$i]['auth_type']     = 'cookie'; 

Still, every time I try to connect using phpMyAdmin I get Error #2003 Cannot log in to the MySQL server

I'm really out of ideas. I uninstalled phpmyadmin and tried just placing all the files manually in the html directory, I got the same result. I've tried changing most of the settings in the config file (compress, connect type, auth_type), no luck. I can't believe I've been trying unsuccessfully to get phpMyAdmin working for like 2.5 hours now. No idea what's going on, all web searches come up dry...seems like I'm the only person who's had this problem.

If anyone has ANY idea that might help me figure this out, man would I appreciate it.

Answer 1

Couple of things are required for fixing this - one change in config.inc.php, another change in mysql user's table, and another on the AWS console...

First the host in /etc/phpMyAdmin/config.inc.php should be assigned to the internal private IP - not the external one. The internal one can be picked up from the AWS console page - Dashboard ->EC2 -> Instances. Choose the instance on the right and see the Private IPs field in the description tab and edit the following line in config.inc.php

$cfg['Servers'][$i]['host']  = xx.yy.zz.aa

Second - mysql needs to know that the root login request coming from the phpMyAdmin page is actually from an AWS internal IP address - the same as the ip described above and not the external one - so you need to update the "user" table on the mysql database as follows using the GRANT command

mysql -u root -p

use mysql

GRANT ALL PRIVILEGES ON *.* TO 'root'@'xx.yy.zz.aa' IDENTIFIED BY 'xxpassword';

Replace xx.yy.zz.aa with your internal ipaddress provided by EC2 Replace xxpassword with your root password for mysql

Restart apache and you should be good :)

Third - allow incoming connections from mysqlport in the security group that is used... This is done on the AWS console.

Type = MYSQL
Protocol = TCP
Port  = 3306
Source = 0.0.0.0/0

Answer 2

A bit late, but maybe this will help someone. I had the same issue, and I just needed to make sure that the MySQL port was open for the RDS's security group. ..worked for me

Answer 3

Thank you for the responses.

For anyone finding this discussion because they were having the same issue, there is a helpful comment left by Isaac Bennetch that can help you debug your issue.

For me, I wasn't able to solve, but eventually for different reasons I started another EC2 instance and this time phpMyAdmin worked immediately. I have no idea what my issue was, but hopefully nobody else faces something similar.

Answer 4

Can you try to add the below settings in the phpMyAdmin configuration and retry?

$cfg['Servers'][$i]['user'] = 'root'; //mysql username here
$cfg['Servers'][$i]['password'] = 'password'; //mysql password here