問(wèn)題描述：

由于隨著生產(chǎn)環(huán)境的運(yùn)行，MySQL中存儲(chǔ)的數(shù)據(jù)越來(lái)越多，備份的時(shí)間也是越來(lái)越長(zhǎng)，極大的影響了客戶的體驗(yàn)，在客戶以及同事的要求下，決定更改MySQL的備份方式，從之前的每天全量備份改為周末進(jìn)行一次全量備份，工作日為增量備份。

問(wèn)題的解決：

數(shù)據(jù)庫(kù)中主流的增量備份工具有mysqldump和xtrabackup，與mysqldumop相比較，xtrabackup屬于物理備份，備份數(shù)據(jù)快，備份過(guò)程不會(huì)打斷正在執(zhí)行的事物，能夠?qū)崿F(xiàn)自動(dòng)備份校驗(yàn)，而且備份的時(shí)候不會(huì)增加服務(wù)器的負(fù)載。綜合考慮采用xtrabackup來(lái)實(shí)現(xiàn)增量備份

一）xtrabackup的安裝

1）執(zhí)行

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

安裝yum倉(cāng)庫(kù)

2）執(zhí)行

yum list | grep percona

查看倉(cāng)庫(kù)是否成功安裝

3）執(zhí)行

percona-release enable-only tools release

使倉(cāng)庫(kù)中的工具生效

4）執(zhí)行

yum install percona-xtrabackup-24

安裝percona xtrabackup

5）創(chuàng)建備份的Mysql用戶，并完成授權(quán)

mysql> CREATE USER 'xtraback'@'%' IDENTIFIED BY '<YOUR PASSWD>';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtraback'@'%';
mysql> FLUSH PRIVILEGES;

二）xtrabackup備份的相關(guān)命令

innobackupex --user=<User> --password=<Password> --port <Port> /data/apps_data/mysql-backup/

全量備份

innobackupex --user=<User> --password=<Password> --incremental <Incremental Dir> --incremental-basedir=<Full Backup Dir>

增量備份

innobackupex --apply-log --redo-only <Full Backup Dir>

在恢復(fù)增量備份時(shí)，需要先對(duì)主庫(kù)執(zhí)行該操作，然后再將增量備份的拷貝到主庫(kù)里面去

innobackupex --apply-log --redo-only <Full Back Dic> --incremental-dir=<Incremental Dic>

增量備份加入到全量備份中（不能對(duì)最后一個(gè)增量備份使用）

innobackupex --apply-log <Full Backup Dir> --incremental-dir=<Incremental Dir>

增量備份加入到全量備份中（只能夠?qū)ψ詈笠粋€(gè)增量備份使用）

xtrabackup --copy-back --target-dir=<Full Backup Dir>

備份恢復(fù)（數(shù)據(jù)庫(kù)需要停止，且數(shù)據(jù)目錄需要清空）

三）腳本實(shí)現(xiàn)

#!/bin/bash

weekday=`date +%w`  # 0 is sunday, 1-6 mon-sat
backup_day=`date +%m-%d`
backup_time=`date +%m-%d-%H-%M-%S`
backup_month=`date +%Y-%m`
backup_dir=/data/apps_data/mysql-backups
user=<Your user>
password=<Your password>
port=3307
log_dir=/data/apps_data/mysql-backups/logs
log_file=`date +%F`.log
index_dic=/data/apps_data/mysql-backups/.index
index_file=/data/apps_data/mysql-backups/.index/index.txt


test ! -d $backup_dir && mkdir -p $backup_dir;
test ! -d $index_dic && mkdir -p $index_dic;
test ! -d $log_dir && mkdir -p $log_dir;

function full_backup()
{
    test ! -d $backup_dir/$backup_month/${backup_day}_full && mkdir -p $backup_dir/$backup_month/${backup_day}_full;
    echo "start full backup"
#    echo "$backup_dir/$backup_month/$backup_day_full" > $index_file
    innobackupex --user=$user --password=$password --port $port --no-timestamp $backup_dir/$backup_month/${backup_day}_full >> $log_dir/$log_file 2>&1
    if [ $? == 0 ];
    then
        grep "innodb_to_lsn" $backup_dir/$backup_month/${backup_day}_full/xtrabackup_info|awk -F "=" {'print $2'}|cut -d " " -f 2 > $index_file
        cd /data/apps_data/mysql-backups/$backup_month && gzip ${backup_day}_full
    else
        echo "full backup fail" >> $log_dir/$log_file 2>&1
        rm -rf $backup_dir/$backup_month/${backup_day}_full
        exit
    fi
}

function incremental_backup()
{
#    test ! -r $index_file && echo "Cant't Scan Full Backup, Now Start Full Backup" > $log_dir/$log_file 2>&1 && full_backup && exit;
    if [ ! -r $index_file ];
    then
        echo "Cant't Scan Full Backup, Now Start Full Backup"
        echo "Cant't Scan Full Backup, Now Start Full Backup" >> $log_dir/$log_file 2>&1
        full_backup
        exit
    fi
#    incremental_lsn=`cat $index_file`
    if [ -s "$index_file" ]
    then
        incremental_lsn=`cat $index_file`
    else
        echo "index file is none, now start full backup"
        full_backup
        exit
    fi
    test ! -d $backup_dir/$backup_month/$backup_day/inc_$backup_time && mkdir -p $backup_dir/$backup_month/$backup_day/inc_$backup_time
    innobackupex --user=$user --password=$password --port $port --incremental $backup_dir/$backup_month/$backup_day/inc_$backup_time --no-timestamp --incremental-lsn=$incremental_lsn >> $log_dir/$log_file 2>&1
    if [ $? == 0 ];
    then
        grep "innodb_to_lsn" $backup_dir/$backup_month/$backup_day/inc_$backup_time/xtrabackup_info|awk -F "=" {'print $2'}|cut -d " " -f 2 > $index_file
        cd $backup_dir/$backup_month/$backup_day && gzip inc_$backup_time
#        echo "$backup_dir/$backup_month/$backup_day_full" > $index_file
    else
        echo "incremental backup fail" >> $log_dir/$log_file 2>&1
        rm -rf $backup_dir/$backup_month/$backup_day/inc_$backup_time
        exit
    fi
#    echo "$backup_dir/$backup_month/$backup_day/inc_$backup_time" > $index_file
}

function get_backup_type()
{
    if [ "$weekday" == 0 ];
    then
        full_backup
    else
        echo "start incremental backup"
        incremental_backup
    fi
}

function main()
{
    get_backup_type
}

main

注意：

1）在上線的時(shí)候可以加上--host來(lái)指定備份某一臺(tái)主機(jī)的數(shù)據(jù)庫(kù)
2）如果調(diào)用該腳本的用戶是普通用戶，例如centos，會(huì)導(dǎo)致不能備份，因?yàn)閏entos用戶沒(méi)有訪問(wèn)mysql文件的權(quán)限，執(zhí)行usermod -a -G mysql centos將centos用戶加入到mysql組中，即可解決
3）當(dāng)該腳本以增量備份模式運(yùn)行的時(shí)候，需要加載上一次備份的index文件，所以不支持集群部署，在需要集群部署時(shí)，需要在另外兩臺(tái)編寫(xiě)格外的腳本去增量備份的主機(jī)執(zhí)行增量備份的腳本