MySQL is widely used, and there are many topics related to MySQL, such as performance optimization, high availability, strong consistency, security, backup, clustering, horizontal scaling, vertical scaling, load balancing, read/write separation, etc. It takes a lot of work to master the essence of this, and while there are many popular MySQL alternatives, MySQL is the best choice for the least costly and most maintainable. From the perspective of application scenarios, the technical points of MySQL are organized and a knowledge map is written for further study and summary.

As shown in the figure below, I try to divide the application scenarios of MySQL into 6 kinds. In each scenario, the key problems to be considered are different, so as to draw out the knowledge points to be supplemented under different problem points, and continue to study and sort out based on these knowledge points.

A, single Master

The situation of single Master is common. For many personal sites, start-up companies and small internal systems, considering the cost, update frequency, system importance and other issues, the system only relies on a singleton database to provide services, which has basically met the needs. In this scenario, I think we should focus on four topics as shown in the picture above.

The most important step is data backup, and simple mysqldump can do the job if the transaction volume is very low and the time of service is very clear. However, this is flawed, as data is destined to be lost between the backup point and the restore point. In most cases, however, backups can be tricky. Here are a few details that will be covered in more practical articles next semester.

1) Cold standby: stop, copy physical files directly, InnoDB engine (FRM files, shared tablespace files, independent tablespace files, redo log files, my.cnf).

Restore: Copy the file to the corresponding directory.

2) Hot backup: Ibbackup or XtraBackup records the LSN of the checkpoint of redo log files, copies shared tablespace files and independent tablespace files (without any blocking), and records the LSN of the checkpoint of redo log files after copy. Copy backup is the generated redo log.

Restore: Restores tablespace files and applies redo log files.

3) Warm preparation:

  • The mysqldump, –single-transaction parameter is used for transaction management to ensure data consistency. DDL statements cannot be used for backup. Mysql -uroot -p < file name. SQL >

  • Binary semi-synchronous replication, incremental replication between primary and secondary servers

Recovery: mysqlbinlog

Two, one master and one subordinate

The primary consideration is mainly about system performance and high availability. In addition to the backup work in the single-master scenario, performance optimization, read/write separation, and load balancing need to be considered. Which is more, the content of the performance optimization is also a big topic, supported by service index of the system to take the corresponding action, most of the system requirements is 3 seconds to complete the request, the overall scaling down, the database can probably have the total execution time of 1.5 seconds, can meet the performance requirements is reasonable optimization solution. Next semester, we will arrange the contents according to the following priorities: Index Optimization – table Design optimization – Database configuration optimization – hardware optimization.

Separation and the realization of load balancing is relatively simple to read and write some, I the present maintenance system is relatively backward, there is no separation of doing, speaking, reading and writing, because it is a mainly reports class function of system, and load balancing is dependent on the PHP code to do, from the point of actual operational effect, is not ideal, and load balancing of code embedded in the business logic code too much, Bring some noise to code maintenance. Next semester, we plan to conduct practical and performance tests on various middleware and share some of the test data.

N n n n n n n n n

Once you start thinking about a master/slave server architecture, you’re proving that your system has high requirements for availability, consistency, and performance. Many systems are built in this direction because it “looks” more robust. However, MySQL configuration and MySQL middleware alone cannot solve the problem of availability and consistency.

Fourth, horizontal cluster

The fact that the system is so large that it needs to be divided into tables and libraries is something to be celebrated, but it is important to remember that these solutions will increase the complexity of the system only after the performance optimization mentioned above has been done to the utmost. Horizontal clustering is mainly used to segment the system from the perspective of business characteristics, and is most thoroughly divided into subsystems. Some core data is shared between subsystems through some data synchronization schemes to avoid cross-library call cross-library join.

Then there are various system interface calls, breaking up large transactions into smaller ones, isolating and synchronizing them. The three problems in the figure above should be characteristic of a horizontal cluster architecture and should be avoided in real projects, but if they are needed, there should be a solution. The next semester will also address each of these issues and test some middleware that claims to support these capabilities.

Vertical clusters

A system I operated and maintained had 256 slices of data in advance. Among the 256 slices, 0~127 slices and 128~255 slices respectively existed in two database clusters with one master and two slaves. The system had been operated and maintained for more than 3 years. There is currently no demand for expansion. The original design should be taken into consideration. If the data volume is very large one day, 256 slices can be divided into four large chunks and stored in four clusters with one master and two slave nodes, thus achieving capacity expansion.

This idea is indeed desirable, but our branch library logic is currently implemented by PHP code, which also affects the logic of business code to a certain extent. It is a little scary to operate and maintain, so it is better to keep the business code clean.

In the next semester, we will introduce the use of some middleware that realize the library routing function, and also put some expansion and shrinkage schemes into practice according to the actual situation. Please look forward to sharing the practical operation effect.

6. Hybrid mode

This section is more of a summary than a discussion of the mix of the above five scenarios. In the above five scenarios, a total of 17 problem points are listed. These 17 problem points are basically superimposed. The further we go into the framework, the more we need to consider all these 17 problem points. When all 17 problem points are considered, problems in hybrid mode are no longer a problem.