MySQL master-slave Replication Principle Introduction

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.

1.1 MySQL Supported Replication types:

(1): statement-based replication: SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient.

Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.

(2): Row-based replication: Copy the changed content past, rather than executing the command from the server again. Support Starting from mysql5.0

(3): Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

1.2 Replication-Resolved issues

MySQL replication technology has some of the following features:

(1) Data distribution (distribution)

(2) Load balancing (load Balancing)

(3) Backup (Backups)

(4) High-availability and fault-tolerant lines of availability and failover

1.3 How replication works

As a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);

(2) Slave copies the binary log events of master to its trunk logs (relay log);

(3) Slave redo the event in the trunk log and change the data to reflect its own.

The basic process for MySQL replication is detailed below:

1. Slave the above IO line thread attached the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side.

In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;

3. After the Slave IO thread receives the information, it writes the received log content to the end of the relay log file (mysql-relay-bin.xxxxxx) on the Slave side,

And the read to the master side of the Bin-log's file name and location to the Master-info file, so that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me"

4. When the Slave SQL thread detects a new addition to the Relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the Master end, and executes the query itself.

In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.

This article is from the "Legend of the Hungry Wolf" blog, please be sure to keep this source http://9548010.blog.51cto.com/9538010/1760339

MySQL master-slave Replication Principle Introduction