MySQL Advanced (ii) MySQL replication architecture

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

Mysql/slave

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/98/87/wKiom1k9_iWCGdXvAAFKadhSYXs200.png "title=" Master-slave. png "style=" float:left; "alt=" Wkiom1k9_iwcgdxvaafkadhsyxs200.png "/>

Master/slave is simpler, Master is responsible for responding to client write requests, Slave is responsible for responding to client read requests

Implementation principle:

Slave starts two threads, I/O threads, and SQL threads, master initiates the dump thread, and master stores the corresponding SQL statement in the binary log file whenever master's data changes, slave by i/ o thread connects to master's dump thread and each time it asks master if the binary file has changed, if the binary changes slave i/ o thread will read the changes in the binary file stored in the local trunk log and the contents of the trunk log through the SQL thread playback, and record the read to the binary file location, complete replication.

A problem exists:

1, master pressure is too large

2. User write request will not be completed after master outage

3. Node data may be inconsistent

4, the default use of asynchronous replication mode

5. Delay

reasons for the delay:

1,slave node too much, master will open a dump thread for each slave

2, Concurrent write request operation, because MySQL supports concurrent requests, if multiple write requests arrive at the same time, these writes will be recorded in the binary log, but can only be written to the binary file by serial

3,Slave read the binary file through I/O thread and write to the relay log is also only through the serial write

Master/master

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/98/87/wKioL1k9_xrjiV4fAAH2vvIN1So334.png "title=" Primary master. png "alt=" Wkiol1k9_xrjiv4faah2vvin1so334.png "/>

The advent of master/master solves the problem of Master/slave if the user's write request cannot be completed after the master outage, but at the same time introduces more problems than Master/slave, the principle of implementation and master/slave the same, You only need to start the binary and trunk logs separately in both master

Note the problem:

1. Avoid duplication of primary keys

2,Server-id Global Unique

A problem exists:

1, There are often two master data inconsistencies (there is a huge risk)

2. PRIMARY key conflict

3. Delay

Cascade replication

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/98/87/wKiom1k9_7iQGpVOAAHcuRx_k8o670.png "title=" Record. png "alt=" Wkiom1k9_7iqgpvoaahcurx_k8o670.png "/>

In some applications, it may be that the pressure difference between reading and writing is large, the reading pressure is particularly large, and a master may need 10 or more slave to support the pressure of the reading. At this time, master will be more difficult, because only the Slaveio thread is more, so write a little bit more pressure, the master side because replication will consume more resources, it is easy to cause replication delay. At this point, we can use MySQL to record the binarylog information on the slave side of the change that was generated, that is, to open the log_slave_update option. Then, a two-level (or more-level) copy is required to reduce the stress on the master side as a result of replication.

This multi-tiered replication architecture makes it easy to address the risk of the master side becoming a bottleneck because of the fact that the secondary slave is too many.

But the next class too many of the same changes to the bottom of the slave need to pass the MySQL will also be more, it may also cause a longer delay risk

A problem exists:

1, may lead to more serious delay

2, with the help of blackhole storage engine (black hole engine)

Semi-synchronous replication

MySQL Replication mode:

Async: Send a binary log to slave not wait for slave response to return directly to the client, data storage is successful

Sync: Send binary log to slave wait for all slave to return to client after successful execution, data storage is successful

Semi-synchronous: sends a binary log to slave waits for a slave response to return to the client after successful execution, data storage is successful

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1934414

MySQL Advanced (ii) MySQL replication architecture