20160916-3:mysql Master-slave replication

Source: Internet
Author: User

First, what is the master-slave replication

Copy data from one database node to one or more database nodes (master node, slave node)

Second, the principle of master-slave replication

"Summary": stores the change action on the primary node to Binlog, and after the node has established a replication relationship to the master node, it initiates two threads: IO thread and SQL Thread,io thread are responsible for establishing a relationship with the master node (long connection), The Binlog of the master node is asynchronously saved to relay-log in real time, and then the SQL thread reads the Relay-log from the node in real time, and if the Relay-log has an update, the data is manipulated from the node based on the log contents.
(1) Binlog log format: statement/row/mixed,mysql5.7.7, row is the default log format

Third, the use of master-slave replication

1, real-time disaster preparedness, for failover
2, read and write separation, from the library to provide query services
3, from the library to do backup, to avoid business impact
4, master-slave replication of some modes: among them, mysql5.7 began to support multiple master one from

Iv. Construction of master-slave replication

1, necessary conditions: Open Log-bin parameters, master-slave Server-id different, relay-log, from the library server can connect to the main library

V. The problem of master-slave replication

1, the host after the outage, the data may be lost
2, from the library only one SQL thread, the main library write pressure, replication is likely to delay
"Solution-Semi-synchronous Replication"

(1) Semi-synchronous replication in 5.5 integrated into MySQL, in the form of plug-ins exist, need to install separately;
(2) Ensure that after the transaction commits, the Binlog is transferred to at least one from the library
(3) Do not guarantee the Binlog of this transaction from the library
(4) Performance has a certain reduction, the response time will be longer;
(5) Network anomaly or from the library down, stuck in the main library until the time-out semi-synchronous wait time or recover from the library;

"Solution-Parallel Replication"

(1) Parallel replication means: Multi-threaded apply Binlog (multiple SQL threads) from the library
(2) New in Community Edition 5.6
(3) Library level parallel application Binlog, the same library data changes or serial.
(4) mysql5.7 version of parallel replication is based on the transaction group

From for notes (Wiz)

20160916-3:mysql Master-slave replication

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.