Replication for MySQL

Through replication, the data of a MySQL instance can be completely copied to multiple other MySQL databases at the same time. Under normal circumstances, the replication delay is not long. Each server has the same data only. Application access is no longer just a MySQL instance, but can access any of the MySQL clusters.

Implementation Mechanism of Replication

1. Replication thread

MySQL replication is an asynchronous replication process. It is the process from Master to slave. This process requires three threads to complete. Two threads (SQL thread and I/O thread) are on the slave side, and the other thread (I/O thread) is on the master side.

To implement replication, you must first enable the log-bin function on the master. Because the entire replication process is actually the process in which slave reads the binary logs on the master and completely retries the logs on itself.

The replication process is as follows:

First, the slave Io thread connects to the master and requests the specified position of the BINLOG

2. The master receives the IO request, returns the log information, and the position of the returned information in the bin log on the master end.

3. After the slave Io receives the information, write the log content to the end of the relay log of the slave in sequence, and record the location of the read BINLOG on the master end to the master-info file.

Fourth, after the slave SQL thread finds that the relay log is updated, it will immediately parse the log into a specific query statement and execute it.

2. Replication implementation level

MySQL replication can be based on a statement or a record. Different replication levels will affect the BINLOG format on the master.

1. Row level

BINLOG records each row of data in the modified form. Then, modify the same data on the slave side.

Advantage: BINLOG does not need to record the context-related information of the executed query, as long as the record is modified and changed to what it looks like.

Disadvantage: if an update statement updates the entire table, the change information of the entire table should be recorded in the log, resulting in a large amount of log Content. Especially for alter table. Because MySQL uses the alter table operation to recreate all data in the entire table, that is, every data entry in the table needs to be changed, all records will enter the log.

2. Statement level

Record query to log.

Advantage: the row level disadvantage is solved.

Disadvantage: the query context needs to be recorded at statement level, which is a great challenge for MySQL replication. After some specific functions or functions, such as last_insert_id (), different values may be obtained on different servers.

Third, Mixed Level

In the case of mixed, MySQL selects either statement level or row level. In addition to MySQL's opinion that statement will generate inconsistent data between the master and slave, it will select row level. In other cases, select statement.

3. Common replication Architecture

1. General Master-slave

2. Dual Master

In the conventional master-slave, sometimes the master may fail, so you want to switch the slave to the master to continue providing read/write services. Through Dual Master, MySQL can use each other as its own master and copy as its own slave. At the same time, only one machine acts as the master to provide the write service. [MySQL uses server-ID in log to prevent cyclic replication]

Third, cascade replication Architecture

In some scenarios, the read Pressure on the master node is extremely high (for example, there are 100 slave I/O threads connected ]. You can use MySQL to record the changed BINLOG generated by replication on the slave side, that is, enable the log-slave-update option. Second-level (or more) replication reduces the pressure on the master node due to replication.

Fourth, dual master and cascade replication Architecture

------------------------

Although the Book tells you about data redundancy, it is still unknown how to back up hundreds of large databases. Comrades still need to work hard.