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.
