MySQL备份和恢复策略（页）

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

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

3、器上, 并fl. mysqlhotcopy只能用于备份my isam表。本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行 次完全备份。备份策略布置：（1）、安装dbd-mysql perl模块,支持mysqlhotcopy脚本连接到mysql数据库。shcl1> tar -xzvf dbd-mysql-4. 005. tar. gzshel1> cd dbd-mysql-4. 005shel1> unset langshell> perl makefile- pl -mysql_config二/usi71ocal/mysql/b

4、in/mysql_config -testuser=root -testpassword二uscrpwdshell> makeshell> make testshel1> make instal1(2) 、设置crontab任务，每天执行备份脚本shell> crontab -e0 3* * * /root/mysqlbackup/mysq1 backup, sh >/dev/nul1 2>&1每天凌晨3:00执行备份脚本。mysqlbackup. sh 注释：#!/bin/sh# name:mysqlbackup. sh# ps:mysql dat

5、abase backup, use mysqlhotcopy script.# write by:i. stone# last modify:2007-ll-15#定义变量，请根据具体情况修改#定义脚本所在目录scriptsdir='pwd#数据库的数据目录datadir=/usr/local/mysql/data/#数据备份目录tmpbackupdir=/tmp/tmpbackup/backupdir二/tmp/mysqlbackup/#用來备份数据库的用户名和密码mysqluscrtootmysqlpwd=llllll#定义email地址email=altersomode. com

6、#如果临时备份目录存在，清空它，如果不存在则创建它if -e $tmpbackupdir ; thenrm -rf stmpbackupdiraelsemkdir $tmpbackupdirfi#如果备份目录不存在则创建它if ! -e $backupdir ;thenmkdir $backupdirfi# 清空 mysqlbackup. logif -s mysqlbackup. log ; thencat /dev/null >mysqlbackup. logfi#得到数据库备份列表，在此可以过滤不想备份的数据库for databases in find $datadir -type

7、 d | sed e /zs/usr/l ocal /mysql /data/z， | sed -e "s/test/"、 ; doif $databases =; thencontinueelse#备份数据库/usr/local/mysql/bin/mysqlhotcopy -user=$mysqluser -password=$mysqlpwd -q $databases $tmpbackupdirdatetime二'date +%ym. %d %h:%m:%s'echo z/$datetime database: $databases backupsu

8、ccess!z，»mysqlbackup. logfidone#压缩备份文件date二 date tcd $tmpbackupdirtar czf $backupdir/mysql-$date. tar. gz . /#发送邮件通知if -s mysqlbackup. log ; thencat mysqlbackup. log | mail -s "mysql backup" $email fi#使用smbclientmv. sh脚木上传数据库备份到备份服务器# $scriptsdir/smbclientmv. shsmbclientmv. sh 注释#!/bi

9、n/sh# name:smbclientmv. sh# ps:move the data to backup server.# write by:i.stonen last modify:2007-ll-15n#定义变量#备份服务器名backupscrvcr:=zzbackupscrvcrnamcz，#共享文件夹名backupsharesharename"7#备份服务器的访问用户名和密码backupuser=，smbuserz，backuppw=/zsmbpasswordz，#定义备份目录backupdir=/tmp/mysqlbackup date= date t# move th

10、e data to backupserver smbclient /$backupserver/$backupshare $backuppw -do -w workgroup -u $backupuser -c "put $backupdir/mysql-$date. tar. gz mysql-$datc. tar. gz# delete temp filesrm -f $backupdir/mysql-$date. tar. gz(3) 、恢复数据库到备份时的状态mysqlhotcopy备份出来的是整个数据库口录，使用时可以直接拷贝到mysqld 指定的datadir (在这里是

11、/usr/local/mysql/data/) g录下即可,同时要注意 权限的问题，如下例：shel1> cp -rf db_name /usr/1ocal/mysql/data/shell> chown -r mysql:mysql /usr/1 ocal/mysql/delta/ (将 db_ncime 目录的 展主改成mysqld运彳亍用户)本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数 据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制 机制(replication)。备份策略三、使用mysqldump备份数据库mysqldump

12、是采用sql级别的备份机制，它将数据表导成sql脚本文件，在不同 的mysql版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump比肓接拷 贝要慢些。关于mysqldump的更详细解释见最后的附录。对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备 份，每周再做一次完全备份，如此一直重复。而对于重要的r繁忙的系统来说，则可能盂要 每天一次全量备份，每小时一次增量备份，其至更频繁。为了不影响线上业务，实现在线备 份，并且能增最备份，最好的办法就是采用主从复制机制(replication),在slave机器上 做备份。备份策略布置：(1) 、创建备份

13、目录shell> mkdir /tmp/mysqlbackupshell> mkdir /tmp/mysqlbackup/daily(2) 、卅用二进制口志采用binlog的方法相对来说更灵活，省心省力，而且还可以支持增最备份。启用binlog时必须要重启mysqldo首先，关闭mysqld,打开/etc/f,加入以下几 行：mysqldlog-bin然后启动mysqld就可以了。运行过程中会产生hostname-bin.oooool以及hostname-bin.index,前面的文件是mysqld记录所有対数据的更新操作，后面的文件则 是所冇binlog的索引，都不能轻易删除。关

14、于binlog的更详细信息请查看手册。(3) 、配置ssh密钥登录,用于将mysql备份传送到备份服务器(如果备份服务器为windows, 请跳过此部)。1) 、在mysql所在服务器(192.168.0.20)生成ssh密钥rootlab # ssh-keygen -t rsagenerating public/private rsa key pair.enter file in which to save the key (/root/.ssh/id_rsa): /直接回乍enter passphrase (empty for no passphrase):/jfi接回午，不使用密码ent

15、er same passphrase again:/直接冋千，不使用密码your identification has been saved in /root/.ssh/id_rsa.your public key has been saved in /root/.ssh/id_rsa.pub.the key fingerprint is:c2:96:9f:2d:5a:8e:0&42:43:35:2f:85:5e:72:f8:lc rootlab2) 、在备份服务器(192.168. 0. 200)上创建目录，修改权限，并传送公钥。root®lab # ssh 192.168

16、.0.200 "inkdir .ssh;chmod 0700 .ssh"the authenticity of host '19268.0.200 (192.168.0.200)' can't be established.rsa key fingerprint is 37:57:55:cl:32:fl:dd:bb:lb:8a:13:6f:89:fb:b&9d.are you sure you want to continue connecting (yes/no)? yeswarning: permanently added '19

17、2.168.0.200' (rs a) to the list of known hosts.root® 192.168.0.200's password:/输入备份服务器的 root 密码rootlab # scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2root 192.16&().2()0's password:id_rsa.pub100% 2180.2kb/s 00:003) 、测试ssh登录rootlab # ssh 192.168.0.200/测试 ssh 登录last logi

18、n: fri nov 16 10:34:02 2007 from 192.168.0.20root©lib #(4) 、设置crontab任务，每天执行备份脚本shel1> crontab -e#每个星期日凌晨3:00执行完全备份脚本0 3*0 /root/mysqlbackup/mysqlfulibackup. sh >/dev/null 2>&1#周一到周六凌晨3:00做增虽备份0 3* 1-6 /root/mysqlbackup/mysq1 dai 1ybackup. sh >/dev/nul1 2>&1 mysqlfuliback

19、up. sh 注释:#!/bin/sh# name:msqlfulibackup. sh# ps:mysql database full backup.# write by:i. stone # last modify:2007-ll-17 # use mysqldunip -help get more detail.n#定义变量，请根据具体情况修改#定义脚本目录 scriptsdir='pwd#定义数据库目录 mysqldir=/usr/local/mysql#定义用于备份数据库的用户名和密码user二rootuserpwd=llllll#定义备份目录databackupdir=/t

20、mp/mysqlbackup#定义邮件正文文件emaiifile=$databackupdir/emai1. txt#定义邮件地址email=aitersomode com#定义备份日志文件logfile=$databackupdir/mysqlbackup. logdate='date -iecho "” > $emailfileecho $（date +y-%m-%d %h:%m:%s） » $ema订file cd $databackupdi r#定义备份文件名dumpfi1e=mysq1_$date. sqlgzdumpfi1e=mysql_$date

21、. 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-mastert ogs -x > $dumpfilc#压缩备份文件if $?- 0 ; thentar czf $gzdumpfile $dumpfile

22、 >> $emaiifile 2>&1echo "backupfi1ename:$gzdumpfile >> $cmailfileecho "'database backup success!，z >> $emailfilerm -f $dumpfile# delete deiily backup files.cd $databackupdir/dailyrm -f *# delete old backup files (mtime>2).$scriptsdir/rmbackup. sh#如果不需要将备份传送到

23、备份服务器或备份服务器为windows,请将标绿的行 注释掉# move backup files to backup server.#适合linux (mysql服务器)到linux (备份服务器)$scriptsdir/rsyncbackup. sh» $ema订f订e$ema订f订eif ( !$? ); thenecho "move backup files to backup server success!elseecho "move backup files to backup server fail!" >> fielseech

24、o "'database backup fail!" >> $emai lfi lefi#写日志文件echo "$logfilecat $emailfile >> $logfile#发送邮件通知cat $emailf订c | mail -s "mysql backup" $emailmysqldailybackup. sh 注释:#!/bin/sh# name: mysqldail)backup. sh# ps:mysql database daily backup.# write by:i stonen las

25、t modify:2007-ll-17#定义变量，请根据具体情况修改#定义数据库目录和数据目录 scriptsdir='pwdmysqldir=/usr/local/mysqldatadi r二$mysqldi r/data#定义用于备份数据库的用户名和密码user=rootuserpwd=llllll#定义备份口录，每日备份文件备份到$databackupdir/daily databackupdi r=/tmp/mysqlbackupdail ybackupd i r=$databackup) ir/dail#定义邮件正文文件emailfile=$databackupdir/ema

26、il, txt#定义邮件地址email=altersomode. com#定义日志文件logfile=$databackupdir/mysqlbackup log#得到数据库所在主机的主机名hostname' uname -n'#echo > $cmailfilcecho $ (date +%y-%m-%d %h:%m:%s) >> $emailfile#刷新日志，使数据库使用新的二进制日志文件$mysqldir/bin/mysqladmin u$user -p$userpwd flush-logscd $datadir#得到二进制日志列表f订elist=&#

27、39;cat $hostname-bin. indexicounter=0for file in $filelistdoicounter='expr $icounter + 1donenextnum=0ifile=0for file in $filelistdobinlogname='basename $filenextnum二 expr $nextmum + 1#跳过最后一个二进制fl志(数据库当前使用的二进制fl志文件) if $ncxtnum = $icounter ; thenecho "skip lastest!" > /dev/nullel

28、sedest=$dailybackupdir/$binlogname#跳过已经备份的二进制口志文件if -e $dest ; thenecho "skip exist $binlogname!> /dev/nullelse#备份fl志文件到备份目录cp $binlognamc $dailybackupdirif $?= 0 ; then ifile='expr $ifile + 1 echo $binlogname backup success!z，>> $emailfile fififidoneif $if订e 二二 0 ;thenecho "n

29、o binlog backup!" >> $emaiifileelseecho "backup $ifile file（s）.>> $emailfileecho "backup mysql binlog 0k! >> $emailfile#如果不需要将备份传送到备份服务器或备份服务器为windows,请将标绿的行 注释掉# move backup files to backup server#适合linux （mysql服务器）到linux （备份服务器）$scriptsdir/rsyncbackup.shif $?- 0 ;

30、thenecho "move backup fi les to backup server success!z，>> $emai ifile elseecho "move backup files to backup server fail!" >> $emailfilefifi#发送邮件通知cat $cmailfilc | mail -s "mysql backup" $cmail#写日志文件echo »$logfilecat $emaiifile >> $logfile rsyncbackup.

31、sh 注释：#!/bin/sh# name:rsyncbackup. sh# ps:move backup files to backup server.# write by:i stone# last modify:2007-ll-17#请根据具体情况修改，注意最后有#定义数据库备份目录databackupdir=/tmp/mysq1 backup/#定义备份服务器上存放备份数据的目录 backupserverdir=/root/mysqlbackup/#定义备份服务器backupserver二192. 168. 0. 200#同步备份文件到备份服务器rsync -a -delete $da

32、tabackupdir -e ssh sbackupserver: sbackupserverdir > /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#删除mtime>2的日志备份文件find $databackupdir -name mysqlgzz，-type f -mtime +2 -exe

33、c rm ; > /dev/null 2>&1（5）、恢复数据库到备份时的状态用mysqldump备份出来的文件是一个可以直接倒入的sql脚本,直接用mysql客户端导 入就可以了。/usr/local/mysql/bin/mysql -uroot -puserpwd db_name < db_name.sql对于任何可适用的更新li志，将它们作为mysql的输入：% is -t -r - i hostname-bin* i xargs mysq i bin log i mysql -uuser -puserpwdis命令生成更新口志文件的一个单列列表，根据服务器产生

34、它们的次序排序（注意：如果你 修改任何一个文件，你将改变排序次序，这将导致更新h志以错谋的次序被运用。）木套备份策略只能恢复数据库到最示一次备份时的状态，要想在崩溃时丢失的数据尽量少应 该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制（replication） o 如果使用本套备份脚木，将口志文件和数据文件放到不同的磁盘上是一个不错的主义，这样 不仅可以提高数据写入速度，还能使数据更安全。备份策略四、使用主从复制机制(replication)mysql支持单向、异步复制，复制过程屮一个服务器充当主服务器，而-个或多 个其它服务器充当从服务器。主服务器将更新写入二进制f1志文件，

35、并维护f1 志文件的一个索引以跟踪口志循环。当一个从服务器连接到主服务器时，它通知 主服务器从服务器在h志屮读取的最后一次成功更新的位置。从服务器接收从 那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。为什么使用主从复制？1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到 从服务器作为备份。2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的 客户响应吋间。但是不要同吋在主从服务器上进行更新，这样可能引起冲突。3、使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服 务器。在备份过程屮主服务器可以继续处理更新。mysql使用3个

36、线程来执行复制功能(其中1个在主服务器上，另两个在从服务 器上。当发出start slave ii寸，从服务器创建一个i/o线程，以连接主服务器并 让主服务器发送二进制口志o主服务器创建一个线程将二进制口志中的内容发送 到从服务器。从服务器i/o线程读取主服务器binlog dump线程发送的内容并将 该数据拷贝到从服务器数据目录屮的本地文件中，銀屮继日志。第3个线程是 sql线程，从服务器使用此线程读取中继日志并执行日志中包含的更新。show processlist语句可以查询在主服务器上和从服务器上发生的关于复制的信息。默认中继口志使用力ost_namc-rclay-bin. /“7形式的

37、文件名，其中host_namc 是从服务器主机名，皿皿皿是序列号。用连续序列号来创建连续屮继h志文件， 从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。 默认屮继日志索引文件名为host_name-relay-bin. indexq在默认情况，这些文 件在从服务器的数据廿录中被创建。中继fi志与二进制fi志的格式相同，并且可 以用mysqlbinlog读取。当sql线程执行完中继口志中的所有事件后，中继日志 将会被自动删除。从服务器在数据目录中另外创建两个状态文件-master, info和 relay-log. info。状态文件保存在硬盘上，从服务器关闭时不会丢

38、失。下次从服 务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以 及处理自己的中继日志的程度。设置主从复制:1、确保在主服务器和从服务器上安装的mysql版木和同，并且最好是mysql的 最新稳定版本。2、在主服务器上为复制设置一个连接账户。该账户必须授予replication slave 权限。如果账户仅用于复制（推荐这样做），则不需要再授予任何其它权限。mysql> grant replication slave on *-> to 'replication'©'% y ourdomai n. com， identified

39、 by 'slavepass'3、执行flush tables with read lock语句清空所有表和块写入语句：mysql> flush tables with read lock；保持mysql客户端程序不要退出。开启另一个终端对主服务器数据口录做快照。shell> cd /usr/local/mysql/shell> tar -cvf /tmp/mysql-snapshottar /data如果从服务器的用户账户与主服务器的不同，你可能不想复制mysql数据库。在 这种情况下，应从归档中排除该数据库。你也不需要在归档中包括任何日志文件 或者 mas ter. info 或 relay-log info 文何二。当flush tables with read lock所置读锁定冇效时（即mysql客户端程序不退 出），读取主服务器上当前的二进制日志名和偏移量值：mysql > show master status