MySQL_DBA整理
分类： Mysql/postgreSQL
转自：丁丁：http://www.itpub.net/thread-831154-2-1.html
mysql_dba_备份与还原部分介绍.doc (72 KB)
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语句就可以实现只更改装备的数据了。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
