Learn how to set binlog_expire_logs_seconds in MySQL. Our MySQL Support team is here to help you with your questions and concerns.

MySQL set binlog_expire_logs_seconds | Guide

Managing MySQL binary logs is a key part of database administration, enabling data recovery, replication, etc.

Today, we are going to take a walk through setting up up and optimizing MySQL binary logs, including configuring settings, granting privileges, and automating log purging.

1. First, we have to update the MySQL configuration to enable binary logs. To do this, head to the MySQL config file and make sure these settings are under the [mysqld] section:

   [mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog_format = mixed

   Furthermore, we have to add the following for MySQL 8+:

   binlog_expire_logs_seconds = 864000

   And add this for MySQL 5:

   expire_log_days = 10
2. Now, it is time to use the new feature ‘binlog_expire_logs_seconds’ by setting the expiration time. This feature is useful for setting expiration periods that aren’t integral multiples of days.
3. Then, create a MySQL user with replication capabilities. We can do this by running this command:

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysqluser'@'%';
4. Then, it is time to restart the MySQL server to apply the changes:

   sudo service mysql restart

Furthermore, we can change ‘expire_logs_days’ without restarting the server with these steps:

1. Check the current setting in MySQL:

   mysql> show variables like 'expire_logs_days';
2. Then, change the global setting:

   mysql> set global expire_logs_days=1;
3. After that, update the my.cnf file to make the setting permanent:

   sudo vim /etc/my.cnf
expire_logs_days = 1
4. Finally, flush the logs for the changes to take effect:

   mysql> flush binary logs;

Additionally, we can automatically purge MySQL binary logs based on time by adding the ‘binlog_expire_logs_seconds’ variable to our configuration file:

binlog_expire_logs_seconds = 259200

Let us know in the comments if you need further help with configuring MySQL Binary Logs.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Experts demonstrated how to set binlog_expire_logs_seconds in MySQL.