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.