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

What is the principle of master-slave replication in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

0. Master-slave replication

First of all, what is master-slave replication? To put it simply, ask one MySQL server to copy the data of another MySQL, so that the data of the two servers are consistent.

This way is not too different from the idea of master-slave replication of Redis. If you are interested in Redis master-slave replication, you can take a look at "Redis master-slave replication". So now that both Redis and MySQL use replication, what is the meaning of master-slave replication?

Through the replication function, the construction of one or more slave libraries can improve the high availability and scalability of the database, and achieve load balancing at the same time. When the master database fails, you can quickly cut to one of its slave libraries and promote the slave database to the master database, because the data are all the same, so it will not affect the operation of the system; when the MySQL server needs to carry more read requests, it can divert the traffic of the read requests to each slave database, and the write requests can be forwarded to the master database to form a read-write separation architecture to provide better read expansion and request load balancing.

In fact, the read-write separation architecture is widely used, such as MySQL, Redis, and the Follower of Zookeeper,Zookeeper that we are familiar with will not handle the read request by itself, but will forward the read request to Leader. If you are interested, you can learn about it by yourself, so you won't miss the point here.

1. Principle of replication

Master-slave replication of MySQL supports two ways:

Line-based

Statement-based

Statement-based replication is already available in MySQL3.23, while the statement-based approach is implemented in 5.1. Its essence is based on the binlog of the master library. The master library records the binlog, and then the slave library replays the binlog on its own server, thus ensuring the data consistency between the master and the slave.

1.1 binlog

Logs in MySQL are divided into two dimensions, one for the MySQL server and one for the underlying storage engine. The binlog mentioned above is the log that belongs to the MySQL server, and the binlog is also called the binary log, which records all the changes made to MySQL.

The way of replication based on rows and statements has something to do with the way binlog is stored. Binlog has three storage formats, Statement, Row, and Mixed.

Statement is sentence-based and only records the SQL statements that have modified the data, which can effectively reduce the amount of data in binlog and improve the performance of reading and binlog-based playback.

Row records only the rows that have been modified, so Row records generally have more binlog logs than Statement format. The binlog log based on Row is very complete and clear, recording all the data changes, but the disadvantage is that there may be a lot, such as a update statement, all the data may be modified; for example, alter table and so on, if a field is modified, every record will be changed.

How to combine Mixed Statement and Row? For example, statements such as update or alter table are modified in Statement format. Other changes to the data, such as update and delete, are recorded in Row format.

Why are there so many ways? Because Statement only records SQL statements, there is no guarantee that these statements will be replayed correctly from the library in all cases. Because it may be in the wrong order.

When will MySQL record binlog? When the transaction is committed, it is not recorded in the order in which the statements are executed. When the binlog is recorded, the underlying storage engine is notified to commit the transaction, so it is possible that the statement error occurs due to the wrong order of statements.

1.2 View binlog

Here is an example of MySQL 5.6. binlog is turned off by default. We can view the configuration of binlog by calling show variables like'% log_bin%'.

Default configuration

Log_bin indicates whether binlog is enabled, and its default value is OFF.

Log_bin indicates whether binlog is enabled. The default value is OFF.

Log_bin_basename binlog stores the full name of the file, adding an incremental sequence number to the default file name, such as mysql-bin.000001

Log_bin_index binlog index file name, such as mysql-bin.index

Sql_log_bin can disable the binlog of the current session when binlog is enabled

You can view all the binlog files in MySQL by calling show binary logs.

Picture

View binlog

Now that we know which files are available, we can take a look at the contents of the binlog file, which can be viewed in MySQL through the show binlog events command.

Show binglog events looks at the first binlog file, and we can also specify it through the in parameter. Assuming that the file name we want to see is mysql-bin.000001, we can use the command show binlog events in 'mysql-bin.000001' to view the specified binlog file.

View binlog

Next, let's take a look at the binlog content corresponding to our operations in MySQL.

Initialization

As we mentioned above, binlog is made up of one event. Starting with MySQL 5. 0, the first event of binlog is Format_desc, which is in the Event_type column in the figure. You can see that the content is Server ver;5.6.50-log, Binlog ver: 4, indicating that the currently used version of MySQL is 5.6.50 Magi Binlog, and the version is V4.

Create a database

Then I create a DB called student, whose Event_type is Query, and the content of this event is CREATE DATABASE student DEFAULT CHARACTER SET = utf8mb4, a library-building statement.

New table

Then I create a table called student, Event_type is also Query, and the content is use student; CREATE TABLE student (id INT (11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT), a table statement.

Insert data

Then we execute the INSERT statement to insert two rows of data into the table and look at the binlog again.

INSERT INTO `student` (`id`, `name`) VALUES (NULL, 'Zhang San'); INSERT INTO `student` (`id`, `name`) VALUES (NULL,'Li Si')

Image-20210106123550397

You can see that each time INSERT opens a transaction, you may wonder that we simply executed the INSERT statement and did not show the open transaction. Then why is there a transaction?

This is because MySQL uses an autocommit (AUTOCOMMIT) mechanism, and the InnoDB storage engine I use supports transactions, and all user activity occurs in transactions. We can see it through the show variables like'% AUTOCOMMIT%'; command, and if the result is ON, it means it's on.

1.3 Core steps of replication

We assume that the main library has enabled binlog and recorded binlog normally.

First, start the I / O thread from the library and establish a client connection with the main library.

The master library starts the binlog dump thread, reads the binlog event on the master library and sends it to the I / O thread of the slave library, and the Icano thread gets the binlog event and writes it to its own Relay Log.

Then start the SQL thread from the library, replay the data in the Relay, and complete the data update from the library.

To sum up, there will be only one thread on the master library and two threads on the slave library.

Master-slave replication process

1.4 Relay Log

Relay log is not much different from binlog, there is no Relay Log part before MySQL 4.0.There are only two threads in the whole process. But this also brings a problem, that is, the replication process needs to be carried out synchronously, it is easy to be affected, and inefficient. For example, the master library must wait for the slave library to finish reading before sending the next binlog event. This is somewhat similar to a blocked channel and a non-blocking channel.

Blocking channel

The blocking channel is just like you are at the counter. You have to return something to the teller, but there is no place between you and the teller to put anything, so you can only hold the document until the teller takes over; while the non-blocking channel is like there is a place between you to put the documents, you just put it on, and you don't have to wait for the teller to take over.

After the introduction of Relay Log, the original synchronous acquisition events and playback events are decoupled, the two steps can be carried out asynchronously, and Relay Log acts as a buffer. Relay Log has a relay-log.info file that records the progress of the current replication, and the Pos from which the next event is written is updated by the SQL thread.

1.5 Relay Log core parameters

Next let's take a look at the core parameters of Relay Log.

The maximum size of the max_relay_log_size relay log. The default is 0. If it is 0, the default size 1G will be taken, otherwise it will be the set value.

Relay_log defines the name of relay. The default host name is + relay-bin, such as hostname-relay-bin.

The full path of the relay_log_basename relay log, that is, the path + file name, such as / path/to/hostname-relay-bin, with a maximum length of 256

Relay_log_index defines the full path of the index file of the relay log, and its maximum length is 256. Its default value is hostname + relay-bin.index, for example, / path/to/hostname-relay-bin.index

Relay_log_info_file defines the name of the relay-log.info file

The way relay_log_info_repository stores the data replayed by relay log can be set to FILE and TABLE. The FILE representative records the data replayed by the relay log in the relay-info.log, and the TABLE stores it in the slave_relay_log_info table.

Whether relay_log_purge automatically clears unwanted relay logs. Default is ON.

Relay_log_recovery when the slave library is down, if the relay log is damaged and some relay logs are not synchronized, all the relay logs that have not been replayed will be automatically discarded and retrieved from the master database. The default value is OFF.

Relay_log_space_limit sets the maximum value of the relay log to prevent it from filling up the disk. However, it is not recommended to set this value. It is recommended to give the space needed for the relay log. 0 means no limit, and 0 is also the default value.

Sync_relay_log is used to control the variable that the relay log is written to disk. If the value is n, the fdatasync () function will be called after every n binlog events received by the relay log to force the relay log to be brushed into the disk. On the contrary, if the value is 0, the relay log will be written into the buffer of OS, and it is up to OS scheduling to decide when the relay log will be brushed to disk. In this way, if an error is reported before the relay log is brushed in, the relay log will be lost. The default value is 10000, which means that for every 1w binlog events written to the relay log, the relay log is forced to be brushed to disk.

Sync_relay_log_info the impact of this parameter has something to do with the parameter relay_log_info_repository, as well as the use of a storage engine that supports transactions. The default value is also 10000.

Relay_log_info_repository is FILE, and if the value is set to N, then every N transactions will call fdatasync () to force relay-log.info to be brushed to disk.

Relay_log_info_repository is TABLE, and if you use an engine that supports transactions, the table is updated every time the transaction ends; if you do not use the transaction engine, the table is updated when N binlog events are written.

If relay_log_info_repository is FILE,MySQL, fdatasync () will not be called, but the scheduling of flushing into disk will be given to OS.

Relay_log_info_repository is TABLE. If a storage engine that supports transactions is used, the table will be updated every time a transaction occurs. If a transaction engine is not used, the table will never be updated.

When sync_relay_log_info is 0

When sync_relay_log_info is greater than 0

two。 Copy model

In ordinary development, in fact, it is rarely said to directly implement the master-slave architecture. It takes time and money and introduces additional complexity, and it turns out that with so much investment in a single MySQL server, you can completely handle.

This is the same as the architectural iteration of a product, and a single application is sufficient at the beginning. When your business expands, requests expand, and monomers can't resist the pressure, you will consider deploying multiple instances and start using micro-service architecture for scale-out and load balancing.

2.1 one master and multiple followers

Of course, you can also think of it as one master, one slave.

This is the simplest model, especially suitable for a small amount of writing and a lot of reading. The read request is assigned to each slave library, which effectively helps the master library to disperse the pressure and can improve the read concurrency. Of course, you can just think of the slave database as a disaster recovery repository, without any requests and data transfers except master-slave replication.

You can even use one of the databases as the database of your pre-release environment, which, in the final analysis, directly touches the database of the production environment, which is too ideal, because it also involves the data sensitivity of the database of the production environment. Not everyone has access to it, and there needs to be a perfect authority mechanism.

MySQL has one master and multiple slaves

It is worth noting that if there are n slave libraries, then there will be n binlog dump threads on the master library. If this n is relatively large, it may cause the performance jitter of the main library during replication. Therefore, cascading replication can be used in the case of more slave libraries.

2.2 Cascade replication

Cascading replication is a nesting doll in vernacular.

Originally, slave library B, C, D, E, F, G are copied from the main library A, but now because of the pressure of A, it does not do so and is adjusted to the following mode.

B, C copy A

D, E copy B

F, G copy C

MySQL cascade replication

This is called cascading replication, which turns on crazy nesting mode. You may even think that this kind of doll looks familiar. In Redis master-slave replication, you can also use cascading mode, slave to copy another slave.

The advantage of cascading replication is that it greatly reduces the pressure on the master library. The master library only needs to care about the slave library with which it has a direct replication relationship, and the rest of the replication can be handed over to the slave library. On the contrary, because of this layer-by-layer nesting relationship, if an error occurs at the upper level, it will affect all the sublibraries hanging under the server, and the impact of these errors is magnified.

2.3 Master replication

As the name implies, the two main libraries copy each other, and the client can write to any of the main libraries. Any change in the data on any main library server will be synchronized to another server. Somewhat similar to Eureka Server's dual-node model, the two registries register with each other. In this way, the failure of any one of them will not affect the system.

And master master replication can break the bottleneck of database performance, a cool feature-scale-out. Why is it so cool? if DB can scale horizontally, many bottlenecks restricted by database concurrency can be broken through.

But the master replication is actually unreliable, and there is a great possibility that the data on both sides will conflict. For example, when replication stops, the system is still writing data to the two main libraries, that is, one part of the data is in An and the other part is in B, but it is not replicated and the data is out of sync. It will be quite difficult to repair this part of the data.

So I think the meaning of dual hosts is more about HA than load balancing.

2.4 active and passive primary replication

It is also a dual-master structure, but the difference is that one of them is a read-only passive server, and the client does not write to the library.

What is its use? For example, we need to maintain and optimize MySQL without interrupting the service, for example, to modify the table structure. Suppose we have two databases, the master library An and the passive master library B. notice that the passive master library here is read-only. Let's stop copying A to B first, that is, stop the SQL thread on A.

Master stops replication

In this way, the very time-consuming operations we perform on B that may need to lock the table will not be synchronized to An immediately. Because An is providing services at this time, it cannot be affected, but because it is in an asynchronous replication mode, the Relay Log continues to be written by the Imax O thread, but not to be replayed.

Then we perform this maintenance operation on B, and note that the updates that occur on A will be normally synchronized to B. Swap read and write roles after execution. That is to say, A becomes a read-only passive main library, while B becomes an active main library to provide services.

Restart the SQL thread

Then restart the SQL thread, and A starts to replay the event accumulated in the previous Relay Log. Although A may be blocked at this time, A no longer provides services, so there is no problem.

The benefits of active and passive master mode are clear, we can update the structure of the database without stopping the service, and then we can quickly switch to ensure the HA of the database in the case of failure of the main database.

3. Replication mode

We mentioned more than once that replication is asynchronous, so let's take a look at the ways of master-slave replication in MySQL.

3.1 Asynchronous replication

The first is async, which is the default way for MySQL. In the case of asynchronous replication, the master database will not actively send messages to the slave library, but wait for the slave library's Icano thread to establish a connection, and then the master library will create a binlog dump thread and send the binlog event to the I / O thread, as shown in the following figure.

MySQL replication mode

The main library will return directly after executing its own transaction and recording the binlog, and will not confirm any results with the client. The binlog is then read asynchronously by the binlog dump thread and sent to the slave library. Processing requests and master-slave replication are two completely asynchronized processes.

3.2 synchronous replication

In synchronous mode, the master library executes a transaction, so the master library must wait for all the slave libraries to return to commit after all the transactions have been executed before returning success to the client.

Synchronous replication

It is worth noting that the master library commits the transaction directly, rather than waiting for all slaves to return before committing. MySQL simply delays the return to the client and does not delay the commit of the transaction.

Synchronous mode uses toes to know that performance is greatly compromised, it couples client requests with master-slave replication, and if a slave replication thread executes slowly, the response to the client is much slower.

3.3 semi-synchronous replication

The difference between semi-synchronization and synchronization is that synchronization needs to wait for all slave commit, while semi-synchronization only needs a slave commit to return. If there is no commit from the library beyond the default time, it will switch to asynchronous mode and then commit. The client won't wait all the time.

MySQL replication mode

Because even if the master library goes down later, at least one slave node is available, and the waiting time during synchronization is reduced.

4. Data consistency in replication

We discussed the core steps of replication in 1.3. a seemingly simple process is that the binlog dump of the main library reads binlog, then reads and writes Relay Log from the I / O thread of the library, and then reads Relay Log from the SQL thread of the library for playback.

What if the Ithumb O thread suddenly dies in the middle of the copy? Or is the copy halfway down in the main library? What if it is consistent with the data?

As we mentioned above, there is a relay-log.info file that records the Pos of the binlog currently being copied from the library and the Pos of the Relay Log written from the library. As long as the file is still there, when the file is accidentally restarted from the library, the file will be re-read and continue copying from the place where it was copied last time. This is similar to master-slave replication in Redis, where both parties maintain an offset and perform psync incremental data synchronization by comparing offset.

However, before MySQL 5.5, the progress of replication can only be recorded in the relog-log.info file. In other words, the parameter relay_log_info_repository only supports FILE, so you can go back to the 1.5 Relay Log core parameter above. So the relay-log.info file is brushed to disk only after the sync_relay_log_info transaction.

If you hang from the library before brushing to disk, you will find that the actual execution location of the SQL thread is inconsistent with the database record after restart, and the problem of data consistency arises.

So in MySQL 5.6, the parameter relay_log_info_repository supports TABLE, so we can put the progress of replication in the mysql.slave_relay_log_info table of the system, and bind the update progress, SQL thread execution of user transactions into a transaction execution. Even if slave is down, we can use MySQL's built-in crash recovery mechanism to restore the actual execution location to the progress of database preservation.

Secondly, there is the semi-synchronous replication mentioned above, in which the master library commits the transaction first, then waits for the return of the slave library, and then returns the result to the client, but what if the slave library dies while the master library is waiting?

At this point, the transaction has been committed on the main library, but there is no such data on the slave library. So lossless semi-synchronous replication was introduced in MySQL 5.7, increasing the value of the parameter rpl_semi_sync_master_wait_point, which defaults to after_sync in MySQL 5.7 and after_commit in MySQL 5.6.

The after_sync master library does not commit the transaction and waits for a slave library to return the result before committing the transaction. In this way, if the slave library goes down without any return, the master side cannot commit the transaction. The master and the subordinate are still consistent.

After_commit as discussed earlier, the main library commits the transaction first and waits for the result to be returned from the library before notifying the client

After reading the above, have you mastered the principle of master-slave replication in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.