??? 要備份MYSQL，很多人用mysqldump，其實(shí)這種方式，導(dǎo)出的文件是最大的，導(dǎo)入的時(shí)間是最久的。命令是方便的，但真正發(fā)生錯(cuò)誤的時(shí)候，恢復(fù)效率很低。
??? 我主張，另外找一臺(tái)比較空閑的機(jī)器，來做數(shù)據(jù)庫(kù)的備份。這臺(tái)機(jī)器作以下用途：
?? 它是主數(shù)據(jù)庫(kù)帶的slave數(shù)據(jù)庫(kù)群里面的一臺(tái)，每天凌晨定時(shí)啟動(dòng)同步數(shù)據(jù)，等追上bin-log并全部執(zhí)行后，停止同步，并用select * into outfile將數(shù)據(jù)全部導(dǎo)出成文件，并且在每周的某一天，清除掉主數(shù)據(jù)庫(kù)上已經(jīng)同步好的bin-log，以確保硬盤空間不被log占滿。
?? 為此，我寫了3個(gè)腳本，分別執(zhí)行1、啟動(dòng)mysql，追log，然后停止slave；2、導(dǎo)出全部數(shù)據(jù)庫(kù)全部文件到文件；3、刪除主數(shù)據(jù)庫(kù)的log
---------------------------------------------------------------------------------------
#!/bin/bash
#readMasterMysql.sh
CHECK_MYSQL=0
/home/mysql/bin/mysqld_safe &
until [ "$CHECK_MYSQL" = "1" ]
do
? sleep 10
? CHECK_MYSQL=`/home/mysql/bin/mysql -uroot -e"show slave status"|awk '{if($14==$21)print "1"}'|tail -n1`
done
/home/mysql/bin/mysql -uroot -e"slave stop"
/home/script/backupMysql.sh
/home/mysql/bin/mysqladmin shutdown
WEEK=`date "+%w"`
if [ $WEEK = "5" ]
then
??? /home/script/purgeLog.sh
fi
------------------------------------------------------------------------------
#!/bin/bash
#purgeLog.sh
LOG_FILE=/home/mysql/data/master.info
DB_SERVER=`sed -n '4p' $LOG_FILE`
DB_USER=`sed -n '5p' $LOG_FILE`
DB_PASS=`sed -n '6p' $LOG_FILE`
DB_LOGFILE=`sed -n '2p' $LOG_FILE`
/home/mysql/bin/mysql -h$DB_SERVER -u$DB_USER -p"$DB_PASS" -e"purge master logs to '$DB_LOGFILE'"
------------------------------------------------------------------------------
#!/bin/bash
#backupMysql.sh
database=$1
table=$2
MYSQL_CLIENT="/home/mysql/bin/mysql -uroot --default-character-set=gbk"
MYSQL_DUMP="/home/mysql/bin/mysqldump -d -uroot --default-character-set=gbk"
OUTPUT_PATH=/date/backup
for databases in `$MYSQL_CLIENT -e "show databases"|grep -v Database`
do
if [ "$#" = "0" -o "$database" = "$databases" ] ; then
??????? mkdir -p -m777 $OUTPUT_PATH/$databases/
??????? $MYSQL_DUMP $databases > $OUTPUT_PATH/$databases/$databases.sql
??????? for tables in `$MYSQL_CLIENT -e "show tables" $databases|grep -v Tables_in_`
??????? do
??????? if [ "$#" = "0" -o "$#" = "1" -o "$table" = "$tables" ] ; then
??????????????? mv -f $OUTPUT_PATH/$databases/$tables $OUTPUT_PATH/$databases/$tables.old
??????????????? $MYSQL_CLIENT -e "select * into outfile '$OUTPUT_PATH/$databases/$tables' from $tables" $databases
??????? fi
??????? done
fi
done