Shulou(Shulou.com)06/01 Report--

Database is the core of all application systems, so ensuring the stable, efficient and safe operation of database is the top priority of the daily work of all enterprises. Once there is a problem in the database system, it is possible that the whole system can no longer work. Therefore, a successful database architecture also needs to be fully considered in terms of high availability design. Here's how to build a highly available MySQL database system.

Students who have done DBA or OPS should know that the existence of a single point for any device or service will bring great risk, because once the physical machine goes down or the service module crash, if the replacement device cannot be found within a short period of time, it is bound to affect the entire application system. Therefore, how to ensure that there is no single point is our important work. Using MySQL high availability solutions can solve this problem very well, generally, there are the following:

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

The Replication that comes with MySQL is what we often call master-slave replication (AB replication). By making a slave to the master server, the business can be quickly switched to the slave machine when the master server is down to ensure the normal use of the application. There are also several different architectures for making high availability scenarios with AB replication:

1. General MASTER---SLAVE solution

Ordinary MASTER---SLAVE is the most commonly used architecture scheme for most small and medium-sized companies at home and abroad. the main advantages are simple, less equipment (low cost) and convenient maintenance. This architecture can not only solve the single point problem, but also solve the performance problem of the system to a great extent. It is possible to carry one or more SLAVE (master-slave cascade replication) after a MASTER, but this architecture requires that an MASTER must be able to meet all write requests of the system, otherwise it will need to be split horizontally to share the read pressure.

Figure 1

Figure 2

Figures 1 to 2 show the process of solving a single point problem and using read-write separation to improve performance.

2. The combination of DUAL MASTER and cascade replication

Two-master and multi-follower is a more reasonable scheme derived from the above scheme. The advantage of this solution is that when either of the two primary servers dies, the entire architecture does not have to make major adjustments.

Figure 3

Figure 4.

Figure 5

This process is shown in the figure above. But figure 5 is a special case, that is, what if the MASTER-B goes down? First of all, it is certain that all our Write requests will not be affected, and all Read requests can be accessed normally, but all Slave replication will be interrupted and the data on Slave will begin to lag. At this point, all we need to do is CHANGE MASTER TO all the Slave and copy it from Master A. Since all Slave replication can not be ahead of the original data source, we can compare the timestamp information in Relay Log on Slave with the timestamp information in Master A to find the exact starting point of replication so as to avoid data loss.

Second, use MYSQL CLUSTER to achieve overall high availability

At present, the solution of using MYSQL CLUSTER to achieve overall high availability (that is, NDB CLUSTER) is not very popular in domestic companies. The NDB CLUSTER node is actually a multi-node MySQL server, but it does not contain data, so any machine can be used as long as it is installed. When a sql node in the cluster crash, because the node does not store specific data, so the data will not be lost. Figure 6:

Figure 6

Third, achieve high availability through derivatives of MySQL

Among the derivatives with high availability achieved by MySQL at present, the ones with high popularity and popularity are GALERA CLUSTER and PERCONA XTRDB CLUSTER (PXC). The relevant content of this article will not be described for the time being, interested students can consult the relevant materials for further understanding. The implementation of these two clusters is similar, as shown in figure 7 and figure 8:

Figure 7.

Figure 8.

IV. Comparison of the advantages and disadvantages of various highly available schemes

In the previous introduction of various high-availability designs, readers may have found that no matter which one, there are their own unique advantages, but there are more or less limitations. This section will make an analysis of the advantages and disadvantages of the above major schemes for reference in the process of selection.

1 、 MySQL Replication

Advantages: it is easy to deploy, easy to implement, and not complex to maintain. It is a function that MySQL inherently supports. And the switching between the active and standby computers is convenient, and the switching between the active and standby computers can be completed automatically through third-party software or self-written scripts.

Disadvantages: if the Master host hardware fails and cannot be recovered, it may result in the loss of some data that has not been delivered to the Slave side.

2. MySQL Cluster (NDB)

Advantages: very high availability, very good performance. There is at least one copy of each data on a different host, and redundant data copies are synchronized in real time.

Disadvantages: the maintenance is more complex, the product is newer, there is some bug, which is not necessarily suitable for the core online system.

3. GALERA CLUSTER and PERCONA XTRDB CLUSTER (PXC)

Advantages: high reliability, all nodes can read and write every data at the same time, at least one copy exists on different hosts, and redundant data copies are synchronized in real time.

Disadvantages: as the size of the cluster increases, the performance will get worse and worse.

4. The DRBD disk network mirroring scheme has to be mentioned.

Architecturally, it is a bit similar to Replication, except that it is a process of data synchronization through third-party software, which is more reliable than Replication, but at the expense of performance.

Advantages: the software is powerful, data is mirrored across physical hosts at the underlying block device level, and different levels of synchronization can be configured according to performance and reliability requirements. IO operations are kept in order to meet the stringent requirements of the database for data consistency.

Disadvantages: non-distributed file system environments cannot support the simultaneous visibility of mirrored data, that is, performance and reliability contradict each other and cannot be applied to environments that require both. The maintenance cost is higher than that of MySQL Replication.

After talking about the advantages and disadvantages of various common architectures, what is left is the question of how to choose the right architecture to use in a real production environment. Everyone has their own ideas and experience in this respect, and it depends on which scheme is the best. In the daily work, the improvement of the structure is not achieved overnight, but a process of continuous evolution, optimization and improvement.

In the aspect of database, it has also experienced the process from single point to master-slave and then to master-slave + high availability, and also experienced the evolution from single MySQL+redis to MySQL+redis+es, and finally to MySQL+redis+es+codis and so on. Each evolution is to solve the practical problems and pain points in the production environment. In terms of MySQL alone, no one architecture can solve all the problems (pain points). You need to choose an appropriate architecture according to the actual situation. The solution of MySQL cluster implementation is very flexible, and it is also a challenge for MySQL workers to choose an appropriate architecture, and it is also the driving force for us to delve into and learn MySQL.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.