mysql备份工具 percona-xtrabackup

https://blog.frognew.com/2023/08/percona-xtrabackup-8.0-notes-05.html

1. mysql for docker安装

# /opt/compose/zabbix/mysql.yml 
version: '3'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql
    restart: always
    ports:
      - 3306:3306
      - 33060:33060
    volumes:
      - /data/mysql:/var/lib/mysql
    environment:
      - MYSQL_ROOT_HOST=%
      - MYSQL_DATABASE=zabbix
      - MYSQL_USER=zabbix
      - MYSQL_PASSWORD=*zabbix@2024*
      - MYSQL_ROOT_PASSWORD=*root@2024*
    command:
      - mysqld
      - --max_connections=1000
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_0900_ai_ci
      - --default-authentication-plugin=mysql_native_password
    networks:
      custom-net:
        ipv4_address: 10.0.0.21

networks:
  custom-net:
    external: true


# 建立mysql默认数据目录的软连接
ln -s /data/mysql /var/lib/mysql

2. percona-xtrabackup本地安装

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

3. 全量备份和差异备份

# 全量(周日执行)
fullDate=$(date -d now +%Y%m%d)

xtrabackup --host=127.0.0.1 --user=root --password=*root@2024* --backup --target-dir=/data/xtraBackup/full-$fullDate

# 差异(周一至周六执行)
fullDate=$(date -d "last sunday" +%Y%m%d)
diffDate=$(date -d now +%Y%m%d)

xtrabackup --host=127.0.0.1 --user=root --password=*root@2024* --backup --incremental-basedir=/data/xtraBackup/full-$fullDate \
--target-dir=/data/xtraBackup/difference-$diffDate

4. 全量恢复

# 准备
xtrabackup --host=127.0.0.1 --user=root --password=*root@2024* --prepare --target-dir=/data/xtraBackup/full-20240422

# 恢复
rm -rf /var/lib/mysql/*
xtrabackup --host=127.0.0.1 --user=root --password=*root@2024* --copy-back --target-dir=/data/xtraBackup/full-20240422