AWS_EN

Amazon Linux 2 How to Connect to RDS – ALL Engines

スポンサーリンク
Amazon Linux 2 - How to connect to RDS - ALL Engines AWS_EN
スポンサーリンク
スポンサーリンク

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

Diagram of how to connect to all types of RDS engine - Amazon Linux 2

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.

Explanation of key points in template files

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

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

Instance IDEngineEngine VersionInstance ClassPortLicense ModelRemarks
dbinstance1PostgreSQL13.5db.t4g.micro5432postgresql-license
dbinstance3MySQL8.0.27db.t4g.micro3306general-public-license
dbinstance5MariaDB10.5.13db.t4g.micro3306general-public-license
dbinstance6Oracle(SE)19.0.0.0.ru-2021-10.rur-2021-10.r1db.t3.small1521license-included
dbinstance7Microsoft SQL Server(EX)15.00.4153.1.v1db.t3.small1433license-includedDBName is not configurable.

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

Cluster IDInstance IDEngineEngine VersionInstance ClassPort
dbcluter1dbinstance2PostgreSQL13.4db.t4g.medium5432
dbcluter2dbinstance4MySQL8.0.mysql_aurora.3.01.0db.t4g.medium3306

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

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.

All types of RDS have been created.

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.

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.

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.

DB EngineClientAmazon Linux Repository
(2022/3)
PostgreSQLpsqlpostgresql.x86_64(9.2.24-6.amzn2)
Aurora(PostgreSQL)psqlpostgresql.x86_64(9.2.24-6.amzn2)
MySQLmysqlmariadb.x86_64(1:5.5.68-1.amzn2)
Aurora(MySQL)mysqlmariadb.x86_64(1:5.5.68-1.amzn2)
MariaDBmysqlmariadb.x86_64(1:5.5.68-1.amzn2)
Oraclesqlplus
Microsoft SQL Serversqlcmd
タイトルとURLをコピーしました