Maintenance of MySQL Binary logs

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.

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