mysql 备份方法比较好_我认为最好的MYSQL备份方式

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

posted on 2006-04-11 12:47 无风之雨 阅读(590) 评论(0)  编辑  收藏 所属分类: MySQL