[Practice 3] Set up Amazon RDS

Co-author: Eileen Chu

Photo by fabio on Unsplash

In this practice, you will do the following:

• Create and Configure RDS
• Connect to RDS from MySQL Workbench
• Connect to RDS in EC2 Terminal
• Basic MySQL Command Lines

Create and Configure RDS

Follow the tutorial described by Amazon. Remember your RDS endpoint, port, username and password, it would be used in the later section and also be used in the next practice.

Note: The location of the RDS will affect the speed of accessing data from your database. That is, it would be better to set up your RDS location as same as your EC2 so that they will be in the same Amazon VPC (Virtual Private Cloud).

Then, you will change the security group to all traffic — anywhere in inbound and outbound. Alternatively, you can customize the options for the security group too.

To connect your RDS with MySQL Workbench, you will switch the ‘Public Accessibility’ to yes.

Connect to RDS from MySQL Workbench

After download and install MySQL Workbench, click + to add a new connection.

Enter the hostname, which is the endpoint on RDS console, and the port of your DB, usually is 3306. The username and the password are as same as your RDS.

Then click ‘Test Connection’ to connect.

If the connection succeeds, you will be navigated into the workplace. Your initial DB name will be ‘innodb’. Now you can import your file (E.g. CSV file) into your DB by right-clicking on the table and choose ‘Table Data Import Wizard’.

Note: The file format (there are several versions of CSV) might influence the success of the file import.

After importing successfully, right click on the table and choose ‘ Select Rows -Limit 10000’ to check the data in the table.

For more information about RDS MySQL DB instance, please refer to this tutorial link.

Connect to RDS in EC2 Terminal

Install MySQL server and Mysql client in EC2.

Note:
MySql Client — The mysql-client package allows you to connect to a MySQL server. It will give you the “mysql” command-line program.
MySql Server — The mysql-server package allows to run a MySQL server which can host multiple databases and process queries on those databases.

sudo yum install mysql-server mysql-clinet

To log in to MySQL as the user you just created, type the following command:

Note:
-h host name; -u username; -p password to use when connecting to server

mysql -h <endpoint> -u <username> -p

Then, you will be asked to enter the password which is the same one as your RDS. If succeed, you can see the Welcome message.

Basic MySQL Command Lines

To show databases, type:

mysql> show databases;

To choose a database, type:

mysql> use mysql;

To display tables, type:

mysql> show tables;

Previous: [Practice 2] Clone Existing Repo to AWS EC2

Next: [Practice 4] Host a PHP Dynamic Website on Amazon EC2

Support my writing by becoming a Medium member today and getting access to an unlimited number of articles.