Linux下mysql数据库的每周完全备份和每天差异备份具体步骤

原创

张语诺 2012-10-09 15:47:00 ©著作权

©著作权归作者所有：来自51CTO博客作者张语诺的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建所需要的文件或目录（完全备份在/backup/mysql下，差异备份在/backup/mysql/daily下）

mkdir -p /backup/mysql
touch /backup/mysql/mysqlbak.log
mkdir -p /backup/mysql/daily
mkdir -p /var/lib/mysql
进入home目录下创建my.cnf

touch my.cnf

vi my.cnf在此文件下写

[mysqladmin]
password =aptech
user= root
[mysqldump]
user=root
password=aptech （设置此文件只有root可读 chmod 600 my.cnf ）

在/etc/my.cnf中 [mysqld]的下一行添加log-bin 重启mysql

完整备份的脚本（vi weekly.sh）

#!/bin/bash
# mysql data backup script
# 2012-10-08
#
# use mysqldump --help,get more detail.
#
BakDir=/backup/mysql
LogFile=/backup/mysql/mysqlbak.log
DATE=`date +%Y%m%d`
echo " " >> $LogFile
echo " " >> $LogFile
echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
cd $BakDir
DumpFile=$DATE.sql
GZDumpFile=$DATE.sql.tgz
mysqldump --quick --lock-tables=0 --all-databases --flush-logs --delete-master-logs --lock-all-tables > $DumpFile
echo "Dump Done" >> $LogFile
tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1
echo "[$GZDumpFile]Backup Success!" >> $LogFile
rm -f $DumpFile
#delete previous daily backup files
cd $BakDir/daily
rm -f *
cd $BakDir
echo "Backup Done!"
echo "please Check $BakDir Directory!"
echo "copy it to your local disk or ftp to somewhere !!!"
ls -al $BakDir

差异备份（下行8和10的zhang，均代表主机名）（vi daily.sh）

#!/bin/bash
#
# mysql binlog backup script
#
/usr/bin/mysqladmin flush-logs
DATADIR=/var/lib/mysql
BAKDIR=/backup/mysql/daily
cd $DATADIR
FILELIST=`cat *-bin.index`
COUNTER=0
for file in $FILELIST
do
COUNTER=`expr $COUNTER + 1 `
done
NextNum=0
for file in $FILELIST
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $COUNTER ]
then
echo "skip lastest"
else
dest=$BAKDIR/$base
if(test -e $dest)
then
echo "skip exist $base"
else
echo "copying $base"
cp $base $BAKDIR
fi
fi
done
echo "backup mysql binlog ok"