轉載地址：

改良版本mysqldump來備份MYSQL資料庫我的備份指令碼都是在凌晨執行的，經常在慢查詢日誌裡面看到這樣的資訊：select * from table1;
之前一直很納悶，最後才瞭解到原來是MYSQLDUMP搞的鬼。因為MYSQLDUMP會為整個庫來加一個全域性鎖定。
如果單純用MYSQLDUMP進行全庫備份會造成以下三個方面的影響。
1、伺服器CPU嚴重阻塞。
2、磁碟I/O直線增加。
3、所有的查詢都成了慢查詢。我現在的網站資料庫大概是5個G左右，而且每天都有增大。
表結構是MYISAM,INNODB,MEMORY三者混合。
所以單純用HOTCOPY工具恐怕有點困難。所以我今天簡單變了一下我上次寫的關於用OUTFILE來備份MYSQL的指令碼。
可以解決上面說的三個缺點。 1、備份指令碼內容
[david_yeung@localhost ~]$ cat fast_backup
#!/bin/sh # # Created by david yeung. # # 20080707. # # Backup mysql's full data. # DBNAME=$1
BACKUPDIR=/home/david_yeung/backup_new
USERNAME=backup_user
PASSWD=123456
TARNAME="$BACKUPDIR"/backup"$1"`date '+%Y%m%d'`
# Add your own database name here.
case "$1" in
my_site);;
*) exit;;
esac
# Get all the tables' name.
NUM=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME|wc -l`
HEADNUM=`expr ${NUM} - 3`
TAILNUM=`expr ${NUM} - 7`
ARR1=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME| head -n"$HEADNUM" | tail -n "$TAILNUM"`
ARR2=($ARR1) i=0
while [ "$i" -lt "${#ARR2[@]}" ]
do
tmpFileName=${ARR2[$i]}
# The real dump process.
/usr/local/mysql/bin/mysqldump -u$USERNAME -p"$PASSWD" "$DBNAME" "$tmpFileName" >> "$TARNAME"
let "i++"
done
2、因為我們一直用儲存過程，所以得單獨備份出來。
[david_yeung@localhost ~]$ cat fast_sp
#!/bin/sh
# Created by david yeung 20080122.
#
# Backup site's routine.
TARNAME=/home/david_yeung/backup_new/spBackup"$1"`date '+%Y%m%d'`
/usr/local/mysql/bin/mysqldump -ubackup_user -p123456 -n -t -d -R my_site > "$TARNAME" 3、丟到計劃任務裡面去，就不管了。
[root@localhost backup_new]# crontab -l
0 01 * * * /home/david_yeung/fast_backup my_site
0 0 * * 5 /home/david_yeung/fast_sp 每天凌晨1點備份資料，每個週五凌晨備份儲存過程。

4、因為有人說碰到要恢復單表的資料，所以把備份單表指令碼也貼上。

#!/bin/sh

#

# Created by david yeung.

#

# 20080707.

#

# Backup mysql's full data.

#

DBNAME=$1
USERNAME=backup_user
PASSWD=123456
PREFIX=/home/david_yeung/backup_new/
DIRNAME=$PREFIX`date '+%Y%m%d'`
echo $TARNAME
# Add your own database name here.
case "$1" in
t_girl);;
*) exit;;
esac
if [ ! -d "$DIRNAME" ]
then
mkdir "$DIRNAME"
fi
# Get all the tables' name.
NUM=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -S/tmp/mysql50.sock -s -vv -e "show tables" -D $DBNAME|wc -l`
HEADNUM=`expr ${NUM} - 3`
TAILNUM=`expr ${NUM} - 7`
ARR1=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -S/tmp/mysql50.sock -s -vv -e "show tables" -D $DBNAME| head -n"$HEADNUM" | tail -n "$TAILNUM"`
ARR2=($ARR1)

i=0
while [ "$i" -lt "${#ARR2[@]}" ]
do
tmpFileName=${ARR2[$i]}
# The real dump process.
/usr/local/mysql/bin/mysqldump -u$USERNAME -p"$PASSWD" -S/tmp/mysql50.sock "$DBNAME" "$tmpFileName" > "$DIRNAME"/"$tmpFileName"
let "i++"
done -------------------------------------------------------------------------------- 使用mysqldump定時備份資料庫的指令碼 2009-02-02 10:26 2.1、備份指令碼
使用指令碼每天定期執行資料庫備份操作，對每個使用mysql資料庫的人來說都很有必要，這樣的指令碼網上有很多，這裡摘抄一個朋友的指令碼 dbbackup：
這個指令碼每天最多隻執行一次，而且只保留最近五天的備份在伺服器上。

dbbackup程式碼:

#!/bin/bash
#This is a ShellScript. For Auto DB Backup
#Powered by aspbiz
#2004-09

#Setting
#設定資料庫名，資料庫登入名，密碼，備份路徑，日誌路徑，資料檔案位置，以及備份方式
#預設情況下備份方式是tar，還可以是mysqldump,mysqldotcopy
#預設情況下，用root(空)登入mysql資料庫，備份至/root/dbxxxxx.tgz
DBName=mysql
DBUser=root
DBPasswd=
BackupPath=/root/
LogFile=/root/db.log
DBPath=/var/lib/mysql/
#BackupMethod=mysqldump
#BackupMethod=mysqlhotcopy
#BackupMethod=tar
#Setting End

NewFile="$BackupPath"db$(date +%y%m%d).tgz
DumpFile="$BackupPath"db$(date +%y%m%d)
OldFile="$BackupPath"db$(date +%y%m%d --date='5 days ago').tgz

echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
#Delete Old File
if [ -f $OldFile ]
then
rm -f $OldFile >> $LogFile 2>&1
echo "[$OldFile]Delete Old File Success!" >> $LogFile
else
echo "[$OldFile]No Old Backup File!" >> $LogFile
fi

if [ -f $NewFile ]
then
echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile
else
case $BackupMethod in
mysqldump)
     if [ -z $DBPasswd ]
     then
        mysqldump -u $DBUser --opt $DBName > $DumpFile
     else
        mysqldump -u $DBUser -p$DBPasswd --opt $DBName > $DumpFile
     fi
     tar czvf $NewFile $DumpFile >> $LogFile 2>&1
     echo "[$NewFile]Backup Success!" >> $LogFile
     rm -rf $DumpFile
     ;;
mysqlhotcopy)
     rm -rf $DumpFile
     mkdir $DumpFile
     if [ -z $DBPasswd ]
     then
       mysqlhotcopy -u $DBUser $DBName $DumpFile >> $LogFile 2>&1
     else
        mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>&1
     fi
     tar czvf $NewFile $DumpFile >> $LogFile 2>&1
     echo "[$NewFile]Backup Success!" >> $LogFile
     rm -rf $DumpFile
     ;;
*)
     /etc/init.d/mysqld stop >/dev/null 2>&1
     tar czvf $NewFile $DBPath$DBName >> $LogFile 2>&1
     /etc/init.d/mysqld start >/dev/null 2>&1
     echo "[$NewFile]Backup Success!" >> $LogFile
     ;;
esac
fi

echo "-------------------------------------------" >> $LogFile

2.2、放入crontab定期執行dbbackup
假定dbbackup在/root目錄下。我們透過使用crontab命令，設定每天0點10分執行/root/dbbakup指令碼。
1、使用crontab –e 編輯crontab
2、在crontab中加入：

#back for mysql database
10 0 * * * /root/dbbackup

新增：

1）、把遠端mysql伺服器上的選定表的指定資料匯入到本地的文字檔案中：
[hello@localhost hello]$ mysqldump -hhostname -uusername -p -w "last>'2007-01-01'" dbname tablename > ./filename
Enter password:

2）、把遠端mysql伺服器上的選定資料庫的全部資料匯入到本地的文字檔案中：
[hello@localhost hello]$ mysqldump -hhostname -uusername -p dbname > ./filename
Enter password:

3）、把遠端mysql伺服器上的選定表的指定資料匯入到伺服器的文字檔案中（所以在本地不能操作如下）：
mysql> use dbname;
mysql> select fieldname into outfile '/home/hello/a.txt' from tablename where last>"2007-1-1";
ERROR 1045: Access denied for user: 'hello@%' (Using password: YES)
mysql>

4)、把遠端mysql伺服器上的選定表的指定資料匯入到本地的文字檔案中:

[hello@localhost hello]$ mysql -e "select fieldname from tablename where last>'2007-1-1'" -hhostname -uusername -p dbname > ./filename
Enter password:

直接複製data目錄其實也可以，但是如果有innodb引擎表的話，還要複製my.ini檔案以及innodb表空間檔案以及他的回滾日誌檔案

mysqldump來備份MYSQL資料庫(指令碼)

相關文章