请教高手 mysql 镜像问题 急急。。。。。

Submitted by celine on 周二, 2009/02/17 - 11:41

请教高手，我现在用脚本执行mysql 镜像机制。在执行同步前需要LOAD DATA FROM MASTER。功能没有问题，只是在测试的时候，测试人员经常在执行LOAD DATA FROM MASTER时，切断网络，导致调用此脚本的进程死在了这儿。不知道哪位大侠帮忙解决一下。多谢啦！！！！！！

Taxonomy upgrade extras:

yejr

周二, 2009/02/17 - 12:00

查看当时的状态是什么，另外，设置一下replication的timeout时间试试看

MySQL方案、培训、支持

celine

周二, 2009/02/17 - 12:07

怎样查看当时的状态， 怎样设置replication的timeout时间

这是我的脚本。

#DATE=`date +%H%M%S`
DB_NAME=$1
HOST=$2
HOST1=$3

#USERNAME=lightmaze
#PASSWORD=LitgCitW
#MYSQL="mysql -u$USERNAME -p$PASSWORD"
TMPDIR=/tmp

export LD_LIBRARY_PATH=/usr/local/lib

function dumpDB() {
#no Args to function uses the global variables

#DUMPFILE=/tmp/LMDB_22.dump

#$MYSQL -h $1 -e "flush tables;"
#mysqldump -u$USERNAME -p$PASSWORD -h $1 $DB_NAME > $DUMPFILE

/usr/local/bin/mysql -ulightmaze -pLitgCitW -e "flush tables;"
/usr/local/bin/mysqldump -ulightmaze -pLitgCitW -h $1 LMDB_22 > /tmp/LMDB_22.dump

if [ $? == 0 ] ; then
echo "DB Dump successful from Host: $1 for Database $DB_NAME"
else
echo "DB Dump failed from Host: $1 for Database $DB_NAME"
return
fi

#$MYSQL -e "drop database $DB_NAME;"
#$MYSQL -e "create database $DB_NAME;"
#$MYSQL -e "use $DB_NAME;"
#$MYSQL $DB_NAME < $DUMPFILE

mysql -ulightmaze -pLitgCitW -e "drop database LMDB_22;"
mysql -ulightmaze -pLitgCitW -e "create database LMDB_22;"
mysql -ulightmaze -pLitgCitW -e "use LMDB_22;"
mysql -ulightmaze -pLitgCitW LMDB_22 < /tmp/LMDB_22.dump

if [ -e /tmp/LMDB_22.dump ]; then rm /tmp/LMDB_22.dump; fi
}

function changeMaster() {
# Arg 1 - IP address of the new master
#$MYSQL -h $1 -e "SHOW MASTER STATUS" > $TMPDIR/TEMP_FILE

/usr/local/bin/mysql -ulightmaze -pLitgCitW -h $1 -e "SHOW MASTER STATUS" > /tmp/TEMP_FILE

LOG_FILE=$(cat /tmp/TEMP_FILE | cut -f 1 | tail -n 1)
LOG_POS=$(cat /tmp/TEMP_FILE | cut -f 2 | tail -n 1)
echo "LOG_FILE= $LOG_FILE; LOG_POS= $LOG_POS"
#$MYSQL -f -e "stop slave;"
#$MYSQL -f -e "reset slave;"
#$MYSQL -f -e "CHANGE MASTER TO MASTER_HOST='$1',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='$LOG_FILE',MASTER_LOG_POS=$LOG_POS;"
#$MYSQL -f -e "start slave;"

/usr/local/bin/mysql -ulightmaze -pLitgCitW -f -e "stop slave;"
/usr/local/bin/mysql -ulightmaze -pLitgCitW -f -e "reset slave;"
/usr/local/bin/mysql -ulightmaze -pLitgCitW -f -e "CHANGE MASTER TO MASTER_HOST='$1',MASTER_USER='lightmaze',MASTER_PASSWORD='LitgCitW',MASTER_LOG_FILE='$LOG_FILE',MASTER_LOG_POS=$LOG_POS;"
/usr/local/bin/mysql -ulightmaze -pLitgCitW -f -e "LOAD DATA FROM MASTER;"
/usr/local/bin/mysql -ulightmaze -pLitgCitW -f -e "start slave;"

if [ -e /tmp/TEMP_FILE ]; then rm /tmp/TEMP_FILE; fi
}

#First take a dump of the Master DB and load it
#There are redundant path to master check which one works
#And use this as the new master

ping -c 1 $HOST > /dev/null

if [ $? == 0 ] ; then
echo "ping successful to Host: $HOST "
#dumpDB $HOST
changeMaster $HOST
else
echo "Ping Failed to Host: $HOST "
echo "Trying ... HOST1: $HOST1"
ping -c 1 $HOST1 > /dev/null

if [ $? == 0 ] ; then
echo "ping successful to Host: $HOST1 "
dumpDB $HOST1
changeMaster $HOST1
else
echo "ping Failed to Host: $HOST1 "
echo "Both paths have failed exiting..."
exit 1
fi
fi