Introduction to MySQL replication
Tags: mysql, replication, distributedsystems
Published: 2021-04-18 19:12:19

Hello, ultra devs! 👋🏻⚡️
Today I would like to talk about Replication and how it works in MySQL.

Let's start from the beginning. If you know what replication is, feel free to skip the next chapter.

### What is replication?
Basically, a replication mechanism means storing some copy of data on multiple machines. _"How is it different from backups?"_ you may wonder. Replication is a bit more than that. When a backup is just a snapshot of the data in a certain time, replication helps you to not only keep the copy of the data in real-time ensuring availability but also facilitate the overload on the database providing both reading and writing to the client and therefore increase throughput. Also, replication helps you to distribute your data storage across the globe decreasing the response time for clients from different parts of the world.

In this article, we will be talking about **leader-based** replication, and to continue our journey into this world, it's required to introduce several terms:
1. _Leader (master)_ – part of the replication system eligible to write and read from the database.
2. _Follower (replica)_  – part of the replication system eligible *only to read*.

Basically, _leader_ is responsible for all inserts, updates, deletes, and once these changes go through the _leader_, it transfers these changes to all its _followers_ that are responsible for reads and never writes.

Here is a simple example of single-leader replication with two followers:
![Pic.1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2pcwa3h4dw2fdqssu7jo.png)

### How it works in MySQL?
Ok, now we know what replication is, but how it actually works in MySQL? How data got transferred from leader to followers and how MySQL keeps the consistency between them?

Imagine the situation that dev.to is powered by MySQL. You just wrote the article and clicked on the **Publish** button. Here is what happens:
1. Data comes to the leader and get saved in the database;
2. The leader saves data changes in the special file called **binary log**;
3. Follower copies changes in **binary log (binlog)** to its own file called **relay log**;
4. Follower replays these changes from **relay log** to its own data.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u0rjg8cotgwpe2cpesq0.png)

As you can see, to synchronize **relay log** with leader's **binary log**, MySQL starts a worker thread that is called _I/O follower thread_. It's basically an ordinary client connection to the leader that starts reading its **binary log**.

Digging a bit into details, we can ask a quite reasonable question: in which format do **binary log** and **relay log** store the data?

### Replication types
Currently, MySQL supports two types of replication: 
1. Statement-based replication;
2. Row-based replication.

#### Statement-based replication
So, as it's clear from the name, **statement-based replication** records the whole query that changed the state of the data in **binlog**. So, when a follower decides to synchronize its data with the leader, it copies the query and replays it by executing this query and applying changes to its own data.

This kind of replication is very easy to implement and has multiple advantages:
* It still works when the schema is different on the leader and the follower;
* It's easy to audit and debug;
* It requires not that much disk space.

Of course, with _great advantages comes great disadvantages_:
* _Non-deterministic functions_. With non-deterministic functions, it can come up with different data on **leader** and **follower**. By non-deterministic functions I mean function like the following: `CURRENT_USER()`, `RAND()`, `IS_FREE_LOCK()` and so on. Executing them first on **leader** and then on **follower** can lead to inconsistent data;
* _Performance penalty_. Imagine if you execute the following query: 
```SQL
INSERT INTO post_statistics VALUES (
  SELECT status AS statistics_type, COUNT(id) AS posts_amount FROM posts GROUP BY status
)
```
without having an index on the `status` field, and after pressing 'Enter' you just went for a tea (a hypothetical situation, I know, you probably drink coffee). The query got executed on **leader**, consuming all available CPU, and then **follower** picked up the baton, copied the query to its **relay log**, and cheerfully ate all CPU as well;
* _Triggers and stored routies_. Triggers and stored routines, as well as _Non-deterministic functions_, can cause a lot of problems with different side-effects on **leader** and **follower**.

So, **Statement-based replication** has its own advantages, but big disadvantages. Therefore not every database supports this type of replication, but in the case of MySQL, up until **MySQL 5.0**, this type of replication was the only one supported.

#### Row-based replication
Compare to **Statement-based replication**, **Row-based replication** stores the actual data changes in **binary log**, but not the query. So, when a _follower_ replicates the data, it doesn't execute the query, but applies the changes to each record it was applied to on _leader_.

Let's consider the advantages of this approach:
* _Less CPU intensive_. If we execute the query, described in **Statement-based replication** chapter, **follower** does not replay this query, but copy the value and apply the change to its own data record. So, the query gets executed once and doesn't consume all available CPU;
* _Helps to find data inconsistency_. Since **Row-based replication** stores the changes only, when **follower** replays these changes and tries to apply to the data that exists on **leader** but doesn't exist on **follower**, it throws the error. Meanwhile **statement-based replication** proceeds with what it has and keeps the inconsistency hidden, complicating the ability to find the point of failure and fix it;
* _No non-deterministic behavior_. Compare to **statement-based replication**, if you execute the query that has _Non-deterministic functions_, it ends up with the same result for both **leader** and **follower**.

Looks nice, that's what we were expecting from replication, right? But along with the advantages come disadvantages:
* _High disk space consumption_. Yeah, we just talked about _Less CPU consuming_ for this replication, but the disk space is also very important. Imagine if you have the following statement:
```SQL
UPDATE posts SET status = 'draft' WHERE status = 'published'
```
Considering the fact, that the `posts` table has about _1.000.000_ of _Published_ posts, this query becomes quite expensive, since it requires storing _1.000.000_ of changes in **binary/relay log** files;
* _Does not allow different schemas_. Sometimes it might be useful when you have different schemas on **leader** and **follower** (I don't know about these cases, but they definitely exist). As it was described above, **row-based replication** throws an error in case of data inconsistency, caused by different schemas;
* _Statement is not included in the **binary log**_. It can be not a problem at all until you try to debug or audit what's going on and what query caused damage to your database. **Row-based replication** makes it hard to analyze.

Now we are fluent in replication process language. Everything is clear. Hold on, on the picture of replication example, we can see one **leader** and two **followers**. Can we do things differently?🕵️‍♀️ 

### MySQL supported topologies
#### Single-leader replication
![Single-leader topology](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/481z07e9as3fxomjn2gu.png)

This type of replication is the most common one. It is useful when you have a lot of reads but not that many writes. You can distribute users reads among **followers** load-balancing them and therefore providing better response time. With this replication topology, you can easily add one more **follower** to it. Also, this topology prevents a lot of problems that multiple leaders topology have (will be described in _Leader-leader replication_), since it has only one **leader**.

#### Leader-leader replication
![Leader-leader replication](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uge6q3vos5g12bkaomel.png)
As it is visible from the picture, this topology involves two **leaders**.

This topology is useful when you have different data centers in different locations and you need to provide fast writes to both regions.

But with this advantage comes a great cost. Suppose, we have a table `post_statistics` and you just realized that the number of posts with **Published** `status` is triple more than it's actually written in table and you decide to fix the situation. So, you connect to MySQL and execute the following query:
```SQL
UPDATE posts_statistics SET posts_amount=posts_amount * 3 WHERE status='published'
```

Meanwhile, somebody from a different part of the world just published his/her first post (just like me) and triggered the following query to be executed:
```SQL
UPDATE posts_statistics SET posts_amount=posts_amount + 1
```

Suppose, the original number of posts was 10k. Due to replication lag, databases ended up with two different numbers: 30.001 and 30.003. And no errors were thrown. 

This is a big disadvantage of this topology, and in practice, it brings more problems than advantages. But if you ended up with this topology, it's better to add few more replicas to it 😁
![Leader-leader with replicas](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/523qvnf7soz5qxkhxwcu.png)


#### Active-passive leader-leader replication
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hos5gxyt01k0zihf5ahf.png)

In _Active-passive leader-leader replication_ topology one server takes the role of the **leader** and another one takes the role of the **follower**. But in comparison to the ordinary _leader-follower topology_, it allows you to swap easily the **leader** responsibility from one server to another. 

It's useful in many cases. For example, if you execute `ALTER TABLE` that locks the whole table for reads and writes, you can stop the replication process, easily swap **leader** responsibility, execute `ALTER TABLE` on the passive server, then swap the responsibility back, restore replication process, and execute `ALTER TABLE` on the remaining server. It can help you to keep your service alive while executing that expensive query.

#### Other topologies

There are many other topologies that are supported by MySQL:
1. Replication Ring topology 
![Replication Ring topology](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k8x5ik6ozxn5i0d07h7o.png)
2. Tree of pyramid topology
![Tree of pyramid topology](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uwwivdpuap96gfjxrbnp.png)

And many others. You can choose the best topology that fits your purposes or create your own. This is the list 👆🏻 of the most common topologies used in MySQL.

### Summary
**Replication** is a mechanism of having a consistent copy of the data storage. It provides:
1. Data distribution;
2. Load balancing;
3. Backups;
4. High availability and failover.

_Leader-based replication_ consists of **leader** and **follower**. Both of them have their own journal of changes: **binary log** and **relay log**.

There are two types of replication:
1. **Statement-based replication**. It's represented in queries itself.
2. **Row-based replication**. It's represented in direct data changes.

There are multiple topologies for replication:
1. Leader-follower topology;
2. Leader-leader topology;
3. Leader-leader active-passive topology;
4. Ring topology;
5. Tree of Pyramids topology.
And many specialized topologies together with custom ones.

That's it! Thank you for your attention! I hope you liked this post 😌