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.

 

One thought on “Binlog expiry now in seconds (MySQL 8.0)

  1. Hello,

    We are using MYSQL 8.0.11 master-slave replication with GTID.

    Binlog files from the master are not getting deleted as per expiration time but from the slave, it gets deleted automatically.

    We have set binlog_expire_logs_seconds to 259200 and expire_logs_days to 0 in my.cnf file in both master and slave.

    After changing the binlog_expire_logs_seconds value I have not run flush logs command nor restarted the MYSQL service. As MYSQL 8 applies changes at runtime without restarting the service.

    Do we need to run flush logs command every time whenever we change the values of binlog_expire_logs_seconds?

    Thanks in advance.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s