使用mysqldump备份数据库.docx
《使用mysqldump备份数据库.docx》由会员分享,可在线阅读,更多相关《使用mysqldump备份数据库.docx(8页珍藏版)》请在冰豆网上搜索。
使用mysqldump备份数据库
备份策略三、使用mysqldump备份数据库
mysqldump是采用SQL级别的备份机制,它将数据表导成SQL脚本文件,在不同的MySQL版本之间升级时相对比较合适,这也是最常用的备份方法。
mysqldump比直接拷贝要慢些。
关于mysqldump的更详细解释见最后的附录。
对于中等级别业务量的系统来说,备份策略可以这么定:
第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。
而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。
为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在slave机器上做备份。
备份策略布置:
(1)、创建备份目录
Shell>mkdir/tmp/mysqlbackup
Shell>mkdir/tmp/mysqlbackup/daily
(2)、启用二进制日志
采用binlog的方法相对来说更灵活,省心省力,而且还可以支持增量备份。
启用binlog时必须要重启mysqld。
首先,关闭mysqld,打开/etc/f,加入以下几行:
[mysqld]
log-bin
然后启动mysqld就可以了。
运行过程中会产生HOSTNAME-bin.000001以及HOSTNAME-bin.index,前面的文件是mysqld记录所有对数据的更新操作,后面的文件则是所有binlog的索引,都不能轻易删除。
关于binlog的更详细信息请查看手册。
(3)、配置SSH密钥登录,用于将MySQL备份传送到备份服务器(如果备份服务器为Windows,请跳过此部)。
1)、在MySQL所在服务器(192.168.0.20)生成SSH密钥
[root@lab~]#ssh-keygen-trsa
Generatingpublic/privatersakeypair.
Enterfileinwhichtosavethekey(/root/.ssh/id_rsa):
//直接回车
Enterpassphrase(emptyfornopassphrase):
//直接回车,不使用密码
Entersamepassphraseagain:
//直接回车,不使用密码
Youridentificationhasbeensavedin/root/.ssh/id_rsa.
Yourpublickeyhasbeensavedin/root/.ssh/id_rsa.pub.
Thekeyfingerprintis:
c2:
96:
9f:
2d:
5a:
8e:
08:
42:
43:
35:
2f:
85:
5e:
72:
f8:
1croot@lab
2)、在备份服务器(192.168.0.200)上创建目录,修改权限,并传送公钥。
[root@lab~]#ssh192.168.0.200"mkdir.ssh;chmod0700.ssh"
Theauthenticityofhost'192.168.0.200(192.168.0.200)'can'tbeestablished.
RSAkeyfingerprintis37:
57:
55:
c1:
32:
f1:
dd:
bb:
1b:
8a:
13:
6f:
89:
fb:
b8:
9d.
Areyousureyouwanttocontinueconnecting(yes/no)?
yes
Warning:
Permanentlyadded'192.168.0.200'(RSA)tothelistofknownhosts.
root@192.168.0.200'spassword:
//输入备份服务器的root密码
[root@lab~]#scp.ssh/id_rsa.pub192.168.0.200:
.ssh/authorized_keys2
root@192.168.0.200'spassword:
id_rsa.pub100%2180.2KB/s00:
00
3)、测试SSH登录
[root@lab~]#ssh192.168.0.200//测试SSH登录
Lastlogin:
FriNov1610:
34:
022007from192.168.0.20
[root@lib~]#
(4)、设置crontab任务,每天执行备份脚本
shell>crontab-e
#每个星期日凌晨3:
00执行完全备份脚本
03**0/root/MySQLBackup/mysqlFullBackup.sh>/dev/null2>&1
#周一到周六凌晨3:
00做增量备份
03**1-6/root/MySQLBackup/mysqlDailyBackup.sh>/dev/null2>&1
mysqlFullBackup.sh注释:
#!
/bin/sh
#Name:
mysqlFullBackup.sh
#PS:
MySQLDataBaseFullBackup.
#Writeby:
i.Stone
#LastModify:
2007-11-17
#
#Usemysqldump--helpgetmoredetail.
#
#定义变量,请根据具体情况修改
#定义脚本目录
scriptsDir=`pwd`
#定义数据库目录
mysqlDir=/usr/local/mysql
#定义用于备份数据库的用户名和密码
user=root
userPWD=111111
#定义备份目录
dataBackupDir=/tmp/mysqlbackup
#定义邮件正文文件
eMailFile=$dataBackupDir/email.txt
#定义邮件地址
eMail=alter@
#定义备份日志文件
logFile=$dataBackupDir/mysqlbackup.log
DATE=`date-I`
echo"">$eMailFile
echo$(date+"%y-%m-%d%H:
%M:
%S")>>$eMailFile
cd$dataBackupDir
#定义备份文件名
dumpFile=mysql_$DATE.sql
GZDumpFile=mysql_$DATE.sql.tar.gz
#使用mysqldump备份数据库,请根据具体情况设置参数
$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\
--delete-master-logs\
-x>$dumpFile
#压缩备份文件
if[[$?
==0]];then
tarczf$GZDumpFile$dumpFile>>$eMailFile2>&1
echo"BackupFileName:
$GZDumpFile">>$eMailFile
echo"DataBaseBackupSuccess!
">>$eMailFile
rm-f$dumpFile
#Deletedailybackupfiles.
cd$dataBackupDir/daily
rm-f*
#Deleteoldbackupfiles(mtime>2).
$scriptsDir/rmBackup.sh
#如果不需要将备份传送到备份服务器或备份服务器为Windows,请将标绿的行注释掉
#MoveBackupFilesToBackupServer.
#适合Linux(MySQL服务器)到Linux(备份服务器)
$scriptsDir/rsyncBackup.sh
if((!
$?
));then
echo"MoveBackupFilesToBackupServerSuccess!
">>$eMailFile
else
echo"MoveBackupFilesToBackupServerFail!
">>$eMailFile
fi
else
echo"DataBaseBackupFail!
">>$emailFile
fi
#写日志文件
echo"--------------------------------------------------------">>$logFile
cat$eMailFile>>$logFile
#发送邮件通知
cat$eMailFile|mail-s"MySQLBackup"$eMail
mysqlDailyBackup.sh注释:
#!
/bin/sh
#Name:
mysqlDailyBackup.sh
#PS:
MySQLDataBaseDailyBackup.
#Writeby:
i.Stone
#LastModify:
2007-11-17
#
#定义变量,请根据具体情况修改
#定义数据库目录和数据目录
scriptsDir=`pwd`
mysqlDir=/usr/local/mysql
dataDir=$mysqlDir/data
#定义用于备份数据库的用户名和密码
user=root
userPWD=111111
#定义备份目录,每日备份文件备份到$dataBackupDir/daily
dataBackupDir=/tmp/mysqlbackup
dailyBackupDir=$dataBackupDir/daily
#定义邮件正文文件
eMailFile=$dataBackupDir/email.txt
#定义邮件地址
eMail=alter@
#定义日志文件
logFile=$dataBackupDir/mysqlbackup.log
#得到数据库所在主机的主机名
HOSTNAME=`uname-n`
#
echo"">$eMailFile
echo$(date+"%y-%m-%d%H:
%M:
%S")>>$eMailFile
#
#刷新日志,使数据库使用新的二进制日志文件
$mysqlDir/bin/mysqladmin-u$user-p$userPWDflush-logs
cd$dataDir
#得到二进制日志列表
fileList=`cat$HOSTNAME-bin.index`
iCounter=0
forfilein$fileList
do
iCounter=`expr$iCounter+1`
done
nextNum=0
iFile=0
forfilein$fileList
do
binLogName=`basename$file`
nextNum=`expr$nextNum+1`
#跳过最后一个二进制日志(数据库当前使用的二进制日志文件)
if[[$nextNum==$iCounter]];then
echo"Skiplastest!
">/dev/null
else
dest=$dailyBackupDir/$binLogName
#跳过已经备份的二进制日志文件
if[[-e$dest]];then
echo"Skipexist$binLogName!
">/dev/null
else
#备份日志文件到备份目录
cp$binLogName$dailyBackupDir
if[[$?
==0]];then
iFile=`expr$iFile+1`
echo"$binLogNameBackupSuccess!
">>$eMailFile
fi
fi
fi
done
if[[$iFile==0]];then
echo"NoBinlogBackup!
">>$eMailFile
else
echo"Backup$iFileFile(s).">>$eMailFile
echo"BackupMySQLBinlogOK!
">>$eMailFile
#如果不需要将备份传送到备份服务器或备份服务器为Windows,请将标绿的行注释掉
#MoveBackupFilesToBackupServer.
#适合Linux(MySQL服务器)到Linux(备份服务器)
$scriptsDir/rsyncBackup.sh
if[[$?
==0]];then
echo"MoveBackupFilesToBackupServerSuccess!
">>$eMailFile
else
echo"MoveBackupFilesToBackupServerFail!
">>$eMailFile
fi
fi
#发送邮件通知
cat$eMailFile|mail-s"MySQLBackup"$eMail
#写日志文件
echo"--------------------------------------------------------">>$logFile
cat$eMailFile>>$logFile
rsyncBackup.sh注释:
#!
/bin/sh
#Name:
rsyncBackup.sh
#PS:
MoveBackupFilesToBackupServer.
#Writeby:
i.Stone
#LastModify:
2007-11-17
#
#请根据具体情况修改,注意最后有“/”
#定义数据库备份目录
dataBackupDir=/tmp/mysqlbackup/
#定义备份服务器上存放备份数据的目录
backupServerDir=/root/mysqlbackup/
#定义备份服务器
backupServer=192.168.0.200
#
#同步备份文件到备份服务器
rsync-a--delete$dataBackupDir-essh$backupServer:
$backupServerDir>/dev/null2>&1
rmBackup.sh注释:
#!
/bin/sh
#Name:
rmBackup.sh
#PS:
DeleteoldBackup.
#Writeby:
i.Stone
#LastModify:
2007-11-15
#
#定义备份目录
dataBackupDir=/tmp/mysqlbackup
#删除mtime>2的日志备份文件
find$dataBackupDir-name"mysql_*.gz"-typef-mtime+2-execrm{}\;>/dev/null2>&1
(5)、恢复数据库到备份时的状态
用mysqldump备份出来的文件是一个可以直接倒入的SQL脚本,直接用mysql客户端导入就可以了。
/usr/local/mysql/bin/mysql-uroot-pUserPWDdb_name对于任何可适用的更新日志,将它们作为mysql的输入:
%ls-t-r-1HOSTNAME-bin*|xargsmysqlbinlog|mysql-uUser-pUserPWD
ls命令生成更新日志文件的一个单列列表,根据服务器产生它们的次序排序(注意:
如果你修改任何一个文件,你将改变排序次序,这将导致更新日志以错误的次序被运用。
)
本套备份策略只能恢复数据库到最后一次备份时的状态,要想在崩溃时丢失的数据尽量少应该更频繁的进行备份,要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
如果使用本套备份脚本,将日志文件和数据文件放到不同的磁盘上是一个不错的主义,这样不仅可以提高数据写入速度,还能使数据更安全。