Connect MySQL client with private AWS RDS database, from EC2 instance

How to use the MySQL client command line interface with a private AWS RDS MySQL database. Let’s suppose we already have an EC2 instance, with an app that communicates with the database. For instance, the EC2 instance could be part of an Elastic Beanstalk environment (please see here how to setup a private AWS RDS database with a Docker Elastic Beanstalk env).

We will execute the mysql client via SSH on this EC2 instance.

1) SQL connection variables

Let’s gather the SQL connection variables we will need for this tutorial. You can find them in the RDS section of the AWS console. Please look at the end of this article for more information on how to find the SQL connection variables. The connection variables should look like this:

host=database-1.a.eu-central-1.rds.amazonaws.com
port=3306
dbName=""
username=admin
password=abc

2) SSH to the EC2 instance that has access to the AWS RDS database

Start by finding the Instance ID from the EC2 section of the AWS console. Then follow this 2-minute guide to SSH to the instance.

3) Install the mysql client on the EC2 instance

Now you should have established a SSH connection to the EC2 instance. To install the mysql client on the EC2 instance, simply run:

$ sudo yum install mysql

4) Connect to the private remote AWS RDS database from the EC2 instance

To connect to the AWS RDS database, run:

$ mysql -u $username -h $host -P $port -p $dbName --password=$password

Using my own particular SQL connection variables from part 1), my mysql connect command is:

$ mysql -u admin -h database-1.a.eu-central-1.rds.amazonaws.com -P 3306 -p test --password=abc

Now the mysql client is connected to the AWS RDS database, and it should look like this:

[ec2-user@ip-172-31-35-157 ~]$ mysql -u admin -h database-1.a.eu-central-1.rds.amazonaws.com -P 3306 -p test --password=secret
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 264
Server version: 8.0.23 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]>

5) Execute SQL commands in the mysql CLI

You can now execute SQL commands directly on the remote database. For instance, if you want to list the tables in the database called test, run:

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer       |
| tutorials_tbl  |
+----------------+
2 rows in set (0.00 sec)

That’s it for this tutorial ! If you encounter any connection issue with your AWS RDS database, please leave a reply below, we will help you.