2

How do I connect to an AWS (or other) instance in a private subnet in a VPC using MySQL Workbench ?

My arrangement is a typical aws Scenario 2 :

I can:
- SSH into my NAT instance located in my VPC public subnet
- SSH from my NAT instance into the db instance in my VPC private subnet
- connect to MySQL as root user once connected to my private db instance

What is the correct procedure to set up the system to connect remotely via MySQL workbench ?

3

Connect to db in private subnet instance via NAT with MySQL Workbench


Create a mysql user who can connect remotely:

  • by default the root user cannot connect remotely
  • create a user who is allowed to connect from your IP address (or use % which means any address)
    -- connect to the db instance in the private subnet via SSH
    -- log into mysql as root and type password when prompted:
    mysql -h localhost -u root -p
    -- run the following query to create a user
    CREATE USER 'username'@'XX.XX.XX.XX' IDENTIFIED BY 'mypassword';
    -- ensure that the user you've added has all privileges required:
    GRANT ALL PRIVILEGES ON *.* TO 'username'@'IP' IDENTIFIED BY 'password';

  • ensure that the users you are adding are not duplicates in either username or password
    -- view the user table in terminal with the following mysql query:
    select * from mysql.user\G;
    -- it's a good idea to delete the anonymous user for both security and potential user collisions: https://stackoverflow.com/questions/10299148

  • when complete run query: FLUSH PRIVILEGES;


Set up your security groups to allow remote MySQL connection via the NAT instance:

  • with WorkBench you're SSH tunnelling up to the NAT then connecting via port 3306 to the db instance

  • ensure that the IP you're connecting from is allowed to connect to port 3306 in AWS security group
    -- the NAT instance should have SSH(22) inbound from your IP address
    -- the NAT instance should have MySQL(3306) outbound towards the VPC address range (eg 10.0.0.0/16)
    -- the db instance in the VPC private subnet should allow inbound MySQL(3306) from the VPC private IP range (eg 10.0.0.0/16)


Set up Workbench to connect to your db instance via the NAT instance:

  • open WorkBench
  • create a new connection and give it a name (eg my_vpc_db1)
    -- choose 'Standard TCP/IP over SSH' as connection method
    -- SSH host is the public IP of the NAT intance e.g. XX.XX.XX.XX
    -- SSH Username = ec2-user
    -- SSH Password is blank (clear this if necessary)
    -- SSH Key File = browse to location of the key_pair.pem file
    -- MySQL Hostname = private IP address of the db instance in the VPC
    -- MySQL Server Port = 3306
    -- username = the name you just added in CREATE USER
    -- password = just added in CREATE USER

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.