Study On Database Cluster Based On MySQL Replication

With the rapid development of Internet technology and the feature of high ratio of reading and writing, the solution of database cluster has been the best choice of all.The commercial database cluster solutions are not friendly to small and medium enterprises due to technical barriers as well as economic reasons while the open source programme MySQL has been widely used in the Internet as its easy to use, economical and convenient features, and it always has kept high availability and high scalability.But there has been a lot of MySQL cluster solutions, and there has also been some difficulties, such as maintenance, replication latency, the lack of middleware and other issues. So the study on MySQL cluster is very important.The thesis designed a high available database cluster model based on MySQL Replication which named MRBCHA after analyzing the current popular technology of high available MySQL database cluster and studying on MySQL Replication based database cluster and improving it. The main contents of the model are as follows:1. The thesis proposed an improved method for MySQL replication and data partitioning techniques. To solving the problem of replication latency, thesis carried out three improved methods:donot rewrite the costly part, parallel writing beyond replication and prefetch cache. The partition technology uses a feature in ID-based strategy, and we use the same prefix ID features of this fragmentation which is easier to expand the data when compared to traditional hash modulo.2. The thesis proposed a improved read/write splitting algorithm and implemented by using MySQL Proxy’s lua script. As been the agent of clients’requests, the algorithm allocates the read/write operation by analyzing the SQL statement and set the dbname (database name) argument, based on the number of dbname.3. The thesis proposed a dynamic feedback load balance algorithm based on probability distribution which provides an excellent ability of load balance and scalability and minimized the impact caused by single point failure.The algorithm first calculates its rate of change of the residual load by every child nodes, then it compares with the set threshold, and child nodes report the information to the master node through a dynamic feedback mechanism, at last the master node distributes the requests in a random probability.4. The thesis build a MySQL database cluster of high available named MRBCHA which was constituted by read/write splitting part, load balance part and replication part at last. The service is stable after testing and it gets the expected result. The read/write splitting,load balancing and data replication synchronization will be run effectively.