Mysql – Percona Server – binlog not automatically purge with thesqladmin flush-logs

binlogMySQLmysql-8.0percona-server

I have a little problem and I need your help please. Here is the story : I have Percona Server in version 8.0.13 with one slave and the old binlogs on master are not deleted automatically when flush-logs appending.
Variables are set as :

# mysql -e"select @@expire_logs_days, @@binlog_expire_logs_seconds"
+--------------------+------------------------------+
| @@expire_logs_days | @@binlog_expire_logs_seconds |
+--------------------+------------------------------+
|                  0 |                        86400 |
+--------------------+------------------------------+

I have logrotate which execute mysqladmin flush-logs every night and binlogs are correctly rotated but old ones are not removed. When I execute this command manually the behaviour is the same.

In MySQL CLI the command PURGE BINARY LOGS BEFORE datetime works fine.

The slave replication is up to date or a little bit late but not more than a couple hour. So it can't be explain why old binlogs are still present on disks (some are 7 days old by example)

I also execute lsof to check if MySQL retain descriptor on old binlogs but no. I have no more idea to explain the problem.

Do you have any ideas to explain and fix this please ?

Thanks,

Best Answer

Please note Paragraphs 2,3 in the MySQL Documentation for binlog_expire_logs_seconds:

The default binary log expiration period is 2592000 seconds, which equals 30 days (30*24*60*60 seconds). The default applies if neither binlog_expire_logs_seconds nor the deprecated system variable expire_logs_days has a value set at startup. If a non-zero value for one of the variables binlog_expire_logs_seconds or expire_logs_days is set at startup, this value is used as the binary log expiration period. If a non-zero value for both of those variables is set at startup, the value for binlog_expire_logs_seconds is used as the binary log expiration period, and the value for expire_logs_days is ignored with a warning message.

To disable automatic purging of the binary log, specify a value of 0 explicitly for binlog_expire_logs_seconds, and do not specify a value for expire_logs_days. For compatibility with earlier releases, automatic purging is also disabled if you specify a value of 0 explicitly for expire_logs_days and do not specify a value for binlog_expire_logs_seconds. In that case, the default for binlog_expire_logs_seconds is not applied.

Please review your my.cnf

If you have this line in the my.cnf

expire_logs_days = 0

change it to

expire_logs_days = 1

Then go execute

mysql> SET GLOBAL expire_logs_days = 1;
mysql> FLUSH BINARY LOGS;

This will cause a new thread to be opened with the new setting.

It's OK to have

expire_logs_days = 1
binlog_expire_logs_seconds = 86400

because the documentation says when both are set, binlog_expire_logs_seconds will take precedence and a simple warning is posted for expire_logs_days.

As a last resort, please restart mysql

service mysql restart

If you still have a problem after doing this, then I would suspect a bug.

Related Solutions

MySQL – Connection blocked because of many connection errors – How to find root cause

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings

MySQL Replication – Error 1236 ‘Found Old Binary Log Without GTIDs’ After Master Restart

Something is not right about your process.

Usually, when I see error 1236, such the following I used in my old post How can you monitor if MySQL binlog files get corrupted?

[ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236).
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
111014 20:25:48 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.001067', position 183468345.

here was the situation: When doing MySQL Replication without GTID, the IO Thread examines the position from the latest Master binlog. If Read_Master_Log_Pos is bigger than the actual filesize of the binlog, you get error 1236.

When doing MySQL Replication with GTID, the situation is somewhat similar. The IO Thread is looking for some kind of closure with regard to the GTID it was last using. When you restarted MySQL on the Master, you closed the last binlog on the Master and opened a new binlog upon startup. The IO Thread on the Slave was still active. Thus, the same error number is coming up.

The next time you restart a Master, remember the Slaves are active.

The slave should have reconnected after a minute, but that is not happening for you.

To play it safe, you should do the following

On the Slave, STOP SLAVE;
On the Master, service mysql restart
On the Slave, START SLAVE;

You should not have to do this. As an alternative, try setting up replication with heartbeat set at one tenth of a second:

CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 100;

This should make the IO Thread on the Slave a little more sensitive

Best Answer

Related Solutions

MySQL – Connection blocked because of many connection errors – How to find root cause

MySQL Replication – Error 1236 ‘Found Old Binary Log Without GTIDs’ After Master Restart

Related Question