11/07/2019
HighLoad++ 2019
Nickolay Ihalainen
I’m going to explain how each type of replication works and why you should use a specific type of mysql replication for certain workloads.
There are many different kinds of mysql replication (Async replciation, Galera, Group Replication and many others). You may think about MySQL as a database for web servers (like LAMP stack), but in a wild it’s used for many different kinds of loads. Let’s share the knowledge how to scale mysql replication horizontally, vertically and geographically.
The replication
There are many types of MySQL replication:
- Asynchronous Master-Slave replication
- Cold standby replication
- Semi-synchronous replication
- Master – Master replication
- Delayed replication
- Parallel replication
- Multi-source replication
- Crash-safe replication
- GTID replication
- Synchronous replication
- Galera Replication and Percona XtraDB Cluster
- Group Replication and InnoDB Cluster
The project
It’s hard to choose what kind of MySQL replication is suitable for particular project:
- Just a small web site or public API
- A hosting solution for massive amount of small, rarely used applications
- Complex big database running on a dedicated hardware with directly attached storage
- Big database with recently updated data reads and huge amount of writes
- Cloud-based solution working with million users
- Geo-distributed MySQL databases
Requirements
Each database cluster should be:
- able to ignore single or multiple node faults
- manageable (new node addition automation, backups & backup verification, full or partial restore)
- instrumented (performance graphs for OS and MySQL daemon), queries analysis
- scalable vertically and horizontally
