MySQL_DBA整理

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语句就可以实现只更改装备的数据了。