----- Original Message -----
From: "Shaun Adams" <[EMAIL PROTECTED]>
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 [EMAIL PROTECTED] -t
[EMAIL PROTECTED] [EMAIL PROTECTED]
-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/[EMAIL PROTECTED]
