Organizing various replications in MySQL 8.0
This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
What is Replication?Types of replication configurations and common usesMulti-stage replication1:N configurationN:1 configurationAbout asynchronous and semi-synchronous replicationAsynchronous replicationSemisynchronous replicationAbout Group ReplicationAbout InnoDB ClusterAbout InnoDB ReplicaSetFeature Comparison of Replication TypessummaryConclusion
What is Replication?
Replication is a mechanism that allows you to copy data to another server.
This feature has been implemented in early versions of MySQL.
The master server accepts update queries and forwards changes to the slaves.
The slave accepts changes from the master but is available to clients as read-only.
The terms master and slave have been renamed to source and replica.
Types of replication configurations and common uses
Replication can be configured in a variety of ways depending on your needs.
Multi-stage replication
This is a configuration in which the slave in a normal master-slave configuration also serves as the master.
Also called grandson slave.
One example of using this configuration is when performing a rolling MySQL version upgrade.
By building such a multi-stage slave configuration and promoting a slave to a master, it is possible to upgrade to MySQL 5.7.
1:N configuration
This configuration has one master and multiple slaves.
This is the most orthodox configuration.
It is mainly used to distribute the reference load.
It is common to group slaves under a virtual VIP on a load balancer, creating slaves without user references for backup purposes.
Slaves are also candidates for promotion in the event of a master failure.
N:1 configuration
This configuration allows one slave to accept data from multiple masters.
We don't have many use cases for it.
For example, if you use a slave machine exclusively for backup, one slave can have multiple copies of data, so you can reduce the number of backup servers.
Alternatively, it is possible to re-aggregate a database that has been horizontally partitioned using sharding into the same schema (database) on a single slave.
About asynchronous and semi-synchronous replication
There are two types of replication: asynchronous and semi-synchronous.
Each method has different guarantees of update synchronization, and there are differences in update performance and data persistence in the event of a master crash.
The default is asynchronous replication, which I think is fine for general web services.
Asynchronous replication
The data on the master and binary log updates are guaranteed to be synchronized.
This setting does not guarantee that the data will be propagated to the slaves after that.
This means that if the master database crashes, there may be data that has not been propagated to the slave.
Semisynchronous replication
The data and binary log on the master and updates to the relay logs on at least one slave are guaranteed to be synchronized, but there is no guarantee for slaves beyond the first one.
This behavior was implemented in MySQL 5.5.
Update throughput will be reduced because updates are considered complete when one slave completes the relay log update.
About Group Replication
This feature was implemented in MySQL 5.7.
A group consists of three or more devices and all devices have the same data.
There is a "primary" that accepts updates and a "secondary" that does not.
Group replication has only one primarySingle Primary Mode"and
All servers become primaryMulti-Primary Mode" exists.
When the primary server receives the update SQL, the group communication engines on each server go through the certification process and write it to the relay log.
Certification is a write conflict checking process.
In conflicting transactions, only the process that is committed first will be considered successful, and all others will be considered failed to maintain consistency.
Certification is done synchronously, and the data on each node is updated asynchronously.
About InnoDB Cluster
This feature was implemented in MySQL 5.7.
It consists of a combination of MySQL Router + MySQL Shell + Group Replication.
MySQL Router is a product that transparently routes connections to MySQL servers.
MySQL Shell is a multi-functional client.
This configuration allows for automatic DB promotion using the group replication function, and automatic DB redirection of applications using the MySQL Router function.
This is recommended when you want to prioritize high availability. However, there is overhead and restrictions on update processing, so prior verification is essential.
About InnoDB ReplicaSet
This is a new feature implemented in MySQL 8.0.19.
It consists of a combination of MySQL Router + MySQL Shell + asynchronous replication.
Asynchronous replication at this time requires GTID.
This configuration is suitable for reference load balancing.
There is no automatic failover.
Feature Comparison of Replication Types
This is a comparison table of the replication features introduced above.
Previously, asynchronous and semi-synchronous replication required the use of separate solutions (such as HA Proxy, LB, or MHA) to achieve automatic failover or redirection from the application, but InnoDB Cluster now makes it possible to achieve all of this using only official MySQL functionality.
However, you need to be careful as there is a large overhead when updating and many other restrictions.
summary
Many people probably only use asynchronous replication, but there are actually many different types of replication available to suit different purposes.
It's important to know that it exists so that you can choose the optimal replication option.
Conclusion
SRG is looking for people to work with us.
If you're interested, please contact us here.