Shulou(Shulou.com)05/31 Report--

This article mainly explains the example analysis of MySQL high availability architecture at the business level. Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the example Analysis of MySQL High availability Architecture at the business level.

First, MySQL architecture design-business analysis

(1) read more and write less

Dotted lines represent deployment across computer rooms, such as e-commerce systems, where a Master has both read and write, which is important for read data consistency, and reads should be placed on top of the Master.

M (R) is only a standby library, and only after M (WR) is dead will it be switched to M (R). At this time, M (R) becomes a read-write library. For example, in the game system, there are a lot of Salve will be mounted on the next M (R).

(2) read more and write less MMS- e-commerce

If it is an e-commerce type, this kind of read more and write less, it is generally a master to drag 4 to 6 slave, all slave mounted in a master is also enough.

When switching, switch the read and write service of M1 to M2, and then attach all the slave on M1 to M2, as shown below:

(3) read more and write less MMSS- games

If it is the game industry, it is obvious to read a lot, there will be a general situation that 1 Master will be hung with more than 10 Slave, so at this time, you can mount part of the Slave to the new M (R). At least some pressure will be reduced so that at least when the server hangs, all slave will not be affected, and some slave on M (R) will continue, which will not affect all slave, as shown in figure 3.

(4) read less and write more.

It means that reading does not affect the efficiency of writing, so both reading and writing can be placed on one M1 (WR), while the other does not provide reading or writing, and only provides standby redundant remote disaster recovery.

This remote disaster recovery is very important, otherwise, if it is a stand-alone, unilateral business, in case the idc computer room fails, it will generally affect the online business, which causes the business to be unable to be applied for 2 hours. For online e-commerce transactions, the impact is quite great, so in order to maximize the 7 hours and 24 hours, it is necessary to achieve remote disaster recovery, and MM has to cross the idc computer room. Although there are some requirements for resources, it is indispensable for HA, and there must be this MM mechanism.

When switching, just switch all reads and writes from M1 to M2 directly.

(5) Reading and writing are equally divided.

Reading and writing are similar, but reading does not affect the ability of writing. Put reading and writing on M1 (WR), and then part of the reading is also placed on M2 (R). Of course, M1 and M2 are also deployed across computer rooms.

When switching, just switch part of the read and all the writes from M1 to M2.

Second: MySQL architecture design-common architecture

(1) strong consistency

For the tradeoff of read consistency, if the real-time requirement of reading and writing is very high, put both read and write on M1, M2 is only as standby, that is, it adopts the same architecture mode as reading less and writing more in the above one (4).

For example, the order processing process requires strong consistency in reading, real-time realistic reading, and this architecture model should be used for transactions or dynamic real-time report statistics.

(2) weak consistency

If it is weak consistency, it can be put on M2 by sharing some reading pressure and traffic, such as reading some reports and reading static configuration data. For example, monthly statistical reports, such as the home page recommendation business real-time requirements are not very high, we can adopt this weak consistency of the design architecture pattern.

(3) intermediate consistency

If there is neither strong consistency nor weak consistency, then we will adopt the intermediate strategy of deploying another S1 (R) in the same computer room as a backup library to provide reading services and reduce the pressure on M1 (WR), while the M2 of the other idc server room is only used for standby disaster recovery.

Of course, three database servers will be used here, which may increase the purchasing pressure, but we can provide better capabilities and ways to provide external data services, in practice, as much as possible.

(4) Statistical business

For example, PV, UV operation, page count, traffic statistics, data summary, and so on, can be classified as statistical services.

The statistics of large queries on the database are very resource-consuming. Statistics are divided into real-time statistics and non-real-time statistics. Because the master and slave of mysql are logical sql mode, 100% real-time can not be achieved. If online requires strict real-time statistics such as train tickets and financial settlement in different places, mysql is not its strong point, it can only be realized by querying M1 master database.

A, but for statistics that are not strictly real-time, mysql has a good mechanism is binlog. We can parse Parser through binlog, write statistics into statistical tables, or send messages to applications for statistics. This is a quasi-real-time statistical operation, and there is a certain temporary acceptable statistical delay. If you want 100% real-time statistics, you can only query the M1 main database.

Through the way of binlog to achieve statistics, in the Internet industry, especially e-commerce and games, can solve more than 90% of the statistical business. Sometimes, if users or customers propose real-time read- time, they can communicate why they need real-time and understand specific business scenarios. Some may not really need real-time statistics and need to make tradeoffs. They need to communicate effectively with users and customers for many times to make a statistical architecture model that is more suitable for the business.

B, there is also a kind of offline statistics service, such as monthly report and annual report statistics, which can put the data into the data warehouse or the third party Nosql for statistics.

(5) Historical data migration

For the migration of historical data, we need to try not to affect the online business and the query writing operation on the online as much as possible, so why do we need to migrate historical data? Because the data of some businesses are timely, such as the completed historical orders in e-commerce, there will be no more update operations, only very simple query operations, and queries will not be very frequent, maybe not even once a day.

If the historical data is still in the online library or online table, it will affect the performance of online, so for this, you can migrate the data to a new historical database, which can be mysql or nosql, data warehouse or even hbase big data.

The way to achieve this is to query all the data through the slave library, and then filter out the data according to business rules such as time, a certain latitude, and put it into the historical database (History Databases). After the migration, go back to the main database M1 and delete the historical data. In this way, at the business level, the query should take into account both real-time data and historical data, and the online query and history query can be connected on the filter according to the migration rules. For example, if you query in the online library within one month and one month before the query in the history library, you can put this rule in the migration filter layer and the application query business module layer of DB. If possible, the configuration can be more detailed, and the migration filter layer of DB can be affected by applying the query business module layer. For example, in the past, the query business was divided into one month as the benchmark, but now the query business has changed, based on 15 days. Then the application query business module layer changes will automatically change the filter layer of DB, achieving half automation and more intelligence.

(6) MySQL Sharding

Like oracle, which is based on rac and shared storage, it can be implemented without sharding, which only needs to be expanded from rac storage. But the cost is relatively high, shared storage is generally more expensive, with the explosive growth of data expansion of the business, you will continue to accumulate your costs, or even reach an astronomical figure.

At present, except for the perfect business logic layer of oracle, other solutions of this kind of share disk are not perfect.

Sharding of Mysql also has its limitations. There will be great problems in data query, access and statistics after sharding. Sharding of mysql is a distributed method to solve the storage of share nothing, which is generally divided into vertical split and horizontal split.

(6.1) Vertical split

Can be split horizontally, longitudinally, horizontally and vertically, and according to the business.

6.1.1 horizontal split

The horizontal split in the Mysql library means that there are many db libraries in each database instance, and there are tables An and B in each db library. For example, there are tables An and B in the db1 library, and tables An and B are also available in the db2 library. Then we split the An and B tables of the db1 and db2 libraries into two, and split the database into db1, db2, db3 and db4. Table An is stored in the DB1 and db2 libraries, and table B is stored in the db3 and db4 databases. There are only A table data in db1 and db2 libraries, and only B table data in db3 and db4 libraries.

For example, as an e-commerce business, each library has a log table and an order table. If form An is a log form log, and form B is an order table Order, generally speaking, there is no strong relationship between logging and writing an order. We can separate form A log form from form B order table. Then a horizontal split is done at this time. The log table of form An and the order table of form B are separated and placed in different databases. Of course, the database names of tables An and B can also be kept the same (PS: in different instances), as shown in the following figure:

PS: this split is mainly aimed at the fact that different businesses have little impact on tables, and the business association between tables is very weak or there is basically no business association. The advantage of splitting is that irrelevant data tables are split into different instances, which is good for the balance of database capacity expansion and performance improvement.

6.1.2 Longitudinal split

Split out different db libraries on the same instance and put them into separate different instances. The adaptation scenario and requirement of this split is that there is little business connection between db1 and db2, similar to tables An and B in 6.1.2. If you have used cross-database business to use both db1 and db2, it is personally recommended to reconsider the business and try to put the table of a module in a library instead of collapsing the library operation.

In this vertical split, the separate library db1, Table An and Table B are strongly related. As shown in the following figure:

PS: see a lot of people who use mysql, always put a lot of tables that have no business relevance in one library, or always put many db libraries in the same instance, just like using oracle. One of the major principles of the use of Mysql is simple, as simple as possible, simple to use mysql, the library should be strictly separated; tables do not matter, should be strictly split into the library. In this way, it is very convenient and simple to expand our business. We only need to split the db where the business module is located and put it on the new database server.

6.1.3 horizontal and vertical split

For those who have just started, they put all the tables of all the libraries on an instance in order to produce products quickly. After business development, they are faced with data splitting. Here, horizontal and vertical splits are combined and implemented together, as shown in the following figure:

6.1.4 Service split

It's a bit similar to horizontal split, but there are differences. For example, for a supplier, there may be 10 suppliers on the whole website, each supplier on a website has a certain quantity, and the amount of data between suppliers is of the same scale. then the latitude of the supplier can be used to split.

For example, in the usern library, tables a, b and c are all strongly related and have complete business logic. Here, only the user (supplier) latitude is not related, so the data can be split according to the user's latitude.

That is, user 1 and user 2 each have a complete set of business logic and are not related to each other, so user 1 and user 2 data can be split into different database instances. At present, many Internet companies or game companies have a lot of business that is split by user latitude, such as qunaer, sohu game, sina and so on.

(6.2) horizontal split

Horizontal split is relatively simple, but difficult, which can lead to distributed situation, cross-data situation, cross-transaction situation can be divided into about three categories: 1 is historical data and real-time data split, 2 is single database multi-table split, 3 is multi-database multi-table split.

6.2.1 splitting of real-time data and historical data

It is the same logic as historical data migration, that is, to migrate the data from the online library to the listory database. For real-time reading and writing, the data is placed in the online db library, and for the distant data, it is placed in the historical History DB record library, where the history library can be mysql or other nosql libraries.

6.2.2 split of single database and multiple tables

The main purpose is not to solve the capacity problem, but to solve the performance problem. To add the current instance, there is only one DB, one large table, and one large table occupies the whole instance. At this time, we cannot split the db, because there is only one single table. At this time, we can only split the table. The main way to split the table is to solve the performance problem, because the larger the single table is, the bigger the table is. For mysql, traversing the tree structure of the table consumes more resources, and sometimes a simple query may cause many leaf nodes of the entire db to change. The insert, update and delete operations of the table will cause changes to almost all nodes. At this time, the amount of operation will be very large, and the read and write performance during the operation will be very low. At this time, we can consider splitting the large table into multiple small tables. The work experience is divided into 16 small tables according to hash, and there are also 50 small tables according to the id primary key / 50. The following example is divided into 2 small tables.

6.2.3 Multi-database and multi-table split

On the basis of a single database with multiple tables, if the space resources of a single database are no longer sufficient to provide business support, you can consider the multi-database and multi-table approach to solve the space and performance problems. However, there will be a problem of cross-database query operation, and the query will have other strategies, such as adding a logic db layer to achieve cross-database and cross-instance automatic query, as shown in the following figure:

6.2.4 horizontal split summary

Horizontal split principle:

-a. Split the dimensions as evenly as possible.

-b. Try to avoid cross-library transactions.

-c. Try to avoid cross-database queries.

Design:

-a do mod to split the data table according to the split dimension, most of which are modular splitting mechanism, such as the 16-mode principle of hash.

-b split the database according to the data capacity

Data operation

-a cross-transaction operation: distributed transactions, implemented indirectly by pre-writing logs.

-b Cross-database query: data summary or message service

6.2.5 case description

U case:

-split into 64 sublibraries and 1024 subtables according to the user dimension

User_id24 is split into 1024 subtables

Each sub-database stores 1024 account 64 sub-tables.

When taking the model, you can use the last 4 digits or 3 digits of the id to take the model.

U operation 1: using Configure DB

-after the query operation is split, create a Configure DB. This DB stores the mapping relationship of the database tables of all instances. When I send a request to query user1 data from APP, which database table of thousands of instances is the user1 data stored in? The related information is in Configure DB. APP first goes to Configure DB to obtain the related contact information of user1 (for example, in the twee0016 table stored in d01 database), and then APP directly queries the twee0016 table of the corresponding dumb01 instance based on the related information to obtain data.

U operation 2: using Proxy

-after the query operation is split, a Proxy,APP access Proxy,Proxy can be directly routed to a specific db instance according to the access rules, and a new sql is generated to operate the corresponding db instance, and then the operation result is returned to APP through the Proxy protocol.

-the advantage is that Proxy and db instances are in the same network segment, so the operation time of Proxy and db instances is very short.

U operation 3: using Data Engine

-for the query operation after the split, there is a Data Engine Service, in which the mapping relationship of all database instances is configured. You need to install an Agent on the APP application, which is synchronization logic. In the JDBC layer, DES can achieve the separation of read and write. The principle can be found in the implementation of TDDL.

6.3 Cluster management

Vertical expansion: an instance is split into multiple instances. It is relatively simple to split longitudinally, with few modifications. Notify Configure DB or DES when splitting, so that data cannot be queried or cannot be entered into the new db after split, as shown in the following figure:

Horizontal expansion: it is complicated to expand the database table again on the basis of vertical expansion to 2 libraries, so you need to inform Configure DB and DES of the routing connection information of more detailed libraries and tables in time.

At this point, I believe you have a deeper understanding of "example analysis of MySQL high availability architecture at the business level". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.