percona-xtrabackup-8.0.7简单快捷使用

percona-xtrabackup-8.0.7简单快捷使用

# 参考资料：

https://blog.csdn.net/vkingnew/article/details/83012316

# 环境:centos6.x

yum -y install perl-DBI
yum -y install perl-DBD-MySQL 
yum -y install perl-IO-Socket-SSL.noarch
yum -y install perl-Time-HiRes  
yum -y install perl-TermReadKey
yum -y install perl-ExtUtils-MakeMaker
[root@dbhost ~]# rpm -qa|grep libgcrypt
libgcrypt-1.4.5-12.el6_8.x86_64

# 下载对应的版本

cd /opt/
wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/tarball/percona-xtrabackup-8.0.7-Linux-x86_64.libgcrypt145.tar.gz
tar -zxf percona-xtrabackup-8.0.7-Linux-x86_64.libgcrypt145.tar.gz 
cd /usr/local/
ln -s /opt/percona-xtrabackup-8.0.7-Linux-x86_64 xtrabackup
ln -fs  /opt/percona-xtrabackup-8.0.7-Linux-x86_64/bin/* /usr/bin/ 
xtrabackup -v

# 在主库中创建专用备份账号

create user 'bk_user'@'10.192.30.%' identified WITH mysql_native_password by 'v9SimLKsIHpwzyOgVwlM' PASSWORD EXPIRE NEVER ; 
GRANT BACKUP_ADMIN,SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'bk_user'@'10.192.30.%';

# 全量备份(在本地从库上备份)

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=10.192.30.60 --user='bk_user' --password='v9SimLKsIHpwzyOgVwlM' --port=3306  --backup --compress --compress-threads=8 --use-memory=4G --slave-info --parallel=8 --target-dir=/data/backup/

# 全量恢复

# 需要先清空数据目录下的文件及其目录

-- rm -rf tmp/* undolog/* data/* logs/mysql-bin*

# 先解压，需要单独安装

/*
wget http://www.quicklz.com/qpress-11-linux-x64.tar
tar xvf qpress-11-linux-x64.tar
cp qpress /usr/bin

# 如果无法下载，登录其官网，单独下载，再上传
*/
xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/backup/  # 压缩备份
xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --use-memory=4G --parallel=8 --target-dir=/data/backup/  # 恢复数据
xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --copy-back --use-memory=4G --parallel=8 --target-dir=/data/backup/  # 拷贝会目录所在，这里推荐使用--copy-back方式，因为我的my.cnf中的配置路径是分散的
chown -R mysql.mysql *

# 附录：简单的全备shell脚本

#!/bin/bash
# file_name: /usr/local/scripts/full_xtrabackup.sh
#
#################################################################################################################################################################
#
#
# create user 'bk_user'@'10.192.30.%' identified WITH mysql_native_password by 'v9SimLKsIHpwzyOgVwlM' PASSWORD EXPIRE NEVER ; 
# GRANT BACKUP_ADMIN,SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'bk_user'@'10.192.30.%';
# mkdir -p /data/backup/logs/ ; mkdir -p /data/backup/full_bak/
# 33 18 13 08 * /bin/bash  /usr/local/scripts/full_xtrabackup.sh &>/dev/null

. /etc/init.d/functions
Date_Time=`date +%Y%m%d%H%M%S`
Date_Day=`date +%Y%m%d%H%M`
Target_Dir="/data/backup/full_bak/"
User_Name="bk_user"
Pass_Word="v9SimLKsIHpwzyOgVwlM"
Host_Ip="10.192.30.60"
Port_MySQL="3306"
Xtrabackup_Cmd="/usr/bin/xtrabackup"
Compress_Threads="8"
Parallel_Num='8'
Use_Memory="4G"
My_Conf="/data/mysql/mysql_3306/my_3306.cnf"
Log_File_Dir="/data/backup/logs/"
Log_File="/data/backup/logs/full_bak_"${Date_Day}""
Count_Day="30"
Scp_Target_Host="root@10.192.30.53:/data/backup/full_backup/"

# 全备份
function Full_Backup(){
    "${Xtrabackup_Cmd}" --defaults-file="${My_Conf}" --host="${Host_Ip}" --user="${User_Name}" --password="${Pass_Word}" --port="${Port_MySQL}" --backup --compress --compress-threads="${Parallel_Num}" --use-memory="${Use_Memory}" --slave-info --parallel="${Parallel_Num}" --target-dir="${Target_Dir}"full_bak_"${Date_Day}"/ &>>"${Log_File}".log
    Results=`tail -1  "${Log_File}".log | awk '{print $3}'`
    if [ "${Results}"x == "completed"x ]; then
        echo "############################################################################" &>>"${Log_File}".log
        action "full_backup files are successful" /bin/true &>>"${Log_File}".log
    else
        echo "############################################################################" &>>"${Log_File}".log
        action "full_backup files are error" /bin/false &>>"${Log_File}".log
    fi
}

# 给远端传输一份，需要实现配置rsync或者ssh互信
    function Scp_BackupFiles(){
    Results=`tail -3  "${Log_File}".log |head -1| awk '{print $3}'`
    if [ "${Results}"x == "completed"x ]; then
        scp -rp -l 52428800 "${Target_Dir}"full_bak_"${Date_Day}" "${Scp_Target_Host}"
        if [ "$?" -eq 0 ]; then 
            action " "${Target_Dir}"full_bak_"${Date_Day}" are successfully transformed. " /bin/true &>>"${Log_File}".log
        fi
    else
        echo "############################################################################" &>>"${Log_File}".log
        action " "${Target_Dir}"full_bak_"${Date_Day}" are failed transformed.  " /bin/false &>>"${Log_File}".log
    fi
}

# 删除旧文件，保留最近n天的文件
function Delete_Files(){
    find "${Target_Dir}" -type d -mtime  +"${Count_Day}" -name "full_bak*" -exec rm -rf  {} \;
    find "${Log_File_Dir}" -type f -mtime  +"${Count_Day}" -name "full_bak_*.log" -exec rm -rf  {} \;
}

Full_Backup
sleep 10
Scp_BackupFiles
Delete_Files

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