MariaDB - Binary log retention period, part two

Colleagues hello to all.

In today's article, we'll talk about how to delete binary logs in MariaDB. As we know,  binary logs are created all the time and clog up disk space, and therefore they need to be deleted sometimes, or set up for automatic cleanup. Today we will analyze all options for clearing binary logs.

Article content:

1. View the entire list of binary logs in the console.
2. Viewing the entire list via the mariadb-binary-log.index.
3. View which binary log is currently active for writing.
4. Delete all RESET MASTER logs.
5. Purge logs with PURGE BINARY LOGS.
6. The binlog_expire_logs_seconds and expire_logs_days variables

1. Viewing the entire list of binary logs in the console.

To view the entire list, we have two options: SHOW BINARY LOGS and SHOW MASTER LOGS.

mariadb> SHOW BINARY LOGS;

mariadb> SHOW MASTER LOGS;

Both of these options are exactly the same, they will show you the entire list of binary logs currently on the server.

2. Viewing the entire list via mariadb-binary-log.index

There is another way to view the list, and that is through the  mariadb-binary-log.index file. I use it very rarely, I use it only when I want to be 100% sure that the console result matches the result in this file.

First, let's find this file, you can use the command in the console to search:

mariadb> SHOW VARIABLES LIKE 'log_bin_index';

As you can see, there are 5 entries in the mariadb-binary-log.index file, which means the database console told us the truth.

3. View which binary log is currently active for writing.

As we remember from the last article, each binary log has a default size of 1 gigabyte. And to find out which log the database is currently using to write to it, there is a command:

mariadb> SHOW MASTER STATUS;

As you can see, the database is now using the binary log mariadb-binary-log.000005 for writing. Position means at what position the file is currently being written, this parameter is very important when you have replication built.

4. Deleting all RESET MASTER logs.

The easiest way to remove all binary logs is to use the command:

mariadb> RESET MASTER;

After executing this command, absolutely all binary logs are deleted from the server, even the log that the database is currently using. I recommend that you use this command very carefully.

!!!IMPORTANT!!! Never use this command if you have active replication built.

5. Purge logs with PURGE BINARY LOGS.

In my opinion, PURGE BINARY LOGS is the safest manual command for deleting binary logs. With this command, you can explicitly specify which logs you want to delete.

Let we now have 7 magazines.

And let's say I want to delete only the log named mariadb-binary-log.000005 and leave the rest. THIS WILL NOT WORK!

If you specify a command:

mariadb> PURGE BINARY LOGS TO 'mariadb-binary-log.000005';

It turns out that all logs that were above the log mariadb-binary-log.000005 will be deleted.

MariaDB also has the ability to delete binary logs for a specific date:

mariadb> PURGE BINARY LOGS BEFORE '2022-05-28';

mariadb> PURGE BINARY LOGS BEFORE '2022-05-28 19:50:00';

6. The binlog_expire_logs_seconds and expire_logs_days variables.

The final option for deleting binary logs is to set two variables.

• expire_logs_days - expiration date in days.
• binlog_expire_logs_seconds - expiration time in seconds.

By default, they are set to 0, which means there is no age limit for binary logs.

Variables are dynamic, so we can change the values ​​in the console with the SET GLOBAL command.

mariadb> SET GLOBAL expire_logs_days = 2;

In this example, we've set a binary log retention period of two days, and the database will automatically delete the binary logs after those two days.

 Thank you all, I hope my article was of some help to you.