??? 要備份MYSQL，很多人用mysqldump，其實這種方式，導出的文件是最大的，導入的時間是最久的。命令是方便的，但真正發生錯誤的時候，恢復效率很低。
??? 我主張，另外找一臺比較空閑的機器，來做數據庫的備份。這臺機器作以下用途：
?? 它是主數據庫帶的slave數據庫群里面的一臺，每天凌晨定時啟動同步數據，等追上bin-log并全部執行后，停止同步，并用select * into outfile將數據全部導出成文件，并且在每周的某一天，清除掉主數據庫上已經同步好的bin-log，以確保硬盤空間不被log占滿。
?? 為此，我寫了3個腳本，分別執行1、啟動mysql，追log，然后停止slave；2、導出全部數據庫全部文件到文件；3、刪除主數據庫的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