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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to 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 ReplicationSemi-synchronous replicationAbout Group ReplicationAbout InnoDB ClusterAbout InnoDB ReplicaSetFeature Comparison of Replication TypessummaryConclusion
What is Replication?
Replication is a mechanism for copying data onto 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
In this configuration, the slave in a normal master-slave configuration also acts as the master.
Also called grandson slave.
One example of using this configuration is when performing rolling version upgrades of MySQL.
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 also common to group slaves under a load balancer virtual VIP, 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 this.
For example, if you use a slave machine exclusively for backup, one slave machine can have multiple copies of data, so you can reduce the number of servers required for backup machines.
Alternatively, it is also possible to re-aggregate databases that have 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 has a different level of guarantee for 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 and binary log updates on the master are guaranteed to be synchronized.
This setting does not guarantee that the data will be propagated to the slaves thereafter.
This means that if the master DB crashes, there may be data that has not been propagated to the slave.
Semi-synchronous 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 no guarantees are made for slaves beyond the first one.
This behavior was implemented in MySQL 5.5.
Since an update is considered complete when one slave's relay log update is complete, update throughput will be reduced.
About Group Replication
This feature was implemented in MySQL 5.7.
A group is made up 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 an update SQL is received by the primary server, the group communication engines of each server go through the certification process and write it to the relay log.
Certification is a process that checks for write conflicts.
In conflicting transactions, only the first committed operation will succeed, and all others will fail to maintain consistency.
The certification process is carried out 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 full featured client.
This configuration makes it possible to automatically promote databases using the group replication function, and to automatically change the database destination of an application using the MySQL Router function.
It is recommended to use this option when you want to prioritize high availability. However, it does have 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 an application, but InnoDB Cluster can now handle all of this using only official MySQL functionality.
However, care must be taken 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 depending on the purpose.
It's important to know that it exists so you can choose the optimal replication option.
Conclusion
SRG is looking for people to work with us. If you are interested, please contact us here.