MySQL cluster with Multi-master or Replica mode

If you can’t afford for your database to be down for even a few minutes, you need a Multi-Master cluster to ensure high availability. This means that one node can be taken offline (e.g. for maintenance or upgrade purposes) without impacting availability, as the other node will continue to serve production traffic. Further, it doubles your capacity to read or write to the database and provides an additional layer of protection against data loss.

MySQL includes a Multi-Master replication. Elestio enables you to set up your MsSQL Multi-Master cluster in just a few clicks.

 

To begin, you will need to have deployed two MySQL instances

 

1) Go to elestio Dashboard > Deploy new service> Databases > select MySQL, scroll down and name it for example mysql-1 then click on the "Create service button"

2) Again, go to elestio Dashboard > Deploy new service> Databases > select MySQL, scroll down and name it for example mysql-2 then click on the "Create service button"

3) Wait for the 2 instances to be ready

4) In elestio dashboard open the service details of mysql-1 and click on the "Configure cluster" button

5) Select in the partner instance dropdown "mysql-2" as the partner, then select "Multi Master" in Cluster mode, then click on "Apply changes" button

6) In elestio dashboard open the service details of mysql-2 and click on the "Configure cluster" button

7) Select in the partner instance dropdown "mysql-1" as the partner, then select "Multi Master" in Cluster mode, then click on "Apply changes" button

All done. You now have a multi-master MySQL cluster.

 

You can now read and write on both instances. If instance A is down you will still be able to use instance B and vice versa. Also, if you restore a backup on one instance it will be automatically replicated to the other instance.

How to use Multi-Master cluster from Node.js

If you can configure your two master clusters in Round Robin in your MySQL driver, a load balancer is not needed. The client side will split the traffic between your instances and avoid a dead node. This helps to greatly simplify the high-availability system.

The regular MySQL driver for node.js supports this: https://www.npmjs.com/package/mysql#poolcluster

 

How to test your High Availability Cluster

1. Shut down one of the VMs (instance A). You should still be able to connect, read and write on your cluster.
2. Restart instance A, wait 30 seconds, then shut down instance B.
3. Test your connectivity and read/write access to the cluster again.
4. Finally, restart instance B.

How to use PHPMyAdmin to test your cluster

1. Open the service details and click on Admin UI to get url and credentials of PHPMyAdmin.
2. Open a browser tab with the Admin UI for instance B.
3. Open another browser tab for instance A.
4. Create a new database in instance A, add a table, insert a line with sample data.
5. Check if the database created from A is correctly replicated to instance B.
6. Open the database in instance B.
7. Add or edit some rows in the database on instance B and check if correctly replicated to instance A.