Mysql备份策略大概可分为4种:
1) 直接拷贝数据库文件(不推荐)
2) 使用mysqlhotcopy备份数据库
3) 使用mysqldump备份数据库
4) 使用主从复制机制(replication)(实现数据库实时备份)
一般mysqldump使用比较广泛,操作也方便,下面就具体描述mysqldump备份策略。
数据备份分为三步:
1) 定时导出备份数据库并打包成gz压缩包 (Linux shell 脚本 + crontab)
mysqlFullBackup.sh
#
!/bin/sh
# Use mysqldump --help get more detail.
#
# 定义变量,请根据具体情况修改
# 定义脚本目录
scriptsDir = `pwd`
# 定义用于备份数据库的用户名和密码
user = root
userPWD = 123456
# 定义备份数据库名称
dbNames = (database1 database2 database3)
# 定义备份目录
dataBackupDir =/ home / zhanghuihua / backup
# 定义邮件正文文件
eMailFile = $dataBackupDir / log / email . txt
# 定义邮件地址
eMail = Huihua . Zhang @quidos . co . uk
# 定义备份日志文件
logFile = $dataBackupDir / log / mysqlbackup . log
# DATE=`date -I`
DATE = `date - d " now " + %Y%m%d `
echo `date - d " now " " +%Y-%m-%d %H:%M:%S " ` > $eMailFile
for dbName in ${dbNames[ * ]}
do
# 定义备份文件名
dumpFile = $dataBackupDir / db / $dbName - $DATE . sql . gz
# 使用mysqldump备份数据库,请根据具体情况设置参数
mysqldump - u $user - p $userPWD $dbName | gzip > $dumpFile
done
if [[ $ ? == 0 ]]; then
echo " DataBase Backup Success! " >> $eMailFile
else
echo " DataBase Backup Fail! " >> $emailFile
fi
# 写日志文件
echo " ================================ " >> $logFile
cat $eMailFile >> $logFile
echo $dumpFile >> $logFile
# 发送邮件通知
#cat $eMailFile | mail -s "MySQL Backup" $eMail
# Use mysqldump --help get more detail.
#
# 定义变量,请根据具体情况修改
# 定义脚本目录
scriptsDir = `pwd`
# 定义用于备份数据库的用户名和密码
user = root
userPWD = 123456
# 定义备份数据库名称
dbNames = (database1 database2 database3)
# 定义备份目录
dataBackupDir =/ home / zhanghuihua / backup
# 定义邮件正文文件
eMailFile = $dataBackupDir / log / email . txt
# 定义邮件地址
eMail = Huihua . Zhang @quidos . co . uk
# 定义备份日志文件
logFile = $dataBackupDir / log / mysqlbackup . log
# DATE=`date -I`
DATE = `date - d " now " + %Y%m%d `
echo `date - d " now " " +%Y-%m-%d %H:%M:%S " ` > $eMailFile
for dbName in ${dbNames[ * ]}
do
# 定义备份文件名
dumpFile = $dataBackupDir / db / $dbName - $DATE . sql . gz
# 使用mysqldump备份数据库,请根据具体情况设置参数
mysqldump - u $user - p $userPWD $dbName | gzip > $dumpFile
done
if [[ $ ? == 0 ]]; then
echo " DataBase Backup Success! " >> $eMailFile
else
echo " DataBase Backup Fail! " >> $emailFile
fi
# 写日志文件
echo " ================================ " >> $logFile
cat $eMailFile >> $logFile
echo $dumpFile >> $logFile
# 发送邮件通知
#cat $eMailFile | mail -s "MySQL Backup" $eMail
2) 通过ftp 服务定时下载到本地 (Linux shell 脚本 + crontab)
或从服务器上备份数据库到ftp 服务器 (Linux shell 脚本 + crontab)
ftpDownload.sh
#
!/bin/bash
# 定义变量
DATE = `date - d " now " + %m%d%y `
ftpServer = 192.168 . 1.103
ftpUser = zhanghuihua
ftpPasswd = 123456
remoteDir =/ backup / db
localDir =/ home / zhanghuihua / backup
# 开始ftp操作
/ usr / bin / ftp - niv <<!
open $ftpServer
user $ftpUser $ftpPasswd
binary
cd $remoteDir
lcd $localDir
mget database1 - $DATE . sql . gz
mget database2 - $DATE . sql . gz
mget database3 - $DATE . sql . gz
bye
!
# 定义变量
DATE = `date - d " now " + %m%d%y `
ftpServer = 192.168 . 1.103
ftpUser = zhanghuihua
ftpPasswd = 123456
remoteDir =/ backup / db
localDir =/ home / zhanghuihua / backup
# 开始ftp操作
/ usr / bin / ftp - niv <<!
open $ftpServer
user $ftpUser $ftpPasswd
binary
cd $remoteDir
lcd $localDir
mget database1 - $DATE . sql . gz
mget database2 - $DATE . sql . gz
mget database3 - $DATE . sql . gz
bye
!
dbPutToFtp.sh
#
!/bin/bash
# 定义变量
DATE = `date - d " now " + %Y%m%d `
ftpServer = 192.168 . 1.103
ftpUser = zhanghuihua
ftpPasswd = 123456
remoteDir =/ backup / db
localDir =/ home / zhanghuihua / backup / db
# 开始ftp操作
/ usr / bin / ftp - niv <<!
open $ftpServer
user $ftpUser $ftpPasswd
binary
cd $remoteDir
lcd $localDir
mput database1 - $DATE . sql . gz
mput database2 - $DATE . sql . gz
mput database3 - $DATE . sql . gz
bye
!
# 定义变量
DATE = `date - d " now " + %Y%m%d `
ftpServer = 192.168 . 1.103
ftpUser = zhanghuihua
ftpPasswd = 123456
remoteDir =/ backup / db
localDir =/ home / zhanghuihua / backup / db
# 开始ftp操作
/ usr / bin / ftp - niv <<!
open $ftpServer
user $ftpUser $ftpPasswd
binary
cd $remoteDir
lcd $localDir
mput database1 - $DATE . sql . gz
mput database2 - $DATE . sql . gz
mput database3 - $DATE . sql . gz
bye
!
3) 定期刻录光盘 (人工操作)
3) 定期刻录光盘 (人工操作)
Crontab 定时任务管理
crontab -e
01 03 * * * /var/proftpd_data/backup/bin/mysqlFullBackup.sh
10 04 * * * /var/proftpd_data/backup/bin/ftpDownload.sh
前五个字段的整数取值范围及意义是:
0~59 表示分
1~23 表示小时
1~31 表示日
1~12 表示月份
0~6 表示星期(其中0表示星期日)