Skip to content

Relational Databases#

Amazon Relational Database Service (Amazon RDS) makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need. BDTI provides Amazon RDS MySQl, Amazon RDS PostreSQL and Amazon RDS Oracle.

Accessing Amazon RDS MySQL#

In order to access Amazon RDS MySQL, you will need following details:

  • DNS address of the RDS instance
  • User credentials
  • Root certificate if SSL TLS connections are required. Can be downloaded from here

MySQL is available at port 3306. MySQL can be accessed via standard SQL tools such as the MySQL command utility (mysql - The MySQL Command Line Tool) or MySQL Workbench (Download MySQL Workbench). Example connection via MySQL command line tool (without SSL):

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com -P 3306 -u mymasteruser -p

Example connection via MySQL command line tool (with SSL):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 350
Server version: 5.6.40-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM MYDATABASE.MYTABLE

Example connection via MySQL Workbench:

mysql -h mysql–instance1.123456789012.us-east-1.rds.amazonaws.com --ssl-ca=rds-ca-2015-root.pem -p

alt-text

  1. Download and install MySQL Workbench (Download MySQL Workbench)
  2. Start MySQL Workbench
  3. From Database, choose Manage Connections
  4. In the Connect to Database window, enter the following information:
    • Stored Connection – Enter a chosen name for the connection
    • Hostname – Enter the provided DNS
    • Port – Enter the port
    • Username – Enter the username of a database user
    • Password – Enter the password

  5. Following window should look similar:

    alt-text

  6. You can either test the connection by clicking “Test Connection” or you can close the window by clicking “Close”.

  7. From Database, choose Connect to Database
  8. From Stored Connection, choose your connection.
  9. Choose OK

Accessing Amazon RDS PostgreSQL#

In order to connect to an Amazon RDS PostgreSQL instance you need following details:

  • DNS of the DB instance
  • User credentials
  • Root certificate if SSL TLS connections are required (this can be downloaded from here: https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem)

PostgreSQL can be accessed via standard SQL tools such as psql or pgAdmin.

Example connection setup via psql on Linux:

psql \
   --host=<DB instance endpoint> \
   --port=<port> \
   --username=<master user name> \
   --password \
   --dbname=<database name>

Example connection setup via psql on Windows:

psql ^
   --host=<DB instance endpoint> ^
   --port=<port> ^
   --username=<master user name> ^
   --password ^
   --dbname=<database name>

Example connection via psql on Linux or Windows:

psql --host=mypostgresql.c6c8mwvfdgv0.us-west-2.rds.amazonaws.com --port=5432 --username=awsuser --password --dbname=mypgdb

Example connection by using pgAdmin:

  1. Launch pgAdmin
  2. On the Dashboard tab, choose Add New Server
  3. In the Create – Server dialog box, type a name on the General tab to identify the server in pgAdmin.
  4. On the Connection tab, type the following information from your DB instance:
    • For Host, type the provided DNS of the PostgreSQL database
    • For Port, type the provided port
    • For Username, type the username
    • For Password, type the password

  5. Following window looks similar:

    alt-text

  6. Choose save.

  7. To access, expand Servers, the DB instance and then Databases, choose the database name.
  8. To open a panel where you can enter SQL commands, choose Tools, Query Tool