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

What are the key issues buried in the MYSQL database application, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The use of Mysql is very common, and there are many topics related to mysql, such as performance optimization, high availability, strong consistency, security, backup, clustering, scale-out, scale-up, load balancing, read-write separation and so on. It takes a lot of effort to grasp the essence of it, and although there are many popular alternatives to mysql, mysql is the first choice from the point of view of the lowest cost and the easiest to maintain. From the perspective of the application scenario, the following is to organize the technical points of mysql and write a knowledge graph to facilitate more in-depth study and summary.

In the following picture, I try to divide the Mysql application scenarios into six types, and the key issues to be considered in each scenario are different, which leads to the knowledge points that need to be filled under different problem points, and then continue to study and organize based on these knowledge points. Look forward to your comments and provide learning materials, thank you! )

1. Single Master

The situation of single Master is common, for many personal sites, start-ups, small internal systems, taking into account 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 demand. In this scenario, I think the key topics that we should focus on are the four points shown in the picture above.

The most important link is data backup, if the transaction volume is very low, and has a very clear service period characteristics, simple mysqldump can be competent. But this is flawed, and the data from the backup point to the restore point is destined to be lost after the data is restored. However, in most cases, backup work can not be careless, the following list of small details, next semester will share more operational articles.

1) Cold backup: downtime, direct copy physical files, InnoDB engine (frm files, shared tablespace files, independent tablespace files, redo log files, my.cnf).

Restore: copy the files to the corresponding directory.

2) Hot backup: Ibbackup or XtraBackup tools, LSN,copy shared tablespace files and independent tablespace files recording redo log file checkpoints (without any blocking). The LSN,copy backup of redo log file checkpoints after recording copy is the resulting redo log.

Restore: restore tablespace files and apply redo log files.

3) warm preparation:

The mysqldump,--single-transaction parameter is used for transaction management to ensure data consistency. You cannot use DDL statements when backing up. Restore: execute the file directly, mysql-uroot-p

Binary semi-synchronous replication, master-slave server incremental replication

Restore: mysqlbinlog

One Master and one Slave

Most of the original intentions of "one master, one slave" are system performance and system high availability issues. In addition to the backup work in a single Master scenario, there are three key tasks to consider: performance optimization, read-write separation, and load balancing. Among them, there are many contents of performance optimization, and it is also a big topic. To take corresponding actions based on the service indicators of the system, most systems require that the request be completed within 3 seconds. The database can probably have a total execution time of 1.5 seconds. To meet this performance requirement is a reasonable optimization scheme. Next semester, organize the content according to the following priority: index optimization-"table design optimization -" database configuration optimization-"hardware optimization.

The implementation of read-write separation and load balancing is relatively simple. At present, the system I maintain is relatively backward and does not do read-write separation, because it is a system based on report functions, while load balancing relies on php code. From the actual operation and maintenance effect, it is not ideal, and the load balancing code is too embedded in the business logic code, which brings some noise to code maintenance. Next semester, we plan to carry out practice and performance tests on various middleware, and then share some test data.

3. One master, n slave

Once you start thinking about an one-master and multi-slave server architecture, it proves that your system has high requirements for availability, consistency, and performance. Many systems will look in this direction when they are first built, after all, it "looks" like the system will be much more robust. However, we can not rely solely on the configuration of mysql and the middleware that comes with mysql to solve the problems of availability and consistency.

IV. Horizontal clusters

The system is so large that it needs sub-database and sub-table, which is actually a matter of congratulations, but it is important to remember that it is necessary to mention the extreme performance optimization before considering these solutions that will increase the complexity of the system. Horizontal cluster mainly divides the system from the perspective of business characteristics, and the most thorough is that it is divided into subsystems. Some core data are shared among subsystems through some data synchronization schemes to avoid cross-database calls to cross-database join.

Then there are various system interface calls, which split large transactions into small transactions, and do a good job of isolation and synchronization between transactions. The three problems in the above figure should be unique in the horizontal cluster architecture. In actual projects, we try to avoid these requirements, but if they are really needed, there must be solutions. We will also sort out these problems one by one next semester and test some middleware that claim to support these functions.

V. Vertical cluster

In the end, the segmentation idea of the horizontal cluster is the cutting sub-system, while the most thorny problem encountered in the vertical cluster is capacity expansion. One of the systems of our operation and maintenance is to make 256 slices of the data in advance. 0x127 slices and 128 slices of 256 slices exist in two one-master and two-slave database clusters respectively. The system has been operated and maintained for more than 3 years, but there is no need for capacity expansion at present. The original intention of the design should be taken into account, assuming that one day the amount of data is so large that 256 slices can be divided into four large slices and stored in four clusters with one master and two slaves respectively, so as to achieve capacity expansion.

This idea is indeed desirable, but our sub-library logic is currently implemented in php code, which also affects the logic of the business code to a certain extent. Operation and maintenance is a little scary, so it is better to keep the business code fresh.

VI. Mixed mode

Rather than discussing the mixture of the above five scenarios, this part is a summary. In the above five scenarios, a total of 17 problem points are listed, and these 17 problem points are basically superimposed, and the deeper the framework is to do, the more we need to consider these 17 problem points. After 17 problem points are considered, the problem in mixed mode will not be a problem.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.