An analysis of MySQL schema of open source database

Database is the core of all application systems, so to ensure the stable, efficient and safe operation of the database is the most important work of all enterprises. Once a database system fails to provide services, it may cause the entire system to fail to continue working. Therefore, a successful database architecture needs to be fully considered in terms of high-availability design. Here's a look at how to build a highly available MySQL database system.

A DBA or an OPS student should know that a single point of any device or service poses a huge risk because once the physical machine is down or the service module crash, if a replacement device cannot be found in a short period of time, is bound to affect the entire application system. So how to ensure that no single point is our important work, the use of MySQL High-availability program can be a good solution to this problem, generally have the following:

First, using MySQL 's own Replication to achieve high availability

MySQL comes with Replication is what we often call master-slave replication (AB replication), by making a slave to the master server, Quickly switch the business to the slave while the primary server is down, ensuring that the application is in good working condition. Using AB replication for high-availability scenarios is also divided into several different architectures:

1, the general MASTER---SLAVE solution

The common MASTER---SLAVE is one of the most common architecture schemes used by most small and medium-sized companies at home and abroad, the main advantage is simple, less equipment (lower cost), convenient maintenance. This architecture solves the problem of single point, and also can solve the system performance problem to a great extent. You can take one or more SLAVE(Master-slave cascade replication) behind a master, but this architecture requires a master must be able to meet all of the system's write requests, or you need to do a horizontal split to share the pressure of reading.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M01/06/7F/wKiom1m499ezvejeAAgdfcNNbpo339.jpg-wh_500x0-wm_ 3-wmp_4-s_1559805807.jpg "title=" 1.jpg "alt=" Wkiom1m499ezvejeaagdfcnnbpo339.jpg-wh_50 "/>

Figure A

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M01/06/7F/wKiom1m49-aDgNc9AAPODSgDlOg665.jpg-wh_500x0-wm_ 3-wmp_4-s_218909247.jpg "title=" 2.jpg "alt=" Wkiom1m49-adgnc9aapodsgdlog665.jpg-wh_50 "/>

Figure II

Figure one to figure two shows: solve the single point problem and the use of read and write separation to achieve improved performance process.

2.DUAL MASTER combined with cascade replication

Dual-Master is a more reasonable scheme derived from the above scheme. The benefit of this scenario is that when any one of the two primary servers is hung up, the entire architecture is not resized.

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/06/7F/wKiom1m49_zjfqJrAAO4PXBnciE392.jpg-wh_500x0-wm_ 3-wmp_4-s_2020054322.jpg "title=" 3.jpg "alt=" Wkiom1m49_zjfqjraao4pxbncie392.jpg-wh_50 "/>

Might

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/A5/2F/wKioL1m49-DwrMKtAAPP0DDybOU447.jpg-wh_500x0-wm_ 3-wmp_4-s_337567649.jpg "title=" 4.jpg "alt=" Wkiol1m49-dwrmktaapp0ddybou447.jpg-wh_50 "/>

Figure Four

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M01/06/7F/wKiom1m4-BrAU1zfAAQdWNpAgFI585.jpg-wh_500x0-wm_ 3-wmp_4-s_3366012527.jpg "title=" 5.jpg "alt=" Wkiom1m4-brau1zfaaqdwnpagfi585.jpg-wh_50 "/>

Figure Five

The process is as shown. But figure five is a very special caseMaster-bWhat happens if you go down? The first thing we can be sure of is all of ourWritethe request is not affected, and allReadrequests can also be accessed normally, but allSlavereplication will be interrupted,Slavethe data above will begin to lag. All we need to do is put all theSlavemakeChange MASTER tooperation, change fromMaster Afor replication. Because allSlavereplication is impossible to advance the original data source, so you canSlaveabove theRelay Logthe timestamp information in theMaster Athe timestamp information in the control, to find the exact starting point of replication, so as to avoid the loss of data.

Second, using MYSQL CLUSTER to achieve the overall high availability

mysql cluster achieve a high overall availability (i.e. ndb cluster ) is not widely available in domestic companies. ndb cluster The node is actually a multi-node mysql server, but does not contain data, So any machine can be used as long as it is installed. When a sql node crash

650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M00/A5/2F/wKioL1m49__zy1r3AAQFGOyz48Q211.jpg-wh_500x0-wm_ 3-wmp_4-s_1447837960.jpg "title=" 6.jpg "alt=" Wkiol1m49__zy1r3aaqfgoyz48q211.jpg-wh_50 "/>

Figure Six

Iii. high availability through MySQL 's derivative products

In the current MySQL Implementation of highly available derivative products, the popularity and popularity of the higher is galera CLUSTER and PERCONA Xtrdb CLUSTER (PXC). Related content This article is not open to tell, interested students can consult the relevant information for further understanding. The two clusters are implemented in a similar way, and figure eight:

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/06/7F/wKiom1m4-D7zXDb5AABLoSjCyZs028.png-wh_500x0-wm_ 3-wmp_4-s_3337715902.png "title=" 7.png "alt=" Wkiom1m4-d7zxdb5aablosjcyzs028.png-wh_50 "/>

Figure Seven

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/A5/2F/wKioL1m4-CGyk61KAARk3-U_tk0515.png-wh_500x0-wm_ 3-wmp_4-s_3255448607.png "title=" 8.png "alt=" Wkiol1m4-cgyk61kaark3-u_tk0515.png-wh_50 "/>

Figure Eight

Iv. comparison of advantages and disadvantages of various high-availability schemes

In the introduction of various high-availability designs, readers may have discovered that, regardless of the scheme, there are unique advantages, but there are more or less limitations. This section will do a pros and cons analysis of the above major scenarios, for everyone to choose the process of reference.

1.MySQL Replication

Advantages: Simple deployment, ease of implementation, maintenance is not complex, is the nature of MySQL support features. It is easy to switch between the main and standby machines, and the primary and standby switch can be done automatically through the third party software or a script written by itself.

Disadvantage: If the master host hardware fails and cannot be recovered, it may result in some data loss that is not delivered to the Slave end.

2.MySQL Cluster (NDB)

Advantage: Very high availability, very good performance. Each piece of data has at least one copy on top of the different hosts, and redundant copies of the data are synchronized in real time.

Disadvantage: Maintenance is more complex, the product is newer, there are some bugs, not necessarily applicable to the core of the online system.

3,galera CLUSTER and PERCONA xtrdb CLUSTER(PXC)

Advantage: Very high reliability, all nodes can read and write each data at the same time, at least one copy on the different hosts, and redundant data copy in real-time synchronization.

Disadvantage: As the size of the cluster expands, performance will become worse.

4, have to mention the DRBD Disk network mirroring scheme

Architecturally, it's a bit like Replication, but it's the process of synchronizing data through third-party software, which is more reliable than Replication , but also sacrificing performance.

Advantage: Software is powerful, data is mirrored across physical hosts at the underlying block device level, and different levels of synchronization can be configured based on performance and reliability requirements. IO operations are maintained in order to meet the database's demanding data consistency requirements.

Disadvantage: Non-Distributed File system environments cannot support mirrored data at the same time, performance and reliability are contradictory and cannot be applied to environments that are both demanding. Maintenance costs are higher than MySQL Replication.

After saying the pros and cons of a variety of common architectures, the rest is how to choose the right architecture to use in a real-world production environment. In this respect everyone has their own ideas and experience, the specific plan is the best of opinion. In the daily work of the structure of the perfect is not an overnight, but a constantly evolving process of optimization and improvement.

a push in the database also experienced from a single point to master-slave to master-slave + High-availability process, but also experienced from a single mysql+redis to mysql+redis+es , finally to the present Mysql+redis+es+codis and so on evolution. Each time the evolution is to solve the actual problems and pain points in the production environment. Single from MySQL No one architecture can solve all the problems (pain points), you need to choose a suitable architecture according to the actual situation. the MySQL cluster implementation is very flexible, and it is a challenge for MySQL workers to choose a suitable architecture, and we are constantly studying and learning the power of MySQL.

An analysis of MySQL schema of open source database