mysql备份

可以使用percona-xtrabackup-2.0.7-552.rhel6.x86_64.rpm对mysql进行备份。

xtrabackup官方网址：https://www.percona.com/downloads/XtraBackup/

基本语法：

多实例备份：

语法解释：--user=数据库用户

        --password=数据库密码

        --socket=指定socket

        --default-file=指定配置文件

        最后面是存放位置

 innobackupex --user=root --port=3306 --host=127.0.0.1 --defaults-file=/etc/my.cnf --defaults-group=mysqld3306 --stream=tar /usr/local/backup |gzip > 127.0.0.1_3306.tar.gz 

 

 全数据还原

/etc/init.d/mysqld stop      //停掉mysql  

 mv /var/lib/mysql /var/lib/mysql_bak   //数据目录备份  

 mkdir -p /var/lib/mysql  //重建数据目录  

--apply-log选项的命令是准备在一个备份上启动mysql服务  

 innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/tank/backup/2014-09-18_16-35-12  

--copy-back 选项的命令从备份目录拷贝数据,索引,日志到my.cnf文件里规定的初始位置  

 innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /home/tank/backup/2014-09-18_16-35-12  

chown -R mysql.mysql /var/lib/mysql  //改变文件所属  

/etc/init.d/mysqld stop  //启动mysql  

#!/bin/bash

#       by jchen

#       Mysql Backup for mysql 5.1.60

#       2013-12-02 14:00:00

#       Version V1.1

###############################################

# 保留备份的天数

DAY=3

#等待n秒后再执行备份

wait=60

#是否开启增量备份默认0为开启1为关闭

open_increase_bak=1

#全备时间点

default_time=4

#设置不进行备份的时间点

exclude_time="0 5"

###############################################

#载入环境变量，否则无法找到/usr/local/mysql/bin/mysql路径

source /etc/profile

DBIP="127.0.0.1"

DBUSER="xxx"

DBPWD="root"

DATE=`date +%F_%H-%M`

Hour=`date '+%H'`

PORT=`netstat -tunlp|grep mysql |awk '{print $4}'|awk -F: '{print $2}'`

MYCNF="/etc/my.cnf"

LOG_FILE="/home/msbakscript/logs/xtrabackup.log"

BACKUP_PATH="/usr/local/hero_all_backup"

MYSQL_LOG="/home/msbakscript/logs/mysql.log"

INNOBACKUPEX="/usr/bin/innobackupex"

bak_log="/home/msbakscript/logs/mysqlbak.log"

MYSQL_PATH="/usr/local/mysql/var"

HOST=$(awk -F= '/IPADDR=/ {print $2}' /etc/sysconfig/network-scripts/ifcfg-eth1)

CHECKPOINT=$(awk '/to_lsn/ {print $3}' /usr/local/hero_all_backup/xtrabackup_checkpoints 2>/dev/null)

all_bak=("$HOST"_"$DATE")

increase_bak=("$HOST"_"$DATE""-increase")

 

if [ $# -eq 0 -o $# -eq 1 -o $# -eq 2 ];then

        echo "OK" > /dev/null

else

        echo "sh $0 allbakfile 或者 sh $0 allbakfile incbakfile"

        exit 1

fi

[ ! -d ${BACKUP_PATH} ] && mkdir -p ${BACKUP_PATH}

[ ! -d /home/msbakscript/logs ] && mkdir -p /home/msbakscript/logs

 

if [ -z "`/sbin/pidof -s mysqld`" ];then

        echo "mysqld is not running"

        exit 1

fi

 

if [ `rpm -qa |grep -c xtrabackup-0.9-2.rhel4` -eq 1 ];then

        rpm -e xtrabackup-0.9-2.rhel4

fi

if [ ! -f /usr/bin/innobackupex ];then

        echo "xtrabackup is not installed"

        rpm -i --nodeps http://122.228.194.133:8080/percona-xtrabackup-2.0.7-552.rhel5.x86_64.rpm

elif [ -f /usr/bin/innobackupex ] && [ `ps -ef|grep -v grep |grep -c  /usr/bin/innobackupex` -ge 1 ];then

        echo "xtrabackup process already exist."

        exit 1

fi

 

complete_bak ()

{

        cd ${BACKUP_PATH}

        ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --stream=tar ${BACKUP_PATH}/ 2>>${LOG_FILE} |gzip >${BACKUP_PATH}/${all_bak}_${P

ORT}.tar.gz

        if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then

                tar zxvfi ${BACKUP_PATH}/${all_bak}_${PORT}.tar.gz xtrabackup_checkpoints

                echo "${all_bak}_${PORT}.tar.gz backup successed" >>${bak_log}

                echo -en "ok\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                sh /home/msbakscript/redis_backup.sh

                chown nobody.nobody *

        else

                echo "${all_bak}_${PORT}.tar.gz backup failed" >>${bak_log}

                echo -e "failure\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                exit 1

        fi

}

complete_bak

increase_bak ()

{

        cd ${BACKUP_PATH}

        if [ ! -f ${BACKUP_PATH}/xtrabackup_checkpoints -o -z ${BACKUP_PATH}/xtrabackup_checkpoints ];then

                echo "xtrabackup_checkpoints does not exist" >>/{LOG_FILE}

                complete_bak

                exit 0

        fi

        ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --no-timestamp --incremental --throttle=30 ${BACKUP_PATH}/${increase_bak}_${PORT

} --incremental-lsn=${CHECKPOINT} >>${LOG_FILE} 2>&1

        if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then

                tar zcfi ${increase_bak}_${PORT}.tar.gz ${increase_bak}/

                rm -rf ${increase_bak}_${PORT}

                echo "${increase_bak}_${PORT}.tar.gz backup successed" >>${bak_log}

                echo -e "ok\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

        else

                echo "${increase_bak}_${PORT} backup failed" >>${bak_log}

                echo -e "failure\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                exit 1

        fi

       

 

}

increase_bak

del_bakfile ()

{

        for dbfile in `find "${BACKUP_PATH}/" -name "[0-9]*.tar.gz" -type f -mtime +${DAY}`; do

                rm -f ${dbfile}

        done

}

del_bakfile

bakmysql ()

{

        for i in ${exclude_time[@]};do

                [ "${Hour}" -eq "$i" ] && exit 0

        done

        if [  "${Hour}" -eq "${default_time}" ];then

                complete_bak

        elif [  "${Hour}" -ne "${default_time}" -a ${open_increase_bak} -eq 0 ];then

                increase_bak

        else

                exit 0

        fi

}

bakmysql

 

case $# in

        0)

        bakmysql

        del_bakfile

        ;;

        1)

        if [ "$1" = "all" ];then

                complete_bak

                del_bakfile

        elif [  "$1" = "inc" ];then

                increase_bak

                del_bakfile

        else

                echo "sh $0 all or sh $0 inc" && exit 0

        fi

        ;;

esac