MySQL Master-slave replication (i)

MySQL replication is a very important feature of MySQL, which is mainly used for data copy operation between the primary server and the slave server.

MySQL replication refers to the process of copying data from one MySQL master server (master) to another or multiple MySQL slave servers (salves).

The DDL and DML operations of the primary database are uploaded to the slave server via a binary log.

The logs are then re-executed from the server to keep the data in sync with the master and slave servers.

In MySQL, the copy operation is asynchronous, and the slaves server does not need to persist the connection to accept the master server's data.

MySQL supports one primary server to replicate from the server at the same time, from the server to the other primary server from the server.

If the MySQL master server has a large number of accesses, you can reduce the primary server access pressure by replicating the data and then querying from the server.

At the same time, from the server as the primary server backup, you can avoid the primary server because of failure data loss problems.

A copy operation can be broadly divided into 3 steps:

1. The master server records the change of data into binary log (binary logs);

2. Copy the binary log of the primary server from the server to its trunk log (relay log);

3. Redo the relay log from the server and keep the data changes in sync with the master server.

It seems that the principle is still very simple.

A more detailed procedure is as follows:

First, the primary server logs the binary log, and the primary server logs the information of these operations into the binary log (why binary, not text) before each transaction updates the data.

After the event is written to the binary log, the primary server notifies the storage engine to commit the transaction.

The I/O process on the slave connects to master and issues a log request, and master receives a request from the I/O process from slave

The log information after the specified location of the log is read based on the requested information through the I/O process responsible for replication.

Returns the I/O process to slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file that was returned to the master side and the location of the Bin-log.

After the slave I/O process receives the information, the received log content is added to the end of the Relay-log file at the slave end,

and record the Bin-log file name and location of the read to the master side to the Master-info file (is this master-info file on master or slave?). ）

After slave's SQL ' process detects the new additions in relay-log, it immediately resolves the contents of Relay-log as executable content at the real execution time on the master side and executes on its own.

MySQL replication environment, more than 90% is a master with one or more slave architecture mode, if the pressure of master and slave is not too large,

Asynchronous replication has very little latency, especially slave-side replication to two processes (I/O and SQL?). After processing, the delay of the slave end is reduced.

For applications where the data is not in real-time demand, as long as the number of slave is extended by a cheap computer server, the reading pressure is spread across multiple slave,

Can solve the reading pressure bottleneck on the data side.

MySQL Master-slave replication (i)