Binlog expiry now in seconds (MySQL 8.0)

Apr 13, 2017

Mins to Read

All

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.

Features

MySQL

No items found.

About the Author

Mydbops

Binlog expiry now in seconds (MySQL 8.0)

About the Author

Topic covered

Continue Reading

MongoDB Shutdown Methods: db.shutdownServer() vs systemctl stop

PostgreSQL 17: Convert Streaming Replica to Logical Replication with pg_createsubscriber

Evolution of Histograms in MySQL 8.4: From Manual to Automated Performance Optimization

Mastering Null Handling in MongoDB

Subscribe Now!