今天有朋友问我有没有最简单的mysql备份的方法，所以想到以前的mysql都是通过的手工备份或者是备份到服务器本地，如果该台服务器over的话，后果就很麻烦了（我是说万一，呵呵，没有那么幸灾乐祸
），写了个自动备份的脚本，目的是备份后将备份文件以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服务器就可以了。
