Mysql – How to remotely manage (with PhpMyAdmin) a MySQL RDS on a load-balanced AWS Elastic Beanstalk setup

amazon ec2MySQLpermissionsphpmyadminssl

Although there are similar questions and topics, I can't find a solution that currently works.

I specifically wrote remotely manage because I'd like to use my home computer's PhpMyAdmin to directly connect to the RDS instance and keep its 'public accessibility' to private.

What I've done so far:

  • installed PhpMyAdmin on my home computer (mac osx) and got it working
  • added an inbound rule for the security group used by the RDS (rule: my.home.network's.ipv4/32, port 3306)

  • configured my mac's phpmyadmin conf.inc.php according to this suggestion. I've never done SSH tunneling, but the idea seems safer than opening 3306 to the public. So I've set it up accordingly, but these are the PhpMyAdmin errors I get:

    • Cannot log in to the MySQL server
    • mysqli_real_connect(): MySQL server has gone away
    • mysqli_real_connect(): Error while reading greeting packet. PID=xxxx
    • mysqli_real_connect(): (HY000/2006): MySQL server has gone away

Any ideas what I'm missing/doing wrong? Also, do I need to have MySQL at all installed on the mac? I assumed I didn't need it since I'd be using the remote one, but I've installed it anyway, and tried both with and without it. Same errors.

Thank you!

Best Answer

Ok, so I finally figured it out. I was close. It turns out I was doing the SSH tunneling wrong, and that's because I had no idea what I was doing. So after a bit more research I realized that I needed to change the remote host to be the RDS, not the EC-2.

So I ended up running the ssh command like this:

ssh -L 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

Or add -fN if you want to run it in the background:

ssh -fNL 3306:my-rds-endpoint:3306 ec2-user@my-ec2-endpoint -i /path/to/aws-eb/key

The config.inc.php ended up being configured like so:

$cfg['Servers'][$i]['host']          = '127.0.0.1';
$cfg['Servers'][$i]['port']          = '3306';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['auth_type']     = 'cookie';

So yeah, my issue was understanding the ssh command, and realizing that I needed to change it so it would tunnel to the RDS endpoint through the EC2 instance. Also, I had some weird issue with my mac's local MySQL still running in the background, so it wouldn't allow a new connection to the 3306 port. Once I ran mysqladmin -u root -p shutdown PhpMyAdmin could finally connect to the RDS!

Related Question