Nico Sabbi wrote:
Hi,
I have a configuration with 1 master and 2 slaves; all servers are
correctly running mysql-max-4.0.20.
All is fine, but I'm observing a strange usage of logs.
On one of the slaves I have:
mysql> show slave status \G
*************************** 1. row ***************************
Master_Host: idb
Master_User: root
Master_Port: 3306
Connect_retry: 60
Master_Log_File: db-bin.3322
Read_Master_Log_Pos: 1013397930
Relay_Log_File: www4-relay-bin.009
Relay_Log_Pos: 174693397
Relay_Master_Log_File: db-bin.3322
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_do_db:
Replicate_ignore_db: mysql
Last_errno: 0
Last_error:
Skip_counter: 0
Exec_master_log_pos: 1013397930
Relay_log_space: 174693397
1 row in set (0.00 sec)
and the relay log is 170MB. I don't want relay logs to grow so big;
what I'd like Mysql
to do is to destroy the relay-log file as soon as the SQL thread is
finished. (usually relay logs
grow up to 4GB in 1GB chunks, so this example doesn't show my problem
in all its extent).
Similar problem on the master (db):
-rw-rw---- 1 mysql mysql 196122 Jan 23 2004 db-bin.3319
-rw-rw---- 1 mysql mysql 1073772277 Sep 13 14:55 db-bin.3320
-rw-rw---- 1 mysql mysql 1073773581 Sep 15 10:18 db-bin.3321
-rw-rw---- 1 mysql mysql 1019108019 Sep 17 09:08 db-bin.3322
-rw-rw---- 1 mysql mysql 42 Sep 15 10:18 db-bin.index
There are 3 GB of logs that no one needs anymore. Since the master
knows that all
the slaves are correctly aligned up to a certain MASTER_LOG_POS, can't
it automatically remove the logs up to that position?
On another slave where I set
set-variable = max_relay_log_size=25000000
set-variable = relay_log_space_limit=50000000
max_relay_log_size seems to be respected, but the problem remains:
after having the data inserted in the DB those logs are useless.
Can't Mysql automatically purge logs when they are not needed anymore?
Thanks,
Here is a script we have croned to run every 5 minutes. It checks that
both slaves are in sync and then it archives the un-needed logs to
different disks in case we might need them. You'll have to set up ssh
keys so the master server can log into the slaves.
#!/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
SLAVE1=database2.nea-fast.com
SLAVE2=database3.nea-fast.com
MASTER=db1
# 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="/storage/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()
{
echo "in check slave"
M_LOG=$1
M_LOG_SEQ=${M_LOG:$STRIP} #strip off "$MASTER-bin. from file name"
#
# We've got to check both SLAVE1 and SLAVE2
#
echo "getting info from slave1 `date`"
DATA=`ssh $SLAVE1 cat /var/lib/mysql/master.info`
echo "got data from slave1 `date`"
SLAVE1_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off "$MASTER-BIN." from file name to get sequence number
S1_LOG_SEQ=${SLAVE1_LOG:$STRIP}
# Let check slave2
echo "getting data from slave2 `date`"
DATA=`ssh $SLAVE2 cat /var/lib/mysql/master.info`
echo "got data from slave2 `date`"
SLAVE2_LOG=`echo $DATA | awk '{print $1}'` # Master log is first line
#strip off "$MASTER-BIN." from file name to get sequence number
S2_LOG_SEQ=${SLAVE2_LOG:$STRIP}
# We've got the log number for SLAVE1 in $S1_LOG_SEQ and
# $S2_LOG_SEQ for SLAVE2
if [ $M_LOG_SEQ -lt $S1_LOG_SEQ ] && [ $M_LOG_SEQ -lt $S2_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 -w 5 $SLAVE1 > /dev/null # dev/null so cron dameon doesn't spam
if [ $? -ne 0 ]; then
echo "couldn't ping $SLAVE1"
rm /var/lib/mysql/archiving
exit 0
fi
ping -c 5 -w 5 $SLAVE2 > /dev/null # dev/null so cron dameon doesn't spam
if [ $? -ne 0 ]; then
echo "couldn't ping $SLAVE2"
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}'`
# Check the number of files
LogFileCount=`ls db1-bin.* |wc -l`
if [ "$SIZE" -gt "2048" ] || [ $LogFileCount -gt "2" ]; then
# Get all log files
LOGS=`ls $MASTER-bin.* | grep -v $MASTER-bin.index`
/usr/bin/mysqladmin flush-logs
sleep 30
for i in $LOGS; do
check_slave $i
done
fi # End if size >= 2MB or $LogFileCount -gt "2"
rm -f /var/lib/mysql/archiving
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
