The process and principle of MySQL's master-slave replication

The first few days to build the MySQL master-slave replication, has been built, but the principle of master-slave replication is not known, so to study.

Part of this article comes from the Internet.

The process of master-slave replication

The first thing to understand is that a one-way master-slave replication implementation is done by three threads, master an IO thread, slave an IO

thread and a SQL thread.

1. Slave The above IO line thread attached the master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2. When master receives a request from an IO thread from slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the slave side. In addition to the information contained in the log, the returned information includes the name of the Bin-log file on the master side of the returned information and its location in the Bin-log;

3. After the slave IO thread receives the information, it writes the received log content to the end of the relay log file (hostname-relay-bin.xxxxxx) on the slave side, The file name and location of the Bin-log on the master side are recorded to the Master-info file so that the next read will tell Master clearly "where I need to start the log content from somewhere in the Bin-log, please send it to me"

4. When the slave SQL thread detects a new addition to the relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the master end, and executes the query itself. In this way, the same query is actually executed on the master and slave ends, so the data on both ends is exactly the same.

Here is the actual operation diagram

1. First look at the state of the main library

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/92/wKioL1VMc9WAJUolAADln3tdavQ440.jpg "title=" Qq20150508162316.png "alt=" Wkiol1vmc9wajuolaadln3tdavq440.jpg "/>

2. Then take a look at the Master.info and nginx-relay-bin.000008 from the library (please disregard the name, this is related to the host name)

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/97/wKiom1VMc7iwixsnAAFNotQJQJ8605.jpg "title=" Qq20150508162907.png "alt=" Wkiom1vmc7iwixsnaafnotqjqj8605.jpg "/>

Master.info a portion of the file content, consistent with the name and location of Master's Bin-log

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/97/wKiom1VMc-yyt_TnAABnbg3KZ5Q941.jpg "title=" Qq20150508162555.png "alt=" Wkiom1vmc-yyt_tnaabnbg3kz5q941.jpg "/>

Then the nginx-relay-bin.000008 file (with abridged)

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/92/wKioL1VMecPSwu1oAAV1-8HsXG4894.jpg "title=" Qq20150508164339.png "alt=" Wkiol1vmecpswu1oaav1-8hsxg4894.jpg "/>

Then the main library builds a library to test

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/97/wKiom1VMeK6TpglhAAEWvIzE2Yc027.jpg "title=" Qq20150508165017.png "alt=" Wkiom1vmek6tpglhaaewvize2yc027.jpg "/>

Take a look at the master.info and nginx-relay-bin.000008 files from the library

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6C/97/wKiom1VMee_iFlBmAACMcP6l_lc383.jpg "style=" float: none; "title=" Qq20150508165219.png "alt=" Wkiom1vmee_iflbmaacmcp6l_lc383.jpg "/>

nginx-relay-bin.000008 file

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/93/wKioL1VMe2OQ_KeRAAIOLzVRi0s218.jpg "style=" float: none; "title=" Qq20150508165326.png "alt=" Wkiol1vme2oq_keraaiolzvri0s218.jpg "/>

Now let's simulate the failure, at this time from the database down, at this time the master.info file is not changed, but the nginx-relay-bin.000008 file has changed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/93/wKioL1VMfLjQ9QJ9AAGaO_LKvww685.jpg "title=" Qq20150508170119.png "alt=" Wkiol1vmfljq9qj9aagao_lkvww685.jpg "/>

Create a new library to test the main database,

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/97/wKiom1VMfCvjx0QXAAFPL9ZQ99k350.jpg "title=" Qq20150508170513.png "alt=" Wkiom1vmfcvjx0qxaafpl9zq99k350.jpg "/>

Now start from the database, at this time the Relay-bin log will be re-generated two, look at the large number of the

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/93/wKioL1VMfvmSbrTQAAFlGqdgxE0597.jpg "title=" Qq20150508170948.png "alt=" Wkiol1vmfvmsbrtqaaflgqdgxe0597.jpg "/>

View nginx.relay-bin.000010

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6C/98/wKiom1VMf9bTVz4qAAZ53PEyrFA804.jpg "title=" Qq20150508171325.png "alt=" Wkiom1vmf9btvz4qaaz53peyrfa804.jpg "/>

At this point the Master.info file is also updated to the latest POS

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/93/wKioL1VMgeOjolceAABurZycrxY285.jpg "title=" Qq20150508172321.png "alt=" Wkiol1vmgeojolceaaburzycrxy285.jpg "/>

Failure simulation completed.

Write here, suddenly feel the writing is too cumbersome, a bit better, to ensure that we can see clearly, will not foggy feeling.

--------------------------------------Split Line-----------------------------------------

Tell me some little details about your experiment.

1. Build one-way master-slave, in the main library to create a library sunys01 to do the test, and then delete him from the library, at this time the master-slave data is inconsistent, but the master and slave is normal, the main library to build a library, from the library can be synchronized to the data. I have been to master from synchronization as long as the operation from the library will cause the master and slave to break down. Now take the above situation, delete the sunys01 from the library, in the main library also delete sunys01, at this time the master-slave break, the main library write data from the library can not synchronize data, now I re-created a sunys01 library from the library, restart MySQL, master and slave recovery, the main library of subsequent data also successfully synchronized.

2. Do a two-way master in the Master 1 to create a new library, while his master status has been changed, the Master 1 created the library in the main 2 also have, but the master status of the main 2 has also been changed. My classmate is also a double master, but Master 2 of the master status will not change, do not know what the reason

Write here finally ended, what questions can be asked in the comments, we progress together

The process and principle of MySQL's master-slave replication