MySQL master-slave replication process

Source: Internet
Author: User

First, the principle of master-slave replication The process of implementing the entire replication between master and slave is done by 3 threads, of which two threads (SQL thread and IO thread) are on the slave side, and another thread (IO) on the master side    To implement MySQL replication you must first open the master side of the Binary log (that is, the binary log) otherwise it will not be implemented. The basic process of  MYSQL replication is as follows:    (1) slave above IO   Line subroutines the master and requests the location of the specified log file (or the log contents after the start of the log)     (2) master receives an IO thread request from Slave, After specifying the location of the log based on this request information, the IO thread responsible for the replication returns this information to the IO thread of the slave (the returned confidence includes the binary file name of the master side and the location of the   binary file, in addition to the information contained in the log)         (3) after the slave IO thread receives the return information from the master side, the log contents are written to the Relay log file at the slave end, ( MYSQL-RELAY-BIN.XXX), and the Bin-log file and location records,    to the master end are recorded in the Master-info file, So that the next time I can clearly tell master where I need to start from somewhere in the Bin-log, please send it to me.        (4), slave SQL thread detection Relay log Center add content, immediately parse the contents of the master binary file, and execute the query statement inside .      Advantages: If there is a problem with the primary server, you can quickly switch to the services provided from the server          You can perform query operations from the server, reducing the access pressure on the primary server          you can perform backups from the server to avoid services that affect the primary server during backup.     Cons: The foundation is currentlyThe industry's way of sending backup data, when the first server crashes, there will still be a small amount of new data not coming and sent to the second server, this part of the data will be lost. Read and write separation advantages: Increase the pressure on the second server, that is, increase the instability. The separation of Read and write is a kind of compromise in the scheme of dual-machine hot-standby under the premise of a lack of funds and the need to ensure the data security.   Second, the master-slave Environment master ip:10.0.0.201slave  ip:10.0.0.202 (1) Operations on master first open the Log-bin log and then set the Server-id to 1 to shut down the log-slave-updates to set up an internal replication communication user >grant replication  slave on *.* to  ' admin ' @ ' 10.0.0.202 '  identified by  ' 123456 ';flush  privileges;show master status\g; (2) on slave, first open the Log-bin log, and then set the Server-id to 2 to turn log-slave-updates on/etc/ Init.d/mysqld restart>stop slave;>change master to  master_host= ' 10.0.0.201 ',  master_user= ' admin ',  master_password= ' 123456 ', master_log_file= ' mysql-bin.000005 ',  master_log_pos=578;>start slave;>show slave status\g; to test


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: info-contact@alibabacloud.com 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.