MySQL 备份和恢复策略.doc-得力文库

资源描述

《MySQL 备份和恢复策略.doc》由会员分享，可在线阅读，更多相关《MySQL 备份和恢复策略.doc（18页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。本文主要对MyISAM表做备份恢复。备份策略一：直接拷贝数据库文件（不推荐）备份策略二：使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份）备份策略三：使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份）备份策略四：使用主从复制机制(replication)（实现数据库实时备份）脚本下载地址：点击下载脚本备份策略一、直接拷贝数据库文件直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致

2、性，需要在备份文件前，执行以下SQL语句：FLUSHTABLESWITHREADLOCK;也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。备份策略二、使用mysqlhotcopy备份数据库mysqlhotcopy是一个PERL程序，最初由TimBunce编写。它使用LOCKTABLES、FLUSHTABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy只能

3、用于备份MyISAM表。本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。备份策略布置：（1）、安装DBD-mysqlperl模块，支持mysqlhotcopy脚本连接到MySQL数据库。shelltar-xzvfDBD-mysql-4.005.tar.gzshellcdDBD-mysql-4.005shellunsetLANGshellperlMakefile.PL-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=UserPWDshellm

4、akeshellmaketestshellmakeinstall（2）、设置crontab任务，每天执行备份脚本shellcrontab-e03*/root/MySQLBackup/mysqlbackup.sh/dev/null2&1每天凌晨3:00执行备份脚本。mysqlbackup.sh注释：#!/bin/sh# Name:mysqlbackup.sh# PS:MySQL DataBase Backup,Use mysqlhotcopy script.# Write by:i.Stone# Last Modify:2007-11-15# 定义变量，请根据具体情况修改# 定义脚本所在目录sc

5、riptsDir=pwd# 数据库的数据目录dataDir=/usr/local/mysql/data/# 数据备份目录tmpBackupDir=/tmp/tmpbackup/backupDir=/tmp/mysqlbackup/# 用来备份数据库的用户名和密码mysqlUser=rootmysqlPWD=# 定义eMail地址eMail=alter# 如果临时备份目录存在，清空它，如果不存在则创建它if -e $tmpBackupDir ; then rm -rf $tmpBackupDir/*else mkdir $tmpBackupDirfi# 如果备份目录不存在则创建它if ! -e

6、$backupDir ;then mkdir $backupDirfi# 清空MySQLBackup.logif -s MySQLBackup.log ; then cat /dev/null MySQLBackup.logfi# 得到数据库备份列表，在此可以过滤不想备份的数据库for databases in find $dataDir -type d | sed -e s/usr/local/mysql/data/ | sed -e s/test/; do if $databases = ; then continue else# 备份数据库 /usr/local/mysql/bin/my

7、sqlhotcopy -user=$mysqlUser -password=$mysqlPWD -q $databases $tmpBackupDir dateTime=date +%Y.%m.%d %H:%M:%S echo $dateTime Database:$databases backup success! MySQLBackup.log fidone# 压缩备份文件date=date -Icd $tmpBackupDirtar czf $backupDir/mysql-$date.tar.gz ./# 发送邮件通知if -s MySQLBackup.log ; then cat M

8、ySQLBackup.log | mail -s MySQL Backup $eMailfi# 使用smbclientmv.sh脚本上传数据库备份到备份服务器# $scriptsDir/smbclientmv.shsmbclientmv.sh注释#!/bin/sh# Name:smbclientmv.sh# PS:Move the data to Backup Server.# Write by:i.Stone# Last Modify:2007-11-15# 定义变量# 备份服务器名BackupServer=BackupServerName# 共享文件夹名BackupShare=ShareN

9、ame# 备份服务器的访问用户名和密码BackupUser=SMBUserBackupPW=SMBPassword# 定义备份目录BackupDir=/tmp/mysqlbackupdate=date -I# Move the data to BackupServersmbclient /$BackupServer/$BackupShare $BackupPW -d0 -W WORKGROUP -U $BackupUser -c put $BackupDir/mysql-$date.tar.gz mysql-$date.tar.gz# Delete temp filesrm -f $Backu

10、pDir/mysql-$date.tar.gz(3)、恢复数据库到备份时的状态mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：shell cp -rf db_name /usr/local/mysql/data/shell chown -R mysql:mysql /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃

11、时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。备份策略三、使用mysqldump备份数据库mysqldump是采用SQL级别的备份机制，它将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump比直接拷贝要慢些。关于mysqldump的更详细解释见最后的附录。对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了

12、不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在slave机器上做备份。备份策略布置：（1）、创建备份目录Shellmkdir/tmp/mysqlbackupShellmkdir/tmp/mysqlbackup/daily（2）、启用二进制日志采用binlog的方法相对来说更灵活，省心省力，而且还可以支持增量备份。启用binlog时必须要重启mysqld。首先，关闭mysqld，打开/etc/f，加入以下几行：mysqldlog-bin然后启动mysqld就可以了。运行过程中会产生HOSTNAME-bin.以及HOSTNAME-bin.

13、index，前面的文件是mysqld记录所有对数据的更新操作，后面的文件则是所有binlog的索引，都不能轻易删除。关于binlog的更详细信息请查看手册。（3）、配置SSH密钥登录，用于将MySQL备份传送到备份服务器（如果备份服务器为Windows，请跳过此部）。1）、在MySQL所在服务器（192.168.0.20）生成SSH密钥rootlab#ssh-keygen-trsaGeneratingpublic/privatersakeypair.Enterfileinwhichtosavethekey(/root/.ssh/id_rsa):/直接回车Enterpassphrase(empt

14、yfornopassphrase):/直接回车，不使用密码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:1crootlab2）、在备份服务器（192.168.0.200）上创建目录，修改权限，并传送公钥。rootlab#ssh192.168.0.

15、200mkdir.ssh;chmod0700.sshTheauthenticityofhost192.168.0.200(192.168.0.200)cantbeestablished.RSAkeyfingerprintis37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d.Areyousureyouwanttocontinueconnecting(yes/no)?yesWarning:Permanentlyadded192.168.0.200(RSA)tothelistofknownhosts.root192.168.0.200spassword:

16、/输入备份服务器的root密码rootlab#scp.ssh/id_rsa.pub192.168.0.200:.ssh/authorized_keys2root192.168.0.200spassword:id_rsa.pub100%2180.2KB/s00:003）、测试SSH登录rootlab#ssh192.168.0.200/测试SSH登录Lastlogin:FriNov1610:34:022007from192.168.0.20rootlib#（4）、设置crontab任务，每天执行备份脚本shellcrontab-e#每个星期日凌晨3:00执行完全备份脚本03*0/root/MySQ

17、LBackup/mysqlFullBackup.sh/dev/null2&1#周一到周六凌晨3:00做增量备份03*1-6/root/MySQLBackup/mysqlDailyBackup.sh/dev/null2&1mysqlFullBackup.sh注释：#!/bin/sh# Name:mysqlFullBackup.sh# PS:MySQL DataBase Full Backup.# Write by:i.Stone# Last Modify:2007-11-17# Use mysqldump -help get more detail.# 定义变量，请根据具体情况修改# 定义脚本目

18、录scriptsDir=pwd# 定义数据库目录mysqlDir=/usr/local/mysql# 定义用于备份数据库的用户名和密码user=rootuserPWD=# 定义备份目录dataBackupDir=/tmp/mysqlbackup# 定义邮件正文文件eMailFile=$dataBackupDir/email.txt# 定义邮件地址eMail=alter# 定义备份日志文件logFile=$dataBackupDir/mysqlbackup.logDATE=date -Iecho $eMailFileecho $(date +%y-%m-%d %H:%M:%S) $eMailFi

19、lecd $dataBackupDir# 定义备份文件名dumpFile=mysql_$DATE.sqlGZDumpFile=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-l

20、ogs -x $dumpFile# 压缩备份文件if $? = 0 ; then tar czf $GZDumpFile $dumpFile $eMailFile 2&1 echo BackupFileName:$GZDumpFile $eMailFile echo DataBase Backup Success! $eMailFile rm -f $dumpFile# Delete daily backup files. cd $dataBackupDir/daily rm -f *# Delete old backup files(mtime2). $scriptsDir/rmBackup

21、.sh# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉# Move Backup Files To Backup Server.#适合Linux（MySQL服务器）到Linux（备份服务器） $scriptsDir/rsyncBackup.sh if ( !$? ); then echo Move Backup Files To Backup Server Success! $eMailFile else echo Move Backup Files To Backup Server Fail! $eMailFile fielse echo DataBase

22、 Backup Fail! $emailFilefi# 写日志文件echo - $logFilecat $eMailFile $logFile# 发送邮件通知cat $eMailFile | mail -s MySQL Backup $eMailmysqlDailyBackup.sh注释：#!/bin/sh# Name:mysqlDailyBackup.sh# PS:MySQL DataBase Daily Backup.# Write by:i.Stone# Last Modify:2007-11-17# 定义变量，请根据具体情况修改# 定义数据库目录和数据目录scriptsDir=pwdm

23、ysqlDir=/usr/local/mysqldataDir=$mysqlDir/data# 定义用于备份数据库的用户名和密码user=rootuserPWD=# 定义备份目录，每日备份文件备份到$dataBackupDir/dailydataBackupDir=/tmp/mysqlbackupdailyBackupDir=$dataBackupDir/daily# 定义邮件正文文件eMailFile=$dataBackupDir/email.txt# 定义邮件地址eMail=alter# 定义日志文件logFile=$dataBackupDir/mysqlbackup.log# 得到数据库

24、所在主机的主机名HOSTNAME=uname -n#echo $eMailFileecho $(date +%y-%m-%d %H:%M:%S) $eMailFile# 刷新日志，使数据库使用新的二进制日志文件$mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logscd $dataDir# 得到二进制日志列表fileList=cat $HOSTNAME-bin.indexiCounter=0for file in $fileListdo iCounter=expr $iCounter + 1donenextNum=0iFile=0for fi

25、le in $fileListdo binLogName=basename $file nextNum=expr $nextNum + 1# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件） if $nextNum = $iCounter ; then echo Skip lastest! /dev/null else dest=$dailyBackupDir/$binLogName# 跳过已经备份的二进制日志文件 if -e $dest ; then echo Skip exist $binLogName! /dev/null else# 备份日志文件到备份目录 cp $binLog

26、Name $dailyBackupDir if $? = 0 ; then iFile=expr $iFile + 1 echo $binLogName Backup Success! $eMailFile fi fi fidoneif $iFile = 0 ;then echo No Binlog Backup! $eMailFileelse echo Backup $iFile File(s). $eMailFile echo Backup MySQL Binlog OK! $eMailFile# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉# Move

27、 Backup Files To Backup Server.#适合Linux（MySQL服务器）到Linux（备份服务器） $scriptsDir/rsyncBackup.sh if $? = 0 ; then echo Move Backup Files To Backup Server Success! $eMailFile else echo Move Backup Files To Backup Server Fail! $eMailFile fifi# 发送邮件通知cat $eMailFile | mail -s MySQL Backup $eMail# 写日志文件echo - $

28、logFilecat $eMailFile $logFilersyncBackup.sh注释：#!/bin/sh# Name:rsyncBackup.sh# PS:Move Backup Files To Backup Server.# Write by:i.Stone# Last Modify:2007-11-17# 请根据具体情况修改，注意最后有“/”# 定义数据库备份目录dataBackupDir=/tmp/mysqlbackup/# 定义备份服务器上存放备份数据的目录backupServerDir=/root/mysqlbackup/# 定义备份服务器backupServer=192.

29、168.0.200# 同步备份文件到备份服务器rsync -a -delete $dataBackupDir -e ssh $backupServer:$backupServerDir /dev/null 2&1rmBackup.sh注释：#!/bin/sh# Name:rmBackup.sh# PS:Delete old Backup.# Write by:i.Stone# Last Modify:2007-11-15# 定义备份目录dataBackupDir=/tmp/mysqlbackup# 删除mtime2的日志备份文件find $dataBackupDir -name mysql_*

30、.gz -type f -mtime +2 -exec rm ; /dev/null 2&1（5）、恢复数据库到备份时的状态用mysqldump备份出来的文件是一个可以直接倒入的SQL脚本,直接用mysql客户端导入就可以了。/usr/local/mysql/bin/mysql-uroot-pUserPWDdb_name GRANT REPLICATION SLAVE ON *.* - TO replication% IDENTIFIED BY slavepass;3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句：mysql FLUSH TABLES W

31、ITH READ LOCK；保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。shell cd /usr/local/mysql/shell tar -cvf /tmp/mysql-snapshot.tar ./data如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。当FLUSH TABLES WITH READ LOCK所置读锁定有效时（即mysql客户端程序不退出)，读取主服务器上当前的二进制日志名和偏移量值：

33、TUS显示的日志名和位置值为空。在这种情况下，当以后指定从服务器的日志文件和位置时需要使用的值为空字符串()和4.取得快照并记录日志名和偏移量后，回到前一中端重新启用写活动：mysql UNLOCK TABLES；4、确保主服务器主机上f文件的mysqld部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项，其中master_id必须为1到2321之间的一个正整数值。例如：mysqldlog-binserver-id=1如果没有提供那些选项，应添加它们并重启服务器。5、停止从服务器上的mysqld服务并在其f文件中添加下面的行：mysqldserver-i

34、d=2slave_id值同Master_id值一样，必须为1到2321之间的一个正整数值。并且，从服务器的ID必须与主服务器的ID不相同。6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件，如同在主服务器上一样。Shell chown -R mysql:mysql /usr/local/mysql/data7、启动从服务器。在从服务器上执行下面的语句，用你的系统的实际值替换选项值： mysql CHANGE MASTER TO -MASTER_HOST=master_host_name, -MASTER_USER=replication_user

35、_name, -MASTER_PASSWORD=replication_password, -MASTER_LOG_FILE=recorded_log_file_name, -MASTER_LOG_POS=recorded_log_position;8、启动从服务器线程： mysql START SLAVE；执行这些程序后，从服务器应连接主服务器，并补充自从快照以来发生的任何更新。9、如果出现复制错误，从服务器的错误日志（HOSTNAME.err）中也会出现错误消息。10、从服务器复制时，会在其数据目录中发现文件master.info和HOSTNAME- relay-log.info。从服务器使用这两个文件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件，除非你确切知你正在做什么并完全理解其意义。即使这样，最好是使用CHANGE MASTER TO语句。

展开阅读全文