Introduction of Mysql Master-Slave synchronization principle _mysql

Overview

MySQL's Replication (replication) is an asynchronous replication process, copied from a MySQL instance (called Master) to another MySQL instance (we call it Slave). The implementation of the entire replication process between Master and Slave is done primarily by three threads, where two threads (SQL threads and IO threads) are on the Slave side and another thread (IO thread) is on the master side.

Master-Slave synchronization requirements

To achieve MySQL's Replication, you must first turn on the Binarylog (mysql-bin.xxxxxx) feature on the Master side, otherwise it will not be implemented. Because the entire replication process is actually the slave from the master to get the log and then in the full order of their own execution log of the various operations recorded. Open MySQL's Binary Log to add "mysqld" by using the "-log-bin" parameter option during the startup of MySQL Server, or in the MY.CNF configuration file mysqld parameter group ([Log-bin] The parameters section of the identity) The parameter item.

Master-Slave synchronization process

The basic process of MySQL replication is as follows:

1.Slave The IO line above is Chenglian to master and requests the log content from the specified location of the log file (or from the first log);
2.Master receives a request from an IO thread from slave, it returns the IO thread at the slave end, based on the request information, after the IO thread responsible for the replication reads the log information from the specified log location. In addition to the information contained in the log, the return information includes the name of the binary log file on the master side and the position in the binary log;
When the 3.Slave IO thread receives the information, it writes the received log contents sequentially to the end of the Relaylog file (mysql-relay-bin.xxxxxx) at the Slave end, and log the Bin-log file name and location of the master end read to the Master-info file so that you can clearly tell master "where I need to start from a bin-log, please send me the next read."
After the 4.Slave SQL thread detects the new additions to the relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the master end, and executes the query itself. In this way, the same query is actually executed at the master end and the slave end, so the data at both ends are exactly the same.

In fact, in the old version, the MySQL replication implementation is not done by the two threads of SQL thread and IO thread, but by a single thread to do all the work on the Slave side. But the MySQL engineers quickly discovered that there were significant risks and performance problems with this, mainly as follows:

1. First of all, if this work is done independently through a single thread, then the Binarylog log of the Master, and the process of parsing the logs, and then performing them on their own, become a serial process, and the performance will naturally be greatly limited. The delay of the replication in this architecture is naturally longer.

3. Second, the Slave end of this replication thread from the master to obtain the Binary Log, you need to then parse the content, revert to the master to execute the original Query, and then execute on their own. In this process, the master end is likely to have produced a lot of changes and generated a lot of binary Log information. If there is an inability to fix the storage system at the master end of this phase, all changes resulting from this phase will be lost forever and cannot be found again. This potential risk is especially pronounced when the slave end pressure is greater, because if the slave pressure is large, the time spent parsing logs and applying them will naturally be longer and more data may be lost.

Therefore, in the later transformation, the new version of MySQL in order to minimize this risk and improve the performance of replication, the slave-side replication to two threads to complete, that is, the aforementioned SQL thread and IO thread. One of the first to propose this improvement was a Yahoo! Engineer, "Jeremyzawodny". Through this transformation, this solves the performance problem to a great extent, shortens the asynchronous delay time and reduces the potential data loss.

Of course, even if it is now such two threads to collaborate processing, there is also the possibility of slave data latency and data loss, after all, this replication is asynchronous. These problems exist as long as the data changes are not in a single transaction.

Summarize

Preliminary understanding of the principle of MySQL master-slave replication.