Binlog expiry now in seconds (MySQL 8.0)

In this blog we are going to view about a  new feature in latest release MySQL 8.0.1 for MySQL Replication . It is binlog_expire_logs_seconds.

Previously we can set the expire logs for binlogs in number of days. By using the new feature (binlog_expire_logs_seconds) we can see set the expire time into number of seconds.

The both expire_logs_days and binlog_expire_logs_seconds works when the binlog rotate happens in the server.

The ultimate usage of this feature will be used to set expire periods smaller than a day by providing another extra variable.

The new variable binlog_expire_logs_seconds, can be used in those cases where the expire period is not an integral multiple of days like 4 day 1 hours and 14 minutes.

Here is the example which we have worked on MySQL 8.0.1 development realease.

We have set 300 seconds as the value for binlog_expire_logs_seconds variable.

[8.0.1-dmr-log] Thu Apr 13 15:16:10 2017 (none)> select now();show global 
variables like 'binlog_expire_logs_seconds';
+---------------------+
| now()               |
+---------------------+
| 2017-04-13 15:16:11 |
+---------------------+
1 row in set (0.00 sec)
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| binlog_expire_logs_seconds | 300   |
+----------------------------+-------+
1 row in set (0.00 sec)

Number of binary logs present in the server.

[8.0.1-dmr-log] Thu Apr 13 15:23:31 2017 (none)> select now();
show binary logs;
+---------------------+
| now()               |
+---------------------+
| 2017-04-13 15:23:32 |
+---------------------+
1 row in set (0.00 sec)
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| labs1-bin.000020 |   5467528 |
| labs1-bin.000021 |   5468425 |
| labs1-bin.000022 |   5467158 |
| labs1-bin.000023 |   5468349 |
+------------------+-----------+
4 rows in set (0.01 sec)

We can note that the variable has updated with 300 seconds ( 5 minutes). But the logs doesn’t get expire.

As we mentioned earlier the log get expire when the log rotate of current binary log (labs1-bin.000023) happens. I have manually rotated the binary logs using flush logs command so that new setting will take effect.

[8.0.1-dmr-log] Thu Apr 13 15:23:38 2017 (none)> flush logs;
Query OK, 0 rows affected (0.50 sec)

Now we can see the difference that the logs are expired based on the binlog_expire_logs_seconds values ( 5 minutes / 300 seconds ).

[8.0.1-dmr-log] Thu Apr 13 15:23:43 2017 (none)> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| labs1-bin.000023 |   5468439 |
| labs1-bin.000024 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

Now we can set the binlog expiry in seconds too.