要备份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