配置AWS RDS Router EC2

背景

最近同事需要给一个PowerBI Gateway 配置防火墙以便访问一个RDS 实例。然后问题出现了，RDS endpoint的域名倒是不会变，但是他对应的IP地址是动态的，而防火墙规则只能使用IP地址而不能使用域名。解决方法是配置一个EC2 实例，EC2可以有一个静态的IP，然后把这个EC2当成一个软路由转发数据库的请求到RDS上。下面看看具体如何实现的。

配置

主要参考资料来自于AWS的这篇博客

https://aws.amazon.com/blogs/database/how-to-use-amazon-rds-and-amazon-aurora-with-a-static-ip-address/

!

当然他做演示的时候是用的cloudformation 自动配置了整套nlb，vpc，rds和ec2。我已经有现成的vpc和rds了，因此我手动配一个EC2就行了。因为PowerBI Gateway的refresh可能会花很多时间，因此我这里没有考虑使用NLB。NLB的有点在于高可用和负载平衡，但是有个350秒的 timeout限制，因此这里我就用方案2直接连接EC2了

配置脚本可以参考官方的cloudformation的代码，把里面的userdata稍微改改就行

exec > >(tee /var/log/user-data.log|logger -t user-data ) 2>&1
yum update -y
yum install nc -y
yum install go -y
yum install iptables-services -y
systemctl enable iptables
cd ~
cat <<EOF > /root/dummyport.go
package main

import (
	"fmt"
	"net/http"
)

func main() {
	http.HandleFunc("/", HelloServer)
	http.ListenAndServe(":15000", nil)
}

func HelloServer(w http.ResponseWriter, r *http.Request) {
	fmt.Fprintf(w, "Hello Server $(hostname), %s!", r.URL.Path[1:])
}
EOF
mkdir ~/go
export GOPATH=~/go
export GOCACHE=/root/.cache/go-build
echo "export GOPATH=~/go" >> ~/.bash_profile
echo "export GOCACHE=~/.cache/go-build" >> ~/.bash_profile
nohup go run /root/dummyport.go &
cat << 'EOF' > /root/dummygo-check.sh
#!/bin/bash
date
ps -ef | grep dummyport | grep -v grep
DUMMYPORT_PROCESS_COUNT=`ps -ef | grep dummyport | grep -v grep | wc -l`
echo "The number of dummyport process: " $DUMMYPORT_PROCESS_COUNT

if [ `expr $DUMMYPORT_PROCESS_COUNT + 0` -gt 0 ]
then
  echo "Exiting."
  exit 0
else
  echo "Run dummyport.go process."
  nohup go run /root/dummyport.go &
fi
EOF
sysctl -w net.ipv4.ip_forward=1
echo "net.ipv4.ip_forward = 1" > /etc/sysctl.d/nat.conf
sysctl net.ipv4.ip_forward
RDS_CONNECTION_IP=`nslookup ${MySqlDb1.Endpoint.Address} | egrep "^Address" | grep -v "#53" | awk '{print $2}'`
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 3306 -j DNAT --to $RDS_CONNECTION_IP:3306
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
service iptables save
# crontab
cat << 'EOF' > /root/rds_iptables_update.sh
#!/bin/bash
date
for i in {1..5}
do
  RDS_CONNECTION_IP=`nslookup ${MySqlDb1.Endpoint.Address} | egrep "^Address" | grep -v "#53" | awk '{print $2}'`
  #echo "## Current RDS_CONNECTION_IP : " $RDS_CONNECTION_IP
  set -x
  /sbin/iptables -t nat -R PREROUTING 1 -i eth0 -p tcp --dport 3306 -j DNAT --to $RDS_CONNECTION_IP:3306
  set +x
  sleep 10
done
EOF
cat << 'EOF' > crontab_joblists.txt
* * * * * sh /root/dummygo-check.sh 2>&1 | tee -a /root/dummygo-check.sh.log
* * * * * sh /root/rds_iptables_update.sh 2>&1 | tee -a /root/rds_iptables_update.sh.log
EOF
crontab crontab_joblists.txt