mysql_dba_备份与还原部分介绍

1、MySQL_DBA整理前言几乎所有的资料整理都在Linux系统、和MyISAM存储引擎环境下试验和总结的，以及参考前人经验与MySQL官网资料进行总结的。关于MySQL 的安装与升级，我相信很多MySQL_DBA都比我厉害，MySQL官网与其他网站上应该有很多相关资料：关于安装写一条简单的命令与一些必要的说明：1. 源码安装的脚本./configure -prefix=/usr/local/mysql -with-mysqld-user=mysql -with-charset=gb2312 -with-extra-charsets=all由于我的数据库只使用gb2312，所以为了一致性就这样设

2、置，你也可以设置为自己的类型。2.必要说明：为了方便程序的升级以及性能更高，应该把MySQL程序与数据文件放在不同的目录下面，最好还是不同的磁盘上。若起用了日志功能，最好是把MySQL程序、数据文件和日志文件分别放在三个不同的磁盘上，从而避免程序升级带来的潜在而致命的问题，提高数据的读写能力（注：毕竟磁盘的转速有限）。第一部分：数据库的备份与还原对于MyISAM 存储引擎的表，由于只有表级的锁，所以目前还没有出现象InnoDB存储引擎的那种可以不缩定表的备份方法。目前用得最多最普遍的两种方法：文件的拷贝与dump数据库。文件的拷贝：就是在备份期间锁定数据库表，不允许其他用户进行任何更新操作，然

3、后直接把*.MYD、*MYI、db.opt文件拷贝到备份的目录下。一般使用免费的工具：mysqlhotcopy，至于如何使用会在备份脚本中给出参考，但是该方法没有办法备份存储过程、函数等，但是另系统库(mysql)一起备份的话，是可以实现备份存储过程、函数等的效果。Dump方法：也是非常类似，就是把指定数据库的所有数据表结构与数据生成Create 与Insert语句。总结：这两种方法各有优劣，需要看根据实际情况使用那种备份方法。1>、文件拷贝：此方法备份的速度快，锁定表的时间相对短，且恢复起来也非常快。但是恢复的时候，一般需要在同一种类型的系统、MySQL版本上进行恢复备份的数据库文件。

4、缺乏跨平台的、跨版本等缺点。2>、Dump：此方法备份的速度慢，锁定表的时间长，且恢复起来也慢。可是此种备份的文件，具有跨平台、跨版本、跨存储引擎（备份时设置好就可以）等优势。介于上面的描述，我们应该是两种方法结合。比如：平时进行完整备份时，可以采用文件拷贝的方式；对于备份的文件需要放到不同类型系统平台的或不同 MySQL版本上再进行还原话，建议采用Dump方式。建议以上两种备份方式可以只作为完整备份的方式，若需要精确到秒的话，可以考虑：文件拷贝+日志备份 或Dump+日志备份，最好是采用文件拷贝+日志备份。至于备份的频率，需要根据自己的系统特点与数据重要指定。在此介绍一种比较普遍的策略

5、： 每天的05:00:00左右进行完整备份，然后每小时进行一次日志文件备份。（注意：日志文件太大的话，使用MySQL客户端工具进行重做的话，会比较慢）。下面给出完使用mysqlhotcopy工具备份与自动还原的代码：完整备份：#!/bin/bash#First,create direct /back# setting servernameserver=test#the directory of backupbackdir=/back/mysql#use log file record loglogfile=$backdir/mysqlbak.logtoday=date +%y-%m-%dtod

6、aydir=$server-$todaybackup=$backdir/$todaydir#the password of mysqlrootpass=123456adbname=eugene#remake the directory of backupif ! -d $backdir ; then mkdir $backdirelse chattr -i $backdirficd $backdirif ! -f $logfile ; then touch $logfileelse chattr -i $logfilefiecho " " >> $logfile

7、echo "fully backup starting!" >> $logfileecho $(date +"%y%m%d %h:%m%s") >> $logfileecho " " >> $logfilemkdir $backup#backup with database/usr/local/mysql/bin/mysqlhotcopy $dbname -flushlog -u admin -p $rootpass $backup | logger -t mysqlhotcopyecho &quo

8、t;fully backup database success!" >> $logfile#compress backup file and deleted unregimented file cd $backdirtar -zcf $todaydir.tgz $todaydir/$dbname/rm -rf $todaydir#cd $backdir#Keep Down Thirty Daysfor f in find . -type f -mtime +30 -maxdepth 1do filename=basename $f #can't deleted l

9、og file if $f != 'mysqlbak.log' ; then rm -fr $f fidone#add propertychattr +i $backdir日志备份：#!/bin/bash#everyday and hourly backup log files once#setting server's nameserver=test#log files's directorylogfile=/usr/local/mysql/var#the directory of backupbackdir=/back/incrementhourly=dat

10、e +"%y-%m-%d-%H-%M-%S"today=date +%y-%m-%dtodaydir=$backdir/$server-$todayhourlydir=$server-$hourlybackupdir=$todaydir/$hourlydirrootpass=123456a#create directory ,but back is exist#increment is exist or bot exist,General,first It need creattingif ! -d $backdir ; then mkdir $backdirelse ch

11、attr -i $backdirfi#judge today directory is exist or not existif ! -d $todaydir ; then mkdir $todaydirelse chattr -i $todaydirfi#create directory is using storage log filesmkdir $backupdir#execute flush logs/usr/local/mysql/bin/mysqladmin -u admin -p$rootpass flush-logs#stat mysql-bin log file total

12、, throw off lastly and mysql-bin.indextotal1=ls $logfile/mysql-bin.*|wc -ltotal=$(expr $total1 - 2)for f in ls $logfile/mysql-bin.*|head -n $totaldo mv $f $backupdirdone#compress log filescd $todaydir#Because file'name include mark of : ,it will make a mistake,So we dispart two-step completetar

13、-zcf $hourlydir.tgz $hourlydir/rm -rf $hourlydir#deleted overdue files and directscd $backdir#Keep Down Thirty Days;deleted *.tgz filefor d in find . -type d -mtime +30 -maxdepth 1do #filename=basename $f rm -fr $ddone# alter file and directory propertychattr +i $backdir自动还原：#!/bin/bash#this is rest

14、ore database script#author:eugene#date:2007-07-26#*Define Begin*#fully backup database file directoryfulldir=/back/mysql#increment backup log file directoryincrementdir=/back/increment#it is mysqlbinlog's directorybindir=/usr/local/mysql/binvardir=/usr/local/mysql/varpara=07-08-15time=04#setting

15、 fully backup database at 05:30backuptime=05#databse'snamedbname=eugene#root's passwordpasswrd=123456a#log file's namelogfile=mysql-bin#* Define End *#* Function Start*#parameter: yy-mm-dd#return: max(day)mon_max_day() day=0#Because parameter of date's format yy-mm-dd,so we must conv

16、ert to yyyy-mm-dd year=$( expr $2 + 2000 ) if $1 -gt 0 -a $1 -lt 13 ; then case $1 in 1|01|3|03|5|05|7|07|8|08|10|12) day=31; 4|04|6|06|9|09|11) day=30; 2|02) if expr $year % 4 -eq 0 ; then if expr $year % 400 -eq 0 ; then day=29 elif expr $year % 100 -eq 0 ; then day=28 else day=29 fi else day=28 f

17、i esacfiecho $day#usage: get discretionary date's yesterday date,parameter:yy-mm-dd#return: yy-mm-ddyesterday() num=$1 year=$(echo $num|awk -F- 'print $1') month=$(echo $num|awk -F- 'print $2') day=$(echo $num|awk -F- 'print $3') day=$(expr $day - 1) if $day -eq 0 ; then

18、month=$(expr $month - 1) if $month -eq 0 ; then year=$(expr $year - 1) month=12 fi day=$(mon_max_day $month $year) value=$year-$month-$day else value=$year-$month-$day fi echo $value#parameter: $1-yy-mm-dd; $2-0 or 1 or 2 or 3; $3-fulldir;$4-vardir#0-copy current day's file,#1-copy yesterday day

19、's file#2-copy last month day's file#3-copy last year day's filefullycp() strdate=$1 num=$2 fullydir=$3 vardir=$4 year=$(echo $strdate | awk -F- 'print $1') #create directory's year month=$(echo $strdate | awk -F- 'print $2') #create directory's month day=$(echo $

20、strdate | awk -F- 'print $3') #create directory's day case $num in 0) ; 1|2|3) strdate=$(yesterday $strdate) year=$(echo $strdate | awk -F- 'print $1') #create directory's year month=$(echo $strdate | awk -F- 'print $2') #create directory's month day=$(echo $strda

21、te | awk -F- 'print $3') #create directory's day ; esac for f in ls $fullydir do #if -d $fullydir/$f ; then filename=basename $f if $filename!='mysqlbak.log' ; then #test catalog include mysqlbak.log,so filt the file num1=$(echo $f | awk -F- 'print $2') #create directory&

22、#39;s year num2=$(echo $f | awk -F- 'print $3') #create directory's month num4=$(echo $f | awk -F- 'print $4') #create directory's day.tgz num3=$(echo $num4 | awk -F. 'print $1') #create directory's day if $year -eq $num1 -a $month -eq $num2 -a $day -eq $num3 ; th

23、en echo "success!" tar zxvf $f cp -rf $fullydir/$f/$dbname $vardir fi fi done#* Function End*#alter back/increment directory attributechattr -i $incrementdiryear=$(echo $para | awk -F- 'print $1')month=$(echo $para | awk -F- 'print $2')day=$(echo $para | awk -F- 'print

24、$3')#shutdown mysql service#Judge mysqld is running or stopping#for d in ps -e|grep mysql|awk ' print $4 '#do# if $d = 'mysqld' ; then# #/etc/init.d/mysql stop# fi#doneif $backuptime -lt $time ; then #05 < time #deal with fully backup files with transfer function fullycp($strd

25、ate $flag $fulldir $vardir) fullycp $para 0 $fulldir $vardir #deal with log files for f1 in ls $incrementdir do num1=$(echo $f1 | awk -F- 'print $2') #create directory's year num2=$(echo $f1 | awk -F- 'print $3') #create directory's month num3=$(echo $f1 | awk -F- 'print

26、$4') #create directory's day if -d $incrementdir/$f1 ; then #judge target is file or director if $year -eq $num1 -a $month -eq $num2 -a $day -eq $num3 ; then for f2 in ls $incrementdir/$f1 do bf2=$(echo $f2 | awk -F: 'print $1') bf3=$(echo $bf2 | awk -F- 'print $5') if $bf3 -

27、le $time ; then #bf3 <= time for f4 in ls $incrementdir/$f1/$f2/$logfile.*|head do $bindir/mysqlbinlog -database=$dbname $f4 >> $incrementdir/test.sql done fi done fi fi doneelse#Because fully backup event is happened in 05:30,so use yesterday's fully backup ,#and deal with yesterday

28、9;s log file and intraday's log files#don't consider restore every month's first day ,must be considered #deal with yesterday's fully backup files with transfer function fullycp($strdate $flag $fulldir $vardir) fullycp $para 1 $fulldir $vardir curryear=$(echo $para | awk -F- 'pri

29、nt $1') currmonth=$(echo $para | awk -F- 'print $2') currday=$(echo $para | awk -F- 'print $3') #get the current's date of yesterday's date lastdate=$(yesterday $para) lastyear=$(echo $lastdate | awk -F- 'print $1') lastmonth=$(echo $lastdate | awk -F- 'print

30、$2') lastday=$(echo $lastdate | awk -F- 'print $3') #need deal with currently and yesterday log files #first,deal with year-mm-(day - 1)'s log files for f1 in ls $incrementdir do if -d $incrementdir/$f1 ; then #judge target is file or director num1=$(echo $f1 | awk -F- 'print $2&

31、#39;) #create directory's year num2=$(echo $f1 | awk -F- 'print $3') #create directory's month num3=$(echo $f1 | awk -F- 'print $4') #create directory's day if $lastyear -eq $num1 -a $lastmonth -eq $num2 -a $lastday -eq $num3 ; then for f2 in ls $incrementdir/$f1 do bf2=$

32、(echo $f2 | awk -F: 'print $1') bf3=$(echo $bf2 | awk -F- 'print $5') if $bf3 -ge $backuptime ; then #bf3 >= backuptime or backuptime - 1 for f4 in ls $incrementdir/$f1/$f2/$logfile.*|head do $bindir/mysqlbinlog -database=$dbname $f4 >> $incrementdir/test.sql done fi done el

33、if $curryear -eq $num1 -a $currmonth -eq $num2 -a $currday -eq $num3 ; then for f2 in ls $incrementdir/$f1 do bf2=$(echo $f2 | awk -F: 'print $1') bf3=$(echo $bf2 | awk -F- 'print $5') if $bf3 -le $time ; then #bf3 <=time for f4 in ls $incrementdir/$f1/$f2/$logfile.*|head do $bindir/mysqlbinlog -database=$dbname $f4 >> $incrementdir/test.sql done fi done fi fi done fiif -f $incrementdir/test.sql ; then sed '/ROLLBACK/ d' $incrementdir/test.sql > $incrementdir/template.sqlelse echo "don't exist backup database files!