innobackupex实现MySQL备份

# 安装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

# 镜像封装xtrabackup

这里以centos:7.8.2003作为基础镜像。

新建目录

mkdir -p /opt/centos7.8_xtrabackup

最终目录结构如下：

./ ├── dockerfile └── percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm

dockerfile

FROM centos:7.8.2003
ADD percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm /
RUN yum install -y /percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm && \
    yum clean all && \
    rm -rf /percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm

生成镜像

docker build -t centos7.8_xtrabackup:v1 .

运行镜像

docker run -it -d \
  --name centos7.8_xtrabackup \
  --restart=always \
  -e TZ=Asia/Shanghai \
  -v /home/backup:/backup \
  -v /home/data:/data \
  centos7.8_xtrabackup:v1

# 完整备份

进入docker

docker exec -it centos7.8_xtrabackup /bin/bash

执行以下命令

innobackupex --user=root --password=12456 \
--port=3306 --host=192.168.31.250 \
 --socket=/data/mysql/data/mysqld.sock \
 --datadir=/data/mysql/data \
 --stream=tar /backup |gzip>/backup/mysql.tar.gz

输出：

... 201110 15:39:17 [00] Streaming 201110 15:39:17 [00] ...done xtrabackup: Transaction log of lsn (295739637) to (295739646) was copied. 201110 15:39:17 completed OK!

有输出completed OK，表示备份成功。

# 使用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 {} \;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

跨主机备份脚本

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上次更新: 2024/11/29, 11:23:26

← 安装MySQL（Windows 64位)，最实用的方式 docker compose部署mysql主从复制（内含故障切换操作）→