I've got a pair of MySQL databases that are set up for Master Slave replication. The slave is doing just fine.
The master, on the other hand, has been hoarding binary logs despite my best (automated) efforts.
I'm trying to set up the 'expire_logs_days' variable in MySQL's my.cnf file, but for some reason it seems to be being ignored. My my.cnf file looks something like:
[mysqld]
...
log-bin=/var/log/mysql/mysql-bin.log
server-id=1
expire_logs_days=3
log_bin_trust_function_creators=TRUE
sync_binlog=1
[mysqld_safe]
...
But when I run SHOW VARIABLES WHERE Variable_Name='expire_logs_days'
in MySQL, it returns me a value of 0
I've tried:
expire_logs_days='3'
mysqld --help --verbose | grep cnf
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf order of preference
my.cnf
file is located at /etc/my.cnf
SET GLOBAL expire_logs_days=3
DOES work within MySQL, but doesn't really solve my problem per seAnd that's about all I could think to do. I've run the manual PURGE command, which works just fine, but I'd prefer (although, if there's no way around it, I'll do it anyway) to not run the PURGE command using cron.
Anybody have any ideas? I'm just about tapped.
Thanks.
Since you can erase binary logs using PURGE BINARY LOGS;
, I have two places for you to look that you have not mentioned
mysql-bin.index
This file contains the location of all binary logs. When expire_logs_days is set, mysqld will open this text file, check the datetime stamps in each file until it encounters a binary logs that has a timestamp less than NOW() - INTERVAL expire_logs_days DAY)
.
The binary logs in mysql-bin.index is expected to be numerically consecutive. If the binary logs are not numerically consecutive, log rotation is disabled.
/var/log/mysql
folderAccording to your my.cnf
, This folder holds all the binary logs.
Here are 2 questions:
/var/log/mysql
that are not numerically consecutive?/var/log/mysql
that are NOT IN mysql-bin.index
?Sometimes, people delete binary logs in the OS. This can throw off mysqld since mysqld uses mysql-bin.index
to internally track the existence of binary logs. Simply deleting binary logs with rm -f
logically breaking the log rotation mechanism as mysqld knows it.
If either or both are the case, you can clean this up as follows:
mysql -ANe"RESET MASTER"
service mysql stop
cd /var/log/mysql
rm -f mysql-bin.*
cd
service mysql start
After this, you should have a brand spanking new binary log setup.
Give it a Try !!!