Amazon Linux 2 How to Connect to RDS – ALL Engines

How to connect to all RDS DB engines from Amazon Linux 2

As of 2022, RDS offers the following seven DB engines

1. aurora(PostgreSQL)
2. aurora(MySQL)
3. PostgreSQL
4. MySQL
5. MariaDB
6. Oracle
7. Microsoft SQL Server

In this article, we will check how to connect to the above 7 types of DB instances from Amazon Linux 2.

Environment

Create two VPCs.
One VPC is for yum repository creation. Deploy the latest Amazon Linux 2 instance, create a repository containing client packages for Oracle and Microsoft SQL Server connections, and upload them to an S3 bucket.
On the other VPC, deploy each DB instance and an Amazon Linux 2 instance for connection verification. No Internet or NAT gateway is deployed in this VPC, and the S3 bucket is accessed via the VPC endpoint for S3.
The following settings are common to all DB instances.

• DB name: testdb
• Master user name: testuser
• Password user password: Passw0rd

CloudFormation template files

The above configuration is built using CloudFormation.
The CloudFormation template is placed at the following URL

awstut-fa/033 at main · awstut-an-r/awstut-fa

Contribute to awstut-an-r/awstut-fa development by creating an account on GitHub.

github.com

Explanation of key points in template files

The following page covers how to build a yum repository in VPC1, or S3 bucket.

Create yum Repository in S3 and Access from Private Subnet

Here is a configuration using CloudFormation to create a yum repository in S3 and reference it from a private subnet.

awstut.com

2022.03.19

In this section, we will review the parameters for building each DB instance.

Parameter 1 for RDS to be created

Check the parameters for the five DB instances other than Aurora.

Resources:
  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref DBAllocatedStorage
      AvailabilityZone: !Sub "${AWS::Region}${AvailabilityZone}"
      DBInstanceClass: !Ref DBInstanceClass
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      DBName: !Ref DBName
      DBSubnetGroupName: !Ref DBSubnetGroup
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      LicenseModel: !Ref LicenseModel
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterUserPassword
      VPCSecurityGroups:
        - !Ref DBSecurityGroup
Code language: YAML (yaml)

Since this is a verification, no special settings are made. In common, set parameters to the above template.
The main parameters for the five DB instances are as follows

Basically, the latest version of March 2022, and the default port number.

Parameter 2 for RDS to be created

Check the parameters of the two DB instances of Aurora.

Resources:
  DBCluster:
    Type: AWS::RDS::DBCluster
    Properties:
      DatabaseName: !Ref DBName
      DBClusterIdentifier: !Ref DBClusterIdentifier
      DBSubnetGroupName: !Ref DBSubnetGroup
      Engine: !Ref DBEngine
      EngineVersion: !Ref DBEngineVersion
      MasterUsername: !Ref DBMasterUsername
      MasterUserPassword: !Ref DBMasterUserPassword
      StorageEncrypted: true
      VpcSecurityGroupIds:
        - !Ref DBSecurityGroup
        
  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBClusterIdentifier: !Ref DBCluster
      DBSubnetGroupName: !Ref DBSubnetGroup
      DBInstanceIdentifier: !Ref DBInstanceIdentifier
      DBInstanceClass: !Ref DBInstanceClass
      Engine: !Ref DBEngine
      AvailabilityZone: !Sub "${AWS::Region}${AvailabilityZone}"
      PubliclyAccessible: false
Code language: YAML (yaml)

This is also a verification, so no special settings are made.
The main parameters for the two DB instances are as follows

March 2022 latest version, and default port number.

Architecting

Use CloudFormation to build this environment and check its actual behavior.

Create CloudFormation stacks and check resources in stacks

Create a CloudFormation stack.
For information on how to create stacks and check each stack, please refer to the following page

CloudFormation's nested stack

Learn how to build an environment with CloudFormation's nested stack.

awstut.com

2021.12.11

After checking the resources for each stack, the endpoint for the DB instance created this time was “fa-033-[instance ID].cl50iikpthxs.ap-northeast-1.rds.amazonaws.com”.

The RDS creation status is also checked from the AWS Management Console.

We can see that 5 DB instances and 2 Aurora clusters have been created.

Connecting to PostgreSQL

Now that we are ready, we will verify the connection to the DB instance.
Verification is performed by accessing the corresponding EC2 instance.
Use SSM Session Manager to access the EC2 instance. Please refer to the following page for details.

Accessing Linux instance via SSM Session Manager

Learn how to use CloudFormation to access a Linux instance via SSM Session Manager.

awstut.com

2021.12.11

Find out how to connect to a PostgreSQL instance.
To connect to a PostgreSQL instance, you need a dedicated client (psql). psql can be obtained from an Amazon Linux repository built on S3. In this configuration, the EC2 instance is located on a private subnet, so we will access the repository via the VPC endpoint for S3.

First, check the packages that can be installed.

sh-4.2$ yum list all | grep postgres
...
postgresql.x86_64                      9.2.24-6.amzn2                 amzn2-core
...
Code language: Bash (bash)

Since “postgresql.x86_64” contains psql, install it.

sh-4.2$ sudo yum install -y postgresql
...
Installed:
  postgresql.x86_64 0:9.2.24-6.amzn2                                                                                                                    

Dependency Installed:
  postgresql-libs.x86_64 0:9.2.24-6.amzn2                                                                                                               

Complete!
Code language: Bash (bash)

Check the installed packages.

sh-4.2$ yum list installed | grep postgres
postgresql.x86_64                     9.2.24-6.amzn2                   @amzn2-core
postgresql-libs.x86_64                9.2.24-6.amzn2                   @amzn2-core
Code language: Bash (bash)

It is indeed installed.

Check the version of psql.

sh-4.2$ psql -V
psql (PostgreSQL) 9.2.24
Code language: Bash (bash)

Now that the client is installed, access the PostgreSQL instance.

sh-4.2$ psql --host=fa-033-dbinstance1.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com --port=5432 --username=testuser --password --dbname=testdb
Password for user testuser: 
psql (9.2.24, server 13.5)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

testdb=>
Code language: Bash (bash)

Successfully accessed.
Thus, even instances located on private subnets can access the PostgreSQL instance by accessing the Amazon Linux repository via the VPC endpoint and installing psql.

Connecting to an Aurora PostgreSQL Cluster

Find out how to access PostgreSQL type Aurora.
You can connect by installing psql in the same way as above.

sh-4.2$ psql --host=fa-033-dbcluster1.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com --port=5432 --username=testuser --password --dbname=testdb
Password for user testuser: 
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

testdb=>
Code language: Bash (bash)

Successfully accessed.
Thus, by using psql, we can also connect to PostgreSQL type Aurora.

Connecting to a MySQL

Find out how to connect to a MySQL instance.
To connect to a MySQL instance, a dedicated client (mysql) is required. mysql can also be obtained from the Amazon Linux repository. Access the repository via the VPC endpoint for S3.

First, check the packages available for installation.

sh-4.2$ yum list all | grep mariadb
...
mariadb.x86_64                         1:5.5.68-1.amzn2               amzn2-core
...
Code language: Bash (bash)

Install mysql since it is included in “mariadb.x86_64”.

sh-4.2$ sudo yum install -y mariadb
...
Installed:
  mariadb.x86_64 1:5.5.68-1.amzn2                                                                                                                       

Complete!
Code language: Bash (bash)

Check the installed packages.

sh-4.2$ yum list installed | grep mariadb
mariadb.x86_64                        1:5.5.68-1.amzn2                 @amzn2-core
mariadb-libs.x86_64                   1:5.5.68-1.amzn2                 installed
Code language: Bash (bash)

It is indeed installed.

Check the mysql version.

sh-4.2$ mysql -V
mysql  Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
Code language: Bash (bash)

Now that the client is installed, access the MySQL instance.

sh-4.2$ mysql -h fa-033-dbinstance3.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.27 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 [testdb]>
Code language: Bash (bash)

We were able to access the site successfully.
As you can see, even instances located on a private subnet can access the MySQL instance by accessing the Amazon Linux repository via the VPC endpoint and installing mysql.

Connecting to an Aurora MySQL Cluster

Find out how to access Aurora of type MySQL.
You can connect by installing mysql in the same way as above.

sh-4.2$ mysql -h fa-033-dbcluster2.cluster-cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
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 [testdb]>
Code language: Bash (bash)

The system was successfully accessed.
Thus, by using mysql, it is possible to connect to Aurora of type MySQL.

Connecting to MariaDB

Find out how to connect to a MariaDB instance.
To connect to a MariaDB instance, a dedicated client (mysql) is required. As confirmed earlier, mysql can be obtained from the Amazon Linux repository.

Use the mysql client to access the MariaDB instance.

sh-4.2$ mysql -h fa-033-dbinstance5.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -P 3306 -u testuser -p testdb
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.5.13-MariaDB-log managed by https://aws.amazon.com/rds/

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

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

MariaDB [testdb]>
Code language: Bash (bash)

We were able to access the site successfully.
As you can see, even instances located on a private subnet can access the MySQL instance by accessing the Amazon Linux repository via the VPC endpoint and installing mysql.

Connecting to Oracle

Find out how to connect to an Oracle instance.
To connect to an Oracle instance, you need a dedicated client (sqlplus). sqlplus is not available from the Amazon Linux repository. So this time, we will access our own repository via the VPC endpoint for S3 and install sqlplus.
Please refer to the following page for more information on home-made repositories.

Create yum Repository in S3 and Access from Private Subnet

Here is a configuration using CloudFormation to create a yum repository in S3 and reference it from a private subnet.

awstut.com

2022.03.19

Check the repository registered on the EC2 instance.

sh-4.2$ yum repolist
...
repo id                                                                repo name                                                                  status
amzn2-core/2/x86_64                                                    Amazon Linux 2 core repository                                             27418
amzn2extra-docker/2/x86_64                                             Amazon Extras repo for docker                                                 56
myrepo                                                                 myrepo                                                                       4+2
Code language: Bash (bash)

myrepo contains the packages required to install sqlplus.

Follow the steps below to install the required packages.

sh-4.2$ sudo yum install -y oracle-instantclient19.14-basic
...
Installed:
  oracle-instantclient19.14-basic.x86_64 0:19.14.0.0.0-1                                                                                                

Complete!


sh-4.2$ sudo yum install -y oracle-instantclient19.14-sqlplus
...
Installed:
  oracle-instantclient19.14-sqlplus.x86_64 0:19.14.0.0.0-1                                                                                              

Complete!
Code language: Bash (bash)

Check the installed packages.

sh-4.2$ yum list installed | grep oracle
oracle-instantclient19.14-basic.x86_64
oracle-instantclient19.14-sqlplus.x86_64
Code language: Bash (bash)

It is indeed installed.

Check the version of sqlplus.

sh-4.2$ sqlplus -V

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.14.0.0.0
Code language: Bash (bash)

Now that the client is installed, access the Oracle instance.

sh-4.2$ sqlplus 'testuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fa-033-dbinstance6.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=testdb)))'

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 21 02:19:19 2022
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL>
Code language: Bash (bash)

We were able to access the site successfully.
As you can see, even instances located on a private subnet can access an Oracle instance by accessing a home-grown repository via a VPC endpoint and installing sqlplus.

In this case, sqlplus was installed on the EC2 instance by creating a home-grown repository. If your VPC has a NAT gateway or other means to access the Internet, you can install sqlplus directly by executing the following command.

curl -OL https://download.oracle.com/otn_software/linux/instantclient/1914000/oracle-instantclient19.14-basic-19.14.0.0.0-1.x86_64.rpm
curl -OL https://download.oracle.com/otn_software/linux/instantclient/1914000/oracle-instantclient19.14-sqlplus-19.14.0.0.0-1.x86_64.rpm
sudo yum install -y oracle-instantclient19.14-basic-19.14.0.0.0-1.x86_64.rpm
sudo yum install -y oracle-instantclient19.14-sqlplus-19.14.0.0.0-1.x86_64.rpm
Code language: Bash (bash)

Connecting to Microsoft SQL Server

Find out how to connect to a SQL Server instance.
To connect to a SQL Server instance, you need a dedicated client (sqlcmd). sqlcmd is not available from the Amazon Linux repository. You can also access your own repository via the VPC endpoint for S3 here and install sqlcmd.

sqlcmd is included in a package called “mssql-tools”. Follow the steps below to install this package.

4.2$ sudo yum install -y mssql-tools
...
Installed:
  mssql-tools.x86_64 0:17.9.1.1-1                                                                                                                       

Dependency Installed:
  libtool-ltdl.x86_64 0:2.4.2-22.2.amzn2.0.2               msodbcsql17.x86_64 0:17.9.1.1-1               unixODBC.x86_64 0:2.3.1-14.amzn2              

Complete!
Code language: Bash (bash)

Check the installed packages.

sh-4.2$ yum list installed | grep mssql-tools
mssql-tools.x86_64                    17.9.1.1-1                       @myrepo
Code language: Bash (bash)

It is indeed installed.

Check the version of sqlcmd.

sh-4.2$ /opt/mssql-tools/bin/sqlcmd -?
Microsoft (R) SQL Server Command Line Tool
Version 17.9.0001.1 Linux
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
...
Code language: Bash (bash)

Now that the client is installed, access the SQL Server instance.

sh-4.2$ /opt/mssql-tools/bin/sqlcmd -S fa-033-dbinstance7.cl50iikpthxs.ap-northeast-1.rds.amazonaws.com -U testuser -P Passw0rd
1>
Code language: Bash (bash)

Successfully accessed.
As you can see, even instances located on private subnets can access SQL Server instances by accessing their own repositories via VPC endpoints and installing sqlcmd.

In this case, sqlcmd was installed on the EC2 instance by creating a home-grown repository. If your VPC has a NAT gateway or other means to access the Internet, you can install sqlcmd directly by executing the following command.

curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo
sudo yum install -y mssql-tools
Code language: Bash (bash)

Summary

We have identified seven different ways to connect to seven different RDS instances.
Below is a summary of the clients for connecting to each instance and their Amazon Linux repository support.