MySQL Binary Logs in Backup-Restore

Question

I have a problem with MySQL binary log files. I want to tell you something about my database system:

• I have a master-slave replication with two database instances.

• Our master database creates 1.7GB of binary logs every 8 minutes. My problem is actually about these files. I'm trying to figure out how long I should keep these files. My disk partition is 80GB for these files.

• According to the disk size I mentioned above, the value "expire_logs_days=1" that I used in MySQL became meaningless. Because I cannot keep these files even for 8 hours when there is a problem.

• Therefore, I wrote a cronjob that runs every hour and deletes the synchronized binary log files from the master server with the "PURGE BINARY LOGS" command. If the master server cannot reach the slave server, I give it 3 hours, if the problem still persists after 3 hours, I delete the binary log files from the master server.

• I have a daily database backup cronjob.

• My problem is about the backup and restore procedure of the slave server. When I clean the binary log with the cronjob running every hour on the master server, the backup taken by the slave server during the day becomes unusable. Let's also consider another scenario. For example, if there is a problem in the slave database server, take a backup from the master server and restore this backup on the slave server. Let the restore take about 5 hours. While restoring the slave server, new binary log files are created on the master server. Also, while the slave server is being restored, the master will see that the slave server is not working properly after 3 hours and will clear the binary log files. After the restore process is completed on the slave server, the replication will not work properly and error 1236 will be received. Because the binary log file specified in the backup taken by the slave will no longer exist on the master server.

What should we do to get out of this cycle?

Answer 1

I would try:

• binlog_row_image=minimal to decrease the size of ROW based binary logs if your logs are large because the row image contains all columns, but you are changing a subset of columns. Or if a lot of the binlog size is due to deletions.

• binlog_format=MIXED, which means events are recorded in STATEMENT format by default. ROW based binary logs tend to be more bulky, though their benefit is better data integrity.

• Binary Log Compression. I haven't tried this, and it assume it would give a variable amount of log size reduction depending on your data. Worth a try.

• binlog_expire_logs_seconds=N to gain more control over the rate of expiring log files.

• Increase the size of your disk partition for binary logs, as noted in the comment above. There's really no excuse for running out of storage space. Storage is cheap. If you are operating an application that requires 500GB of space for binary logs, then just do it.

• Reduce the rate of binary log writes your application makes. For example, could you queue up changes, merge them together in memory, and periodically commit the unified changes to the database? I have no idea if this would be possible, since you haven't described your application. But many applications treat their database as if it were only an extension of application memory, failing to account for the infrastructure requirements.

• Consider if the rapidly-changing data really needs to be included in the binary log. Use replication filters to exclude schemas that don't need it, or else provision a separate database instance without binary logging configured, and store that type of data on the second database instance. Of course the data won't be replicated if it isn't in the binary log, so it would have to be data you don't need on the replica, and don't need to recover from PITR.

• Make restores take less time. I am accustomed to using Percona XtraBackup, which is a physical backup tool instead of mysqldump, a logical backup tool. Dumps take a long time to restore. Physical backups can restore as quickly as you can recover the files from the backup media.

Answer 2

Check that the "server_id" is configured differently between the two servers!

It sounds like each is replicating everything back to the other. Fortunately, the actions are dropped when they get back to the original sender. But, meanwhile, the binlog and relaylog are filled up as fast as the network allows.