mysql 在线备份脚本_mysqldump 在线备份的一个好用的脚本 --zt

MYSQL_CONN="-hhostip -uusername -ppassword"

mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &

SEARCHING_FOR_SLEEP=0

while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]

do

sleep 3

SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`

done

sleep 1

SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"