The paper

In the actual production environment, if the MySQL database is read and written on the same database server, security, high availability, high concurrency and other aspects cannot meet the actual requirements. Therefore, data is synchronized through master/slave replication, and read/write separation is used to improve the concurrent load capacity of the database.

A master-slave replication

The primary/secondary replication of MySQL is closely related to the read/write separation of MySQL. The primary/secondary replication must be deployed first. Data can be separated from read/write data only after the primary/secondary replication is complete.

MySQL supports replication types

  1. Statement-based replication: SQL statements executed on the primary server are executed on the secondary server. MySQL uses statement-based replication by default, which is efficient, but sometimes cannot achieve accurate replication.
  2. Line-based replication: Copying changes over instead of executing commands on a slave server.
  3. Mixed replication: the statement-based replication is adopted by default. If the statement-based replication cannot be accurately replicated, the row-based replication is adopted.

Master/slave replication process

  1. The master logs these changes in the binary log until the update data for each transaction is complete. After writing to the binary log is complete, the master notifies the storage engine to commit the transaction.
  2. Slave Copies the binary log of the master to the trunk log. The I/O thread opens a normal connection on the master and then starts the binlog dump process. Binlog The Binlog dump process reads events from the master’s binary logs if the master has followed them. He sleeps and waits for the master to generate new events. The I/O thread writes these events to the relay log.
  3. SQL handles the last step of the process from the thread. The Sql thread reads the events from the slave log and replays the events to update the slave data to match the master data.

Reading and writing separation

In simple terms, read/write separation means only write on the MySQL primary server and only read on the MySQL secondary server. The basic idea is to have the master database handle transactional queries while the slave database handles SELECT queries. Database replication is used to synchronize changes resulting from transactional queries to databases in the cluster.

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 the 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. There are two common agents:

Mysql-proxy: it is an open source project of Mysql. It uses its own Lua script to perform SQL judgment. Although it is an official product of Mysql, it is not recommended to be used in the production environment.

Amoeba: Developed by Siru Chen, this program is developed by Java language. This software is dedicated to the distributed database front-end agent layer of MySQL, which acts as the SQL routing function for the application layer to access MySQL. Amoeba can implement functions such as high availability, load balancing, and data slicing of multiple data sources.

  • Reprinted from

www.cnblogs.com/skfa/articl…