20160916-3:mysql Master-slave replication

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