里歐's 布拉格

Automatically Purge MySQL Master Log
My Friend Andre Hartawan is troubling due to his mysql server at work stop operating. After quick check he found that no more disk space available on the system. Further check on /var partition he found many replication log file on disk which no longer needed but have big size (100MB). He can easily purge the logs file but wanted the server due it chores automatically. So here it’s I share my script (autopurgebinlog.sh) to purge replication logbin automatically.

######################################
#!/bin/bash
# autopurgebinlog.sh
# script to purge mysql replication log-bin
# by am3n.net
# License GNU/GPL

# number of days to keep logbin
AGE=5 # days
AGE_MINS=$[ $AGE * 60 * 24 ]

#find mysql binary log which older than $AGE_MINS
OLDLOGS=`find /var/lib/mysql/$HOSTNAME-bin.??? -cmin +$AGE_MINS | tail -n 1 | gawk -F'/' '{print $5}'`

#change mysqluser and mysqlpass with user that have priviled to purge binary log (i.e. root)
#Example:
#PURGE MASTER LOGS TO 'mysql-bin.010';
#PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
mysql -u mysqluser -pmysqlpass mysql -e "PURGE MASTER LOGS TO '$OLDLOGS'"
######################################

Put the script on crontab, run it once a month.

crontab -e
1 1 1 * * /root/autopurgebinlog.sh

Reference