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

The following content mainly brings you the definition and analysis of Mysql replication. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

Master/Slave

Master: write/read, all write operations are performed on the master node

Slaves: read. All read operations are sent from the node.

Why copy it?

Redundancy: promte (promotion-based), remote disaster recovery, which can be achieved through manual or tool programs (MHA)

Extension: transfer part of a "read" request

Support for secure backup operations

Test need

Master / slave architecture implementation:

Enable the binary log on the master node, start the connection thread from the slave node, and request the master node to send a copy of this event to itself. There is a thread called IO Thread on the slave node. If dump thread,IO Thread is enabled on the master node, the log received by the slave node will be stored to the relay log, and the SQL THread on the cloud server is responsible for generating a data from the relay log. This way of data synchronization is asynchronous. The master server is responsible for the write operation and the slave server is responsible for the read operation.

Master-slave may lead to inconsistency of master-slave data. For example, the master service multi-threaded process, the slave service area is a single-threaded process, which may lead to inconsistent master-slave data, and the data of the slave server may lag behind the master server. This problem can only be found as soon as possible. If the incorrect data is changed manually, if the data is very different, it is difficult to recover manually. You can make a backup on the master server and restore the data directly to the slave server.

The master server is stored in binary log and the slave server is operated asynchronously. The transmission is one-way. But there is a problem here, if the write operation exceeds the performance of the primary server, the solution is to use the dual master model.

There are three forms of master-slave replication: synchronous replication, asynchronous replication and semi-synchronous replication

Synchronous replication:

Dual master model: write operations are written locally, synchronized to another node, and placed in relay.

The master server starts the relay log and the binary log, and all writes sent to this node are recorded in the binary log and sent to the peer master server through dump thread. The peer master server stores the received data in the relay log through io thread and replays it based on sql thread. The primary node is redundant. Each node can read and write. This operation reduces read requests, with half of the requests loaded by each server, but the number of requests for write operations is the same, because the dual-host server writes a piece of data.

The read-write separator of middleware is used to realize the separation of request read and write, and the middleware can use keepalive to achieve redundancy. But with the dual-master model, there is no need for read-write separation, just use haproxy or nginx or lvs to implement the four-tier scheduling of requests and schedule requests to different servers.

The malpractice of mysql master-slave replication may lead to inconsistent master-slave data and inconsistent data after running for a period of time, depending on the business logic. This situation is not allowed in the case of strong consistency of data requirements.

On the master server, the operation is multithreaded in parallel, but the record in the binary file is a serial record, and the slave server runs in a single thread to obtain data, which leads to the speed of pulling data from the server lags behind that of the master server. this leads to differences in data, which in the long run will lead to serious data inconsistency. If read-write separation is set here, it may cause the data read from the server to be incorrect. The two-master model also has the same problem. The master and slave data are inconsistent, and the suggested solution is to modify the data manually. If the data is too different, the slave server will be shut down, a backup will be made on the master server, and the slave server will be restored.

Asynchronous replication:

One master and many followers

One follower and one master

Cascade replication; one master replicates to one slave, at the same time, the slave server is the master of other servers, and the other slave server replicates data to the relay server. the role of cascade is to reduce the pressure on the master server. The middle slave server needs to enable binary log and relay log, at the same time, the middle slave server only plays the role of transition and does not need to save data, so the block hole engine is used on this intermediate slave server to ensure that there is no data storage, but both relay log and binary log are saved normally, which plays the effect of transition.

Circular replication; in multi-master mode, circular replication is used, but the chain is longer, which may lead to serious data backwardness.

Double master replication

Semi-synchronous replication:

One-slave multi-master model: each master server provides a different database. Master only guarantees the success of one operation in the slaves and returns, regardless of the other slave. This feature was introduced by google for MYSQL.

For the above definition and analysis of Mysql replication, if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact those before and after sale on the official website. I hope this article can bring you some knowledge updates.

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.