MySql - Changing expire_logs_days without restarting the server

Question

I'm using MySql 5.5. Is it possible to change expire_logs_days and have the changes take effect without restarting the server?

Answer 1

In MySQL 5.6, you would want to show what your expire_logs_days is set to first. Then confirm that the master doesnt need to keep these logs more than x amount of days. Word of caution, having binary logs that low in days can be a big risk.

Set globally as:

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 5     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global expire_logs_days=1;
Query OK, 0 rows affected (0.62 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 1     |
+------------------+-------+
1 row in set (0.00 sec)

Then dont forget to update the my.cnf file if you want this setting to remain or survive a service restart:

$ sudo vim /etc/my.cnf
    expire_logs_days               = 1

Then flush the current log and to have the binary log statement to take effect on all the logs older than 1 day, in your case:

mysql> flush binary logs;

Answer 2

thanks @dezso

here is how I changed it: SET GLOBAL expire_logs_days = 4;

Answer 3

Since MySQL 8 it's binlog_expire_logs_seconds, e.g. to set to 3 days one would do:

SET GLOBAL binlog_expire_logs_seconds = 259200;

Answer 4

Mysql (community) Version 8.0.17-1.sles12 - OpenSUSE tumbleweed 2019.10.02

mysql> SET GLOBAL expire_logs_days = 4;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds
cannot be used together. Please use binlog_expire_logs_seconds to set the expire
time (expire_logs_days is deprecated)

..