Danny Haworth wrote:
> I too would be interested in a good way of clearing the binary log files
> (preferably non disruptive to the server ;-)
> The only thing thats saving me so far is that the systems capacity is 320Gb.
>
> danny
>
> Anirudha Kukreti wrote:
>
> >hi all
> >i have established a two way replication setup
> >my problem is that my hard disk gets occupied by the log files;
> >i tried purging the files but after some time again my hard disk gets filled
> >with the log files
> >
> >
> >mysql, queries
> >
> >
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Here is a script I wrote which archives the logs in 2 places. We run it every 5
minutes from the cron dameon
It's designed to check a slave database located 40 miles away before moving the
logs. Since you are not keeping
the logs around, you may want to change the mv command to rm.
Hope this helps!
walt
#!/bin/bash
#achive_logs.sh
#
# Purpose - Archvive mysql log if size >= 5MB
# Log files are in master_server_name-bin.xxx format.
# Main script calls check_slave which in turn calls copy_log.
# server names
SLAVE=slave.nea-fast.com
MASTER=master
# length we'll need to strip off of log file name
LENGTH=${#MASTER}
STRIP=$((LENGTH + 5)) # we add 5 for "-bin."
# archive directories
BASE="/var/lib/mysql/"
ARCH1="/var/lib/mysql/mysql_arch1/"
ARCH2="/opt/mysql_arch2/"
# email address
[EMAIL PROTECTED]
[EMAIL PROTECTED]
# page() - Takes 2 arguments. function and dir
page()
{
echo "$1 couldn't copy to $2" | /bin/mail -s "Help!!" $PAGE
}
# copy_log - takes 2 arguments. The log file and a 0 or 1.
# If 2nd arg is 0, the slave db is in sync and it's cool
# to delete the log after coping. If 2nd arg is 1, the slave
# has not caught up and we dont want to delete the log from the
# mysql dir.
# If there is an error coping to either dir, dont
# delete the old log file! We'll try to copy again next time.
copy_log()
{
PROBLEM=$2
cp -f $1 $ARCH1
if [ $? -ne 0 ]; then
echo "copy-log couldn't copy to arch1"
page copy-log arch1
PROBLEM=1
fi
cp -f $1 $ARCH2
if [ $? -ne 0 ]; then
echo "couldn't copy to arch2"
page copy-log arch2
PROBLEM=1
fi
if [ $PROBLEM -eq 0 ]; then
echo "removing file"
rm -f $1
fi
}
# check_slave - takes one argument which is the log name. We
# need this function because we may end up with several log files
# in the mysql dir because the slave was not caught up when we last
# ran.
check_slave()
{
M_LOG=$1
M_LOG_SEQ=${M_LOG:$STRIP} #strip off "$MASTER-bin. from file name"
echo "getting info from slave `date`"
DATA=`ssh $SLAVE cat /var/lib/mysql/master.info`
echo "got data from slave `date`"
SLAVE_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off "$MASTER-BIN." from file name to get sequence number
S_LOG_SEQ=${SLAVE_LOG:$STRIP}
if [ $M_LOG_SEQ -lt $S_LOG_SEQ ]; then
# Slave has switched to new log so were ok. Send file name
# and 0 to copy_log. 0 tells copy_log to delete the file when finished
copy_log $1 0
else
# Slave hasn't switched to new log. Send file name and 1 to copy_log.
# 1 tells copy_log to NOT delete the file when finished.
copy_log $1 1
fi
}
########################################################################
# main script###########################################################
########################################################################
# The first thing we need to do is make sure we're not already running
if [ -f /var/lib/mysql/archiving ]; then
echo "We appear to still be running!!!"
exit 0
else
touch /var/lib/mysql/archiving
chmod 400 /var/lib/mysql/archiving
fi
# Make sure we can talk to other site
ping -c 5 $SLAVE > /dev/null # dev/null so cron dameon doesn't spam
if [ $? -ne 0 ]; then
echo "couldn't ping $SLAVE"
rm /var/lib/mysql/archiving
exit 0
fi
cd $BASE
# Get current log from index file
CUR_LOG=`tail -1 $MASTER-bin.index`
# Log name is stored as "./mysql_master.xxx" so we'll strip "./"
FILE=${CUR_LOG:2}
# Check size of file
SIZE=`du -ks $FILE | awk '{print $1}'`
if [ "$SIZE" -gt "2048" ]; then
# Get all log files
LOGS=`ls $MASTER-bin.* | grep -v $MASTER-bin.index`
/usr/bin/mysqladmin flush-logs
for i in $LOGS; do
check_slave $i
done
fi # End if size >= 5MB
rm -f /var/lib/mysql/archiving
exit 0
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
