生产环境MYSQL备份脚本--基于xtrabackup

Environment：
System:CentOS 5.5X64
xtrabackup:http://www.percona.com/downloads/XtraBackup/

mkdir -p /tools/xtrabackup
cd /tools/xtrabackup
tar zvfx percona-xtrabackup-2.0.2-461.tar.gz
mv percona-xtrabackup-2.0.2 /usr/local/xtrabackup
echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile
source /etc/profile

核心脚本如下:其它具体细节大家可以去网上搜索 这里我就不过多的阐述了

 1 [zhangly@mdb xtrabckup]$ cat xdb_192.168.0.194.sh 
 2 #!/bin/sh
 3 #Info:Script about mysql backup
 4 #Date:2012-11-27
 5 
 6    source /etc/profile
 7    DBuser="xtrabackup"
 8    DBpasswd="123456"
 9    DB_name=$(/usr/local/mysql-5.0.92/bin/mysql -uxtrabackup -p'123456' -e 'show databases'|sed 1d|tr '\n' ' ')
10    BAK_dir="/data/db_bak/192.168.0.194/"
11    MYSQL="/usr/local/mysql-5.0.92/bin/mysql"
12    CNF_dir="/etc/my.cnf"
13    LOCAL_IP=`ifconfig eth1 |grep 'inet addr:'|awk '{print $2}'|awk -F":" '{print $2}'`
14    Remote_IP="192.168.0.221"
15    INNOBACKUPEX="/usr/local/xtrabackup/bin/innobackupex-1.5.1"
16 
17    if [ ! -d $BAK_dir ]
18    then 
19    /bin/mkdir -p $BAK_dir
20    fi
21 
22    echo "-----------------------------------------------------------------------------------" >>  ${BAK_dir}/${LOCAL_IP}.log   
23    echo "Start Backup Time: `date +'%F %T'`" >> ${BAK_dir}/${LOCAL_IP}.log   
24 
25    #Backup Mysql
26    $INNOBACKUPEX --defaults-file=${CNF_dir} --user=$DBuser --password=$DBpasswd --databases="${DB_name}"  $BAK_dir > $BAK_dir/bak_`date +%Y-%m-%d`.log 2>&1
27 
28    echo "Mysql Backup Finshed Time: `date +'%F %T'`" >> ${BAK_dir}/${LOCAL_IP}.log
29 
30    cd ${BAK_dir}/
31    /bin/tar zcf bak_`date +%Y-%m-%d`.tar.gz  *`date +%Y-%m-%d`*
32    md5sum bak_`date +%Y-%m-%d`.tar.gz|awk '{print $1}' >bak_`date +%Y-%m-%d`_MD5
33    /bin/rm -rf `date +%Y-%m-%d`* 
34       
35    echo "Tar Backup Finshed Time: `date +'%F %T'`" >> ${BAK_dir}/${LOCAL_IP}.log
36 
37    #delete four days ago backup 
38    find $BAK_dir -mtime +3 -name "*" -exec rm -f {} \;
39 
40    #Check bak.passwd 
41    if [[ ! -e /shell/rsync-pwd/rsync.password ]];then
42    echo "rsync_passwd" > /shell/rsync-pwd/rsync.password
43    chmod 600 /shell/rsync-pwd/rsync.password
44    fi
45 
46    #Backup:rsync to 192.168.0.221:/data/db_bak/192.168.0.194/
47    if [ $? == 0 ];then
48    Auth_module="mdb_db"
49    Auth_user="rsync_user"
50    Passwd_file="/shell/rsync-pwd/rsync.password"
51    rsync -vzrtopg --progress --bwlimit=4096 --delete ${BAK_dir} $Auth_user@${Remote_IP}::${Auth_module} --password-file=${Passwd_file} 
52    fi
53 
54    #Record Backup Time
55    #echo "Backup Time: `date +%F`" >> ${BAK_dir}/${LOCAL_IP}.log
56    #mail -s "192.168.0.194 Mysql Backup Messages" zhangluya1987@gmail.com < ${BAK_dir}/192.168.0.194.log
57    
58   
59    #GRANT SELECT,RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost' IDENTIFIED BY '123456';

 

posted @ 2013-08-08 09:26  study-notes  阅读(664)  评论(0)    收藏  举报