配置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