MySQL master-slave replication principle

MySQL Master-slave replication

MySQL Replication

MySQL replication supports one-way, asynchronous replication. Writes updates to the binary log file through a primary server and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates. MySQL master-slave replication is performed asynchronously. Synchronization requires version 5.5, which is implemented using plugins provided by Google.

MySQL master-slave replication operation is flexible to achieve the level of replication throughout the service, or to a library, or even a specified object in a database to replicate.

MySQL master-slave replication application Scenario

Improve performance. Through one (multi) master multi-Slave deployment scenario, the operations involving data writes are placed on the master side, and the data read operations are scattered among many slave. It reduces the load of master, improves the response efficiency of data writing, reads from server, shares requests, and improves reading efficiency.

Data security. The data is copied to the slave node, and the slave node holds the full data even if Master is down.

Data analysis. Analyze the data and put it on the slave.

Principle of Master-slave replication

MySQL replication is an asynchronous replication process that replicates from one MySQL instance (Master) to another. The entire process of copying between master and slave is done primarily by 3 threads, of which two threads (SQL thread and IO thread) are on the slave side, and another thread (IO thread) on the master side.

To achieve master-slave replication, first turn on the binary log function on the master side. Because the entire replication process is actually the process of slave the binary logs from Master, and then performing the various operations recorded in the log one at a time in the order in which they were produced.

The exact process of copying is as follows:

MySQL master-slave replication schematic diagram

The slave IO line is thread attached on master, and the log file is requested to specify the location (or log from the beginning of the log) after the contents of the journal.
After master receives an IO thread request from slave, the copy IO thread is responsible for reading the log information after the specified log according to the requested information and returning the IO thread to the slave side. In addition to the information contained in the log, the return information includes the name and location of the binary log file containing the information returned on the master side.
After the slave IO thread receives the information, it writes the log content once to the end of the relay log file (mysql-relay-bin.xxxx) on the slave side, The files and locations of the bin-log that are read to the master end are recorded in the Master-info file so that the next read will clearly tell Master where the next log content will start from Bin-log.
Slave's SQL thread detection detects updates in relay log, resolves the contents of the log file immediately, reverts to the executable SQL statement at the real execution of the master side, and executes these sqk statements. In fact, master and slave execute the same statement.
Binary Log Recording mode

Row level
Binary log is recorded as a modified form for each row of data, and then the same data is modified on the slave side.

Advantage: In row level mode, binnary log can not record the context-sensitive information of the query statement executed, as long as it records which row has been modified and what it looks like. The row level will detail the details of the changes in each row of data, with no stored procedures, or function, in a particular case, and trigger calls and triggers failing to replicate correctly.

Cons: Generate a lot of log content.

Statment level
Each SQL statement that is modified will be recorded in the binnary of master. When the slave is copied, the SQL thread parses the Sing Woo original master and executes the same SQL statement.

Advantages: First, solve the disadvantage of row level, do not need to record each row of data changes, reduce the binnary log volume, saving IO cost, improve performance.

Disadvantage: Because it is the execution statement of the record, in order for these statements to be executed correctly on the slave side. Then it must also record some information about the execution of each statement, that is, contextual information, to ensure that all statements are executed at the slave end and are executed at the same time as the master side of the results. In addition, due to the rapid development of MySQL, a lot of new features continue to join, making MySQL replication encountered a great challenge, copying the content of the design of the father-in-law, the more prone to bugs. At statement level, it has been found that a lot of cases will cause the MySQL replication problem. The main thing is that after modifying the data using certain functions, such as: the Sleep () function is not copied correctly in some versions, the last_insert_id () function is used in the stored procedure, the ID of the slave and master may be inconsistent, and so on.

Mixed level
In mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row. In addition to MySQL's belief that the statement approach could result in inconsistent data between Master and slave during replication. (such as the use of special procedure and funtion, the use of the UUID () function, etc.), it chooses the mode of row to record the change, and uses the statement method.

MySQL master-slave replication principle