MySQL read-write separation

MySQL Read and write separation principle

MySQL master-slave replication and MySQL read-write separation of the two have a close connection, first of all to deploy master-slave replication, only after the copy, can be based on the data read and write separation.

Read-write separation is only written on the primary server, only read from the server, the basic principle is to let the primary database processing transactional queries, and from the database processing select queries, database replication is used to synchronize the updates caused by transactional queries to the database in the cluster.

Implementation steps: Build two servers through the CentOS system virtual machine, master server and slave server, install MySQL through the Yum command in Linux, ensure the service is normal; Modify the server Server-id value, set read-only permissions from the server, the master server writes the data, and the synchronization is updated from the server.

Three ways to replicate:

1) Synchronous replication

The main library executes a transaction, and all of the transactions executed from the library are returned to the client . Because it is necessary to wait for all the transactions from the library to be returned, the performance of full synchronous replication is inevitably severely impacted. A time-out is required.

2) Asynchronous replication

The default copy of MySQL is asynchronous. When the main library finishes executing a client-submitted transaction, it returns the end to the client immediately, and does not care whether the library has been received and processed , so there is a problem, if the Lord crash out, the transaction that the master has committed may not have been passed, if at this point, the force will be promoted from the main, May cause the new Lord's data to be incomplete.

3) Semi-synchronous replication

Between asynchronous replication and full-synchronous replication, the main library does not immediately return to the client after executing a client-submitted transaction, but waits for at least one to be received from the library and written to the Relaylog to return to the client. Semi-synchronous replication improves data security relative to asynchronous replication, and it also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in networks with low latencies.

Note: MySQL read-write separation through program code is a good choice, but not all applications are suitable for the application code to achieve the separation of read and write, such as some large and complex Java applications, if the implementation of read-write separation in the program code to change the code is larger, Applications like this are generally considered to be implemented using the proxy layer.

MySQL read-write separation