Go into the details of the mysql master slave replication delay problem

During the interview with mysqldba, I met a question:

Describe the implementation principle of msyql replication mechanism, how to restore slave database node with non-1 data without losing mysql main library?

A copy of MySQL (replication) is an asynchronous copy from one MySQL instace (called Master) to another MySQL instance (called Slave). The entire replication operation is implemented by three processes, two of which are on Slave (Sql process and IO process) and one on Master (IO process).

mysql replication is a single thread asynchronous replication process based on binlog.
The basic process of MySQL Replication replication is as follows:
1. The IO process above Slave connects to Master and requests the log content after the specified location of the specified log file (or the log from the very beginning);

mysql> CHANGE MASTER TO
            ->     MASTER_HOST='master_host_name',
            ->     MASTER_USER='replication_user_name',
            ->     MASTER_PASSWORD='replication_password',
            ->     MASTER_LOG_FILE='recorded_log_file_name',
            ->     MASTER_LOG_POS=recorded_log_position;

2. After receiving the request from the IO process of Slave, Master reads the log information after the specified location of the log according to the request information through the IO process responsible for replication, and returns it to the Slave process of IO. In addition to the information contained in the log, the return information also includes the name of the bin-log file and the location of bin-log at the Master terminal.

3, Slave IO process after receive the information, will receive the log content, in turn, add to the Slave relay - log file the end, and will read Master side bin - log file name and location of the record to master - info file, so that the next time I read 1 to clear high-speed Master "I need to start from a bin - log which position in the log content, please send me";

4. When the Sql process of Slave detects the new content added to relay-log, it will immediately parse the content of relay-log to become the executable content in the actual execution of Master and execute it on its own

Operation process:
(1) log in the master server and check the status of the master server
mysql > show master status;
Find the data offset value for master at this stage.

(2) log in the slave server and perform synchronous operation.
mysql > stop slave;
mysql > change master to is directly positioned to this worthy location; This is equivalent to indicating the corresponding location for slave.
mysql > start slave;

(3) check the status from the server
mysql > show slave status