percona xtrabackup 之四备份脚本

最新推荐文章于 2024-05-31 23:10:34 发布

原创最新推荐文章于 2024-05-31 23:10:34 发布 · 442 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#xtrabackup

mysql backup recovery 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了在Ubuntu 16.04环境下使用Percona XtraBackup 2.4.13进行MySQL数据库备份的方法。包括安装过程、备份脚本编写及全量与增量备份操作流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

os: ubuntu 16.04
db: mysql 5.7.25
xtrabackup: percona-xtrabackup-24_2.4.13

percona-xtrabackup-24_2.4.13

# cd ~
# wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.13/binary/debian/xenial/x86_64/percona-xtrabackup-24_2.4.13-1.xenial_amd64.deb
# dpkg -i ./

提示缺少依赖包时,手动安装依赖包即可.

root@node1:~# dpkg -L percona-xtrabackup-24
/.
/usr
/usr/bin
/usr/bin/xbcloud_osenv
/usr/bin/xbstream
/usr/bin/xbcloud
/usr/bin/xtrabackup
/usr/bin/xbcrypt
/usr/share
/usr/share/lintian
/usr/share/lintian/overrides
/usr/share/lintian/overrides/percona-xtrabackup-24
/usr/share/man
/usr/share/man/man1
/usr/share/man/man1/xtrabackup.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/innobackupex.1.gz
/usr/share/doc
/usr/share/doc/percona-xtrabackup-24
/usr/share/doc/percona-xtrabackup-24/changelog.Debian.gz
/usr/share/doc/percona-xtrabackup-24/COPYING.gz
/usr/share/doc/percona-xtrabackup-24/copyright
/usr/lib
/usr/lib/xtrabackup
/usr/lib/xtrabackup/plugin
/usr/lib/xtrabackup/plugin/keyring_file.so
/usr/lib/xtrabackup/plugin/keyring_vault.so
/usr/bin/innobackupex

root@node1:~# ls -l /usr/bin/innobackupex
lrwxrwxrwx 1 root root 10 Jan 11 16:54 /usr/bin/innobackupex -> xtrabackup

root@node1:~# ls -l /usr/bin/xtrabackup
-rwxr-xr-x 1 root root 21333048 Jan 11 16:54 /usr/bin/xtrabackup

可以看出 innobackupex 是指向 xtrabackup 的一个软链接.

备份脚本

这个脚本不是自己写的,记不起来是从哪获得的,有路过哥们提醒下,好标记下原始出处.

# vi /mnt/mysql_3306/xtrabackup_script/backup.sh

#!/bin/bash

# 一些基础信息
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASSWORD="rootroot"
MYSQL_SOCKET="/var/run/mysqld/mysqld.sock"
MYSQL_MY="/etc/mysql/my.cnf"
MYSQL_BACKUP_DIR="/mnt/mysql_3306/xtrabackup_backup"
MYSQL_BACKUPLOG_DIR="/mnt/mysql_3306/xtrabackup_backuplog"
DAYcurrentDate=`date "+%Y%m%d"`
currentDate=`date "+%Y%m%d%H"`
DELETE_TIME=`date -d "-7 day" "+%Y%m%d"`

# 使用了 ftp ,也可以简单使用 scp 
FTP_HOST="192.168.56.90"
FTP_USER="root"
FTP_PASSWD="rootroot"
FTP_PORT="50022"
			
full_backup(){
    if [ -d "${MYSQL_BACKUP_DIR}" ];then
        mkdir -p "${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/ful"
        mkdir -p "${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/inc"
        cd "${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/ful"
        for DIR in `pwd`
        do
            if [ -d "$DIR" ];then
                /usr/bin/xtrabackup --defaults-file="${MYSQL_MY}" --backup --datadir=/var/lib/mysql --target-dir="${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/ful" --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" --host="${MYSQL_HOST}" --port=3306 --socket="${MYSQL_SOCKET}" --no-version-check  --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp 

                if [ $? -eq "0" ];then
                    printf "${DAYcurrentDate}\tinnobackupex full backup is ok!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_full.log
                else
                    printf "${DAYcurrentDate}\tinnobackupex full backup is NO!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_full_error.log
                    exit 3
                fi
            else
                printf "${DAYcurrentDate}\tinnobackupex full backup dir is not dir!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_fullday_dir_error.log
                exit 2
            fi
        done
    else
        printf "${DAYcurrentDate}\tinnobackupex_dir is NO!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_dir.log
        exit 1
    fi
}

 
inc_backup(){
    if [ -d "${MYSQL_BACKUP_DIR}/${DAYcurrentDate}" ];then
        cd "${MYSQL_BACKUP_DIR}/${DAYcurrentDate}"
        for DIR_dir in `ls -al|grep ful|awk '{print $9}'|egrep -v '\.|^$'`
        do
            if [ -d "${DIR_dir}" ];then
                #INNOBACKUPEX_FULL_BACKUP_DIR=`ls -al ful/|awk '{print $9}'|egrep -v '\.|^$'`
                for DIR_DIR_dir in `ls -al|grep inc|awk '{print $9}'|egrep -v '\.|^$'`
                do
                    if [ -d "${DIR_DIR_dir}" ];then
                        /usr/bin/xtrabackup --defaults-file="${MYSQL_MY}" --backup --datadir=/var/lib/mysql --target-dir="${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/inc/" --incremental-basedir="${MYSQL_BACKUP_DIR}/${DAYcurrentDate}/ful" --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" --host="${MYSQL_HOST}" --port=3306 --socket="${MYSQL_SOCKET}" --no-version-check  --parallel=4 --throttle=15000 --use-memory=2GB --slave-info --rsync --tmpdir=/tmp
                        
						if [ $? -eq "0" ];then
                            printf "${currentDate}\tinnobackupex inc backup is ok!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_inc.log
                        else
                            printf "${currentDate}\tinnobackupex inc backup is no!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_inc_error.log
                            exit 6
                        fi
                    else
                        printf "${currentDate}\tinnobackupex inc backup dir is not dir!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_incday_dir_error.log
                        exit 5
                    fi
                done
            else
                printf "${currentDate}\tinnobackupex_ful_dir is NO!\n" >> $MYSQL_BACKUPLOG_DIR/innobackupex_ful_dir.log
            fi
        done
    else
        printf "${currentDate}\tinnobackupex bakcup dir is no!\n" >> $MYSQL_BACKUPLOG_DIR/backup_innobackupex_dir.log
        exit 4
    fi
}

scp_lftp(){
# Backup compression transmission to the remote backup machine
    cd "${MYSQL_BACKUP_DIR}"
    for DATA_TIME_Yesterday in `date -d '-1 day' '+%Y%m%d'`
    do
        if [ -d "${DATA_TIME_Yesterday}" ];then
            tar -zcvf "${DATA_TIME_Yesterday}".tar.gz "${DATA_TIME_Yesterday}"
			
            if [ $? -eq "0" ];then
                printf "${DAYcurrentDate}\t Backup directory compression success!\n" >> $MYSQL_BACKUPLOG_DIR/backup_file_dir.log
               else
                printf "${DAYcurrentDate}\t Backup directory compression failure!\n" >> $MYSQL_BACKUPLOG_DIR/backup_file_dir.log
            fi

 
            if [ $? -eq "0" ];then
                rm -rf "${DATA_TIME_Yesterday}"
            fi
        else
            printf "${DAYcurrentDate}\t Backup directory compression failure!\n" >> $MYSQL_BACKUPLOG_DIR/dir_time_backup_file_dir.log
            exit 9
        fi

 
        if [ $? -eq "0" ];then
            echo `date` Starting to sftp 
            echo `date` copy "${DATA_TIME_Yesterday}.tar.gz" to remote server ${FTP_HOST}:/data/backup/mysqlbackup_3_44......
			
            /usr/bin/lftp -u "${FTP_USER}","${FTP_PASSWD}" sftp://"${FTP_HOST}":"${FTP_PORT}" << EOF
            cd /data/backup/mysqlbackup_3_44
            put ${MYSQL_BACKUP_DIR}/${DATA_TIME_Yesterday}.tar.gz
            bye
EOF
            echo `date` over
            if [ $? -eq "0" ];then
                find ${MYSQL_BACKUP_DIR} -type f -mtime +7|xargs rm -rf
            fi
        fi
    done
}   

# To determine parameters
NUMB=0
[ $# -eq "${NUMB}" ] && echo "Ple input [options] (full_backup|inc_backup|scp_lftp)" && exit 7

case $1 in
    full_backup)
        full_backup
        ;;
    inc_backup)
        inc_backup
        ;;
    scp_lftp)
        scp_lftp
        ;;
    *)
        echo "Usage: $(basename $0) [OPTION] (full_backup|inc_backup|scp_lftp)"
        exit 8
esac

执行备份

全量备份

# /mnt/mysql_3306/xtrabackup_script/backup.sh full_backup

增量备份

# /mnt/mysql_3306/xtrabackup_script/backup.sh inc_backup

或者写入 crontab

# crontab -e

参考:
http://www.percona.com/software/percona-xtrabackup
https://www.percona.com/downloads/
https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
https://www.percona.com/downloads/Percona-XtraBackup-LATEST/