MySQL 备份工具 XtraBackup 系列3 --- 备份脚本

一、承上启下

1. XtraBackup安装

https://www.cnblogs.com/eagle6688/p/17066856.html

2. XtraBackup备份

https://www.cnblogs.com/eagle6688/p/17072925.html

3. 目标

为了实现可以投产的备份、准备和数据恢复功能，我们需要定时执行全量备份和增量备份，并且一旦需要可以使用脚本来解压、准备和恢复数据。

二、备份配置

1. 配置文件

(1) 创建配置文件

sudo vi /data/backups/mysql/backup.cnf

(2) 初始化内容为数据库连接账户：

[client]
user=bkpuser
password=bkppassword

(3) 赋权

sudo chown bkpuser /data/backups/mysql/backup.cnf
sudo chmod 600 /data/backups/mysql/backup.cnf

2. 加密字符串

(1) 创建加密字符串：

printf '%s' "$(openssl rand -base64 24)" | sudo tee /data/backups/mysql/encryption_key && echo

(2) 赋权

sudo chown bkpuser: /data/backups/mysql/encryption_key
sudo chmod 600 /data/backups/mysql/encryption_key

三、脚本

1. 备份脚本

(1) 脚本功能：

a. 备份MySQL数据库、压缩并加密压缩文件；

b. 每天创建全量备份；

c. 每小时创建增量备份；

d. 仅维护最近6天的备份文件；

(2) 创建脚本

vi MySQL-Backup.sh

初始化脚本内容为：

#!/bin/bash

export LC_ALL=C

base_dir="/data/backups/mysql"
config_file="${base_dir}/backup.cnf"
encryption_key_file="${base_dir}/encryption_key"

today="$(date +%Y%m%d)"
time="$(date +'%Y-%m-%d %H:%M:%S')"
time_file_name="$(date +%Y%m%d%H%M%S)"

backup_dir="${base_dir}/${today}"
log_file="${backup_dir}/backup.log"
retain_days=6 #Must be less than 7.

# Use this to echo to standard error
error () {
  printf "[${time}]-[%s]: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
  exit 1
}

trap 'error "An unexpected error occurred."' ERR

sanity_check () {
  #Check whether the config file is available
  if [ ! -r "${config_file}" ]; then
    error "Lack of file: ${config_file}"
  fi
  
  #Check whether the encryption key file is available
  if [ ! -r "${encryption_key_file}" ]; then
    error "Lack of file: ${encryption_key_file}"
  fi
}

get_proper_threads () {
  if [ $(nproc --all) -gt 2 ]; then
    return $(($(nproc --all) / 2))
  fi
  
  return 1
}

set_options () {
  #List the xtrabackup arguments
  xtrabackup_args=(
    "--defaults-extra-file=${config_file}"
    "--backup"
    "--compress"
    "--stream=xbstream"
    "--encrypt=AES256"
    "--encrypt-key-file=${encryption_key_file}"
    "--extra-lsndir=${backup_dir}"
    "--no-server-version-check"
  )

  backup_type="full"

  #Add option to read LSN (log sequence number) if a full backup has been taken today. 
  if grep -q -s "to_lsn" "${backup_dir}/xtrabackup_checkpoints"; then
    backup_type="incremental"
    lsn=$(awk '/to_lsn/ {print $3;}' "${backup_dir}/xtrabackup_checkpoints")
    xtrabackup_args+=( "--incremental-lsn=${lsn}" )
  fi

  get_proper_threads
  thread_count=$?

  if [ $thread_count -gt 1 ]; then
    echo "xtrabackup will use $thread_count threads to backup."
  
    xtrabackup_args+=(
      "--parallel=$thread_count"
      "--compress-threads=$thread_count"
      "--encrypt-threads=$thread_count"
    )
  fi
  
  echo "xtrabackup options: ${xtrabackup_args[@]}"
}

clear_old () {
  #Remove the oldest backup in rotation
  obsolete_dir="${base_dir}/$(date --date="${retain_days} days ago" +%Y%m%d)"

  if [ -d "${obsolete_dir}" ]; then
    rm -rf "${obsolete_dir}"
  fi
}

take_backup () {
  #Make sure today's backup directory is available and take the actual backup
  mkdir -p "${backup_dir}"
  find "${backup_dir}" -type f -name "*.inprogress" -delete
  backup_file="${backup_dir}/${backup_type}-${time_file_name}.xbstream"
  xtrabackup "${xtrabackup_args[@]}" --target-dir="${backup_dir}" > "${backup_file}.inprogress" 2> "${log_file}"
  mv "${backup_file}.inprogress" "${backup_file}"
}

sanity_check && set_options && clear_old && take_backup

#Check success and print message
if tail -1 "${log_file}" | grep -q "completed OK"; then
  printf "MySQL backup completed! Backup file saved as %s\n" "${backup_file}"
else
  error "Backup failure! Check ${log_file} for more information."
fi

(3) 赋权

chmod +x MySQL-Backup.sh
sudo mv MySQL-Backup.sh /usr/local/bin

2. 提取脚本

(1) 脚本功能：

a. 创建"extract"目录来存放解压后的备份文件；

b. 将*.xbstream格式的文件解压并提取出来。

(2) 创建脚本

vi MySQL-Extract.sh

初始化脚本内容：

#!/bin/bash

export LC_ALL=C

number_of_args="${#}"
time_file_name="$(date +%Y%m%d%H%M%S)"

base_dir="/data/backups/mysql"
encryption_key_file="${base_dir}/encryption_key"
log_file="${base_dir}/extract-${time_file_name}.log"

# Use this to echo to standard error
error () {
  printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
  exit 1
}

trap 'error "An unexpected error occurred. Try checking the \"${log_file}\" file for more information."' ERR

sanity_check () {
  # Check whether the qpress binary is installed
  if ! command -v qpress >/dev/null 2>&1; then
    error "Could not find the \"qpress\" command. Please install it and try again."
  fi

  # Check whether any arguments were passed
  if [ "${number_of_args}" -lt 1 ]; then
    error "Script requires at least one \".xbstream\" file as an argument."
  fi

  # Check whether the encryption key file is available
  if [ ! -r "${encryption_key_file}" ]; then
    error "Cannot read encryption key at ${encryption_key_file}"
  fi
}

get_proper_threads () {
  if [ $(nproc --all) -gt 2 ]; then
    return $(($(nproc --all) / 2))
  fi
  
  return 1
}

set_options () {
  xtrabackup_args=(
    "--decompress"
    "--decrypt=AES256"
    "--encrypt-key-file=${encryption_key_file}"
    "--remove-original"
  )

  get_proper_threads
  thread_count=$?

  if [ $thread_count -gt 1 ]; then
    xtrabackup_args+=(
      "--parallel=$thread_count"
    )
  fi
  
  echo "xtrabackup options: ${xtrabackup_args[@]}"
}

extract () {
  for file in "${@}"; do
    printf "Start extracting file %s\n" "${file}" >&1
    file_dir="$(dirname "${file}")"
    filename_without_ext="$(basename "${file%.xbstream}")"
    extracted_dir="${file_dir}/extract/${filename_without_ext}"

    # Extract the directory structure from the backup file
    mkdir --verbose -p "${extracted_dir}"
    xbstream -x -C "${extracted_dir}" < "${file}"

    xtrabackup "${xtrabackup_args[@]}" --target-dir="${extracted_dir}"
    find "${extracted_dir}" -name "*.xbcrypt" -exec rm {} \;
    find "${extracted_dir}" -name "*.qp" -exec rm {} \;
    printf "Completed extraction for %s\n" "${file}"
  done > "${log_file}" 2>&1
}

sanity_check && set_options && extract "$@"
ok_count="$(grep -c 'completed OK' "${log_file}")"

# Check the number of reported completions.  For each file, there is an
# informational "completed OK".  If the processing was successful, an
# additional "completed OK" is printed. Together, this means there should be 2
# notices per backup file if the process was successful.
if (( $ok_count != $# )); then
  error "It looks like something went wrong. Please check the \"${log_file}\" file for additional information."
else
  printf "Extraction complete! Backup directories have been extracted to the \"extract\" directory.\n"
fi

3. 准备脚本

(1) 脚本功能

a. 查询指定目录下的唯一full备份；

b. 对已经解压的full备份文件执行prepare命令；

c. 查询指定目录下的所有增量备份；

d. 对已经解压的增量备份文件执行prepare命令。

(2) 创建脚本

vi MySQL-Prepare.sh

初始化脚本内容为：

#!/bin/bash

export LC_ALL=C

number_of_args="${#}"
extract_dir="${1}"
time_file_name="$(date +%Y%m%d%H%M%S)"
log_file="${extract_dir}/prepare-${time_file_name}.log"

# Use this to echo to standard error
error() {
  printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
  exit 1
}

trap 'error "An unexpected error occurred. Try checking the \"${log_file}\" file for more information."' ERR

sanity_check () {
  # Check whether a single full backup directory are available
  if (( ${number_of_args} != 1 )); then
    error "Exactly one extract directory is required."
  fi
  
  full_backup_dir_count=`find ${extract_dir} -maxdepth 1 -type d -name "full-*" -print | wc -l`
  
  if [ ${full_backup_dir_count} -gt 1 ]; then
    error "Exactly one full backup directory is required."
  fi
}

set_variable () {
  full_backup_dir_array=( $(find ${extract_dir} -maxdepth 1 -type d -name "full-*" -print) )
  full_backup_dir="${full_backup_dir_array[0]}"
  
  inc_backup_dir_array=( $(find ${extract_dir} -maxdepth 1 -type d -name "incremental-*" -print) )
}

prepare () {
  # Apply the logs to each of the backups
  printf "Start making prepare for full backup %s\n" "${full_backup_dir}"
  xtrabackup --prepare --apply-log-only --target-dir="${full_backup_dir}"

  for inc_backup_dir in "${inc_backup_dir_array[@]}"; do
    printf "Applying incremental backup %s to %s\n" "${inc_backup_dir}" "${full_backup_dir}"
    xtrabackup --prepare --apply-log-only --incremental-dir="${inc_backup_dir}" --target-dir="${full_backup_dir}"
  done

  printf "Applying final logs to full backup %s\n" "${full_backup_dir}"
  xtrabackup --prepare --target-dir="${full_backup_dir}"
}

sanity_check && set_variable && prepare > "${log_file}" 2>&1

# Check the number of reported completions. Each time a backup is processed, an informational "completed OK" and a real version is printed.
# At the end of the process, a final full apply is performed, generating another 2 messages.
ok_count="$(grep -c 'completed OK' "${log_file}")"

if (( ${ok_count} == ${#inc_backup_dir_array[@]} + 2 )); then
  cat << EOF
Backup looks to be fully prepared.  Please check the "prepare-progress.log" file
to verify before continuing.
If everything looks correct, you can apply the restored files.
First, stop MySQL and move or remove the contents of the MySQL data directory:
    
    sudo systemctl stop mysql
    sudo mv /var/lib/mysql/ /tmp/
    
Then, recreate the data directory and  copy the backup files:
    
    sudo mkdir /var/lib/mysql
    sudo xtrabackup --copy-back --target-dir=${PWD}/$(basename "${full_backup_dir}")
    
Afterward the files are copied, adjust the permissions and restart the service:
    
    sudo chown -R mysql:mysql /var/lib/mysql
    sudo find /var/lib/mysql -type d -exec chmod 750 {} \\;
    sudo systemctl start mysql
EOF
else
  error "It looks like something went wrong.  Check the \"${log_file}\" file for more information."
fi

4. 赋权

chmod +x MySQL-{Extract,Prepare}.sh

四、测试

1. 测试数据

create database if not exists Test_Backup default character set UTF8MB4;

use Test_Backup;

CREATE TABLE IF NOT EXISTS `user` (
    id INT AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    gender BIT not null,
    status TINYINT NOT NULL default 1,
    description VARCHAR(300),
    create_time datetime not null default now(),
    modify_time datetime not null default now(),
    PRIMARY KEY (id)
)  ENGINE = INNODB;

insert into `user`(name, gender, description)
values
('Zhang San', 1, "Hello world!"),
('Li Si', 1, "Hello world!");

select * from user;

2. 第一次备份和恢复(全量)

(1) 备份

sudo -u bkpuser /usr/local/bin/MySQL-Backup.sh

输出：

(2) 提取

sudo -u bkpuser /data/backups/mysql/MySQL-Extract.sh /data/backups/mysql/20230128/full-20230128173713.xbstream

输出：

(3) 准备，需要传入extract目录参数

sudo -u bkpuser /data/backups/mysql/MySQL-Prepare.sh /data/backups/mysql/20230128/extract

输出：

(4) 数据还原

a. 停止MySQL

sudo systemctl stop mysqld

b. MySQL清空数据

sudo mv /var/lib/mysql/ /tmp/mysql
sudo mkdir /var/lib/mysql

c. 数据恢复

sudo xtrabackup --copy-back --target-dir=/data/backups/mysql/20230128/extract/full-20230128173713

输出：

d. 赋权

sudo chown -R mysql:mysql /var/lib/mysql
sudo find /var/lib/mysql -type d -exec chmod 750 {} \;

e. 重启MySQL

sudo systemctl start mysqld

3. 增加数据

insert into `user`(name, gender, description)
values
('Wang Wu', 0, "Hello world!");

select * from user;

4. 第二次备份与恢复(增量)

(1) 再次执行备份

sudo -u bkpuser /usr/local/bin/MySQL-Backup.sh

输出：

(2) 提取

sudo -u bkpuser /data/backups/mysql/MySQL-Extract.sh /data/backups/mysql/20230129/*.xbstream

输出：

(3) 准备

sudo -u bkpuser /data/backups/mysql/MySQL-Prepare.sh /data/backups/mysql/20230129/extract

(4) 数据恢复

a. 停止MySQL

sudo systemctl stop mysqld

b. MySQL清空数据

sudo mv /var/lib/mysql/ /tmp/mysql2
sudo mkdir /var/lib/mysql

c. 数据恢复

sudo xtrabackup --copy-back --target-dir=/data/backups/mysql/20230129/extract/full-20230129152335

d. 赋权

sudo chown -R mysql:mysql /var/lib/mysql
sudo find /var/lib/mysql -type d -exec chmod 750 {} \;

e. 重启MySQL

sudo systemctl start mysqld

输出：

五、参考

1. 官方

https://docs.percona.com/percona-xtrabackup/8.0/index.html

2. 其他

https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-centos-7

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-backups-with-percona-xtrabackup-on-ubuntu-16-04

https://severalnines.com/blog/mysqldump-or-percona-xtrabackup-backup-strategies-mysql-galera-cluster/