How MySQL Master/Slave server configuration works

I. Principle of master-slave Configuration:

MySQL Replication is an asynchronous Replication process, from one Mysql instace (we call it the Master) to another Mysql instance (we call it the Slave ). The entire replication process between the Master and Slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the Slave side, and the other (IO thread) on the Master side.

To implement Replication for MySQL, you must first enable the BinaryLog (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file.

The basic process of MySQL replication is as follows:
1. The IO thread 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. After the Master receives a request from the Slave IO thread, it reads the log information at the specified location of the specified Log Based on the Request Information and returns it to the Slave IO thread. Besides the information contained in the Log, www.bkjia.com also includes the name of the Binary Log file on the Master end and the position of the returned information in the BinaryLog;
3. after the Slave IO thread receives the information, it writes the received log content to the end of the RelayLog file (mysql-relay-lin.xxxxxx) at 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 thread detects that the Relay Log has added a new content, it will immediately parse the content in the Log file to become
Execute the executable Query statements when the end is actually executing, and execute these queries on its own. In this way, it is actually on the Master side and Slave
The data at both ends of the Query is identical.

Ii. Advantages of configuring mysql Master/Slave:
1. Solve the performance bottleneck of the web application system and database, and use the database cluster method to achieve query load. In a system, the database query operation is much more than the update operation, multiple query servers are used to distribute database queries to different query servers, thus improving query efficiency.
2. the Mysql database supports the master-slave replication function of the database. The master database is used for data insertion, deletion, and update operations, while the slave database is used for data query operations, in this way, the update and query operations can be shared to different databases, thus improving the query efficiency.

1 2 Next Page