Mysql备份策略-完成备份+增量备份shell

<b>--dump</b><b>完全备份</b>

vi /tmp/mysql_full_bak.sh

#!/bin/sh

scriptsdir='pwd'

mysqldir='/usr/local/mysql'

user=root

userpwd=root123

databackupdir=/tmp/mysqlbackup

emailfile=$databackupdir/email.txt

[email protected]

logfile=$databackupdir/mysqlbackup.log

#date='date -i'

date=`date -i`

echo "" &amp;gt; $emailfile

echo $(date +"%y-%m-%d %h:%m:%s") &amp;gt;&amp;gt; $emailfile

cd $databackupdir

dumpfile=mysql_$date.sql

gzdumpfile=mysql_$date.sql.tar.gz

#bakup

$mysqldir/bin/mysqldump -u$user -p$userpwd \

--opt --default-character-set=utf8 --extended-insert=false \

--triggers -r --hex-blob --all-databases \

--flush-logs --delete-master-logs \

-x &amp;gt; $dumpfile

#tar

if [[ $? == 0 ]]; then

tar czf $gzdumpfile $dumpfile &amp;gt;&amp;gt; emailfile 2&amp;gt;&amp;1

echo "backupfilename:$gzdumpfile" &amp;gt;&amp;gt; $emailfile

echo "database backup success!" &amp;gt;&amp;gt; $emailfile

rm -rf $dumpfile

#delete previous daily backup files

cd $databackupdir/daily

rm -f *

# delete old backup files(mtime&amp;gt;2).

#$scriptsdir/rmbackup.sh

#move backup files to backup server.

#适合linux（mysql服务器）到linux（备份服务器）

#$scriptsdir/rsyncbackup.sh

#if (( !$? )); then

#echo "move backup files to backup server success!" &amp;gt;&amp;gt; $emailfile

# else

# echo "move backup files to backup server fail!" &amp;gt;&amp;gt; $emailfile

# fi

#else

#echo "database backup fail!" &amp;gt;&amp;gt; $emailfile

fi

#write log

echo "--------------------------------------------------------" &amp;gt;&amp;gt; $logfile

cat $emailfile &amp;gt;&amp;gt; $logfile

#send imail

cat $emailfile | mail -s "mysql backup" $email

<b>增量备份</b>

vi /tmp/mysql_daily_bak.sh

datadir=$mysqldir/var

dailybackupdir=$databackupdir/daily

hostname=`uname -n`

#刷新日志

$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs

cd $datadir

filelist=`cat mysql-bin.index`

icounter=0

for file in $filelist

do

icounter=`expr $icounter + 1`

done

nextnum=0

ifile=0

binlogname=`basename $file`

nextnum=`expr $nextnum + 1`

if [[ $nextnum == $icounter ]]; then

echo "skip lastest!" &amp;gt; /dev/null

else

dest=$dailybackupdir/$binlogname

#跳过已备份的二进制日志文件

if [[ -e $dest ]]; then

echo "skip exist $binlogname!" &amp;gt; /dev/null

# 备份日志文件到备份目录

cp $binlogname $dailybackupdir

ifile=`expr $ifile + 1`

echo "$binlogname backup success!" &amp;gt;&amp;gt; $emailfile

if [[ $ifile == 0 ]];then

echo "no binlog backup!" &amp;gt;&amp;gt; $emailfile

echo "backup $ifile file(s)." &amp;gt;&amp;gt; $emailfile

echo "backup mysql binlog ok!" &amp;gt;&amp;gt; $emailfile

<b>删除old</b><b>文件</b>

vi /tmp/rmbackup.sh

# name:rmbackup.sh

# ps:delete old backup.

# 定义备份目录

# 删除mtime&amp;gt;2的日志备份文件

find $databackupdir -name "mysql_*.gz" -type f -mtime +2 -exec rm {} \; &amp;gt; /dev/null 2&amp;gt;&amp;1

<b>同步备份到备份服务器</b>

vi /tmp/rsyncbackup.sh

# name:rsyncbackup.sh

#定义数据库备份目录

databackupdir=/tmp/mysqlbackup/

# 定义备份服务器上存放备份数据的目录

backupserverdir=/root/mysqlbackup/

# 定义备份服务器

backupserver=172.16.107.133

# 同步备份文件到备份服务器

rsync -a --delete $databackupdir -e ssh $backupserver:$backupserverdir &amp;gt; /dev/null 2&amp;gt;&amp;1

<b>恢复服务器</b>

全部恢复

mysqldump --user=root -p --all-databases &amp;gt; /backup/mysql.sql

db恢复

/usr/local/mysql/bin/mysql -uroot -puserpwd db_name &amp;lt; db_name.sql

增量恢复

1.对于任何可适用的更新日志，将它们作为 mysql 的输入

% ls -t -r -1 hostname-bin* | xargs mysqlbinlog | mysql -uuser –puserpwd

2.一般恢复

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000052 | mysql -uuser -puserpwd

<b>定制任务</b>

<b>#crontab –e</b>

10 4 * * 1-6 root /tmp/mysql_daily_bak.sh #礼拜一到礼拜六运行每天备份脚本

10 4 * * 0 root /tmp/mysql_full_bak.sh #礼拜天执行全备份的脚本