In essence, some mainstream databases are stand-alone systems, such as Oracle and SQL Server. However, mysql, as a kind of database originated from open source, has its own ideas. It is easy to support multiple database instances to work together. It implements master-slave replication, reads and writes separation, and faintly has distributed database wind.

Of course, this should not be a distributed database. Master/slave mode, should be considered cluster mode. Distributed means that a piece of data is divided into several pieces and stored separately on different machines. Clustering, on the other hand, is a piece of data that exists simultaneously on multiple machines. Distributed is intended to solve the problem of high concurrency; Clustering is primarily for high availability. Of course, clusters can also take advantage of load balancing to address some of the high concurrency pressures. (The above is purely my current level of personal understanding, perhaps pure nonsense, said wrong do not blame)

Back to the point. Master/slave replication and read/write separation are two sides of the same body. In fact, they are the same thing: the database is divided into master and slave libraries. The master library accepts writing while the slave library is used for reading, which effectively reduces pressure and improves efficiency. Data is replicated from master to slave for consistency.

Primary/secondary replication must be deployed first. Data can be separated from read/write data only after the primary/secondary replication is complete. Specific principles:

Type of master/slave replication

Statement based replication: SQL statements executed on the master are executed on the slave as well. Mysql uses statement-based replication by default, which is efficient, but sometimes cannot achieve accurate replication.

2. Line-based replication: Copy the changed content directly over.

3. Mixed type replication: by default, statement-based replication is adopted. If statement-based replication cannot be accurately replicated, row-based replication is adopted.

2. Master slave replication process

Synchronization is mainly done between the master and slave libraries through log copy.

1. When the master database data is written, changes will be recorded in binary log;

2. The IO thread of the secondary library reads the binlog content of the primary library at a specified location and stores it in the local Relay Log; The IO thread then sleeps and hangs until new data is written.

Read the relay log from the Sql thread of the library, replay the events in it and update the slave data.

Three, the implementation of read and write separation

At present, there are two common types of mysql read and write separation:

1. Internal implementation based on program code

In the code according to select, INSERT routing classification, this kind of method is also more commonly used in the production environment, the advantage is better performance, because in the program code, do not need to increase the additional equipment as hardware expenses; The disadvantage is that it needs research and development personnel to achieve, and operation and maintenance personnel have no way to start.

2. Implementation based on intermediate proxy layer

The proxy is usually located between the client and the server. After receiving the request from the client, the proxy server forwards the request to the back-end database.

See article: Mysql master-slave replication and read-write separation