AWS Database Blog

How to use Amazon RDS and Amazon Aurora with a static IP address

When you use Amazon Relational Database Service (Amazon RDS) and Amazon Aurora, you may have noticed that the IP addresses of your database instances keep changing. There are several reasons for this. The IP address changes when the primary database of Amazon RDS fails over to a standby database. Furthermore, when Amazon RDS is restarted or the Amazon Elastic Compute Cloud (Amazon EC2) instance is replaced with another instance due to a specific patch, the IP address changes. Even if the IP address changes, normal applications can still access Amazon RDS because these applications can connect using the DNS of Amazon RDS. In general, it is recommended for applications to access RDS using RDS DNS. However, due to the following requirements, many enterprise customers want to use Amazon RDS with a static IP address (a non-changing IP address):

  • In many cases, when your firewall policy applies rules, only a static IP is allowed as a policy rule. Specifically, this rule is applied when connecting servers on a public cloud service from the on-premises servers.
  • Quite often, third-party software that must connect to Amazon RDS only support connection with a static IP address.
  • Some legacy applications in on-premises servers only use a static IP address when connecting to databases.

In this post, I present a way to access Amazon RDS based on static IP addresses.

Solution overview

In the architecture presented in this post, you can connect to Amazon RDS using static IP addresses provided by a Network Load Balancer. You configure an Amazon EC2-based RDS router so that it can connect with Amazon RDS through a Network Load Balancer. To achieve this, you configure an RDS router as the target EC2 instance of the Network Load Balancer. With the help of the iptables command on the RDS router, traffic coming into the RDS router is configured to be connected to the Amazon RDS IP address. Also, if you use the RDS router created in this post, then you can share it with multiple RDS databases. When this architecture is applied to multiple RDS databases, one thing to note is that the listening ports on RDS Routers must be configured differently. For example, when configuring to connect two Amazon RDS for MySQL using this architecture, one RDS database is configured to be connected to port 3306 and the other RDS database is configured to be connected to port 3307.

The following diagram illustrates the proposed architecture.

Considering High Availability, this architecture is configured using two Availability Zones so that it can operate even in the event of one Availability Zone failure. Thus, Network Load Balancer and RDS routers were configured in each Availability Zone.

Deploy the solution

You can implement the architecture using the following AWS CloudFormation stack. To use this CloudFormation, you must have an AWS account. To connect using SSH, you need to create EC2 key pairs in advance so that you can connect to EC2 by using the EC2 key pairs. To deploy the CloudFormation stack, Complete the following steps:

  1. Download the CloudFormation stack file.
  2. On the CloudFormation console, choose Create stack with new resources (standard).
  3. Upload the stack file, and choose Next.
  4. For Stack name, enter rdsStack.
  5. For SSHKeyName¸ enter your own key name.
  6. For MyPcIpAddress, enter your PC IP address by visiting whatismyipaddress.com. Fill the IP address with format of xxx.xxx.xxx.xxx/32.
  7. For RdsMasterUserPassword, enter your password of RDS master user with at least 8 characters.
  8. For RdsMasterUsername, enter your master username of RDS (like admin).
  9. Choose Next.
  10. Choose Next again.
  11. Choose Create stack.

Stack creation takes approximately 20 minutes to complete. This solution incurs costs because all of the resources described here aren’t under the AWS Free Tier. For more information about costs, see AWS Pricing.

Connect to Amazon RDS using a Network Load Balancer

After you create the solution resources with the CloudFormation stack, you’re ready to connect to Amazon RDS using a static IP address.

Check the bastion client Amazon EC2 IP address in the CloudFormation stack output, and connect to Amazon EC2 as follows (use your own bastion host IP address):

ssh -A ec2-user@<<your-bastion-ip-address>> ## BastionClientAz1

The CloudFormation stack configured an Amazon RDS for MySQL as an example database service. Also, it installed a MySQL client in the bastion client. Therefore, you can connect to Amazon RDS for MySQL using the Network Load Balancer URL from the CloudFormation stack output with the following command:

mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>>

You can also connect to Amazon RDS for MySQL using the static IP address of the Network Load Balancer with the following command. You can find IP addresses of the Network Load Balancer by running the nslookup command:

nslookup <<your-NLB-URL>>

With IP addresses of the Network Load Balancer from above output, you can connect to Amazon RDS the following command:

mysql -u '<<rds_username>>' -h '<<your_NLB_IP_ADDRESS_OF_AZ1>>' -p<<rds_password>>
mysql -u '<<rds_username>>' -h '<<your_NLB_IP_ADDRESS_OF_AZ2>>' -p<<rds_password>>

If you’re using Amazon RDS for PostgreSQL with this approach and a proper PostgreSQL client installed in the client machine, then you can connect to Amazon RDS for PostgreSQL using the static IP address or DNS of the Network Load Balancer with the following command:

psql -U <<rds_username>> -d <<database_name>> -h <<your_NLB_IP_or_DNS>> -p <<database_port>>

With an Amazon RDS for Oracle environment, you can connect using the Network Load Balancer URL with the following command:

sqlplus <<rds_username>>/<<rds_password>>@//<<your_NLB_IP_or_DNS>>:<<database_port>>/<<database_name>>

For an Amazon RDS for SQL Server environment, you can connect using the static IP address of the Network Load Balancer with the following command:

mssql -s <<your_NLB_IP_or_DNS>> -u <<rds_username>> -p <<rds_password>> -e

If you’re using an Amazon Aurora MySQL-Compatible Edition environment with this approach, then you can connect using the static IP address of the Network Load Balancer with the following command:

mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>>

For an Amazon Aurora PostgreSQL-Compatible Edition environment with this approach, you can connect using the static IP address of the Network Load Balancer with the following command:

psql -U <<rds_username>> -d <<database_name>> -h <<your_NLB_IP_or_DNS>> -p <<database_port>>

Implement an RDS router

A key component of this architecture is RDS router instances, which forward the Network Load Balancer traffic to the Amazon RDS IP address. The CloudFormation already implemented the RDS router and required integration with Network Load Balancer. To implement this router by yourself, you configure the router on Amazon EC2. The RDS router on Amazon EC2 forwards traffic from the Network Load Balancer to the Amazon RDS IP through the iptables command.

You can configure routing through the DNAT command of iptables so that the incoming traffic in the RDS router on Amazon EC2 is forwarded to the Amazon RDS IP address. To identify the Amazon RDS IP address, you can use the nslookup command toward the DNS of Amazon RDS. I used RDS_CONNECTION_IP environment variable for the Amazon RDS IP address in the upcoming command. For example, see the following code (using Amazon RDS for MySQL):

iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 3306 -j DNAT --to $RDS_CONNECTION_IP:3306

You can connect to other databases, such as Oracle, PostgreSQL, SQL Server, and Aurora, as shown in the following code:

# For Oracle
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 1521 -j DNAT --to $RDS_CONNECTION_IP:1521
# For PostgreSQL
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 5432 -j DNAT --to $RDS_CONNECTION_IP:5432
# For MS SQL Server
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 1433 -j DNAT --to $RDS_CONNECTION_IP:1433
# For Aurora MySQL
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 3306 -j DNAT --to $RDS_CONNECTION_IP:3306
# For Aurora PostgreSQL
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 5432 -j DNAT --to $RDS_CONNECTION_IP:5432

Integrate a Network Load Balancer with an RDS router

Because the RDS router is configured on Amazon EC2, you can configure the router as the target instance of the Network Load Balancer. You can send traffic by specifying the RDS router in each Availability Zone as the target group for a Network Load Balancer.

However, if you configure Network Load Balancer health checks in a standard way, then an issue may arise during a health check. In the case of a specific database management system, when traffic that only checks for port open status without performing a normal database connection, such as a Network Load Balancer health check, the client IP may be blocked. The following is an example client IP blocking message because of health check traffic when your Network Load Balancer tries to connect to Amazon RDS for MySQL:

ERROR 1129 (HY000): Host '10.0.1.14' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

To address this issue when performing a Network Load Balancer health check, you should implement a separate health check port which should be different from the Amazon RDS port. You can implement this function through the health check port override feature of the Network Load Balancer. In the CloudFormation stack provided earlier, this health check override port was configured using port 15000.

One consideration of choosing Amazon EC2 is that every Amazon EC2 instance type has its own network bandwidth limit. Therefore, you must consider your required amount of network traffic when choosing an Amazon EC2 instance type.

Update iptables

The IP address of Amazon RDS may change due to various events in Amazon RDS. To see the current value of the IP address, you must look up the DNS of Amazon RDS. In the RDS router on Amazon EC2, the iptables DNAT command forwards the incoming Network Load Balancer traffic to the Amazon RDS IP address. If the IP address changes due to Amazon RDS events, then you must run DNS lookup to find the latest IP address and rerun the iptables DNAT command to forward incoming Network Load Balancer traffic to the new IP address.

There are two possible ways to automate this procedure:

  • The first method is to periodically run the iptables DNAT command through the Linux crontab command by looking up the DNS of Amazon RDS.
  • The second method is to use AWS Lambda to run an iptables DNAT command whenever an Amazon RDS event occurs.

The CloudFormation stack provided earlier periodically updates iptables using the Linux crontab command. The shorter the crontab run cycle is, the shorter the iptables update cycle can be.

Limitations of Network Load Balancer timeout

The Network Load Balancer provides the advantage of using a static IP. However, you have the limitation of idle timeout when using a Network Load Balancer. If no packets are sent for 350 seconds after making a connection through the Network Load Balancer, then the connection is disconnected. Therefore, if the valid database connection time is less than 350 seconds, it’s okay to use a Network Load Balancer. Also, when a client connects using Network Load Balancer, it is recommended to apply the keepalive timeout of the client to 350 seconds or less. By doing that, the client can quickly process the connection timeout before the Network Load Balancer timeout occurs. If a database job requires idle time more than 350 seconds because of long running queries, then consider another method where you directly connect to the IP address of the RDS router on Amazon EC2. When connecting with the RDS router on Amazon EC2, the database connection can be maintained for more than 350 seconds because it’s not restricted by the Network Load Balancer idle timeout. However, when connecting to the RDS router on Amazon EC2, there is a disadvantage: if the router fails, then the application with direct connection with the router might have a problem connecting to Amazon RDS. The following diagram illustrates these idle timeout options.

How to keep track of client IP addresses

When your client application connects to Amazon RDS using the architecture configured with the CloudFormation stack provided, Amazon RDS can only track the IP address of the RDS router on Amazon EC2 other than your client application. This is because when the traffic that has passed through the RDS router on Amazon EC2 connects to Amazon RDS, it connects using the IP address of the RDS router on Amazon EC2.

To check which IP address is used to connect to Amazon RDS, you can test by accessing RDS from the client. The command below was tested with the client IP address configured as 10.1.1.92 and the IP address of the RDS router on Amazon EC2 configured as 10.1.1.41. Run the following command:

[ec2-user@ip-10-1-1-92 ~]$ mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>> -e"SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip;"
+-----------+
| ip        |
+-----------+
| 10.1.1.41 |
+-----------+
[ec2-user@ip-10-1-1-92 ~]$

As you can see, the connected IP address is the IP address of the RDS router on Amazon EC2.

If you want the RDS router on Amazon EC2 to deliver the original client IP as is, then you can remove the masquerade command from the iptables commands in the router. However, this may cause routing problems because return packets with the return traffic can’t find the original client again. If you’re required to track the original client IP address, then you can avoid the routing problem by configuring only one RDS router on Amazon EC2. Furthermore, you should set the route table of the RDS subnet to use more specific routings, which the return traffic from the RDS subnet should go through using an RDS router. Even if the client traffic originates from another Availability Zone, the return traffic must go through one RDS router on Amazon EC2. This is the only way to avoid this routing problem.

This CloudFormation stack collects the original client IP address of your applications. You can simulate how to collect the client IP address by downloading and implementing this CloudFormation stack. When you’re configuring the stack parameters, you can change the SSH key name to your own key name. Also, you should fill MyPcIpAddress, RdsMasterUserPassword and RdsMasterUsername as you did for the previous CloudFormation template. For the rest of the settings, use the default settings.

The following diagram illustrates this workaround.

After you deploy the CloudFormation stack, you can try to connect to Amazon RDS through the Network Load Balancer. To check the original client IP address from the Amazon RDS connection, use the following command:

[ec2-user@ip-10-1-1-204 ~]$ mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>> -e"SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip;"
+------------+
| ip         |
+------------+
| 10.1.1.204 |
+------------+
[ec2-user@ip-10-1-1-204 ~]$

Because this architecture uses only one RDS router on Amazon EC2, you can’t connect Amazon RDS in the case of router failure on Amazon EC2 in Availability Zone 1. If the RDS router in Availability Zone 1 fails, then you must use the router in Availability Zone 2. Also, in this case, you must update the route table of the RDS subnet to use the RDS router in Availability Zone 2. If necessary, you can use AWS Lambda to automate updating the route table of the RDS subnet. The following diagram explains what to change in the case of RDS router failure on Amazon EC2 in Availability Zone 1.

I created a Lambda function that automatically updates the route tables of the RDS subnet in the preceding CloudFormation stack when the RDS router in Availability Zone 1 fails. You can simulate an automatic routing update of the Lambda function with the following steps:

  1. On the CloudFormation console, navigate to the Outputs tab for the stack.
  2. Locate the RdsRouterVmAz1 key and corresponding value.
  3. Connect to RdsRouterVmAz1 using the Amazon EC2 connection string from the preceding value: 
    ssh -A ec2-user@<<your-rdsRouter-ip-address>> ## RdsRouterVmAz1
  4. Drop packets towards health check port using the following command: 
    sudo iptables -A INPUT -p tcp --dport 15000 -j DROP

    This triggers the Lambda function to update the route tables of the RDS subnet.

    Now you can connect to the bastion host and check if communication with Amazon RDS is possible.

  5. On the CloudFormation console, navigate to the Outputs tab for the stack.
  6. Find the BastionClientAz1 key and corresponding value.
  7. Connect to BastionClientAz1 using the Amazon EC2 connection string from the preceding value: 
    ssh -A ec2-user@<<your-bastion-ip-address>> ## BastionClientAz1
  8. Try to connect to Amazon RDS using the following command: 
    mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>> -e"SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip;"

As shown in the following output, the IP address of the bastion host is collected even when the RDS router in Availability Zone 1 fails:

[ec2-user@ip-10-1-1-204 ~]$ mysql -u '<<rds_username>>' -h '<<your_NLB_IP_or_DNS>>' -p<<rds_password>> -e"SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip;"
+------------+
| ip         |
+------------+
| 10.1.1.204 |
+------------+
[ec2-user@ip-10-1-1-204 ~]$

This Lambda function is configured to run once per minute. Therefore, it may take 2–3 minutes for the route tables to be updated in the case of a failure of the RDS router in Availability Zone 1. You can check what happens in the function when the failure occurs through the Lambda function execution logs in Amazon CloudWatch Logs.

Clean up

As I mentioned earlier, you might incur costs because not all of the resources are under the AWS Free Tier. To avoid ongoing charges, delete the resources you created as part of this post.

Conclusion

In this post, I explained how to connect to Amazon RDS using a static IP provided by a Network Load Balancer. You might be concerned about the situation where you need the additional Amazon EC2 when implementing this architecture. When using Amazon RDS, you may use a large number of databases. Therefore, you can configure and use a Network Load Balancer plus RDS router architecture for multiple databases. By doing so, you can construct the architecture with minimal costs while also meeting the static IP requirements. The static IP feature of Amazon Network Load Balancer provides a solution to the non-changing IP requirements that occurs when connecting on-premises to AWS Cloud.

You can refer to Network Load Balancers documentation for additional details about the feature of static IP of Amazon Network Load Balancer.

About the Author

Byeong-eok Kang is a Solutions Architect at AWS. He specializes in databases and has a deep understanding of the finance industry. Outside of work, he likes riding his bike and playing with his cat.