How to connect to MySQL on Amazon EC2 from Windows?

Last updated on September 14th, 2020 at 06:57 am

You may want to connect to MySQL on Amazon EC2 from Windows 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 Windows using SSH tunnel.

If you are not familiar with connecting to Amazon EC2 instance from Windows using SSH, you can learn about it here

Step 1 : Create the tunnel

Create a session in PuTTY and then select the Tunnels tab in the SSH section. In the Source port text box enter 3100. This is the port PuTTY will listen on on your local machine. It can be any standard Windows-permitted port. In the Destination field immediately below Source port enter 127.0.0.1:3306. This means, from the server, forward the connection to IP 127.0.0.1 port 3306. Here MySQL server is running on the EC2 instance. So 127.0.0.1 is treated as EC2 instance’s local IP and not your local machine. MySQL by default listens on port 3306 and we’re connecting directly back to the server itself, i.e. 127.0.0.1.

Step 2 : Add the tunnel

Click the Add button.

Step 3 : Start a PuTTY session

1. Start PuTTY (from the Start menu, click All Programs > PuTTY > PuTTY).
2. In the Category pane, select Session and complete the following fields:
   1. In the Host Name box, enter user_name@public_dns_name. Be sure to specify the appropriate user name for your AMI. For example:
      • For an Amazon Linux AMI, the user name is ec2-user.
      • For a RHEL5 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.
   2. Under Connection type, select SSH.
   3. Ensure that Port is 22. 
      
3. In the Category pane, expand Connection, expand SSH, and then select Auth. Complete the following:
   1. Click Browse.
   2. Select the .ppk file that you generated for your key pair, and then click Open.
   3. Select Session in the Category tree, enter a name for the session in Saved Sessions, and then click Save.
   4. Click Open to start the PuTTY session.
       
4. If this is the first time you have connected to this instance, PuTTY displays a security alert dialog box that asks whether you trust the host you are connecting to.
5. Click Yes. A window opens and you are connected to your instance. Please keep this window open whenever you want to connect to you MySQL database.

Step 4 : Connect to MySQL on Amazon EC2 from Windows

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 command prompt, navigate to MySQL installation folder (e.g C:\Program Files\MySQL\MySQL Server 5.5\bin) 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.