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

Advantages of mysql replication: 1, data distribution 2, data backup 3, load balancing 4, prompt high availability

Mysql/slave

Master/slave is relatively simple. Master is responsible for responding to client write requests, and slave is responsible for responding to client read requests.

The principle of implementation:

Slave starts two threads, the I / O thread and the master thread, and master starts the SQL thread. Whenever the data of master changes, master will store the corresponding Dump statement in the binary log file. Slave's SQL thread connects to the dump thread of SQL through the iThread and asks master every time whether the binary file has changed. If the binary file changes, the slave I / o thread will read the changed contents of the binary file and store them in the local relay log and play back the contents of the relay log through the sql thread, and record the location of the binary file read this time to complete the copy.

There is a problem:

1. Master pressure is too high.

2. User write requests will not be completed after master downtime

3. Node data may be inconsistent

4. Asynchronous replication is used by default

5. Delay

Reason for the delay:

1. If there are too many slave nodes, master will open a dump thread for each slave

2. Concurrent write request operations. Because mysql supports concurrent requests, if multiple write requests arrive at the same time, these write operations will be recorded in the binary log, but can only be written through serial when writing to binary files.

3. Slave reads binary files and writes to the relay log through the iCompo thread, which can only be written serially.

Master/master

The emergence of master/master just solves the problem that the user's write request can not be completed if the master goes down in master/slave, but at the same time, it introduces more problems than master/slave, and the implementation principle is basically the same as master/slave. You only need to start the binary log and relay log in the two master.

Pay attention to the problems:

1. Avoid repetition of primary keys

2. Server-id is globally unique

There is a problem:

1. There are frequent data inconsistencies between the two master (there is a great risk)

2. Primary key conflict

3. Delay

Cascade replication

In some application scenarios, there may be a great difference between reading and writing pressure, and the reading pressure may be especially high. A Master may need 10 or more Slave to support the pressure of reading. At this time, Master will be more difficult, because there are more SlaveIO threads connected to it. When the pressure of writing is a little greater, the Master side will consume more resources because of replication, which can easily cause replication delay. At this point, we can take advantage of the ability of MySQL to record the BinaryLog information generated by replication on the server side, that is, to turn on the log_slave_update option. Then, reduce the pressure on the Master side due to replication through secondary (or more level) replication.

This multi-level cascading replication architecture easily solves the risk that the Master side will become a bottleneck because there are too many Slaves attached to it.

However, if there are too many lower classes, there will be more MySQL for the same change to be transmitted to the lowest Slave, which may also lead to the risk of long delay.

There is a problem:

1. May lead to more serious delay

2. With the help of BlackHole storage engine (black hole engine)

Semi-synchronous replication

Mysql replication method:

Async: send the binary log to slave and return it to the client without waiting for the slave response. The data is stored successfully.

Synchronization: send the binary log to slave to wait for all slave responses to be successfully executed and return to the client. The data is stored successfully.

Semi-synchronous: send the binary log to slave to wait for a slave response to be successfully executed and return it to the client. The data is stored successfully.

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.