This post is the sixth of a series that started here.
From the previous posts of this series, we now have an HA MySQL service running on EC2. We now need to find a way to point the web servers or application servers to the HA MySQL service. Normally, in an HA setup, this is achieved using virtual IP addresses but EC2 does not support virtual IPs. In addition, we can use neither broadcast nor multicast. So, we will need to reconfigure the web/application servers but that raises a first challenge, how to locate them.
The listing of the web/applications servers if easily done if they are all members (and the only members) of the same security group. A security group is an easy and convenient way to group similar servers. Once in the same group, we can list the instances in the security group and get their IP addresses. Finally, with the IP addresses and pre-configured ssh keys, we can remotely act on the web/application servers through ssh from the monitoring instance. Here is the part of the resource script (monitoring instance) that publishes the IP address of a newly created MySQL server.
MYSQL_IP=`ec2-describe-instances -K $PK -C $CERT $NEW_INSTANCE_ID | /usr/local/bin/filtre_instances.pl | cut -d'|' -f2`
ec2-describe-instances -K $PK -C $CERT | /usr/local/bin/filtre_instances.pl | grep $CLIENT_SECURITY_GROUP > $TMPFILE
while read line
IP=`echo $line | cut -d'|' -f2`
ssh -i /usr/local/bin/update_mysql ubuntu@$IP sudo ./update_xinetd.sh $MYSQL_IP
done < $TMPFILE
There are many ways to configure web/application servers, I chose to have the applications connect to 127.0.0.1 on port 3306 and then redirect the connection to MySQL using xinetd redirect type of services. The update_xinetd.sh script just updates the xinetd and send a kill -HUP to the process. Another equally valid option would be to change the IP address in a php configuration file.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.