MySQL has more slave database logs than the master database

MySQL has more slave database logs than the master database

When chatting in the Group, a group member said that the master database of the MySQL production database is down, but the data of the master and slave databases is consistent, but the logs of the slave database are more than that of the master database, which is hard to understand!

After consulting, we found that the master database of the production database is not set sync_binlog = 1, but sync_binlog = 0.

Therefore, when the master database writes data, the transaction has been committed, but the data is not written to the disk in the binlog cache, and the data has been synchronized to the slave database. At this moment, the master database is down, the data in the binlog cache is lost, resulting in more logs from the slave database than the master database.

After you reconfigure the master and slave nodes, set sync_binlog to 1.

The importance and significance of sync_binlog:

Sync_binlog = n. After each n transaction commits, MySQL runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. At this time, the performance is the best, but the risk is also the biggest. Because once the system Crash, all binlog information in binlog_cache will be lost.

This article permanently updates the link address: