A simple summary of MySQL replication-related technologies

MySQL has many kinds of replication, at least conceptually, traditional master-slave replication, semi-synchronous replication, Gtid replication, multithreaded replication, and group replication (MGR).
I look a lot, a variety of replication, in fact, in principle, the principle of all kinds of replication is not too big similarities and differences, the emergence of new replication, is the original copy of a certain aspect of the enhancement or optimization of the results, it is not difficult to understand why there are so many copies.
Each replication occurs for a reason, and is a potential problem to solve (or compensate for) the previous replication scenario.
In fact, so many concepts, personal feel is due to open source, different replication version of the emergence, because it is a problem constantly found to solve the process.
If it's a closed-source database, you just have to patch it up and there shouldn't be so many concepts.

This paper summarizes the characteristics of various replication techniques and solves the problems from the principle, and does not involve too many details.
MySQL copy schematic diagram (image from the simplified MySQL)

The approximate process is as follows:

1, after building the master and slave, slave connected to Master,slave Io_thread wait for the main library binlog information
Perform a variety of DDL or DML commands on 2,master to perform the execution of the build Binlog
The binlog_dump thread on the 3,master sends the generated binlog to slave io_thread of slave
4,slave Io_thread writes Binlog to the local trunk log file after accepting Binlog on master
5,slave local sql_thread Apply the relay log to the local database

Traditional master-slave replication

For traditional master-slave replication, the typical operation of the slave connection to master is to manually specify slave to start the binary log from which point of the master's file.

Change MASTER toMaster_host='***.***.***.***', Master_user='username', Master_password='pwd', Master_port= 3306, Master_log_file='mysql-bin.000047', Master_log_pos=3112;

Potential problems:

1, the data asynchronous replication, the master commits the thing does not need to confirm with the slave, the Binlog transmits asynchronously to the slave, obviously, the latent problem is if the master goes down, may exist the binlog which does not pass to the slave, causes the thing to lose.
2, you need to manually confirm the logfile and logfile offset

Semi-synchronous replication

The most important feature of semi-synchronous replication is the improvement of the traditional master-slave replication potential of the main never consistent problem,
Semi-synchronous replication requires master to commit a transaction, as long as it waits until a slave receives binlog and successfully writes to the trunk log to return the message to the client for a successful submission.
This ensures that everything on the master can be passed to at least one slave,master outage without losing things and solves the traditional replication potential problem of 1, but problem 2 remains.
Potential problems:
1, still need to manually confirm the logfile and logfile offset

Gtid replication

Gtid is a global transaction ID that has been successfully executed based on the original MySQL server, combined with the server ID and transaction ID.
This global transaction ID is unique not only on the original server, but also on all MySQL servers where the master-slave relationship exists.
It is this feature that makes MySQL's master-slave replication easier and database consistency more reliable.

 to Master_user = ' username ' , Master_password = ' pwd '  for ' Group_replication_recovery ';

Compared to the traditional master-slave replication, or to improve the security of semi-synchronous replication, in the construction of master-slave, or change master-slave,
The way to do this is to manually specify the Binlog file and the offset of the file, and it's plain to judge that slave should start replaying the contents of the binary log from which location in the Binlog from master.
This is bound to increase the likelihood of manual operation and human error, not too convenient to master from or fail over the operation.

Relative to traditional replication, the advantages of Gtid:
1, more simple to build master-slave replication, and to achieve failover, do not need to manually specify Log_file and Log_pos before.
2, under normal circumstances, Gtid is not empty continuously, so the master-slave library when there is data conflict, you can add empty things in a way to skip, skip the wrong things more convenient.

Multithreaded replication

Semi-synchronous replication solves the problem of traditional replication potential slave lost master, Gtid replication simplifies the implementation of replication, solves the problem that traditional replication is too complex, and is considered as two patches of original replication.
Traditional replication has been improved from two latitude of security and complexity, but traditional replication still has a potentially fatal problem, which is the latency of master-slave replication.
Slave on the Sql_thread application relay log to the local database, is a single-threaded operation, so in the face of a large number of binlog, there may be an efficiency problem,
Multi-threaded replication is the way to parse the relay log in parallel, slave the execution efficiency of sql_thread on the feed, thus improving the master-slave replication delay problem (of course, the master's binlog to do some optimization)

　　

MGR (MySQL Group Replication)

Mgr,mysql group replication, not only the problem of replication, can be considered to be a combination of traditional replication, semi-synchronous replication (mechanism is not the same, most nodes synchronous commit), Gtid replication, a series of features such as a high-availability solution, and has fault detection function, automatically detect and eliminate the failure of the node
Therefore, it is a highly available and highly scalable solution, not just the ability to complete replication.

For some features of MGR, refer to from 69791330

High consistency, based on the native replication and Paxos protocol group replication technology, and in the form of plug-ins to provide consistent data security assurance;
High fault tolerance, as long as not most of the nodes can continue to work, there is an automatic detection mechanism, when the different nodes generate contention for resources, will not appear errors, according to first-come priority principle of processing, and built-in automatic brain crack protection mechanism;
High extensibility, the addition and removal of nodes are automatic, after the new node joins, will automatically synchronize the state from other nodes, until the new node and other nodes are consistent, if a node is removed, the other nodes automatically update the group information, automatically maintain the new group information;
High flexibility, single main mode and multi-master mode, in single master mode, will automatically select the master, all the update operations in the Lord; in multi-master mode, all servers can handle update operations at the same time.

For Mgr, the author has only simply done the test, build up as with the ordinary copy and not much difference, not complicated, the network evaluation is very high, there are unified a variety of third-party high-availability technology trends.
The drawback is that the time out is too short (Mgr is the MySQL official launched in December 2016, for the internet, personal feeling more than already short), there may be some location problems.

Summarize

MySQL replication, the emergence of any new scheme, its principle difference is not small, is to solve the potential problems of the former solution, is as a former copy of the promotion or enhancement, open source has no perfect solution, but there is a continuous improvement of the solution, this is not one of the charm of open source?
There may be differences in the technical details of different replications, but the essential things are the same.
Of course, each copy has its own details, which can only be practiced in practical applications.
This also makes it possible to think of various isolation levels (isolation) in various data, although not entirely analogous, as with replication, each of the enhanced isolation levels is designed to address problems in the previous isolation level.

A simple summary of MySQL replication-related technologies