MySQL Database replication for high availability

Question

configuration:

• 1 balancer

• 2 Azure machines(4 gb ram, 2 cores), with MySQL master-master replication enabled. X users connecting to the balancer to sync data.

test:

i've tested the MySQL replication with loader.io (for a connection spike), for 6000 calls for 1 min to the balancer (100 conn per 1 minute test), and with the current setup both dbs sync in about 6-7 mins (all 6000 syncs)

scenario:

A client should sync it's data from last connection no matter what machine the balancer distributed his connection to, and that db for example might not have finished the previous sync period yet (let's say the 6 minute, as in the above example). I guess a sticky balancer solution might not help(Nginx, HAProxy) and Azure balancer is not persistent\sticky anyway (for example user is connected to 3G, 4G and IP changes frequent)..

problem:

1. how do i get the current replication sync the fastest way possible between both machines?
2. is there a way to transform my current setup to synchronous replication?
3. is there any other solution (preferable windows OS) that does synchronous sync (like: semisync replication )?

Answer 1

There are currently two options for MySQL 'synchronous' replication.

The first option meets your requirement of "windows OS" fairly well: MySQL Cluster, as long as you use MySQL Cluster 7.1.3+, but might have other limitations depending on your use-case.

The second is Galera, which can be used via normal MySQL, using Percona XtraDB Cluster, or MariaDB. This is the reason I put 'synchronous' in quotes above, due to the certification process Galera undergoes.

There are pros and cons of using MySQL Cluster as well as Galera. Therefore, I leave to you the requirement to research them for your purposes.