MySQL Purge Binary Logs Statement: How to Purge Logs & Save Space?

Binary log files in the MySQL database are used to keep track of all the modifications made to the database. Binary log files contain events for MySQL statements like create, update, and delete. However, binary files can fill the available storage space in the MySQL server database. Therefore, you need to purge the MySQL Binary Log statements files using the MySQL PURGE BINARY LOGS statement to solve this problem.

The MySQL server creates the index file to keep the record of binary files with its name. The MYSQL PURGE BINARY LOGS statement will delete all the binary files listed in the log index file. The deleted binary log files are removed from the list in the index file, thereby saving space on the MySQL server database.

In this tutorial, you will learn to MySQL Purge Binary Logs Statement and Save Space on the MySQL server database.

Prerequisites

• Basic understanding of databases.

What is MySQL?

Developed in 1995, MySQL is an open-sourced relational database management system. The database server is distributed, developed, and managed by Oracle Corporation. The SQL in MySQL stands for ‘Structured Query Language.’ 

According to the StackOverflow survey of 2020, MySQL is considered the most popular database technology by professional developers due to its open-source reliability, availability, and scalability features. Several organizations leverage MySQL’s data security and strong transactional support to secure online database transactions and customer interactions with the databases.

What is a Binary Log in MySQL?

The Binary log (binlog) consists of a log file that contains information about data modifications made to the MySQL database. You can enable the binary log by starting the MySQL server with the –log-bin option. MySQL 3.23.14 introduced the concept of a Binary log, which contains statements stored in the form of events describing modifications. But, the binary log is not used for statements like SHOW or SELECT as such statements do not modify any data. The binary log consists of information about the server’s state needed for reproducing the statements correctly, metadata required for the maintenance of the binary log, and error codes.

Features of Binary Logging

• Replication: The MySQL database server consists of Master-Slave replication process, where there are two or more Master servers that can replicate data on multiple Slaves. The binary log in MySQL is used on Master replication servers for sending records of the statements to the Slave servers for replication purposes. The details in the binary log format are also specific to replication. The Master’s servers send the event in its binary log to Slave servers. This will result in making the same changes that were made on the Master servers. Slaves store events from Masters servers into a Relay log until they are executed. The format of the Relay log is the same as the binary log.
• Recovery: Data recovery operations mainly leverage the binary log in MySQL server. When the file is restored after the backup, events recorded in the binary log after the backup are re-executed. Therefore, such events create a new database from the backup point.

Types of Binary Logging

• Statement-based Logging: Statement-based logging consists of SQL statements that produce events like insert, update and delete. 
• Row-based Logging: As the name suggests, row-based logging consists of events that change individual rows.
• Mixed-based Logging: Mixed-based logging uses statement-based logging by default, but it automatically switches to row-based logging when needed. 

How to use MySQL Purge Binary Logs Statement?

• Purge Binary Logs using the MySQL Purge Binary Logs Command
• How to Save Space Manually using MySQL Purge Binary Logs Statement?
• Purge Binary Logs using the mysqlbinlogpurge Tool

A) Purge Binary Logs Files using the MySQL Purge Binary Logs Command 

To keep the record of the binary log files, mysqld creates an index file that consists of the names of all binary log files in it. The PURGE BINARY LOGS Statements are used to delete all the list of binary log files in the index file. It requires the BINLOG ADMIN privilege. You need to start the server with –log-bin, or else you cannot enable binary logging.

PURGE BINARY LOGS can run while replicas are replicating. You do not need to stop them. If you have any active replica reading one of the log files you are trying to delete, PURGE BINARY LOGS statements will not delete the file. But, the MYSQL PURGE BINARY LOGS statements will delete any earlier log file. You get a warning message in such scenarios.

Follow the Below Steps to Purge the Binary Log Files

• Use the SHOW REPLICA STATUS on each replica to check which log file it is reading.
• Use SHOW BINARY LOGS  to get the list of binary log files on the source.
• Among all the replicas, determine the earliest log file and also the target file. If all the replicas are new, then the target file is last on the list.
• Take the backup of all the replicas you want to delete (Optional).
• Purge all the log files except the target file.

B) How to Save Space Manually using MySQL Purge Binary Logs Statement?

To purge the binary logs in a MySQL server, you have to connect with the MySQL server and use the PURGE BINARY LOGS command as follows.

PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss';

For example, consider the below command.

PURGE BINARY LOGS BEFORE '2017-03-02 22:46:26';

The above example will purge all the binary logs before the specified date.

Another way is to purge the log file on the « name » attribute of the log file. For example, consider the below command.

PURGE BINARY LOGS TO 'ns30XXXX-bin.009836';

But, when you have no space left on your device, the only solution is to drop the binary log files manually. Assume that your binary files are located at /var/lib/mysql, and follow the below steps.

Stop the MySQL service (syntax may vary depending on your Linux distribution).

service mysql stop

Count and delete half of your binary logs to get some free space. Use the below command to remove the binary log files from the MySQL binary logs index.

cd /var/lib/mysql && a=`ls |grep -v relay |grep bin.index` && b=`wc -l <$a` ; c=`echo $(($b/2))` |xargs -l rm ; echo $c | head -n $b $a |cut -d "/" -f2 && sed 1,$cd $a -i

Start the MySQL service.

service mysql start

Verify the binary logs.

SHOW BINARY LOGS;

It should show some binary logs, as you only deleted half of the binary logs. You can use the « PURGE BINARY LOGS BEFORE » command to delete the rest of the binary logs.

This is how you can use the MySQL Purge Binary Logs Statement to save space manually.

C) Purge Binary Logs using the mysqlbinlogpurgetool

If you use the above two commands/techniques, it is not ensured that the Slave has already performed the binary log transactions and can be removed safely. Therefore, in such scenarios, you can use the mysqlbinlogpurge tool. It is a part of MySQL utilities and can be downloaded from MySQL archives. This tool ensures any files that are in use or required by the Slaves are not deleted.

The mysqlbinlogpurge determines when it is safe to purge the bin logs with the below two threads.

• Slave_IO: It is responsible for gathering events from the Master.
• Slave_SQL: It is responsible for executing the events locally. 

You can verify if the Slave_IO or Slave_SQL is running and where they are with the Slave’s status.

mysql > show slave statusG

Output:

The Slave_IO currently fetches ‘Master_Log_File/Read_Master_Log_Pos’ from the Master.

The Slave_IO is actively executing ‘Relay_Master_Log_File/Exec_Master_Log_Pos’ in terms of the Master’s log file.

The SQL thread is actively executing ‘Relay_Log_File/Relay_Log_Pos’ in the Slave’s Relay log file.

The Master_Log_File is the latest log file on the Master server, and the Slave_IO knows about it. The Slave_IO will read and gather information from the Master_Log_File. Any files after the Master_Log_File should be conserved for replication.

The Relay_Master_Log_File is the execution point in the Master’s binlog that the SQL thread has executed.

The below example shows the use of mysqlbinlogpurge tool in different scenarios. If the Slave is stopped, this tool will throw an error and will not purge any binary logs.

mysqlbinlogpurge --master=root:msandbox@localhost:45007
--slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009
--dry-run

Output:

mysqlbinlogpurge --master=root:msandbox@localhost:45007 --slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009 --dry-run
#Latest binlog file replicated by all slaves: mysql-bin.000011
#To manually purge purge the binary logs Execute the following query:
PURGE BINARY LOGS TO 'mysql-bin.000012'

If you want to keep the binlog files until the SQL thread is executing, use the following code.

 mysqlbinlogpurge --master=root:msandbox@localhost:45007
--slaves=root:msandbox@localhost:45008,root:msandbox@localhost:45009
--dry-run
--binlog=mysql-bin.000002 -v
#Checking user permission to purge binary logs...
# Master active binlog file: mysql-bin.000012
# Checking slave: localhost@45008
# I/O thread is currently reading: mysql-bin.000012
# Checking slave: localhost@45009
# I/O thread is currently reading: mysql-bin.000012
# Range of binlog files available: from mysql-bin.000001 to mysql-bin.000012
# Latest binlog file replicated by all slaves: mysql-bin.000011
# To manually purge purge the binary logs Execute the following query:
PURGE BINARY LOGS TO 'mysql-bin.000002'
# Range of binlog files available: from mysql-bin.000001 to mysql-bin.000012

It is recommended to remove the –dry-run option for deleting the binary log files.

ls -larth data/ | grep -i mysql-bin

Output:

Themysqlbinlogpurge tool does not work well with Multi-source replication enabled. It will not get the proper binlogs. For example, the below code.

mysqlbinlogpurge --master=root:msandbox@localhost:45008 --slaves=root:msandbox@localhost:45009 --dry-run
# Latest binlog file replicated by all slaves: mysql-bin.000000
# No binlog files can be purged.

The Relaylog will get corrupted. Therefore, if you have space constraints and are sure that your Relaylog would not get corrupted, you can use the mysqlbinlogpurge tool for purging the binary log files.

These are the various ways in which you can use the MySQL Purge Binary Logs statement & the mysqlbinlogpurge tool.

Conclusion

This article outlines how to purge the MySQL binary log files when there is no storage space left on your MySQL database. It also focuses on different types of parameters that are used in purging the MySQL binary files. Purging the old binary log files that are not in use currently can save a lot of storage on the database server. 

The list of organizations that use MySQL database extends beyond 5000+. Surely, since you’ve read this article, you are one of those. If you are interested in implementing Data Pipelines to Extract, Transform and Load data from MySQL databases to further analyze it, consider Hevo as your go-to option.

FAQ