Shulou(Shulou.com)06/01 Report--

For historical reasons, MySQL replicates logical binary logs rather than redo logs. I have been asked many times when MySQL can support physics-based replication, but it depends on what the bosses of MySQL think. Last time I brainstormed with teacher Lai, he suddenly said, "will MySQL have a redo copy based on Paxos?"

The real benefit of physical replication is not correctness, because log replication based on ROW format can also fully guarantee the correctness of replication. Because the physical log is written continuously during the execution of the transaction, the binary log is written only when the transaction is committed. Therefore, the advantages of physical logging are as follows:

Under the replication architecture, the commit speed of large transaction logs is fast; under the replication architecture, the delay of master-slave data is small.

Suppose a large transaction has been executed for an hour, and at the time of the final commit, only the redo log of the last committed part is written (redo log can be regarded as a physical log). Although the total amount of writes to this large transaction redo log may be 1G, at the time of commit, the data master-slave replication only needs to transfer the last part of the log to the remote slave. because the previous redo log has been continuously synchronized to the slave within 1 hour of execution.

For binary logs, because the write time occurs when the transaction commits, assuming that a 1G binary log is generated, the transaction commit time will include the write time of the 1G log. There is a saying in Oracle that the commit speed of a transaction is flat, regardless of its size. This is not true in the MySQL database. That is, the commit speed of MySQL depends on the size of the binary log generated by the transaction, and the transaction commit speed is not flat.

To make matters worse, the delay of MySQL master-slave replication under large transactions. Also assuming that a large transaction has been executed on the master server for an hour, it needs to be transferred to the slave server at the last commit time. The master-slave delay is at least 1 hour, and if it takes another 1 hour to execute from the server, the worst-case scenario of master-slave replication delay may be 2 hours. There is no such limitation in physical replication because, as mentioned earlier, the log is already being transferred and played back during the transaction commit.

Physical replication is good, but it also has its own shortcomings, in my own practical experience:

Under physical replication, the bad block of the host will cause the master and slave servers to fail to start. I believe there are not a few students who have encountered this problem. In addition, it is difficult to do ETL, such as how to synchronize physical logs to the Hadoop big data platform.

In a nutshell, for MySQL databases, large transactions are not allowed to be executed at any time. To execute, split the large transaction into small sub-transactions to execute. This is the most basic mental formula, but it is very different from Oracle. In short, Qizong and Jian Zong are not good or bad. Learn to understand the differences and integrate them in order to achieve a state like the wind.

Mysql uses master-slave synchronization to separate reads and writes, reducing the pressure on the master server is now very common in the industry. Master-slave synchronization can basically achieve real-time synchronization. I borrowed the schematic diagram of master-slave synchronization from other websites.

After the master-slave synchronization is configured, the master server will write the update statement to the binlog from the server's IO thread (note here, there is only one IO thread before 5.6.3, and the speed is naturally accelerated after 5.6.3). Go back to read the binlog of the master server and write it to the Relay log of the slave server. Then the SQL thread from the server will execute the sql in the relay log one by one to recover the data.

Relay means passing, and relay race means relay race.

1. The reason for the delay of master-slave synchronization

We know that a server opens N links to the client to connect, so there will be large concurrent update operations, but there is only one thread reading binlog from the server. When a SQL is executed on the slave server for a longer time or because a SQL has to lock the table, the SQL of the master server has a large backlog and has not been synchronized to the slave server. This leads to master inconsistency, that is, master-slave delay.

two。 The solution of master-slave synchronization delay

In fact, there is no single way to control the master-slave synchronization delay, because all SQL must be executed in the slave server, but if the master server keeps writing updates, then once there is a delay, the more likely the delay will increase. Of course, we can take some mitigation measures.

a. We know that because the master server is responsible for the update operation, it has higher security requirements than the slave server. All settings can be modified, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such a high level of data security. It is possible to set sync_binlog to 0 or turn off binlog,innodb_flushlog. Innodb_flush_log_at_trx_commit can also be set to 0 to improve the efficiency of sql execution, which can greatly improve efficiency. The other thing is to use a better hardware device than the main library as the slave. b. That is, a slave server is used as a backup instead of providing a query, where his load comes down, and the efficiency of executing SQL in relay log is naturally high. c. Increase the slave server, this purpose is to spread the pressure of reading, thereby reducing the load on the server.

3. The method of judging master-slave delay

MySQL provides slave server status commands, which can be viewed through show slave status. For example, you can look at the value of the Seconds_Behind_Master parameter to determine whether there is a master-slave delay.

There are several kinds of value:

NULL-indicates that either io_thread or sql_thread has failed, that is, the thread's Running state is No, not Yes.

0-the value is zero, which is what we are eager to see, indicating that the master-slave replication status is normal.

I haven't tried any other methods, so I won't comment for the time being.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.