MySQL master-slave replication principle and practice

MySQL master-slave replication principle:

The 1.Mysql supports single-bidirectional, chained cascade, asynchronous replication. Can one master one from, can also a master more from.

2. The chain cascade is subordinate from the master. Similar to A-->b-->c-->d.

3. After the master-slave replication is well, the database update must be done on the master server to avoid a master-slave conflict.

4. The master-Slave server architecture enables read-write separation of user requests. Updates from the processing user's Select, master processing Update,insert,delete, and so on. In order to maintain the master-slave real-time synchronization.

5. You can split the slave server according to the business.

6.mysql Master-slave replication is asynchronous replication. Between master and slave, three threads are involved, of which two threads (IO and SQL) are in slave, and the other thread (IO) is on the master side.

7. The Binlog (mysql-bin.xxxxxx) feature of master must first be turned on, because the entire replication process is essentially slave getting binlog logs from the master, and then performing the various operations recorded in the slave log in the same order on the Binlog itself.

The following is a brief description of the MySQL master-slave copy process (Binlog does not contain select content ):

Execute start slave on the 1.slave server to turn on the master-slave copy switch.

2. At this point. The IO thread of the slave server connects to the master server by requesting a copy user that is authorized on master, and requests the log contents of Binlog after the specified location from the specified Binlog log file.

After the 3.master server receives a request from an IO thread from the slave server, the IO thread responsible for replication on the master server reads Binlog log information from the specified location of the Binlog log file, based on the information requested by the IO thread of the slave server. The IO thread is then returned to the slave side. Return information in addition to the log content, there is a new Binlog file name on the master server side and a specified location in Binlog after the log content returned.

4. When the IO thread of slave receives the log contents and log files and location points sent from the IO thread of master, Write the Binlog log contents sequentially to the end of the Relaylog (trunk log) file (mysql-relay-bin.xxxxxx) of the slave itself and record the new Binlog file name and location to the Master-info files so that the next time you read the new bin of Master Log logs can tell master where to start requesting new Binlog log content from the file from the newly binlog log.

The SQL thread of the 5.slave server detects the newly added log content in the local relay log in real time, then parses the content into the SQL statement that master once executed and executes the SQL statement in the order of the statements on its own slave.

6. This ensures that the same SQL statements are executed in master and slave.

MySQL master-slave replication principle and practice