JIYIK CN >

This article mainly discusses the implementation mechanism of master-slave replication, which may not be directly helpful for our actual application, but understanding its principle can achieve twice the result with half the effort for future maintenance and optimization. Due to my limited level, please criticize and correct any inappropriateness in the text.

A master-slave replication in MySQL requires three threads to be implemented, one of which (Binlog dump thread) is on the master server, and the other two threads (Slave I/O thread, Slave SQL thread) are on the slave server (if a master server is equipped with two slave servers, there will be two Binlog dump threads on the master server, and each slave server will have two threads). Let's introduce them separately.

Main server thread Binlog dump thread

The Binlog dump thread is created by the master server when a slave server is connected. Its general working process goes through the following stages:
 

First, the bin-log log file is locked, then the update operation is read, the lock is released after the reading is completed, and finally the read record is sent to the slave server.

We can use the following command to view the information of the thread

mysql> SHOW PROCESSLIST\G

Taking my system as an example, because there is one master server and two slave servers in my system, the information of two Binlog dump threads will be listed.

*************************** 1. row ***************************
     Id: 2
   User: repuser
   Host: 192.168.144.131:41544
     db: NULL
Command: Binlog Dump
   Time: 54
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: repuser
   Host: 192.168.144.132:40888
     db: NULL
Command: Binlog Dump
   Time: 31
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

The state field in the above fields will have the following states

1. Sending binlog event to slave
means that the Binlog dump thread has finished reading the updated event in the binlog log and is now sending it to the slave server.

2. Finished reading one binlog; switching to next binlog
means that the Binlog dump thread has finished reading a binlog log and is now opening the next binlog log to read and send to the slave server.

3. Master has
sent all binlog to slave; waiting for binlog to be updated This is the state value we saw above, indicating that the Binlog dump thread has finished reading all binlog log files and sent them to the slave server. It is now idle and is waiting to read the binlog log file with new operations.

4. Waiting to finalize termination
This state lasts very short and we can hardly see it. This state is displayed when the thread stops.

The above states are the states experienced by the Binlog dump thread during a master-slave replication process. If we are in a test environment, we will hardly see the above states 1, 2, and 4 because they are executed very quickly.

In the master-slave system, a major file on the master server is the bin-log log, and the file operated by this thread is also this log file, so this is why we need to open the bin-log log in the configuration file my.cnf and use this file to record our update operations.

[mysqld]
log-bin = mysql-bin
server-id = 1

There is one more thing to note, which has been mentioned above, but I think it is necessary to repeat it here, that is, there are as many Binlog dump threads as there are slave servers connected to the master server.

A brief introduction to Binary Log

Because the file operated by the Binlog dump thread is the bin-log log file, and the master-slave replication on the master server mainly relies on the bin-log log file, so we briefly introduce the bin-log log file.

There are two formats of bin-log log files, one is Statement-Based and the other is Row-Based.

Analysis of Statement-Based Advantages and Disadvantages

advantage

1. The bin-log contains events that describe database operations, but these events only include operations that change the database, such as insert, update, create, delete, etc. On the contrary, similar operations such as select and desc are not recorded, and it records statements, so it takes up less storage space than Row-Based.

2. Because the bin-log file records all statements that change the database, this file can be used as a basis for future database audits

shortcoming

1. Unsafe. Not all statements that change data will be recorded and replicated. Any non-deterministic behavior is difficult to record and replicate.

For example, for a delete or update statement, if limit is used but there is no order by , this is a non-deterministic statement and will not be recorded.

2. For update statements without index conditions , more data must be locked, which reduces database performance.

3. The insert...select statement also needs to lock a large amount of data, which will affect the performance of the database.

For more detailed information, please refer to the official document - Advantages and Disadvantages of Statement-Based

Analysis of advantages and disadvantages of Row-Based

advantage

1. All changes will be copied, this is the safest way to copy

2. Lock fewer rows for update, insert, select, etc.

3. This method is the same as most database systems, so people who are familiar with other systems can easily switch to MySQL

shortcoming

1. Inconvenient to use. We cannot use the bin-log log file to check what statements are executed, nor do we know what statements are received from the server. We can only see what data has changed.

2. Because it records data, the storage space occupied by bin-log log files is larger than that of statement-based log files.

3. Operations with large amounts of data take longer

For more detailed information, please refer to the official document - Advantages and Disadvantages of Row-Based

The default format of the bin-log log file is Statement-Based. If you want to change its format, use the --binlog-format option when starting the service. The specific command is as follows

#mysqld_safe –user=msyql –binlog-format=format&

bin-log log file management

For bin-log log files, the default name is mysql-bin.xxxxxx. There is also an index file mysql-bin.index, which records all current bin-log log files.

For the new master server, there is only one bin-log file mysql-bin.000001. At this time, all operations are recorded by this file. If we want to change the bin-log file, we can use the following command

Mysql>flush logs;

At this time, a mysql-bin.000002 file will be created to record future operations. In addition to using the above command, a new bin-log log file will be generated when the bin-log log file reaches its maximum value.

The maximum file size and file name including the name of the index file can be changed using the --max_binlog_size, --log-bin, and --log-bin-index options. The specific commands are as follows

#mysqld_safe –user=msyql –max_binlog_size=file length –log-bin=new log file name –log-bin-index=new index file name &

For the master server, in a nutshell, the master server creates a Binlog dump thread for each slave server to read the updated operations in the bin-log log and send them to the slave server. After sending, it continues to wait for whether the bin-log log is updated.