Nov 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

 



About the Author

Nickolay Ihalainen

Nickolay joined Percona in December 2010, after working for several years at what is now the most popular cinema site in Russia. During the time he was there, Nickolay and a small team of developers were responsible for scaling the site into one which now serves over a million unique visitors per day. Prior to that, he worked for several other companies, including NetUp, which provides ISP billing and IPTV solutions, and eHouse, the oldest Russian e-commerce company. Nickolay has a great deal of experience in both systems administration and programming. His experience includes extensive hands-on work with a broad range of technologies, including SQL, MySQL, PHP, C, C++, Python, Java, XML, OS parameter tuning (Linux, Solaris), caching techniques (e.g., memcached), RAID, file systems, SMTP, POP3, Apache, networking and network data formats, and many others. He is an expert in scalability, performance, and system reliability. Nickolay has a degree from Moscow State University. His hobbies are Subaru and Mitsubishi Evo engine performance tuning and roller skating. He lives in Moscow with his wife and son.