How to connect to MySQL on Amazon EC2 from Linux / Mac?

Last updated on September 14th, 2020 at 04:45 am

You may want to connect to MySQL on Amazon EC2 from Linux / Mac laptop. But you may not want to open any port on EC2 instance for security reasons. You can forward your local port over Secure Shell (SSH) instead. Now when you connect to your local port, it is forwarded to connect to MySQL server on Amazon EC2 over SSH instead. No need to open the port of your MySQL server on EC2 instance. Since it is over SSH, no one can track it either. Here’s how you can connect to MySQL on Amazon EC2 from Linux / Mac using SSH tunnel.

If you are not familiar with SSH tunnel, you can read about connecting to Amazon EC2 instance from Linux / Mac using SSH.

It’s really easy. Just open the shell/terminal and type a command with the syntax

[sudo] ssh -i "$key" -f -N -L $local_port:127.0.0.1:$remote_port $user@$ec2

What it means:

$key – location of your private key file (.pem) downloaded from Amazon while creating EC2 instance

$local_port – port on your local machine that you are forwarding to MySQL server on EC2. This is the port that listens to connection requests

$remote_port – port on which MySQL server runs on EC2. It is usually 3306

$user –

• For an Amazon Linux AMI, the user name is ec2-user.
• For a RHEL AMI, the user name is often root but might be ec2-user.
• For an Ubuntu AMI, the user name is ubuntu.
• Otherwise, check with your AMI provider.

$ec2 – Public IP or public dns name of EC2 instance

E.g:

sudo ssh -i "/tmp/private_key.pem" -f -N -L 3100:127.0.0.1:3306 ubuntu@192.150.15.1

This will forward port 3100 on your laptop to port 3306 on 192.150.15.1. So when you try connecting to port 3100 on your laptop it will be sent to 192.150.15.1. After reaching EC2 instance, it is sent to 127.0.0.1:3306. In this case, 127.0.0.1 refers to 192.150.15.1 and not your local machine. This will run the ssh session in background. It needs to be running whenever you try to connect to MySQL database.

Connect to MySQL on Amazon EC2 from Linux / Mac

Here for reference is an example connection using MySQL Adminstrator going to localhost: note the Server Host address of 127.0.0.1 which will be transparently forwarded.

You can also open a shell / terminal and type (e.g password is ‘passwd’)

mysql -h 127.0.0.1 --port 3100 -uroot -ppasswd

It means connect to port 3100 on your localhost. This forwards the connection to the EC2 instance’s MySQL server listening on port 3306.