前言
CentOS 7 下 MySQL 5.7 配置 Percona Xtrabackup ，记录一下大致的安装和配置过程。
Percona XtraBackup 的备份工具支持热备份（即不必停止 MySQL 服务而进行备份）。热备份方式主要是通过文件系统级别的文件拷贝，当需要执行崩溃恢复时，可以实现数据集内的一致性。
参考 How To Configure MySQL Backups with Percona XtraBackup on Ubuntu 16.04
参考文档使用的操作系统为 Ubuntu 16.04，本例将操作系统改为 CentOS 7，命令基本一致，主要示例一下数据库的全备份，增量备份以及数据恢复。
环境说明
CentOS 7（Minimal Install）
$ cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
MySQL 5.7
$ mysql --version
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
本系统初始有两个用户
超级管理员: root
管理组用户: admin
安装 Percona Xtrabackup 工具
参考 Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
安装 yum 源
mysql> CREATE DATABASE playground;
mysql> CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));
mysql> INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");
此后我们将用这个数据库查看测试备份和恢复的效果。
mysql> SELECT * FROM playground.equipment;
+----+-------+-------+-------+
| id | type | quant | color |
+----+-------+-------+-------+
| 1 | slide | 2 | blue |
+----+-------+-------+-------+
1 row in set (0.00 sec)
在我们退出 MySQL 会话前，我们先检查一下 datadir 变量。因为我们还要创建一个操作系统的 backup 用户，而且这个需要有权限访问这个目录。
mysql> SELECT @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)
days_of_backups=3 # Must be less than 7
backup_owner="backup"
parent_dir="/backups/mysql"
defaults_file="/etc/mysql/backup.cnf"
todays_dir="${parent_dir}/$(date +%a)"
log_file="${todays_dir}/backup-progress.log"
encryption_key_file="${parent_dir}/encryption_key"
now="$(date +%m-%d-%Y_%H-%M-%S)"
processors="$(nproc --all)"
# 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."' ERR
sanity_check () {
# Check user running the script
if [ "$USER" != "$backup_owner" ]; then
error "Script can only be run as the \"$backup_owner\" user"
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
}
set_options () {
# List the innobackupex arguments
#declare -ga innobackupex_args=(
innobackupex_args=(
"--defaults-file=${defaults_file}"
"--extra-lsndir=${todays_dir}"
"--compress"
"--stream=xbstream"
"--encrypt=AES256"
"--encrypt-key-file=${encryption_key_file}"
"--parallel=${processors}"
"--compress-threads=${processors}"
"--encrypt-threads=${processors}"
"--slave-info"
"--incremental"
)
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" "${todays_dir}/xtrabackup_checkpoints"; then
backup_type="incremental"
lsn=$(awk '/to_lsn/ {print $3;}' "${todays_dir}/xtrabackup_checkpoints")
innobackupex_args+=( "--incremental-lsn=${lsn}" )
fi
}
rotate_old () {
# Remove the oldest backup in rotation
day_dir_to_remove="${parent_dir}/$(date --date="${days_of_backups} days ago" +%a)"
if [ -d "${day_dir_to_remove}" ]; then
rm -rf "${day_dir_to_remove}"
fi
}
take_backup () {
# Make sure today's backup directory is available and take the actual backup
mkdir -p "${todays_dir}"
find "${todays_dir}" -type f -name "*.incomplete" -delete
innobackupex "${innobackupex_args[@]}" "${todays_dir}" > "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" 2> "${log_file}"
mv "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" "${todays_dir}/${backup_type}-${now}.xbstream"
}
# Check success and print message
if tail -1 "${log_file}" | grep -q "completed OK"; then
printf "Backup successful!\n"
printf "Backup created at %s/%s-%s.xbstream\n" "${todays_dir}" "${backup_type}" "${now}"
else
error "Backup failure! Check ${log_file} for more information"
fi
# 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 user running the script
if [ "${USER}" != "${backup_owner}" ]; then
error "Script can only be run as the \"${backup_owner}\" user"
fi
# 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
}
do_extraction () {
for file in "${@}"; do
base_filename="$(basename "${file%.xbstream}")"
restore_dir="./restore/${base_filename}"
printf "\n\nExtracting file %s\n\n" "${file}"
# Extract the directory structure from the backup file
mkdir --verbose -p "${restore_dir}"
xbstream -x -C "${restore_dir}" < "${file}"
innobackupex_args=(
"--parallel=${processors}"
"--decrypt=AES256"
"--encrypt-key-file=${encryption_key_file}"
"--decompress"
)
innobackupex "${innobackupex_args[@]}" "${restore_dir}"
find "${restore_dir}" -name "*.xbcrypt" -exec rm {} \;
find "${restore_dir}" -name "*.qp" -exec rm {} \;
printf "\n\nFinished work on %s\n\n" "${file}"
done > "${log_file}" 2>&1
}
# 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 != 2 * $# )); 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 \"restore\" directory.\n"
fi
# 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 user running the script
if [ "${USER}" != "${backup_owner}" ]; then
error "Script can only be run as the \"${backup_owner}\" user."
fi
# Check whether a single full backup directory are available
if (( ${#full_dirs[@]} != 1 )); then
error "Exactly one full backup directory is required."
fi
}
do_backup () {
# Apply the logs to each of the backups
printf "Initial prep of full backup %s\n" "${full_backup_dir}"
innobackupex --redo-only --apply-log "${full_backup_dir}"
for increment in "${incremental_dirs[@]}"; do
printf "Applying incremental backup %s to %s\n" "${increment}" "${full_backup_dir}"
innobackupex --redo-only --apply-log --incremental-dir="${increment}" "${full_backup_dir}"
done
printf "Applying final logs to full backup %s\n" "${full_backup_dir}"
innobackupex --apply-log "${full_backup_dir}"
}
sanity_check && do_backup > "${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} == 2 * (${#full_dirs[@]} + ${#incremental_dirs[@]} + 1) )); 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 innobackupex --copy-back ${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
这个脚本将在本目录查找以 full- 或 incremental- 为前缀的目录，使用 MySQL 的日志应用提交的事务到全量备份，之后再将增量备份的事务提交到全量备份中。
如果所有的备份都被结合，那么未被提交的事务将会回滚，这时，这个 full- 开头的全量备份文件夹，里面的数据，就是当时 MySQL 的数据文件。
创建完成后，保存这个文件，并更改这个脚本为可执行
$ sudo chmod +x /usr/local/bin/prepare-mysql.sh
测试这个 MySQL 的备份和恢复脚本
创建一个全备份
使用 backup 用户执行 backup-mysql.sh
$ sudo -u backup /usr/local/bin/backup-mysql.sh
Backup successful!
Backup created at /backups/mysql/Wed/full-05-10-2017_17-16-44.xbstream
$ sudo mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'
再次执行 backup-mysql.sh，就会创建增量备份。
$ sudo -u backup /usr/local/bin/backup-mysql.sh
我们查看一下
$ cd /backups/mysql/"$(date +%a)"
$ ls
backup-progress.log full-05-10-2017_17-16-44.xbstream
incremental-05-10-2017_18-39-39.xbstream xtrabackup_checkpoints
$ sudo -u backup /usr/local/bin/prepare-mysql.sh
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 innobackupex --copy-back /backups/mysql/Wed/restore/full-05-10-2017_17-16-44
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
这表示准备完成了，脚本会输出一些接下来恢复数据库的步骤
还原备份数据到 MySQL 数据目录
首先，停止 MySQL 服务
$ sudo systemctl stop mysqld
因为备份目录与 MySQL 的数据目录可能有冲突，我们需要删除或剪切 /var/lib/mysql 目录
$ sudo mv /var/lib/mysql/ /tmp
重新创建 /var/lib/mysql 目录，之后赋予正确的权限
$ sudo mkdir /var/lib/mysql
现在，我们要使用 innobackupex 将备份文件拷贝到 MySQL 的数据目录
$ sudo innobackupex --copy-back /backups/mysql/Wed/restore/full-05-10-2017_17-16-44/
170510 19:04:22 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 6f7a799)
......
......
170510 19:04:33 completed OK!
执行之后，completed OK! 说明拷贝完成，一旦文件已经就位，我们需要将修复所有者和权限，这样 MySQL 的用户和组就可以访问了。
$ sudo chown -R mysql:mysql /var/lib/mysql
因为 CentOS 7 默认启用了 SELinux，这也需要进行授权，可以先查看一下
$ ls -lh -Zd /var/lib/mysql
drwxr-x--x. mysql mysql unconfined_u:object_r:var_lib_t:s0 /var/lib/mysql
授权命令如下
$ sudo chcon -R -t mysqld_db_t /var/lib/mysql
此时，权限已经恢复完全。
启动 MySQL 服务，完成数据恢复。
$ sudo systemctl start mysqld
如果数据库启动顺利，接下来，我们查询一下数据库，验证一下数据是否恢复
$ sudo mysql -u root -p -e 'SELECT * FROM playground.equipment;'
Enter password:
+----+-------+-------+--------+
| id | type | quant | color |
+----+-------+-------+--------+
| 1 | slide | 2 | blue |
| 2 | swing | 10 | yellow |
+----+-------+-------+--------+
$ sudo journalctl -t backup-mysql
-- Logs begin at Wed 2017-05-10 18:28:55 CST, end at Wed 2017-05-10 19:27:58 CST. --
May 10 19:27:13 bogon backup-mysql[6704]: Backup successful!
May 10 19:27:13 bogon backup-mysql[6704]: Backup created at /backups/mysql/Wed/incremental-05-10-2017_19-27-11.xbstream
结束语
本例使用了 xtrabackup 工具，进行了 MySQL 的热备份和恢复
全备份和增量备份
提取备份文件
恢复数据
参考资料
Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS
How To Configure MySQL Backups with Percona XtraBackup on Ubuntu 16.04
How To Create Hot Backups of MySQL Databases with Percona XtraBackup on CentOS 7
QQ群⑧：
窥视卡
雷达卡
发表于 2017-11-15 14:36:14
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡