In this blog, we are going to discuss the Maintenance of binary logs. Before going into our topic, let’s recall the basics of binary logs.

Binary logs

        Binary logs contain all the changes and records that take place in any database. It will log the statements like CREATE, ALTER, DELETE, INSERT and UPDATE. It will not log the SELECT and SHOW statements. In simple terms, we can say that a binary log contains only the write logs, not the read logs. We can view the contents of the binary log using the mysqlbinlog utility. The binary logs play an important role during the replication and recovery process.

We can view the list of binary logs inside the data directory.

In the MySQL server, we can find the list of binary logs along with their size using the

SHOW BINARY LOGS; command.

Need for maintenance of binary logs

       Maintenance of binary logs includes purging the logs more safely without deleting the currently active binary logs in slave. Purging the binary logs happens, when most of the disk space is occupied. Hence it is essential to purge the old binary logs which are not in use. This will pave the way for more space in the disk.

      You will get the following alert when the disk reaches beyond its threshold value

                   CRITICAL: Used Space on – ‘/backup2’ is 94%

Purging the binary logs;

1.BINLOG_EXPIRE_LOGS_SECONDS.

 We can perform the auto-purge using the system variable BINLOG_EXPIRE_LOGS_SECONDS. This option works well in clusters as clusters don’t have slave.The default value is 2592000 seconds(30 days). We can set this variable both globally and in my. cnf file.

We can also manually purge the binary logs, using the following queries

2.PURGE BINARY LOGS TO

             This query will purge all the files prior to the mentioned log file. It will not purge the mentioned binlog.Before purging, ensure the replica status of the slave using SHOW SLAVE STATUS in the slave server.

For example,

Before purging, the following are the list of binary logs

I have to purge the binlog.000001 and binlog.000002, so I have mentioned the

PURGE BINARY LOGS TO ‘binlog.000003‘

3. PURGE BINARY LOGS BEFORE

      This query will purge all the logs prior to the mentioned date. By mentioning the appropriate date, we can purge all the binlog files prior to that date. It will not purge the binlog files created on that mentioned date.

Before purging, the list of binary logs are mentioned below

I have to purge binary logs that were created before 2021-05-06 05:55:22. So I have mentioned

   PURGE BINARY LOGS BEFORE ‘2021-05-06 05:55:22’;

Relay log purge

The replication SQL thread automatically deletes each relay log file after it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the replication SQL thread takes care of doing so.

Checks need to be done before purging the logs

• Before purging, ensure on slave server which log file it is currently reading by using SHOW SLAVE STATUS;
• List the binary log files in master using SHOW MASTER LOGS;
• Take a backup of all binlog files before purging for safety measures ( Binlog server )

Hope this blog clarifies you regarding the maintenance of binary logs.Thanks for your time and attention.