Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the principle analysis of master-slave replication in MySQL. Many people may not understand it very well. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1.1.1 introduction to MySQL master-slave replication

MySQL database supports replication of different business scenarios such as one-way, two-way, chain cascade, ring, and so on. In the process of replication, one server acts as a master server (Master), receiving content updates from users, while one or more other servers act as slave servers (Slave), receiving log contents from binlog files of autonomous servers, parsing SQL and updating them to slave servers, so that the data of master and slave servers are consistent.

If chain cascading replication is set, the slave server itself acts as the master server of the slave server as well as the slave server below it in addition to acting as the slave server. Chain-level replication is similar to that of A → B → C.

1.1.2 Enterprise application scenario of MySQL master-slave replication

The MySQL master-slave replication cluster feature makes it possible for MySQL databases to support large-scale high concurrent reads and writes, while effectively protecting data backups in physical server downtime scenarios.

Application scenario 1: real-time data backup of slave server as master server

The setting of master-slave server architecture can greatly enhance the robustness of MySQL database architecture. For example, when there is a problem with the master server, we can manually or set to automatically switch to the slave server to continue to provide services, and the data of the slave server is almost the same as the master database at the time of downtime.

This is similar to the inotify+rsync synchronization of NFS storage data to the backed up NFS server, except that MySQL's replication scheme is its own tool.

When using the replication function of MySQL to do backup, the data backup is effective in the case of hardware failure or software failure, but in the case of artificially executing drop, delete and other statements to delete data, the backup function from the library is useless, because the deleted statement will also be executed from the server.

Application scenario 2: real-time read and write separation between master and slave server, and load balancing from server

The master-slave server architecture can separate the read and write of the request of the user (client) through the program (PHP, Java, etc.) or agent software (mysql-proxy, Amoeba), that is, the slave server can only process the user's select query request, reducing the user query response time and the access pressure brought by reading and writing on the master server at the same time. The updated data (such as update, insert, delete statements) is still left to the master server to ensure that the master server and slave server are synchronized in real time.

Application scenario 3: split access to multiple slave servers according to business importance

Several different slave servers can be split according to the company's business. For example, there are slave servers that provide query services for external users, slave servers that internal DBA uses for data backup, and background servers that provide access to internal staff, scripts, log analysis and slave servers for developers to query. This split not only reduces the pressure on the master server, but also makes the database not affect each other, such as external user browsing, internal user business processing and DBA personnel backup. You can use the following figure to illustrate:

2.1A scheme to separate master and slave read and write from MySQL

(1) separate read and write through programs (performance and efficiency *, recommended)

Both PHP and Java programs can easily separate reading and writing from the database by setting multiple connection files, that is, when the statement keyword is select, they connect to the connection files of the reading library, and if they are update, insert, delete, they connect to the connection files of the write library.

The disadvantage of achieving read-write separation through the program is that developers need to transform the program to make it transparent to the lower layer, but this way is easier to develop and implement, and is suitable for Internet business scenarios.

(2) Separation of read and write through open source software

MySQL-proxy, Amoda, Mycat, Altas and other agent software can also achieve read-write separation function. The stability and function of these software are general, so it is not recommended to be used in production. Most companies use application-side development programs to achieve read-write separation.

(3) large portals independently develop DAL layer integrated software.

Baidu, Ali and other large portals have developed awesome people, and will make great efforts to develop DAL layer software with a series of functions, such as read-write separation, load balancing, monitoring and alarm, automatic capacity expansion, automatic contraction, and so on.

3.1 introduction to the principle of MySQL master-slave replication

The master-slave replication of MySQL is an asynchronous replication process (although it generally feels real-time). The data will be replicated from one MySQL database (we call it Master) to another MySQL database (we call it Slave). The whole process of master-slave replication between Master and Slave is completed by three threads. Two of these threads (SQL and IO threads) are on the Slave side, and the other thread (the Icano thread) is on the Master side.

In order to realize the master-slave replication of MySQL, the Binlog recording function on Masterside must be turned on first, otherwise it cannot be realized. Because the whole replication process is actually Slave fetching the BInlog log from the Master side, and then performing the various SQL operations recorded in the acquired binlog log on the Slave in the same order.

To turn on the BInlog recording function of MySQL, you can add the "log-bin" parameter option to the mysqld module in the configuration file my.cnf of MySQL, as shown below:

[mysqld] detailed description of the principle and process of log-bin3.2 MySQL master-slave replication

The following is a brief description of the replication principle process of MySQL Replication.

1) execute the start slave command on the Slave server to turn on the master-slave replication switch, and the master-slave replication starts.

2) at this point, the I / O thread of the Slave server connects to the Master server through a request for authorized replication user rights on the Master, and requests that the contents of the Binlog log be sent from the specified bits of the specified Binlog log file (the log file name and location are specified by the change master command when configuring the master-slave replication service).

3) after the Master server receives the request from the I / O thread from the Slave server, the I / O thread responsible for replication on it reads the Binlog log information after the specified location of the specified Binlog log file in batches according to the information requested by the I / O thread of the Binlog server, and then returns it to the I / O thread on the Slave side. In addition to the contents of the Binlog log, the new Binlog file name recorded on the Master server side and the next specified update location in the new Binlog are returned.

4) when the I / O thread of the Slave server obtains the log content and the log file and location point sent by the Icano thread on the Master server, the Binlog log content will be written to the end of the server's own Relay Log (relay log) file (MySQL-relay-bin.xxxxxx), and the new Binlog file name and location will be recorded in the master-info file. So that the next time you read the new Binlog log on the Master side, you can tell the Master server that you need to request new Binlog log contents from the specified file and location of the new Binlog log.

5) the Slave server-side SQL thread will detect the newly added log contents of the local Relay Log thread in real time, then parse the contents of the Relay Log file into SQL statements in a timely manner, and execute and apply these SQL statements on its own Slave server according to the location order of the parsing SQL statements, and record the file name and location point of the current application relay log in the relay-log.info.

After going through the above process, you can ensure that the same SQL statements are executed on both the Master side and the Slave side. When the replication state is normal, the data on the Master side and the Slave side are exactly the same. Of course, the replication mechanism of MySQL also has some special cases. Please refer to the official instructions for details. In most cases, you don't have to worry about it.

The following is a summary of the principle of MySQL master-slave replication.

Master-slave replication is asynchronous logical SQL statement-level replication

When replicating, the master library has one I / O thread, and the slave library has two threads, Ibank O and SQL threads.

All MySQL nodes that are replicated cannot have the same server-id.

The binlog file records only SQL statements that make changes to the database (changes from the contents of the database), not any query (select,slow) statements.

5.6 SQL multithreaded write

GTID (no need to find the location)

3.3 Master-slave replication practice

1. Prepare

Main library log_bin

Master-slave server-id is different.

2. Prepare the main library

Mysql-S / data/3306/mysql.sock grant replication slave on *. * to 'rep'@'172.16.1.%' identified by' oldboy123'; flush privileges

3. get the data from the master database to the slave database.

Let the slave be consistent with the master before copying.

Mysql-S / data/3306/mysql.sock mysql > flush table with read lock; cannot close window mysql > show master status; +-+-+ | File | Position | +-+-+ | oldboy-bin.000002 | 405 |

Single window

Mysqldump-A-B-- master-data=2-S / data/3306/mysql.sock | gzip > / opt/3306.sql.gz

Original window

Mysql > unlock table

Restore from the library:

Gzip-d / opt/3306.sql.gz mysql- uroot-poldboy123-S / data/3307/mysql.sock CHANGE MASTER TO MASTER_HOST='172.16.1.51', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=405; # View MASTER_LOG_FILE use sed-n 22p / opt/3306.sql# to view the master library # slave library

6. Turn on the synchronization switch and check whether it is synchronized.

Mysql > start slave; mysql > show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0

Description of configuration information from library

Mysql > CHANGE MASTER TO MASTER_HOST='172.16.1.51', # here is the ip MASTER_PORT=3306 of the main library, and # here is the port of the main library The port of the slave library can be different from that of the master library MASTER_USER='rep', # here is the user rpo MASTER_PASSWORD='oldboy123', # # established on the master library for replication, here is the password MASTER_LOG_FILE='mysql-bin.000001', # of the rep user, here is the name of the binary log file found in show master status, please note that you cannot have multiple spaces MASTER_LOG_POS=405 # # here is the offset of binary log files found in show master status. Note that you cannot read the above contents with more spaces. Do you have any further understanding of the principle analysis of master-slave replication in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.