MySQL 备份和恢复策略.docx
- 文档编号:10705868
- 上传时间:2023-05-27
- 格式:DOCX
- 页数:19
- 大小:23.18KB
MySQL 备份和恢复策略.docx
《MySQL 备份和恢复策略.docx》由会员分享,可在线阅读,更多相关《MySQL 备份和恢复策略.docx(19页珍藏版)》请在冰点文库上搜索。
MySQL备份和恢复策略
MySQL 备份和恢复策略
在数据库表丢失或损坏的情况下,备份你的数据库是很重要的。
如果发生系统崩溃,你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。
本文主要对MyISAM表做备份恢复。
备份策略一:
直接拷贝数据库文件(不推荐)
备份策略二:
使用mysqlhotcopy备份数据库(完全备份,适合小型数据库备份)
备份策略三:
使用mysqldump备份数据库(完全+增量备份,适合中型数据库备份)
备份策略四:
使用主从复制机制(replication)(实现数据库实时备份)
备份策略一、直接拷贝数据库文件
直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。
为了保证数据的一致性,需要在备份文件前,执行以下SQL语句:
FLUSHTABLESWITHREADLOCK;
也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。
这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。
备份策略二、使用mysqlhotcopy备份数据库
mysqlhotcopy是一个PERL程序,最初由TimBunce编写。
它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。
它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上,并且mysqlhotcopy只能用于备份MyISAM表。
本备份策略适合于小型数据库的备份,数据量不大,可以采用mysqlhotcopy程序每天进行一次完全备份。
备份策略布置:
(1)、安装DBD-mysqlperl模块,支持mysqlhotcopy脚本连接到MySQL数据库。
shell>tar-xzvfDBD-mysql-4.005.tar.gz
shell>cdDBD-mysql-4.005
shell>unsetLANG
shell>perlMakefile.PL-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=UserPWD
shell>make
shell>maketest
shell>makeinstall
(2)、设置crontab任务,每天执行备份脚本
shell>crontab-e
03***/root/MySQLBackup/mysqlbackup.sh>/dev/null2>&1
每天凌晨3:
00执行备份脚本。
mysqlbackup.sh注释:
#!
/bin/sh
#Name:
mysqlbackup.sh
#PS:
MySQLDataBaseBackup,Usemysqlhotcopyscript.
#Writeby:
i.Stone
#LastModify:
2007-11-15
#
#定义变量,请根据具体情况修改
#定义脚本所在目录
scriptsDir=`pwd`
#数据库的数据目录
dataDir=/usr/local/mysql/data/
#数据备份目录
tmpBackupDir=/tmp/tmpbackup/
backupDir=/tmp/mysqlbackup/
#用来备份数据库的用户名和密码
mysqlUser=root
mysqlPWD=111111
#定义eMail地址
eMail=alter@
#如果临时备份目录存在,清空它,如果不存在则创建它
if[[-e$tmpBackupDir]];then
rm-rf$tmpBackupDir/*
else
mkdir$tmpBackupDir
fi
#如果备份目录不存在则创建它
if[[!
-e$backupDir]];then
mkdir$backupDir
fi
#清空MySQLBackup.log
if[[-sMySQLBackup.log]];then
cat/dev/null>MySQLBackup.log
fi
#得到数据库备份列表,在此可以过滤不想备份的数据库
fordatabasesin`find$dataDir-typed|\
sed-e"s/\/usr\/local\/mysql\/data\///"|\
sed-e"s/test//"`;do
if[[$databases==""]];then
continue
else
#备份数据库
/usr/local/mysql/bin/mysqlhotcopy--user=$mysqlUser--password=$mysqlPWD-q"$databases"$tmpBackupDir
dateTime=`date"+%Y.%m.%d%H:
%M:
%S"`
echo"$dateTimeDatabase:
$databasesbackupsuccess!
">>MySQLBackup.log
fi
done
#压缩备份文件
date=`date-I`
cd$tmpBackupDir
tarczf$backupDir/mysql-$date.tar.gz./
#发送邮件通知
if[[-sMySQLBackup.log]];then
catMySQLBackup.log|mail-s"MySQLBackup"$eMail
fi
#使用smbclientmv.sh脚本上传数据库备份到备份服务器
#$scriptsDir/smbclientmv.sh
smbclientmv.sh注释
#!
/bin/sh
#Name:
smbclientmv.sh
#PS:
MovethedatatoBackupServer.
#Writeby:
i.Stone
#LastModify:
2007-11-15
#
#定义变量
#备份服务器名
BackupServer="BackupServerName"
#共享文件夹名
BackupShare="ShareName"
#备份服务器的访问用户名和密码
BackupUser="SMBUser"
BackupPW="SMBPassword"
#定义备份目录
BackupDir=/tmp/mysqlbackup
date=`date-I`
#MovethedatatoBackupServer
smbclient//$BackupServer/$BackupShare\
$BackupPW-d0-WWORKGROUP-U$BackupUser\
-c"put$BackupDir/mysql-$date.tar.gz\
mysql-$date.tar.gz"
#Deletetempfiles
rm-f$BackupDir/mysql-$date.tar.gz
(3)、恢复数据库到备份时的状态
mysqlhotcopy备份出来的是整个数据库目录,使用时可以直接拷贝到mysqld指定的datadir(在这里是/usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:
shell>cp-rfdb_name/usr/local/mysql/data/
shell>chown-Rmysql:
mysql/usr/local/mysql/data/(将db_name目录的属主改成mysqld运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态,要想在崩溃时丢失的数据尽量少应该更频繁的进行备份,要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
备份策略三、使用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$
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL 备份和恢复策略 备份 恢复 策略