How Replication Works in MySQL

siemens.blog
5 min read
fairly easy
Article URL: https://siemens.blog/posts/how-replication-works-in-mysql/ Comments URL: https://news.ycombinator.com/item?id=28653428 Points: 2 # Comments: 0
What is database replication?

MySQL has a feature known as replication where data from one database server (referred to as the source or primary) is copied to other database servers (replicas). Conceptually, it's pretty straight forward. All of your data mutations (INSERT, UPDATE, DELETE, ALTER, etc.) are done against the primary database. Those commands are then copied and applied to the replicas.

Why is database replication useful?

There are a number of reasons replication like this may be desirable.

Scaling out : When the majority of operations in your database are reads, then you can spread the reads across databases while keeping the primary available for handling the writes. For example, imagine you have an application that performs 100k operations per second (OPS): 10k writes and 90k reads. We can configure a replication setup of 2 replicas so that each database now takes on 30k of those reads. However, because we still need to perform writes on the primary database, and each replica needs to copy them, we are effectively doing 40k OPS on each server (10k writes + 30k reads). This results in a net reduction of stress on the primary database and allows us to scale horizontally on the basis that our reads will grow more than our writes.

: When the majority of operations in your database are reads, then you can spread the reads across databases while keeping the primary available for handling the writes. For example, imagine you have an application that performs 100k operations per second (OPS): 10k writes and 90k reads. We can configure a replication setup of 2 replicas so that each database now takes on 30k of those reads. However, because we still need to perform writes on the primary database, and each replica needs to copy them, we are effectively doing 40k OPS on each server (10k writes + 30k reads). This results in a net reduction of stress on the primary database and allows us to scale horizontally on the basis that our reads will grow more than…
Read full article