Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

How distributed high-availability databases work

Saturday, June 10th, 2017 by Servage

databaseSometimes a website becomes so popular it outgrows the single database model and the workload must be distributed across multiple database instances. Or perhaps the website is so critical it cannot tolerate database failures even for short periods of time. There issues can be overcome by setting up a high-availability database infrastructure with multiple servers.

Master-Slave Architecture

The master-slave architecture is a common way to distribute load between multiple databases. In this model, one server is chosen as a master server, which can be used to execute read and write actions. The master server’s database is then replicated in real-time to one or multiple slave servers. This works by creating a database user on the slaves and granting it replication rights on the master server. Once this is done, all write operations are automatically replicated to the slave servers.

Adding more slave servers to the master-slave architecture is easy and a powerful way to add more read-only capacity to a database. The downside of the master-slave architecture is that slave servers are read-only databases. All write operations must be made on the master server from which they propagate to all slaves.

Master-Master Architecture

To combat the downside of having only one database with write capabilities, the master-master architecture supports multiple masters that all can write to a replicated database. Setting up a master-master replication is quite similar to a master-slave replication setup, and the biggest difference is in the architecture itself. There are no slave servers, only masters with read and write capabilities. There can be as many master servers as needed.

While this sounds like a great way to replicate a database, there are downsides in this architecture too. Because there is no single master with the highest authority over the server, the structure is loosely consistent. This may cause issues if two master servers attempt to commit the same primary key without having replicated the changes first.

MySQL Cluster

A newer solution called MySQL Cluster attempts to solve these issues with MySQL databases. The Cluster supports automatic conflict detection and resolution between multiple masters. This allows multiple servers to read as well as write simultaneously without conflicts. MySQL Cluster is a piece of software that must be installed in addition to the normal MySQL server and client software.

If you are looking for a high-availability and distributed database setup, MySQL Cluster may be worth checking out. It solves both of the biggest issues with master-slave and master-master replication and is specifically designed for a high-availability database systems. The biggest downside in MySQL Cluster is the complexity to set it up and higher infrastructure cost due to the increased amount of servers needed. In MySQL Cluster there are three types of servers: a management node for configuration and monitoring, a data node for storing the data of a database and an application node for performing queries. The last node type is optional, but compared to the previously covered architectures, a management node is required.

How distributed high-availability databases work, 5.0 out of 5 based on 2 ratings
Categories: Tips & Tricks

Keywords:

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.