sudo su -

yum -y install nc


# ------------------------------------------------------------------------------
# setup haproxy
# ------------------------------------------------------------------------------

amazon-linux-extras install epel -y

yum -y install haproxy18

systemctl enable haproxy18

systemctl start  haproxy18



# ------------------------------------------------------------------------------
# setup mariadb
# ------------------------------------------------------------------------------

echo "[mariadb]"                                            >> /etc/yum.repos.d/MariaDB.repo
echo "name = MariaDB"                                       >> /etc/yum.repos.d/MariaDB.repo
echo "baseurl = http://yum.mariadb.org/10.5/centos7-amd64"  >> /etc/yum.repos.d/MariaDB.repo
echo "gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB"   >> /etc/yum.repos.d/MariaDB.repo
echo "gpgcheck=1"                                           >> /etc/yum.repos.d/MariaDB.repo


yum -y install MariaDB-server


sed -i 's/#bind-address/bind-address/g' /etc/my.cnf.d/server.cnf
echo "performance_schema=ON" >> /etc/my.cnf.d/server.cnf

systemctl enable mariadb

systemctl start  mariadb

mysql -e "set global max_connect_errors=5 ;"
mysql -e "select @@max_connect_errors ;"
mysql -e "SHOW VARIABLES LIKE 'performance_schema' ;"


LOCAL_IP=$(ifconfig | grep -w inet | grep -v 127.0.0.1 | awk '{ print $2}')


# ------------------------------------------------------------------------------
# test without proxy protocol
# ------------------------------------------------------------------------------


# make 4 connect errors
nc -z -w 1 ${LOCAL_IP} 3306
nc -z -w 1 ${LOCAL_IP} 3306
nc -z -w 1 ${LOCAL_IP} 3306
nc -z -w 1 ${LOCAL_IP} 3306

# check connect error count
mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"

# a success logon
mysql -h ${LOCAL_IP} -P 3306 -e "select 'OK' ;"

# check connect error count should be reset
mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"



# ------------------------------------------------------------------------------
# test with proxy protocol
# ------------------------------------------------------------------------------

mysql -e "set global proxy_protocol_networks='*' ;"
mysql -e "select @@proxy_protocol_networks ;"

mv /etc/haproxy18/haproxy.cfg  /etc/haproxy18/haproxy.cfg.original

echo "listen  haproxy_test"                                       >> /etc/haproxy18/haproxy.cfg
echo "        bind *:3307"                                        >> /etc/haproxy18/haproxy.cfg
echo "        mode tcp"                                           >> /etc/haproxy18/haproxy.cfg
echo "        server local-mariadb localhost:3306 send-proxy-v2"  >> /etc/haproxy18/haproxy.cfg


systemctl restart haproxy18
systemctl status  haproxy18


# make 4 connect errors
nc -z -w 1 ${LOCAL_IP} 3307
nc -z -w 1 ${LOCAL_IP} 3307
nc -z -w 1 ${LOCAL_IP} 3307
nc -z -w 1 ${LOCAL_IP} 3307

# check connect error count
mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"

# a success logon
mysql -h ${LOCAL_IP} -P 3307 -e "select 'OK' ;"

# check connect error count should be reset BUT NOT
mysql -e "select ip, host, SUM_CONNECT_ERRORS from performance_schema.host_cache ;"



# ------------------------------------------------------------------------------
# END
# ------------------------------------------------------------------------------