Master-slave replication latency in MySQL

Source: Internet
Author: User

When I interviewed mysqldba, I encountered a question:

Describes the implementation principle of the msyql replication mechanism. How can I restore the database node of the slave with inconsistent data without stopping the mysql master database?

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

By referencing a Sina Daniel, mysql replication is a single-thread asynchronous replication process based on binlog.

The basic process of MySQL Replication is as follows:

1. The IO process on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;

  2. ->MASTER_HOST='Master _ host_name',
  3. ->MASTER_USER='Replication _ user_name',
  4. ->MASTER_PASSWORD='Replication _ password',
  5. ->MASTER_LOG_FILE='Recorded _ log_file_name',
  6. ->MASTER_LOG_POS=Recorded_log_position;

2. After the Master receives a request from the Slave IO process, it reads the log information after the specified location of the log based on the request information and returns it to the Slave IO process. Besides the information contained in the log, the returned information also includes the name of the bin-log file on the Master end and the location of the bin-log;

3. After the Slave IO process receives the information, add the received log content to the end of the relay-log file on the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me ";

4. After the Slave SQL process detects the newly added content in relay-log, it will immediately parse the relay-log content into the executable content during actual execution on the Master end, and execute


(1) log on to the master server and check the status of the master server.

Mysql> show master status;

Find the data offset value of the current master.

(2) log on to the slave server and perform the synchronization operation. [Code]

Mysql> stop slave;

Mysql> change master to directly locate the value location. This is equivalent to specifying the corresponding location for slave.

Mysql> start slave;

(3) view the status on the server

Mysql> show slave status

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.