Research Of Master-slave Synchronization Optimization Methods On DDL Operations For MySQL Cluster

In recent years,the distributed database design based on the read-write separation architecture has been increasingly widely used in many systems at home and abroad.The master-slave synchronization delay problem through data replication technology has been concerned by the industry,and master-slave synchronization has become an important research direction.As the second largest database in the market,MySQL is favored by Internet enterprises due to its open source feature.Therefore,it is of great significance and value to study the master-slave synchronous optimization method of MySQL cluster.For MySQL cluster facing DDL operations,master-slave synchronization delay aggravation problem,an optimization method to allow more DDL transactions and DML transactions to be replayed in parallel on the slave are proposed,through in-depth study of MySQL parallel data replication technology based on transaction group commit.The optimization method first modifies the replay mechanism of DML transactions on the slave,then adjusts the organization order of the binary log of a single transaction,and finally allows the slave to replay DDL transactions in advance,thus increasing the parallelism of transaction replay.It also optimizes the transaction group commit strategy based on writeset in the master so that on the premise of data consistency,DML transactions can be replayed in advance on the slave,instead of waiting for the DDL transaction replayed,improving the transaction replay efficiency.The optimization method can ultimately achieve the goal of reducing master-slave synchronization performance fluctuations caused by DDL operations.In the real business scenario simulation and experiment,by comparing before and after optimization method to realize MySQL cluster master-slave synchronization performance fluctuations in the face of DDL operations,it is not hard to found that after the optimization of MySQL cluster by having more DDL transactions and DML transactions be replayed in parallel on the slave,the aggravation of the master-slave synchronization delay was obviously alleviated.