See the evolution of replication from MySQL 5.5 to 5.7

Summary: MySQL 5.5 supports single-threaded mode replication, MySQL 5.6 supports library-level parallel replication, and MySQL 5.7 supports transactional-level parallel replication. With this mainline we can analyze the causes and consequences of MySQL and community development.

MySQL5.5, for replication we can understand this: the main library has a dump binlog thread constantly dump Binlog, and then the event unit to send from the library iothread,iothread The event received from the main library is written to Relaylog and then Sql_thread read Relaylog to replay the event as a transaction unit.

What about the MySQL 5.5 version, the problems encountered during our use, or the inconvenience?

• First When the db pressure is large, the delay from the library is large, affecting the read-only service

  Due to the development of new hardware, the introduction of SSD and the concurrent processing ability of multi-core Cpu,master node continue to improve, the slave node is completely in accordance with the Binlog write sequence of single-threaded playback, has not been able to keep up with the master node throughput capability.

  Without considering the differences in the master-slave hardware configuration, the root cause of the delay is that the master pressure is too high and the slave is a single-threaded replay log. So to solve this problem, from the technology can be a single-threaded to multi-threading, take advantage of the advantages of parallelism, from the business can be removed from the library, some lines of business or functional modules out of the way, further we can split the table, Share the pressure on multiple master.

  1. If we do not change the business situation, from the technical side to solve the problem in what direction:

     • Community Solutions: Ali Open Source Canal, parallel synchronization based on table level, can reduce synchronization delay time

     • Official Solution: A milestone version was released in October 2011 based on the schema-level parallel replication [MySQL5.6.3 (multi-threaded Slave)], and the group commit-based MySQL5.7 version, maximizing the parallelism of the primary library.

     MySQL5.6, for replication we can understand that the main library has a dump binlog thread constantly dump Binlog, and then the event unit to send from the library iothread,iothread Received the event written by the main library Relaylog. "The things that followed and the MySQL5.5 changed", read the Relaylog by the coordinator thread, and then assign the transaction units to different work threads based on the different db. If the Binlog row event operates on objects of different schemas, parallel replication can be implemented without the DDL and foreign key dependencies determined.

     MySQL5.7 can be said to be the largest restore of the main library of parallelism, on the basis of group commit, all the statements on the main library can be completed prepared statement that there is no data conflict, assigned to the same lastcommitted, Can be replicated in parallel on the slave node. So how does it identify those transactions that were submitted together? In fact, in the Gtid event added two fields "Int64 lastcommitted;int64 sequence number", when slave's coordinator thread distributes these event, Transactions with the samelast committed (the collection of event) can be sent to different work threads at the same time to achieve the purpose of parallel synchronization.

     Summary: in parallel replication, according to the granularity of the three strategies, granularity from coarse to fine is by library, by table, by row. In these three comparisons, the degree of parallelism is increasing and the additional loss is also. Irrelevant large transactions do not affect the degree of concurrency. According to COMMIT_ID's strategy, the scope of application is wider and the additional consumption is low. The 5.7 improvement strategy is more concurrency-optimized. But there are big things that can be dragged down.

  3. Then we only have one instance of the database, in which case we only have to split the table:

     In this case, we can choose to do the sub-table at the application layer , or choose to have a middle tier . Different schemes have different merits and demerits.

     • The application layer has good performance, but the code is coupled in the business, if the subsequent expansion of the code will not be able to achieve a smooth expansion of the split, if there are multiple businesses need to achieve the same function, then will bring duplication of effort, and the difficulty of work also rose a step.

     • The middleware layer has good expansibility, low coupling, if the DB is enlarged and split, the application can do no sense, no change. Then there are some mature open source programs, such as Mycat,cobar,atlas,kingshard.

• second , the master-slave switch brings a large complexity, need to calculate position or redo from the library

  In general, our MySQL is a master multi-slave architecture, which can provide us with read-write separation, load balancing convenience, but also provide us with disaster-tolerant capabilities. But if our main library is hung up, then we will promote the library to the main library, but the change from the library to the new master brings about the micro-changes of the architecture. In order to make use of the above conveniences and provide disaster resilience, we have to rebuild multiple slave libraries for this new master. At this point, we have to know from the library that I should now start copying from that location in master, which means I have to get master's position. In order to get this position, we have two ways, a simple rough, redo slave; the other is to calculate the difference between the current data and the new master data through some columns complex calculation and back up the difference data, so as to get the new main library position, which brings the great challenge to the HA handoff and data protection.

  • mmm architecture (master-master Replication Manager for MySQL)

    MMM is a set of scripts that support dual-master failover and dual-master daily management, which ensures that the hot standby is switched to the new main library when the main library fails, and automatically points to the new master from the library. But the architecture itself does not guarantee the consistency of the data.

  • MHA Architecture (Master high availability)

    MHA is currently a relatively mature solution for MySQL high availability, and in the process of automatic failover, the data consistency can be guaranteed to achieve a truly high level of availability.

    So how does HMA maximize data consistency? When the main library is down, MHA tries to save the binary log from the outage's primary server, with the greatest assurance that the data is not lost, but this is not always possible. If the main library sends down machines, the logs can be lost to varying degrees, and one solution is to set up semi-synchronous replication. MHA in the process of upgrading from the main, will be a series of log comparisons, find the closest to the main library from the library to the new main library, the difference between the library data out of the application and so on.

  • GTID (Global Transaction ID)

    After MySQL 5.6, the official introduced the Gtid, that is, within the entire cluster, each transaction has a globally unique identity, so that when our main library sent down, or the MySQL architecture is adjusted, we do not have a headache to calculate position , or to configure a slightly more complex MHA. We just need to tap the Change Master command with a auto_position on it, and then about which binlog the master should start to push the event to slave this is entirely up to MySQL to help us calculate. This is really the gospel of DBAs.

    Simple look, why so gtid so magical. Inside MySQL, we recorded two collections of Gtidpurged and Gtidexecuted. As the name implies, Gtidexecuted represents the current set of Gtid that have been executed, and in general we binlog not be able to save permanently, then Gtid purgedrepresents the Binlog collection that is currently gtid, which is Gtid a subset of executed. We know that the transaction cannot exist across Binlog, meaning that each binlog will have a complete set of transactions, as well as the header portion of each Binlog file, which also holds the Gtidexecuted collection of this binlog. Our slave in the application of Binlog will be recorded at the time of the last transaction we have executed Gtid, then we switch the main library, slave will take this ID to take, and then the master side will get this gtid and their current Gtid Executed, Gtidpurged sets of comparisons to give slave a reasonable explanation.

OK, here MySQL from 5.5 single-threaded replication, to 5.6 schema-level-based replication, to 5.7 to maximize the parallelism of the primary repository is nearing completion. At the same time, we have also given some community, or non-technical solutions.

See the evolution of replication from MySQL 5.5 to 5.7