MySQL_DBA整理

前言
       几乎所有的资料整理都在Linux系统、mySQL5.0.27版本和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.必要说明：
         为了方便程序的升级以及性能更高，应该把MySQL程序与数据文件放在不同的目录下面，最好还是不同的磁盘上。若起用了日志功能，最好是把MySQL程序、数据文件和日志文件分别放在三个不同的磁盘上，从而避免程序升级带来的潜在而致命的问题，提高数据的读写能力（注：毕竟磁盘的转速有限）。
第一部分：数据库的备份与还原
      对于MyISAM 存储引擎的表，由于只有表级的锁，所以目前还没有出现象InnoDB存储引擎的那种可以不缩定表的备份方法。目前用得最多最普遍的两种方法：文件的拷贝与dump数据库。
       文件的拷贝：就是在备份期间锁定数据库表，不允许其他用户进行任何更新操作，然后直接把*.MYD
、*MYI、db.opt文件拷贝到备份的目录下。一般使用免费的工具：mysqlhotcopy，至于如何使用会在备份脚本中给出参考，但是该方法没有办法备份存储过程、函数等，但是另系统库(mysql)一起备份的话，是可以实现备份存储过程、函数等的效果。
       Dump方法：也是非常类似，就是把指定数据库的所有数据表结构与数据生成Create 与Insert语句。
       总结：这两种方法各有优劣，需要看根据实际情况使用那种备份方法。
       1>、文件拷贝：此方法备份的速度快，锁定表的时间相对短，且恢复起来也非常快。但是恢复的时候，一般需要在同一种类型的系统、MySQL版本上进行恢复备份的数据库文件。缺乏跨平台的、跨版本等缺点。
       2>、Dump：此方法备份的速度慢，锁定表的时间长，且恢复起来也慢。可是此种备份的文件，具有跨平台、跨版本、跨存储引擎（备份时设置好就可以）等优势。
       介于上面的描述，我们应该是两种方法结合。比如：平时进行完整备份时，可以采用文件拷贝的方式；对于备份的文件需要放到不同类型系统平台的或不同 MySQL版本上再进行还原话，建议采用Dump方式。建议以上两种备份方式可以只作为完整备份的方式，若需要精确到秒的话，可以考虑：
文件拷贝+日志备份 或Dump+日志备份，最好是采用文件拷贝+日志备份。至于备份的频率，需要根据自己的系统特点与数据重要指定。在此介绍一种比较普遍的策略：
       每天的05:00:00左右进行完整备份，然后每小时进行一次日志文件备份。（注意：日志文件太大的话，使用MySQL客户端工具进行重做的话，会比较慢）。

下面给出完使用mysqlhotcopy工具备份与自动还原的代码：
完整备份：
#!/bin/bash
#First,create direct /back
# setting servername
server=test

#the directory of backup
backdir=/back/mysql

#use log file record log
logfile=$backdir/mysqlbak.log

today=`date +%y-%m-%d`
todaydir=$server-$today
backup=$backdir/$todaydir

#the password of mysql
rootpass=123456a
dbname=eugene

#remake the directory of backup
if [ ! -d $backdir ] ; then
   mkdir $backdir
else
   chattr -i $backdir
fi

cd $backdir

if [ ! -f $logfile ] ; then
    touch $logfile
else
    chattr -i $logfile
fi

echo "             " >> $logfile
echo "fully backup starting!" >> $logfile
echo $(date +"%y%m%d %h:%m%s") >> $logfile
echo "             " >> $logfile

mkdir $backup

#backup with database
/usr/local/mysql/bin/mysqlhotcopy $dbname --flushlog -u admin -p $rootpass $backup | logger -t mysqlhotcopy

echo "fully backup database success!" >> $logfile

#compress backup file and deleted unregimented file
cd $backdir
tar -zcf $todaydir.tgz $todaydir/$dbname/
rm -rf $todaydir

#cd $backdir
#Keep Down Thirty Days
for f in `find . -type f -mtime +30 -maxdepth 1`
do
filename=`basename $f`
#can't deleted log file
if [ $f != 'mysqlbak.log' ] ; then
    rm -fr $f
fi
done
#add property
chattr +i $backdir


日志备份：
#!/bin/bash
#everyday and hourly backup log files once
#setting server's name
server=test
#log files's directory
logfile=/usr/local/mysql/var
#the directory of backup
backdir=/back/increment
hourly=`date +"%y-%m-%d-%H-%M-%S"`
today=`date +%y-%m-%d`
todaydir=$backdir/$server-$today
hourlydir=$server-$hourly
backupdir=$todaydir/$hourlydir
rootpass=123456a
#create directory ,but back is exist
#increment is exist or bot exist,General,first It need creatting
if [ ! -d $backdir ] ; then
   mkdir $backdir
else
   chattr -i $backdir
fi
#judge today directory is exist or not exist
if [ ! -d $todaydir ] ; then
    mkdir $todaydir
else
    chattr -i $todaydir
fi
#create directory is using storage log files
mkdir $backupdir
#execute flush logs
/usr/local/mysql/bin/mysqladmin -u admin -p$rootpass flush-logs
#stat mysql-bin log file total, throw off lastly and mysql-bin.index
total1=`ls $logfile/mysql-bin.*|wc -l`
total=$(expr $total1 - 2)
for f in `ls $logfile/mysql-bin.*|head -n $total`
do
  mv $f $backupdir
done
#compress log files
cd $todaydir
#Because file'name include mark of : ,it will make a mistake,So we dispart two-step complete
tar -zcf $hourlydir.tgz $hourlydir/
rm -rf $hourlydir
#deleted overdue files and directs
cd $backdir
#Keep Down Thirty Days;deleted *.tgz file
for d in `find . -type d -mtime +30 -maxdepth 1`
do
  #filename=`basename $f`
  rm -fr $d
done
# alter file and directory  property
chattr +i $backdir










自动还原：
#!/bin/bash
#this is restore database script
#author:eugene
#date:2007-07-26
#***************Define Begin***************************#
#fully backup database file directory
fulldir=/back/mysql
#increment backup log file directory
incrementdir=/back/increment
#it is mysqlbinlog's directory
bindir=/usr/local/mysql/bin
vardir=/usr/local/mysql/var
para=07-08-15
time=04

#setting fully backup database at 05:30
backuptime=05
#databse'sname
dbname=eugene
#root's password
passwrd=123456a
#log file's name
logfile=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 convert 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
                   fi
    esac
fi
echo $day
}

#usage: get discretionary date's yesterday date,parameter:yy-mm-dd
#return: yy-mm-dd
yesterday()
{
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
    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's file
#2---copy last month day's file
#3---copy last year day's file

fullycp()
{
  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 $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 $strdate | 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'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 ] ; then
               echo "success!"
               tar zxvf $f
               cp -rf $fullydir/$f/$dbname $vardir
           fi
        fi
    done
}

#*************** Function End************************#
#alter back/increment directory attribute
chattr -i $incrementdir

year=$(echo $para | awk -F- '{print $1}')
month=$(echo $para | awk -F- '{print $2}')
day=$(echo $para | awk -F- '{print $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
#done

if [ $backuptime  -lt $time ] ; then  #05 < time
 
  #deal with fully backup files with  transfer function fullycp($strdate $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 $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 -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
else
#Because fully backup event is happened in 05:30,so use yesterday's fully backup ,
#and deal with yesterday'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- '{print $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 $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}') #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=$(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
                 elif [ $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     
fi

if [ -f $incrementdir/test.sql ] ; then
   sed '/^ROLLBACK/ d' $incrementdir/test.sql > $incrementdir/template.sql
else
   echo "don't exist backup database files!"
   exit
fi
#start mysql service
#must copy mysql.mysql to /etc/init.d/mysql
#/etc/init.d/mysql start
/usr/local/mysql/bin/mysql -uadmin -p$passwrd $dbname < $incrementdir/template.sql
rm $incrementdir/test.sql
rm $incrementdir/template.sql
#add /back/increment directory attribute
chattr +i $incrementdir
对此申明下：本人的Shell脚本属于现学现卖的那种，我知道这些脚本肯定可以写得更简单，其实我写的自动还原的脚本是一种特殊情况下用的。。。比如有N 多天前的完整备份存在问题，而只有N+1条的完整备份是完好的，所以需要使用N+1前天的完整备份再加上N+1 条到目前的日志完整，若N>=2条的话，日志就有很多了，手工还原就麻烦，这个脚本就有用了。
       对于游戏、金融等行业需要只针对某个帐号的信息进行回档处理，在MySQL5.0及以上的版本就非常容易了，只要根据需要回档的时间，就可以使用此时间段的日志文件进行回档，二进制日志翻译的语句如下：
usr/local/mysql/bin/mysqlbinlog –start_datetime=”2007-10-29 05:21:00” –stop_datetime=” 2007-10-29 05:25:00 ”  eugene.01831 > mysql –uroot –p Eugene
解释下：只要需要翻译某段时间之间的二进制的日志，可以指定start_datetime与stop_datetime，不指定则是把eugene.01831日志文件全部翻译成SQL语句并且通过mysql客户端工具执行，eugene为指定在那个数据库上执行生成的SQL语句。
      以游戏行业恢复某个玩家角色的物品为例解释下，可能会比较好理解。假设A 玩家在2007-10-29 03:24:00至 2007-10-29 03:29:00之间出现装备丢失。
        若update语句的where 条件是根据角色的编号ID，我们可以先根据角色名称找到对应的ID(社为1001)，然后再执行：
usr/local/mysql/bin/mysqlbinlog –start_datetime=” 2007-10-29 03:24:00” –stop_datetime=” 2007-10-29 03:29:00 ”  eugene.01832 > Eugene.sql，再把eugene.sql放到eugene.doc文档中查找下ID=1001的update角色表的语句，复制出来并稍微修改下再利用客端 执行下修改后的update语句就可以实现只更改装备的数据了。