SQLSplitter - High Availability (HA)
Today's blog is a reaction to queries regarding high availability coming more and more often from our Customers. We would like to focus on a few different ways you can achieve HA in your AWS environment. Below we explain two ways to achieve HA.
Cost-effective HA through one instance in auto-scaling group
This option is the most cost-effective as you run just one SQLSplitter instance in your AWS environment. It means that if the instance fails then your application will experience a few minutes downtime. The exact length depends on your configuration but it should be between 1-5 minutes. After this time the new instance with the recent configuration will be started. There are a few ways which you can make sure that your new instance will have the same IP address as the previous one so you don't have to modify the configuration of your application.
One of the ways to preserve the IP address is to use a separate network interface and assign it to the instance during launch.
You can achieve this by setting the following in your 'user data' section:
INSTANCEID=`curl -s http://169.254.169.254/latest/meta-data/instance-id`
aws ec2 attach-network-interface --region us-east-1 --instance-id $INSTANCE --device-index 2 --network-interface-id eni-xyz123
If there is a failure of your SQLSplitter instance AWS EC2 auto-scaling group will spin up a new SQLSplitter based on your last image. In this scenario it is required to make sure that you create a new AMI image after each change of your SQLSplitter configuration. We are currently working on the solution that will store the whole configuration in your private S3 bucket so creation of images after each change won't be necessary. We are aiming to have this feature available in March 2017.
To create an auto-scaling group please follow these steps: http://docs.aws.amazon.com/autoscaling/latest/userguide/GettingStartedTu...
High-availability using two SQLSplitter nodes
If you want to keep your application running without downtime during an instance failure you can use an extra SQLSplitter node. If one of them fails your application needs to know that there is another one to try. This way during one instance failure the other one will take over until the first one will be launched as described in the previous step. The figure below presents two SQLSplitter nodes operating with one master database and one slave database.
SQLSplitter MySQL proxy solution.
SQLSplitter is a MySQL proxy that allows users to send their write queries like INSERT or UPDATE to the master and read queries to slaves automatically and transparently.
Use SQLSplitter on AWS: https://aws.amazon.com/marketplace/pp/B01LXAS775