[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Cleaning Bin-Log Files
From:       "Rhino" <rhino1 () sympatico ! ca>
Date:       2006-02-28 23:03:49
Message-ID: 012301c63cbb$3cbbcf50$0d02a8c0 () athlon
[Download RAW message or body]


----- Original Message ----- 
From: "Shaun Adams" <shaun@hypraze.net>
To: <mysql@lists.mysql.com>
Sent: Tuesday, February 28, 2006 5:19 PM
Subject: Cleaning Bin-Log Files


> Does anyone know of a method or script that will automatically delete
> bin-log files after x days?  I've got a few customers, all utilizing a
> master and slave server... my problem is that the harddrives often get
> filled to capacity if they go unchecked over a few months.  Most of this 
> is
> attributed to the binlogs which fill to about 1GB and then starts a new 
> log.
> We're using MySQL 4.026.
>
> Any thoughts?
>
>
This may be excessive for what you are trying to do but it's a starting 
point.

The following bash script is one that I run every day via a cron job. (You 
didn't say what OS your server is; mine is Linux Mandrake so I use cron for 
scheduling.) The script is fairly heavily commented so you'll probably 
understand it if you know bash but feel free to ask followup questions if 
things aren't clear. Basically, the script gets the names of each of the 
databases on the system, does a backup via mysqldump, then lists all backups 
older than a given number of days, then deletes those older backups. It 
writes a short report and emails it to me and the system administrator for 
the server. It's run without problems for several years now.

You're not worried about database backups so you can probably remove the 
do/done and simply list all the bin-logs that meet your criteria, then 
delete them. You may not want to bother sending that email but you could use 
cron to schedule this script to run as often as you like.

================================================================
#!/bin/bash

#This script makes a separate database-level backup of each of the current 
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day 
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash > /home/rhino/MySQL/backup2.out 
2>&1; cat /home/rhino/MySQL/backup2.out | sendEmail -f rhino@foo.com -t 
rhino@foo.com fred@foo.com
-u "MySQL Backup Report"

USERID="myuserid"; #The userid to use for creating the backup
PASSWORD="mypasswd"; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d"-"%H%M%S`; #The timestamp 
(YYYYMMDD-HHMMSS) of the backup
BACKUP_PATH="/home/rhino/MySQL/backup"; #The directory into which the backup 
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to 
keep

echo "** REPORT BEGINS **";
echo
echo "Program Name:" $0
report_date=`/bin/date`
echo "Report Date:" $report_date;
echo

#Display the non-secret values used in this run.
echo "Backup Values:";
echo "  Backup timestamp is" $BACKUP_TIMESTAMP;
echo "  Backup path is" $BACKUP_PATH;
echo "  Number of daily backups to keep =" $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;

#For each database currently in MySQL, take a database-level backup, then 
list any backups older than a certain number of days, then delete those old 
backups.
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
   echo
   echo "Backing up database" $ONE_DBNAME;
   /usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD} 
${ONE_DBNAME} -r "${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql"
   echo ">> Deleting these old backups for this database..."
   /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old 
backups (if any)
   /usr/bin/find ${BACKUP_PATH} -mtime 
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';'; 
#delete old backups (if any)
done

echo
echo "** REPORT ENDS **";

================================================================

--
Rhino 



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]