In one of our environment, we have a Master-Slave replication. A few days ago I discovered that I have a space issue with /var/lib/mysq. As I love to use LVM this should not be a problem at all. I will increase the size of /var/lib/mysql, but… I want to see what makes “problem” on this partition, and here it is. Too many mysql-bin files.

Yes, I forgot to add expire-logs-days=N in [mysqld] section in my.cnf file. So this is a story about the secure way to remove mysql-bin files. On a slave server I have to find up to which mysql-bin file is safe to remove. So, I ‘we run

mysql> show slave status \G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1.setenforce.com
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000100
Read_Master_Log_Pos: 1000234247
Relay_Log_File: slave-log.000277
Relay_Log_Pos: 1000234393
Relay_Master_Log_File: mysql-bin.000100
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

As we can see Master_Log_File is pointing to mysql-bin.000100 file. It is safe to remove all files UP TO mysql-bin.000100 file

On Master server I had to run this command (I didn't want to make any risk so I' we decided to leave the last bin file)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000099';

To avoid this situation, I’ we decide to set global variable (I don’t have to restart MySQL service in this case)

mysql> SET GLOBAL expire_logs_days = 10;

To ensure that I will not forget to add variable in my.cnf on next restart, I have to change my.cnf

[root@ mysql1 ~]# cat /etc/my.cnf
[mysqld]
...
expire-logs-days=10
...