Implementation Of Multi-master Replication Database Extension Based On MySQL Replication

With the development of Internet system, the amount of access to the system, theconcurrency and the amount of data is dramatically increasing. As the carrier of the systemdata, database·s performance directly affects the performance of the overall Internet system;therefore, the performance improvement of database is of great significance in terms ofresearch. At present, the MySQL Master-Slave Replication database extension scheme isprimarily used for the extension of database framework. As this scheme only supports singlemaster server, the writing processing pressure is centralized on one single master server, sothat this leads to various database performance issues. Therefore, if MySQL Master-Slavereplication scheme can support multiple master servers, the writing processing pressure onone single master server can be broken down, and the database performance issue can befundamentally resolved.Based on comprehensive analysis of Multi-Master Replication technology research statusin China and overseas, it is discovered that no mature MySQL Multi-Master Replicationimplementation has existed in the market. Therefore, this article proposes the Multi-MasterReplication database extension scheme based on MySQL Replication. MySQL Multi-MasterReplication adopts the replication scheme based on Master-Slaves, upgrading SQL thread inslave servers into client sending modification messages. Based on the analysis of OracleMulti-Master Replication technology, and learning from the design concept of Oracle StreamsMessage Queue, it is proposed to collect modification messages on multiple master serversvia Message Queue in Multi-Master Replication process, and to serially process thesemessages, in order to avoid data conflict issues caused by parallel message processing and torealize the functionality of serial message processing on multiple master servers. As there aremultiple master servers in Multi-Master Replication scheme, which may easily lead to data conflict issues while replicating data from master servers to slave servers, it is proposed toadd SERVER ID filter in tables on slave servers to differentiate data coming different masterservers. While adding SERVER ID, MySQL statements needs lexical and syntax parsing, forthe purpose of selecting proper positions to add SERVER ID filter in. MySQL Multi-MasterReplication scheme adopts Java Complier Complier, i.e. JavaCC, the lexical and syntaxparsing tool, to realize the parsing functionality and SERVER ID addition. At the final stage,experiments of concurrent reading, writing and replicating in the Multi-Master Replicationdatabase extension scheme based on MySQL Replication are conducted; the experimentsprove that the Multi-Master Replication database extension scheme based on MySQLReplication can effectively reduce reading and writing processing pressure in the overalldatabase, so that it can resolve the database performance issue fundamentally. With regard toreplicating, there is normally5-second or shorter delay of time because of the serial MessageQueue processing method.Taking into account the importance of the database for the current information society,the realization of MySQL Multi-Master Replication database extension scheme is of greatsignificance. Multiple master servers in the Multi-Master Replication scheme realizes thebreaking down of writing process pressure, which fundamentally resolved the MySQLdatabase performance issue. As Multi-Master Replication database extension scheme providesthe design of collective database, which provides the online analytical processingfunctionality; meanwhile, multiple master servers provide the online transactional processingfunctionality. In short, this database framework realizes2online processing functionalitiessimultaneously. Although this article discusses the Multi-Master Replication databaseextension scheme based on MySQL Master-Slave Replication scheme, the design concept ofusing Message Queue to serially process messages can be applied in other relational databasemanagement systems as well.