1

We are running mysql v5.6.17 and I cannot find if there is something else that is causing the binary logs to be purged other than the following:

  • Adding expire_logs_days to the my.ini (and confirming it is set on the server via show global variables like 'expire%';)
  • Manually running PURGE BINARY LOGS

There is a nightly backup that backs up the main database, compresses it using 7zip and then FTPs it to another server. I found that the binary logs are purged every night to the binary log which was always created at 00:00 midnight. This is an issue as there is a replication machine running which uses GTIDs and it cannot find the binary logs on the master which are specified by the gtid_purged.

I created 2 events to troubleshoot this. The first event to turn on general logging into a table during this period where it was being purged, and a second to turn general logging off a few hours later. The backup starts at 00:05 and the purge is issued at around 00:07

It seems the PURGE MASTER LOGS TO command was being run by root[root] @ localhost [::1].

There is no one connecting to the server that would run this. As far as I understand the --delete-master-logs in mysqldump command is similar to RESET MASTER, but is not the same as PURGE BINARY LOGS. We do not use --delete-master-logs however.

What could be causing the purge?

0

I think you problem is the version of MySQL you are using.

You have MySQL 5.6.17 but the Change Log for MySQL 5.6.19 has this:

Replication: When the binary log was rotated due to receipt of a SIGHUP signal, the new binary log did not contain the Previous_gtid_event required for subsequent processing of that binary log's GTID events. Now when SIGHUP is received, steps are taken to insure that the server writes the necessary Previous_gtid_event to the new log before writing any GTID events to the new log. (Bug #17026898)

This could just be speculation, but I suspect that if the I/O thread disconnects when it smells a binary log rotation in its master and expire_logs_days = 1, this bug might be happening because the oldest binary log disappears and a slave's I/O thread get thrown under the bus looking for a GTID value it can no longer see.

You may need to do one of following:

  • Upgrade to the latest MySQL 5.6 (5.6.38) or at least 5.6.23
  • Change some options

As for the disappearing of logs, any time FLUSH LOGS is manually or internally executed, it will delete binary logs older than expire_logs_days*86400 seconds.

I remember years ago that expire_logs_days used to trigger every midnight (I am going way back to 5.0.x). Now, expire_logs_days has seconds granularity.

  • Thanks for the information. I have tried everything you have mentioned and it still seems to be purging the logs at midnight. The last thing to try is the upgrade. I will get back to you on this. – HappySloth Jul 13 '17 at 8:30
  • it seems by changing the backup time to 1AM instead of 12AM has stopped the server from internally purging the bin logs. Thanks for the help! – HappySloth Jul 19 '17 at 7:17
0

(Not an answer, but too big for a comment.)

Flipping over to a new binlog happens two ways:

  • passing max_binlog_size, or
  • FLUSH LOGS

But neither should cause the deletion of the previous binlog without PURGE or expires.

Sounds like FLUSH is happening, probably from the 'nightly backup'.

Please provide the details of your "nightly backup"; the answer is likely to be buried in there somewhere.

  • Yes FLUSH LOGS is not a problem, but more the purging of the logs. Currently we use this dump - mysqldump --user=user --password=password --flush-logs --skip-lock-tables --single-transaction --databases db1 --triggers --routines | 7z a -aoa -si c:\mysqlbackups\dump.sql.7z . I intentionally want it to flush, but this should not purge the bin logs. We also have max_binlog_size set in the my.ini. Is there anything that would cause the MySQL server to purge the bin logs internally daily, even when there is a expire_logs_days set? – HappySloth Jul 12 '17 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.