Designing Database Architecture – Horizontal Scaling with MySQL Replication

Horizontal scaling can address high-traffic growth and solve availability issues. It addresses these issues by adding additional servers to your infrastructure Together the servers form a high availability (HA) cluster Adding servers can lead to more complexity in the design of your infrastructure and require changes to your services’ configuration and/or application code. Even with these required changes, running your web application on an HA cluster with proper load balancing is the most reliable and efficient way to start building out your backend infrastructure.

In the previous section, a two-server solution was presented that separated the web server from the database server While it did not solve vertical scaling’s problems, the separation of web servers and database servers is an important step towards creating a horizontal scaling solution.

In a horizontal scaling solution, the single web server is replaced with a high availability cluster of web servers, and the single database server is replaced with another, separate high availability cluster of database servers By having two separate clusters in your architecture for these services, you can horizontally scale them independently of each other and choose server instances with different hardware that is better suited for each workload.

There are several different ways to architect your database cluster, each with varying degrees of complexity and different trade-offs. This section and the diagram below illustrate how to use MySQL replicas to facilitate high availability.

Multi-Replication for Application High Availability

In the diagram above, a load balancer receives traffic from the Internet. A load balancer is a service that forwards and distributes traffic among another set of servers. Cloud providers generally offer load balancing as a service (Linode’s NodeBalancer solution is pictured). This service can also be implemented with open source software, including HAProxy, NGINX, and the Apache HTTP Server.

Before the forwarded traffic arrives at your web servers, it is intercepted by a cloud firewall. The cloud firewall lifts the firewalling burden from the operating system to the network level. Many cloud providers offer managed cloud firewalls, but this service can also be implemented with open source software on commodity hardware.

After being filtered, the traffic is distributed among a cluster of web servers. In many cases, these web servers can all be identical clones of each other. You can add or remove web servers in this cluster as needed to handle scaling traffic demand, which is an example of horizontal scalability. If a server fails, the load balancer will re-route traffic to other healthy servers, so the service remains available. Server creation can be automated with configuration management and Infrastructure as Code (IaC) tools like Ansible and Terraform.

The web servers in turn request data from a database cluster Unlike the web servers, the database server are not all identical in function In particular, only one database is set to receive write operations from the web application This server is designated as the primary database For example, if you operated a news website, the new articles that your writers published would be added to the database on this server.

The other databases in this cluster act as replicas of the primary database These servers receive all data added to the primary database through an asynchronous process Because this process is asynchronous, write operations on the primary database are still fast to execute These servers then receive all read operations from the web server cluster.

MySQL offers tools to help add new replicas to the cluster when needed, so your applications’ read operations can be horizontally scaled However, there is only one primary database in this architecture, so write operations cannot also be horizontally scaled. Still, this architecture can offer significant benefits to certain kinds of applications. In the news organization website example, the source of high-traffic demand is from readers of the website, not from writers adding articles. This traffic pattern aligns with the trade-offs in this setup.

There are a few other notable issues with this architecture First, when a new write operation is received by the primary database, it immediately persists the updated records The database does not wait for the replication process to complete, because that is asynchronous This means that in the event of a primary database failure, the replicas might not have the most recently updated records Also, when the primary database fails, it acts as a single point of failure for write operations Still, read operations will continue on the replicas in this scenario, so a website can continue to display existing content If the primary database fails, MySQL provides tools to manually promote one of the replicas to the position of the new primary database, after which write operations can resume.

Enhancements and alternative replication solutions exist to address these issues:

• MySQL replication can be configured to be semisynchronous instead of asynchronous This means that the primary database will wait for at least one replica to process new write operations before they are persisted Because at least one replica will have the latest data, you can be sure that you will not lose data in the event of a primary database failure However, semisynchronous replication is slower than asynchronous replication.
• MySQL Group Replication is a replication solution in which the database servers automatically coordinate with each other For example, in the event of a primary database outage, a replica server is automatically promoted to the new primary position. MySQL Group Replication can be configured with a single primary database, or it can be configured to have multiple primary databases that receive write operations. However, maintaining multiple primary databases involves other trade-offs.
• InnoDB Cluster bundles a MySQL Group Replication cluster with MySQL Router, which facilitates routing traffic from the web servers to the database cluster, and MySQL Shell, which is an advanced administration client for the cluster
• Galera is a multi-primary database solution where all databases in the cluster can receive write and read operations using completely synchronous replication between them It is also compatible with forks of MySQL like MariaDB and Percona.