安装xtrabackup

官网下载地址：

https://www.percona.com/downloads/XtraBackup/LATEST/

我这边安装的mysql版本是5.7.3.5，系统版本是rocky 9.3

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm?_gl=1m5l07x_gcl_au*MTk2Mzg2ODUwMS4xNzI3NTk5ODI5

执行：yum install percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm -y

使用innobackupex备份

全量备份操作步骤

innobackupex /data/mysql-backup/blog --user=root --password=Tencent@123 --socket=/data/database/mysql/mysql.sock

恢复操作

停止mysqld服务

systemctl stop mysqld

清空数据目录

rm -rf /data/database/mysql/*

恢复备份

innobackupex  --defaults-file=/etc/my.cnf --copy-back /data/mysql-backup/blog/2024-10-18_16-03-27 --user=root --password=Tencent@123 --socket=/data/database/mysql/mysql.sock

在恢复数据后，应用备份日志，以确保数据库处于一致状态：

innobackupex --apply-log /data/mysql-backup/blog/2024-10-18_16-03-27 --user=root --password=Tencent@123 --socket=/data/database/mysql/mysql.sock

更改数据目录权限

chown -R mysql:mysql mysql

启动mysql服务

systemctl start mysqld

全量备份的脚本

#!/bin/bash

#### -- default value -- ####
# mysql

MYSQL_CNF=/etc/my.cnf
MYSQL_USER="root"
MYSQL_PASS="xxx"
MYSQL_HOST='localhost'
MYSQL_SOCKET=/data/database/mysql/mysql.sock
# 

logtime=`date +%x' '%T`
fullbase=/data/mysql-backup/full
logbase=/root/dba/logs
log_file="/root/dba/logs/backup_record_"$(date +%Y%m%d%H%M%S)".log"

innobackupex=/usr/bin/innobackupex

#### -- 创建备份目录 -- ####
[ -d ${fullbase} ] || mkdir -p ${fullbase}
[ -d ${logbase} ] || mkdir -p ${logbase}

#### -- main funcation -- ####
_FULLBackup()
{
dtime=`date +%Y-%m-%d`
$innobackupex --defaults-file=$MYSQL_CNF --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --socket=$MYSQL_SOCKET $fullbase > $logbase/${dtime}.log 2>&1
result=$?
if [ $result -eq 0 ];then
        bkbase=`ls -d ${fullbase}/${dtime}* | sort -r | head -n 1`
        bksize=`du -sh ${bkbase} | awk '{print $1}'`
        echo -e "[$logtime]\tStatus: successful, Backdir: $bkbase, size: $bksize" | tee -a  ${logbase}/full_success_${dtime}.log
else
        echo -e "[$logtime]\tStatus: failed, Reason: check you log $logbase/${dtime}.log" | tee -a  ${logbase}/full_failure_${dtime}.log
fi
}


backup_timer_start=`date "+%Y-%m-%d %H:%M:%S"`
echo "backup_timer_start   :    $backup_timer_start" >>$log_file
# run
_FULLBackup
backup_timer_end=`date "+%Y-%m-%d %H:%M:%S"`
echo "backup_timer_end     :    $backup_timer_end" >>$log_file
backup_duration=`echo $(($(date +%s -d "${backup_timer_end}") - $(date +%s -d "${backup_timer_start}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n+=2){if($1==0)break;s=$1%a[n]a[n+1] s;$1=int($1/a[n])}print s}'`
echo "total backup time    :    $backup_duration"  >>$log_file




cd $fullbase
deldir=`ls -l|awk '/^d/{print $9}'|tail -n 1`

backup_dir_size=`du -sh $deldir|awk '{print $1}'`
echo "backup_size          :    $backup_dir_size" >>$log_file
echo "---------------------------------------" >>$log_file
compress_timer_start=`date "+%Y-%m-%d %H:%M:%S"`
echo "compress_timer_start :    $compress_timer_start" >>$log_file

if [ -d "$deldir" ]
  then
    tar -czf $deldir.tar.gz $deldir
    rm -rf $deldir
fi

compress_timer_end=`date "+%Y-%m-%d %H:%M:%S"`
echo "compress_timer_end   :    $compress_timer_end" >>$log_file
compress_duration=`echo $(($(date +%s -d "${compress_timer_end}") - $(date +%s -d "${compress_timer_start}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n+=2){if($1==0)break;s=$1%a[n]a[n+1] s;$1=int($1/a[n])}print s}'`
echo "total compress time  :    $compress_duration" >>$log_file
compress_size=`du -sh $deldir.tar.gz|awk '{print $1}'`
echo "compress_size        :    $compress_size" >>$log_file
echo "compress_file        :    $deldir.tar.gz" >>$log_file
/root/dba/send_file.sh $deldir.tar.gz
find /data/mysql-backup/full -mtime +14 -exec rm -rf {} \;
find /root/dba/logs -mtime +14 -exec rm -f {} \;

跨主机备份脚本

expect命令安装，参考：https://www.zzppjj.top/pages/b7531e/

#!/usr/bin/expect -f
set filename [lindex $argv 0]
cd /data/mysql-backup/full
set timeout 10800
spawn scp -P 22 $filename root@192.168.153.106:/data/share
expect {
  "*(yes/no)*"
    {
      send "yes\n"
      expect "*password:" { send "TT@123\n"}
    }
  "*password:"
  {
    send "TT@123\n"
  }
}
set timeout -1
expect eof