MySQL Replication (Master/slave implementation)

1, MySQL replication mode:

* Row-based replication (introduced in 5.1)

* Statement-based replication

Note: Asynchronous data replication is accomplished by recording binlog on the main library and replaying the logs on the standby. This means that the data on the repository at the same point in time may be inconsistent with the primary inventory and cannot guarantee a delay between the primary and the standby.

2. Application Scenario:

* Data distribution: MySQL replication typically does not cause much strain on bandwidth, but row-based replication is more stressful than the traditional statement-based replication pattern of bandwidth.

* Load balancing: With MySQL replication, you can load-read operations across multiple servers, optimize for read-intensive applications, and make it easy to implement basic workload balancing with simple code. (for example, hard-coding a robot name or pointing a machine name to multiple IP addresses.) And so on

* Backup Data

* High Availability and failover

* MySQL upgrade test

3. Replication Workflow:

1) Log data changes to the binary log on the main library.

The main library logs the events of the data update to the binary log each time the data update is ready for the commit transaction to complete. MySQL logs binary logs in the order in which they were committed, rather than in the order in which each statement was executed. When the record is complete, the master tells the storage engine to commit the transaction;

2) The standby library copies the logs from the main library to its own trunk log.

The binary dump thread reads events from the binary log on the main library by starting a worker thread called an I/O thread, establishing a connection to the main library, and then starting a special binary dump (Binlog dump) thread on the main library (the thread does not have a corresponding SQL command). It does not poll for events. If the thread catches up with the main library, it goes into hibernation until the main library sends a signal to notify it that a new event is generated before it wakes up, and the standby IO thread logs the received event to the trunk log.

3) The standby reads the events in the trunk log and replays them over the standby data.

4, Xxoo:

MySQL is based on statement-and-row replication and feels similar to Redis's RDB and AOF implementations. It is estimated that the architecture such as master and slave are implemented in this way.

MySQL Replication (Master/slave implementation)