linux下mysql自动远程备份

Share

     今天有朋友问我有没有最简单的mysql备份的方法,所以想到以前的mysql都是通过的手工备份或者是备份到服务器本地,如果该台服务器over的话,后果就很麻烦了(我是说万一,呵呵,没有那么幸灾乐祸shy),写了个自动备份的脚本,目的是备份后将备份文件以ftp或者发邮件的形式保存到远程服务器,这里,用邮件的方式发送备份脚本到指定的Email账户我使用的是mutt+msmpt+shell,mutt和shell 在 linux里面都是自带的,msmpt需要一下的包:
openssl-0.9.7-1.src.rpm
msmtp-1.4.3-2.i586.rpm

一、ftp远程备份
编写如下脚本
#vi autoBakMysql.sh


#!/bin/bash
#Auto DB Backup shell
#Powered by leo

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

echo “start ftp backup file”

ftp -i -in <open 192.168.7.9 21
user username password
cd /
mput $DumpFile
bye
echo “#################### send success! ##################” >;>; $LogFile

二、邮件发送远程备份
首先安装上面下载的文件,然后做下面的配置

vi /etc/Muttrc

set sendmail=”/usr/bin/msmtp -a your_account”
set realname=”your_email”
set use_from=yes
set editor=”vim”

MSMTP

创建user_home/.msmtprc和 user_home/.msmtp.log,分别为配置和日志文件。

vi msmtprc

account default
host smtp.domain.com
from your_email_address
tls on
auth on
user user_name@domain.com
password your_password

account default : dominic

由于password是明码,所以我们需要修改此文件的权限。

chmod 600 .msmtprc

到这里,你可以使用mutt来发送邮件了,我们测试一下。
echo “test” |mutt -s “my_first_test” anyone@163.com -c anytwo@163.com

-s “subject”
-c “carbon-copy”

观察.msmtp.log文件,如果有错会在日志文件中被报告出来,当然,成功发送的日志也会出现在此日志文件内。

编写如下脚本
#vi autoBakMysql.sh

#!/bin/bash
#Auto DB Backup shell
#Powered by leo

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

echo “start email backup file”

echo “test” |mutt -s “mysql_backup_file” anyone@163.com -c anytwo@163.com -s $DumpFile

echo “#################### send success! ##################” >;>; $LogFile

ok,以后你就睡大觉吧,只要检查你的收件邮箱或者是你的备份ftp服务器就可以了。