2. XtraBackup实现全量+增量+Binlog恢复库
2.1 XtraBackup概述
2.1.1 XtraBackup介绍
Percona XtraBackup是一款基于MySQL的服务器的开源热备份实用程序，备份时不影响数据库的正常读写，它可以备份MySQL5.1，5.5，5.6，5.7，8.0服务器上的InnoDB，XtraDB和MyISAM表的数据，以及带有XtraDB的Percona服务器。
xtrabackup功能：
- 在不暂停数据库的情况下创建热的InnoDB备份
- 进行MySQL的增量备份
- 将压缩的MySQL备份传输到另一台服务器
- 在MySQL服务器之间移动表格
- 轻松创建新的MySQL复制从站
- 在不增加服务器负载的情况下备份MySQL
xtrabackup 特点：
- 备份还原过程快速、可靠
- 备份过程不会打断正在执行的事务
- 能够基于压缩等功能节约磁盘空间和流量
- 自动实现备份检验
- 还原速度快
- 开源，免费
xtrabackup 资源：
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
下载: https://www.percona.com/downloads/
2.1.2 XtraBackup使用相关技术点
xtrabackup工具文件组成
Xtrabackup2.2 版之前包括4个可执行文件：
- innobackupex: Perl 脚本
- xtrabackup: C/C++，编译的二进制程序
- xbcrypt: 加解密
- xbstream: 支持并发写的流文件格式
说明：xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互
innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和
MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）
等。即innobackupex是在 xtrabackup 之上做了一层封装实现的。
xtrabackup的新版变化
XtraBackup版本比较多，选用要多加注意，否则备份或者恢复过程中会报错。XtraBackup版本升级到2.4后，相比之前的2.1有了比较大的变化：nnobackupex 功能全部集成到xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过xtrabackup替换innobackupex。
xtrabackup备份过程
备份生成的相关文件
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备
份目录中创建如下文件：
- xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置。
- xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
- xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复。
- backup-my.cnf：文本文件，备份命令用到的配置选项信息
- xtrabackup_logfile：备份生成的二进制日志文件
2.2 XtraBackup实现全量备份和恢复
2.2.1 架构图及环境说明
两台服务器
1 源服务器：
MySQLPri
CentOS 8.4
IP: 192.168.250.58/24
MySQL 8.0.26
percona-xtrabackup-80-8.0.27-19.1.el8.x86_64
2 目标服务器：
MySQL-Bak
CentOS 8.4
IP: 192.168.250.68/24
MySQL 8.0.26
percona-xtrabackup-80-8.0.27-19.1.el8.x86_64
# 基本思路：在源服务器上完成数据库文件备份，传到目标服务器上，在目标服务器上恢复好数据库，确保恢复出来的目标服务器与源服务器的数据一致。在生产中可以用来实现迁移数据库服务器和定时备份数据库。
2.2.2 源服务器和目标服务器准备
准备工作包括：主机名修改、关闭防火墙和SELinux、同步时间、安装好MySQL 8.0.26 数据库
2.2.2.1 源服务器
[root@CentOS84 ]#hostnamectl set-hostname MySQLPri
[root@CentOS84 ]#hostname
MySQLPri
[root@CentOS84 ]#exit
[root@MySQLPri ]#
[root@MySQLPri ]#hostname -I
192.168.250.58
[root@MySQLPri ]#getenforce
Disabled
[root@MySQLPri ]#systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@MySQLPri ]#systemctl enable --now chronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.
[root@MySQLPri ]#date
Fri Feb 25 14:09:42 CST 2022
[root@MySQLPri ]#
[root@MySQLPri ]#pwd
/root
# 下载数据库yum源rpm包，并更新本机的yum源配置，再安装 MySQL 8.0.26
[root@MySQLPri ]#wget https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm
--2022-02-25 15:29:12-- https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 23.12.149.85
Connecting to repo.mysql.com (repo.mysql.com)|23.12.149.85|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 30388 (30K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el8-1.noarch.rpm’
mysql80-community-release-el8-1.noarch 100%[==========================================================================>] 29.68K 113KB/s in 0.3s
2022-02-25 15:29:14 (113 KB/s) - ‘mysql80-community-release-el8-1.noarch.rpm’ saved [30388/30388]
[root@MySQLPri ]#rpm -ivh mysql80-community-release-el8-1.noarch.rpm
warning: mysql80-community-release-el8-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:mysql80-community-release-el8-1 ################################# [100%]
# 此步骤开始 yum 在线安装 MySQL 8.0.26 数据库
[root@MySQLPri ]#yum install mysql-server
# 查看监听端口
[root@MySQLPri ]#ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::]:111 [::]:*
# 在源服务器上启动，并开启开机自启动 MySQL8.0.26数据库
[root@MySQLPri ]#systemctl enable --now mysqld.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@MySQLPri ]#
[root@MySQLPri ]#ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 151 *:3306 *:*
# 默认 root账号是没有密&码的
[root@MySQLPri ]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 登录进数据库，修改root账号的密&码
mysql> alter user'root'@'localhost' identified by 'shone2022';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
# 导入用于测试的 hellodb 数据库
[root@MySQLPri ]#rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring hellodb_innodb.sql...
100% 7 KB 7 KB/sec 00:00:01 0 Errors
[root@MySQLPri ]#ll
total 847304
-rw-r--r-- 1 root root 30388 Oct 4 2019 mysql80-community-release-el8-1.noarch.rpm
-rw-r--r-- 1 root root 13738948 Sep 1 18:13 percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
[root@MySQLPri ]#mysql -uroot -pshone2022 < hellodb_innodb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@MySQLPri ]#mysql -uroot -pshone2022
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> quit
Bye
[root@MySQLPri ]#ll /var/lib/mysql/
total 188884
-rw-r----- 1 mysql mysql 56 Feb 25 15:34 auto.cnf
-rw-r----- 1 mysql mysql 11209 Feb 25 15:41 binlog.000001
-rw-r----- 1 mysql mysql 16 Feb 25 15:34 binlog.index
-rw------- 1 mysql mysql 1676 Feb 25 15:34 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 client-cert.pem
-rw------- 1 mysql mysql 1676 Feb 25 15:34 client-key.pem
drwxr-x--- 2 mysql mysql 132 Feb 25 15:41 hellodb
-rw-r----- 1 mysql mysql 196608 Feb 25 15:42 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 8585216 Feb 25 15:34 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql 5498 Feb 25 15:34 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 25 15:41 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 25 15:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 25 15:34 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 25 15:34 ibtmp1
drwxr-x--- 2 mysql mysql 187 Feb 25 15:34 '#innodb_temp'
drwxr-x--- 2 mysql mysql 143 Feb 25 15:34 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 25 15:41 mysql.ibd
srwxrwxrwx 1 mysql mysql 0 Feb 25 15:34 mysql.sock
-rw------- 1 mysql mysql 6 Feb 25 15:34 mysql.sock.lock
-rw-r--r-- 1 mysql mysql 7 Feb 25 15:34 mysql_upgrade_info
srwxrwxrwx 1 mysql mysql 0 Feb 25 15:34 mysqlx.sock
-rw------- 1 mysql mysql 7 Feb 25 15:34 mysqlx.sock.lock
drwxr-x--- 2 mysql mysql 8192 Feb 25 15:34 performance_schema
-rw------- 1 mysql mysql 1680 Feb 25 15:34 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 25 15:34 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 server-cert.pem
-rw------- 1 mysql mysql 1680 Feb 25 15:34 server-key.pem
drwxr-x--- 2 mysql mysql 28 Feb 25 15:34 sys
-rw-r----- 1 mysql mysql 16777216 Feb 25 15:42 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 25 15:42 undo_002
2.2.2.2 目标服务器
[root@CentOS84 ]#hostnamectl set-hostname MySQL-Bak
[root@CentOS84 ]#exit
[root@MySQL-Bak ]#hostname -I
192.168.250.68
[root@MySQL-Bak ]#getenforce
Disabled
[root@MySQL-Bak ]#systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@MySQL-Bak ]#systemctl enable --now chronyd.service
Created symlink /etc/systemd/system/multi-user.target.wants/chronyd.service → /usr/lib/systemd/system/chronyd.service.
[root@MySQL-Bak ]#cd /data
[root@MySQL-Bak ]#
# 准备和主生产服务器一样版本的 MySQL8.0.26 数据库环境，
[root@MySQL-Bak ]#wget https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm
[root@MySQL-Bak ]#rpm -ivh mysql80-community-release-el8-1.noarch.rpm
[root@MySQL-Bak ]#yum install mysql-server
# 至此目标服务器用于恢复的数据库准备完毕，不启动数据库，确保/var/lib/mysql/目录为空
2.2.3 源服务器全量备份数据库文件
基本任务：下载并安装好xtrabackup-80-8.0.27、在源服务器上用xtrabackup完成全量的数据库文件备份、并将备份好的文件传送给目标服务器
# 下载percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm，并安装
[root@MySQLPri ]#cd /data/
[root@MySQLPri ]#wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.27-19/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm
[root@MySQLPri ]#cd /data/
[root@MySQLPri ]#yum -y install percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm
# 在源服务器上创建备份目录，目标服务器无需手工创建，直接全目录复制
[root@MySQLPri ]#mkdir /backup
# 用xtrabackup全量备份 MySQL 8.0.26 数据库文件
[root@MySQLPri ]#xtrabackup -uroot -pshone2022 --backup --target-dir=/backup/base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
220225 19:14:12 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
220225 19:14:12 version_check Connected to MySQL server
220225 19:14:12 version_check Executing a version check against the server...
220225 19:14:12 version_check Done.
220225 19:14:12 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 8.0.26
220225 19:14:12 Executing LOCK INSTANCE FOR BACKUP...
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1000000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
Number of pools: 1
xtrabackup: inititialize_service_handles suceeded
220225 19:14:12 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
xtrabackup: Redo Log Archiving is not set up.
220225 19:14:13 >> log scanned up to (18249870)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Completed space ID check of 2 files.
Allocated tablespace ID 2 for hellodb/classes, old maximum was 0
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
220225 19:14:13 [01] Copying ./ibdata1 to /backup/base/ibdata1
220225 19:14:13 [01] ...done
#省略很多雷同的数据拷贝内容，这段执行过程可细致研读，便于从原理上去理解xtrabackup备份过程
220225 19:14:15 [00] Writing /backup/base/xtrabackup_binlog_info
220225 19:14:15 [00] ...done
220225 19:14:15 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '18249870'
xtrabackup: Stopping log copying thread at LSN 18249870.
Starting to parse redo log at lsn = 18249774
220225 19:14:15 Executing UNLOCK INSTANCE
220225 19:14:15 All tables unlocked
220225 19:14:15 [00] Copying ib_buffer_pool to /backup/base/ib_buffer_pool
220225 19:14:15 [00] ...done
220225 19:14:15 Backup created in directory '/backup/base/'
MySQL binlog position: filename 'binlog.000003', position '156'
220225 19:14:15 [00] Writing /backup/base/backup-my.cnf
220225 19:14:15 [00] ...done
220225 19:14:15 [00] Writing /backup/base/xtrabackup_info
220225 19:14:15 [00] ...done
xtrabackup: Transaction log of lsn (18249870) to (18249880) was copied.
220225 19:14:16 completed OK!
# 查看备份目录及内部备份的文件、文件属性和文件内容
[root@MySQLPri ]#ll -h /backup/
total 4.0K
drwxr-x--- 6 root root 4.0K Feb 25 19:14 base
[root@MySQLPri ]#ll -h /backup/base
total 69M
-rw-r----- 1 root root 475 Feb 25 19:14 backup-my.cnf
-rw-r----- 1 root root 156 Feb 25 19:14 binlog.000003
-rw-r----- 1 root root 16 Feb 25 19:14 binlog.index
drwxr-x--- 2 root root 132 Feb 25 19:14 hellodb
-rw-r----- 1 root root 5.4K Feb 25 19:14 ib_buffer_pool
-rw-r----- 1 root root 12M Feb 25 19:14 ibdata1
drwxr-x--- 2 root root 143 Feb 25 19:14 mysql
-rw-r----- 1 root root 24M Feb 25 19:14 mysql.ibd
drwxr-x--- 2 root root 8.0K Feb 25 19:14 performance_schema
drwxr-x--- 2 root root 28 Feb 25 19:14 sys
-rw-r----- 1 root root 16M Feb 25 19:14 undo_001
-rw-r----- 1 root root 16M Feb 25 19:14 undo_002
-rw-r----- 1 root root 18 Feb 25 19:14 xtrabackup_binlog_info
-rw-r----- 1 root root 102 Feb 25 19:14 xtrabackup_checkpoints
-rw-r----- 1 root root 470 Feb 25 19:14 xtrabackup_info
-rw-r----- 1 root root 2.5K Feb 25 19:14 xtrabackup_logfile
-rw-r----- 1 root root 39 Feb 25 19:14 xtrabackup_tablespaces
# 查看文件格式
[root@MySQLPri ]#file /backup/base/*
/backup/base/backup-my.cnf: ASCII text
/backup/base/binlog.000003: MySQL replication log, server id 1 MySQL V5+, server version 8.0.26
/backup/base/binlog.index: ASCII text
/backup/base/hellodb: directory
/backup/base/ib_buffer_pool: ASCII text
/backup/base/ibdata1: data
/backup/base/mysql: directory
/backup/base/mysql.ibd: data
/backup/base/performance_schema: directory
/backup/base/sys: directory
/backup/base/undo_001: data
/backup/base/undo_002: data
/backup/base/xtrabackup_binlog_info: ASCII text
/backup/base/xtrabackup_checkpoints: ASCII text
/backup/base/xtrabackup_info: ASCII text
/backup/base/xtrabackup_logfile: X11 SNF font data, MSB first
/backup/base/xtrabackup_tablespaces: ASCII text, with no line terminators
# 查看备份的binlog文件信息
[root@MySQLPri ]#cat /backup/base/xtrabackup_binlog_info
binlog.000003 156
[root@MySQLPri ]#cat /backup/base/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 18249870
last_lsn = 18249870
flushed_lsn = 18249870
[root@MySQLPri ]#cat /backup/base/xtrabackup_info
uuid = 1155ff04-962c-11ec-82ce-005056a30f34
name =
tool_name = xtrabackup
tool_command = -uroot -pshone2022 --backup --target-dir=/backup/base
tool_version = 8.0.27-19
ibbackup_version = 8.0.27-19
server_version = 8.0.26
start_time = 2022-02-25 19:14:12
end_time = 2022-02-25 19:14:15
lock_time = 1
binlog_pos = filename 'binlog.000003', position '156'
innodb_from_lsn = 0
innodb_to_lsn = 18249870
partial = N
incremental = N
format = file
compressed = N
encrypted = N
# 复制备份好的全量完整数据库备份文件到目标服务器，按照先前的规划需要全目录复制
[root@MySQLPri ]#scp -r /backup/ 192.168.250.68:/
[root@MySQLPri ]#
## 至此数据库全量备份文件的过程在主生产服务器IP192.168.250.58上的工作全部完成，下面切换到目标服务器IP192.168.250.68服务器上去，实现数据库恢复
2.2.4 目标服务器全量还原数据库
基本任务：将源服务器传送的备份文件进行预处理；将预处理好的数据库文件回复制到 MySQL 的相应目录下；启动数据库服务并验证数据
# 下载xtrabackup 8.0.27 并安装
[root@MySQL-Bak ]#cd /data/
[root@MySQL-Bak ]#wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.27-19/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm
[root@MySQL-Bak ]#
[root@MySQL-Bak ]#cd /data/
[root@MySQL-Bak ]#yum -y install percona-xtrabackup-80-8.0.27-19.1.el8.x86_64.rpm
# 在源生产服务器已经将全量数据库备份文件通过scp传到恢复用的目标服务器上，开始在目标服务器上实现全量数据库的恢复过程
[root@MySQL-Bak ]#ll /backup/
total 4
drwxr-x--- 7 root root 4096 Feb 25 17:25 base
[root@MySQL-Bak ]#du -sh /backup/base/
71M /backup/base/
# 备份文件的预处理：为了确保数据一致，提交完成的事务，回滚未完成的事务，用xtrabackup 8.0.27命令来完成这个过程
[root@MySQL-Bak ]#xtrabackup --prepare --target-dir=/backup/base
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
xtrabackup: cd to /backup/base/
xtrabackup: This target seems to be not prepared yet.
#省略部分屏显内容，可以对照上文中的备份恢复原理图，研读这部分内容，利于理解xtrabackup工作机制
Log background threads are being closed...
Shutdown completed; log sequence number 18250262
220225 19:28:52 completed OK! #一定要注意出现此行OK提示，再进行下一步的操作
[root@MySQL-Bak ]#du -sh /backup/base/
187M /backup/base/
# 将预处理好的数据库文件复制到目标服务器的数据库对应目录。并确保停掉了目标服务器上的数据库服务和目录为空，再复制数据库文件
[root@MySQL-Bak ]#ll /var/lib/mysql/
total 0
[root@MySQL-Bak ]#systemctl stop mysqld
[root@MySQL-Bak ]#systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Fri 2022-02-25 19:24:29 CST; 1min 23s ago
# 将预处理好的数据库文件复制到目标服务器的数据库对应目录
[root@MySQL-Bak ]#xtrabackup --copy-back --target-dir=/backup/base
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/backup/base
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
220225 19:30:34 [01] Copying undo_001 to /var/lib/mysql/undo_001
#省略很多执行过程中的屏显内容，显示的日志性文件利于理解回归和提交过程
220225 19:30:35 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
220225 19:30:35 [01] ...done
220225 19:30:35 [01] Creating directory ./#innodb_temp
220225 19:30:35 [01] ...done.
220225 19:30:35 completed OK! #一定要注意出现此行OK提示，再进行下一步的操作
# 查看复制后的相应数据库的目录及文件
[root@MySQL-Bak ]#du -sh /var/lib/mysql
179M /var/lib/mysql
[root@MySQL-Bak ]#ll /var/lib/mysql
total 180260
-rw-r----- 1 root root 156 Feb 25 19:30 binlog.000003
-rw-r----- 1 root root 14 Feb 25 19:30 binlog.index
drwxr-x--- 2 root root 132 Feb 25 19:30 hellodb
-rw-r----- 1 root root 5498 Feb 25 19:30 ib_buffer_pool
-rw-r----- 1 root root 12582912 Feb 25 19:30 ibdata1
-rw-r----- 1 root root 50331648 Feb 25 19:30 ib_logfile0
-rw-r----- 1 root root 50331648 Feb 25 19:30 ib_logfile1
-rw-r----- 1 root root 12582912 Feb 25 19:30 ibtmp1
drwxr-x--- 2 root root 143 Feb 25 19:30 mysql
-rw-r----- 1 root root 25165824 Feb 25 19:30 mysql.ibd
drwxr-x--- 2 root root 8192 Feb 25 19:30 performance_schema
drwxr-x--- 2 root root 28 Feb 25 19:30 sys
-rw-r----- 1 root root 16777216 Feb 25 19:30 undo_001
-rw-r----- 1 root root 16777216 Feb 25 19:30 undo_002
-rw-r----- 1 root root 470 Feb 25 19:30 xtrabackup_info
-rw-r----- 1 root root 1 Feb 25 19:30 xtrabackup_master_key_id
# 还原数据库目录的权属属性给mysql用户和组
[root@MySQL-Bak ]#chown -R mysql:mysql /var/lib/mysql
[root@MySQL-Bak ]#ll /var/lib/mysql
total 180260
-rw-r----- 1 mysql mysql 156 Feb 25 19:30 binlog.000003
-rw-r----- 1 mysql mysql 14 Feb 25 19:30 binlog.index
drwxr-x--- 2 mysql mysql 132 Feb 25 19:30 hellodb
-rw-r----- 1 mysql mysql 5498 Feb 25 19:30 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 25 19:30 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 25 19:30 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 25 19:30 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 25 19:30 ibtmp1
drwxr-x--- 2 mysql mysql 143 Feb 25 19:30 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 25 19:30 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Feb 25 19:30 performance_schema
drwxr-x--- 2 mysql mysql 28 Feb 25 19:30 sys
-rw-r----- 1 mysql mysql 16777216 Feb 25 19:30 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 25 19:30 undo_002
-rw-r----- 1 mysql mysql 470 Feb 25 19:30 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Feb 25 19:30 xtrabackup_master_key_id
# 至此恢复过程已经完成，可以启动数据库服务
[root@MySQL-Bak ]#systemctl enable --now mysqld
# 登录 MySQ，并验证数据库的内容
[root@MySQL-Bak ]#mysql -uroot -pshone2022
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
# 至此全量的MySQ备份和恢复全部完成！！
2.3 XtraBackup实现全量+增量备份和恢复
2.3.1 架构图及环境说明
两台服务器
1 源服务器：
MySQLPri
CentOS 8.4
IP: 192.168.250.58/24
MySQL 8.0.26
percona-xtrabackup-80-8.0.27-19.1.el8.x86_64
2 目标服务器：
MySQL-Bak
CentOS 8.4
IP: 192.168.250.68/24
MySQL 8.0.26
percona-xtrabackup-80-8.0.27-19.1.el8.x86_64
# 基本思路：在源服务器上完成数据库文件base备份，再修改两次hellodb数据库，模拟两次数据库增量变化，并完成增量备份，将所有的备份文件传到目标服务器上，在目标服务器上对备份的文件进行三次初处理，合并好数据库文件，再完成数据库的恢复，这样就能确保恢复出来的目标服务器与源服务器的数据完全一致。在生产中可以用来实现数据库异机异地定时备份。
2.3.2 源服务器和目标服务器准备
基本任务：主机名修改、关闭防火墙和SELinux、同步时间、安装好MySQL 8.0.26 数据库
2.3.2.1 源服务器
[root@CentOS84 ]#hostnamectl set-hostname MySQLPri
[root@CentOS84 ]#hostname
MySQLPri
[root@CentOS84 ]#exit
[root@MySQLPri ]#
[root@MySQLPri ]#hostname -I
192.168.250.58
[root@MySQLPri ]#getenforce
Disabled
[root@MySQLPri ]#systemctl status firewalld
[root@MySQLPri ]#systemctl enable --now chronyd.service
[root@MySQLPri ]#date
Fri Feb 25 14:09:42 CST 2022
[root@MySQLPri ]#
# 下载数据库yum源rpm包，并更新本机的yum源配置，再安装 MySQL 8.0.26
[root@MySQLPri ]#cd /data/
[root@MySQLPri ]#wget https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm
[root@MySQLPri ]#rpm -ivh mysql80-community-release-el8-1.noarch.rpm
# yum 在线安装 MySQL 8.0.26 数据库
[root@MySQLPri ]#yum install mysql-server
# 查看监听端口
[root@MySQLPri ]#ss -tnl
# 在源服务器上启动，并开启开机自启动 MySQL8.0.26数据库
[root@MySQLPri ]#systemctl enable --now mysqld.service
[root@MySQLPri ]#ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 151 *:3306 *:*
# 默认 root账号是没有密&码的
[root@MySQLPri ]#mysql -u root -p
# 登录进数据库，修改root账号的密&码
mysql> alter user'root'@'localhost' identified by 'shone2022';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
# 导入用于测试的 hellodb 数据库
[root@MySQLPri ]#cd /data/
[root@MySQLPri ]#rz
rz waiting to receive.
Starting zmodem transfer. Press Ctrl+C to cancel.
Transferring hellodb_innodb.sql...
100% 7 KB 7 KB/sec 00:00:01 0 Errors
[root@MySQLPri ]#mysql -uroot -pshone2022 < hellodb_innodb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@MySQLPri ]#mysql -uroot -pshone2022
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> quit
Bye
[root@MySQLPri ]#ll /var/lib/mysql/
total 188884
-rw-r----- 1 mysql mysql 56 Feb 25 15:34 auto.cnf
-rw-r----- 1 mysql mysql 11209 Feb 25 15:41 binlog.000001
-rw-r----- 1 mysql mysql 16 Feb 25 15:34 binlog.index
-rw------- 1 mysql mysql 1676 Feb 25 15:34 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 client-cert.pem
-rw------- 1 mysql mysql 1676 Feb 25 15:34 client-key.pem
drwxr-x--- 2 mysql mysql 132 Feb 25 15:41 hellodb
-rw-r----- 1 mysql mysql 196608 Feb 25 15:42 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 8585216 Feb 25 15:34 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql 5498 Feb 25 15:34 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 25 15:41 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 25 15:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 25 15:34 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 25 15:34 ibtmp1
drwxr-x--- 2 mysql mysql 187 Feb 25 15:34 '#innodb_temp'
drwxr-x--- 2 mysql mysql 143 Feb 25 15:34 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 25 15:41 mysql.ibd
srwxrwxrwx 1 mysql mysql 0 Feb 25 15:34 mysql.sock
-rw------- 1 mysql mysql 6 Feb 25 15:34 mysql.sock.lock
-rw-r--r-- 1 mysql mysql 7 Feb 25 15:34 mysql_upgrade_info
srwxrwxrwx 1 mysql mysql 0 Feb 25 15:34 mysqlx.sock
-rw------- 1 mysql mysql 7 Feb 25 15:34 mysqlx.sock.lock
drwxr-x--- 2 mysql mysql 8192 Feb 25 15:34 performance_schema
-rw------- 1 mysql mysql 1680 Feb 25 15:34 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 25 15:34 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 15:34 server-cert.pem
-rw------- 1 mysql mysql 1680 Feb 25 15:34 server-key.pem
drwxr-x--- 2 mysql mysql 28 Feb 25 15:34 sys
-rw-r----- 1 mysql mysql 16777216 Feb 25 15:42 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 25 15:42 undo_002
2.3.2.2 目标服务器
[root@CentOS84 ]#hostnamectl set-hostname MySQL-Bak
[root@CentOS84 ]#exit
[root@MySQL-Bak ]#hostname -I
192.168.250.68
[root@MySQL-Bak ]#getenforce
Disabled
[root@MySQL-Bak ]#systemctl status firewalld
[root@MySQL-Bak ]#systemctl enable --now chronyd.service
[root@MySQL-Bak ]#cd /data
[root@MySQL-Bak ]#
# 准备和主生产服务器一样版本的 MySQL8.0.26 数据库环境
[root@MySQL-Bak ]#wget https://repo.mysql.com//mysql80-community-release-el8-1.noarch.rpm
[root@MySQL-Bak ]#rpm -ivh mysql80-community-release-el8-1.noarch.rpm
[root@MySQL-Bak ]#yum install mysql-server
# 至此目标服务器用于恢复的数据库准备完毕，不启动数据库，确保/var/lib/mysql/目录为空
2.3.3 源服务器备份全量及两次增量数据库文件
基本任务：
- 下载并安装好xtrabackup-80-8.0.27；
- 在源服务器上用xtrabackup完成一次全量的数据库文件备份；
- 第一次修改数据库，并完成第一次增量inc1备份；
- 第二次修改数据库，并完成第二次增量inc2备份；
- 最后将备份好的全部文件（base、inc1、inc2）传送给目标服务器
# 创建或者清空/backup/目录
[root@MySQLPri ]#mkdir /backup/
[root@MySQLPri ]#ll /backup
total 0
# 用xtrabackup完成一次全量的数据库备份 base
[root@MySQLPri ]#xtrabackup -uroot -pshone2022 --backup --target-dir=/backup/base
....................
220225 19:47:52 completed OK! #必须见到行备份成功提示
[root@MySQLPri ]#ll /backup/base
total 69684
-rw-r----- 1 root root 475 Feb 25 19:47 backup-my.cnf
-rw-r----- 1 root root 156 Feb 25 19:47 binlog.000004
-rw-r----- 1 root root 16 Feb 25 19:47 binlog.index
drwxr-x--- 2 root root 132 Feb 25 19:47 hellodb
-rw-r----- 1 root root 5498 Feb 25 19:47 ib_buffer_pool
-rw-r----- 1 root root 12582912 Feb 25 19:47 ibdata1
drwxr-x--- 2 root root 143 Feb 25 19:47 mysql
-rw-r----- 1 root root 25165824 Feb 25 19:47 mysql.ibd
drwxr-x--- 2 root root 8192 Feb 25 19:47 performance_schema
drwxr-x--- 2 root root 28 Feb 25 19:47 sys
-rw-r----- 1 root root 16777216 Feb 25 19:47 undo_001
-rw-r----- 1 root root 16777216 Feb 25 19:47 undo_002
-rw-r----- 1 root root 18 Feb 25 19:47 xtrabackup_binlog_info
-rw-r----- 1 root root 102 Feb 25 19:47 xtrabackup_checkpoints
-rw-r----- 1 root root 470 Feb 25 19:47 xtrabackup_info
-rw-r----- 1 root root 2560 Feb 25 19:47 xtrabackup_logfile
-rw-r----- 1 root root 39 Feb 25 19:47 xtrabackup_tablespaces
# 模拟第一次增量：登录 MySQL进入hellodb库，插入三行记录
[root@MySQLPri ]#mysql -uroot -pshone2022 hellodb
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
#插入三行记录
mysql> insert teachers values(null,'xiao yu',10,'F');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers values(null,'xia qingqing',16,'F');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers values(null,'Summer',46,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | xiao yu | 10 | F |
| 6 | xia qingqing | 16 | F |
| 7 | Summer | 46 | F |
+-----+---------------+-----+--------+
7 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
# 第一次增量备份
[root@MySQLPri ]#xtrabackup -uroot -pshone2022 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
...................
220225 19:56:12 completed OK! #必须见到行备份成功提示
# 查看备份文件，多了一个inc1文件夹，此为第一次增量备份文件
[root@MySQLPri ]#ll /backup/
total 8
drwxr-x--- 6 root root 4096 Feb 25 19:47 base
drwxr-x--- 6 root root 4096 Feb 25 19:56 inc1
[root@MySQLPri ]#cat /backup/inc1/xtrabackup_info
uuid = ed151d04-9631-11ec-82ce-005056a30f34
name =
tool_name = xtrabackup
tool_command = -uroot -pshone2022 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
tool_version = 8.0.27-19
ibbackup_version = 8.0.27-19
server_version = 8.0.26
start_time = 2022-02-25 19:56:09
end_time = 2022-02-25 19:56:11
lock_time = 0
binlog_pos = filename 'binlog.000005', position '156'
innodb_from_lsn = 18249890
innodb_to_lsn = 18254005
partial = N
incremental = Y
format = file
compressed = N
encrypted = N
[root@MySQLPri ]#cat /backup/inc1/xtrabackup_binlog_info
binlog.000005 156
[root@MySQLPri ]#
# 模拟第二次增量：登录 MySQL进入hellodb库，再插入三行记录
[root@MySQLPri ]#mysql -uroot -pshone2022 hellodb
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 再次插入三行记录
mysql> insert teachers values(null,'SummerAAAAAAA',36,'F');
Query OK, 1 row affected (0.07 sec)
mysql> insert teachers values(null,'BBBBBBBBBB',36,'F');
Query OK, 1 row affected (0.01 sec)
mysql> insert teachers values(null,'CCCCCCCCC',36,'F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | xiao yu | 10 | F |
| 6 | xia qingqing | 16 | F |
| 7 | Summer | 46 | F |
| 8 | SummerAAAAAAA | 36 | F |
| 9 | BBBBBBBBBB | 36 | F |
| 10 | CCCCCCCCC | 36 | F |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
mysql> QUIT
Bye
# 在第一次inc1增量备份的基础上完成第二次增量备份
[root@MySQLPri ]#xtrabackup -uroot -pshone2022 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
................................
220225 20:01:17 completed OK! #必须见到行备份成功提示
# 查看备份文件，多了第二次增量备份的 inc2 文件夹
[root@MySQLPri ]#ll /backup/
total 12
drwxr-x--- 6 root root 4096 Feb 25 19:47 base
drwxr-x--- 6 root root 4096 Feb 25 19:56 inc1
drwxr-x--- 6 root root 4096 Feb 25 20:01 inc2
# 将所有的备份文件复制到目标服务器
[root@MySQLPri ]#scp -r /backup/ 192.168.250.68:/
[root@MySQLPri ]#
# 至此源服务器上的备份文件作业已经全部完成了，切换到目标服务器上去做恢复实验
2.3.4 目标服务器还原全量及两次增量数据库文件
基本任务：
- 下载并安装好xtrabackup-80-8.0.27；
- 在目标服务器上用xtrabackup对源服务器传送过来的备份文件进行预处理，因后面有增量备份需要合并进来，所以对base不做回滚未完成事务（--apply-log-only）；
- 合并第一次增量inc1到上步处理后的完全备份，因有inc2还需要合并进来，本次也不做回滚未完成事务（--apply-log-only）；
- 合并第二次增量inc2到上步处理后的完全备份，后来没有需要合并的增量文件了，本次需要回滚未完成事务（不需要加--apply-log-only）；
- 最后将经过上面几个步骤处理好的备份文件复制到准备好的MySQL中去，启动数据库，并验证
### 目标服务器利用源服务器的备份的全量和两次增量备份文件恢复数据库的过程
# 确保目标服务器的数据库服务停止、
[root@MySQL-Bak ]#systemctl stop mysqld
# 查看源服务器通过scp命令复制到目标服务器上备份文件
[root@MySQL-Bak ]#ll /backup/
total 12
drwxr-x--- 6 root root 4096 Feb 25 20:06 base
drwxr-x--- 6 root root 4096 Feb 25 20:06 inc1
drwxr-x--- 6 root root 4096 Feb 25 20:06 inc2
[root@MySQL-Bak ]#du -sh /backup/*
71M /backup/base
2.2M /backup/inc1
2.2M /backup/inc2
# 预处理备份文件：用xtrabackup对源服务器传送过来的备份文件进行预处理，因后面有增量备份需要合并进来，所以对base不做回滚未完成事务（--apply-log-only）；
[root@MySQL-Bak ]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
........................
220225 21:17:54 completed OK!
# 预处理备份文件：合并第一次增量inc1到上步处理后的完全备份，因有inc2还需要合并进来，本次也不做回滚未完成事务（--apply-log-only）
[root@MySQL-Bak ]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
................................
220225 21:18:39 completed OK!
# 预处理备份文件：合并第二次增量inc2到上步处理后的完全备份，后来没有需要合并的增量文件了，本次需要回滚未完成事务（不需要加--apply-log-only）
[root@MySQL-Bak ]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
.......................
220225 21:20:50 completed OK!
# 将经过上面几个步骤处理好的备份文件复制到准备好的MySQL相应目录中去，启动数据库，并验证
[root@MySQL-Bak ]#xtrabackup --copy-back --target-dir=/backup/base
.......................
220225 21:21:31 completed OK!
# 查看预处理好并复制到目标服务器MySQL相应目录后的数据库文件
[root@MySQL-Bak ]#ll /var/lib/mysql
total 180260
-rw-r----- 1 root root 156 Feb 25 21:21 binlog.000006
-rw-r----- 1 root root 14 Feb 25 21:21 binlog.index
drwxr-x--- 2 root root 132 Feb 25 21:21 hellodb
-rw-r----- 1 root root 5498 Feb 25 21:21 ib_buffer_pool
-rw-r----- 1 root root 12582912 Feb 25 21:21 ibdata1
-rw-r----- 1 root root 50331648 Feb 25 21:21 ib_logfile0
-rw-r----- 1 root root 50331648 Feb 25 21:21 ib_logfile1
-rw-r----- 1 root root 12582912 Feb 25 21:21 ibtmp1
drwxr-x--- 2 root root 143 Feb 25 21:21 mysql
-rw-r----- 1 root root 25165824 Feb 25 21:21 mysql.ibd
drwxr-x--- 2 root root 8192 Feb 25 21:21 performance_schema
drwxr-x--- 2 root root 28 Feb 25 21:21 sys
-rw-r----- 1 root root 16777216 Feb 25 21:21 undo_001
-rw-r----- 1 root root 16777216 Feb 25 21:21 undo_002
-rw-r----- 1 root root 512 Feb 25 21:21 xtrabackup_info
-rw-r----- 1 root root 1 Feb 25 21:21 xtrabackup_master_key_id
# 修改目录权属
[root@MySQL-Bak ]#chown -R mysql:mysql /var/lib/mysql
[root@MySQL-Bak ]#ll /var/lib/mysql
total 180260
-rw-r----- 1 mysql mysql 156 Feb 25 21:21 binlog.000006
-rw-r----- 1 mysql mysql 14 Feb 25 21:21 binlog.index
drwxr-x--- 2 mysql mysql 132 Feb 25 21:21 hellodb
-rw-r----- 1 mysql mysql 5498 Feb 25 21:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 25 21:21 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 25 21:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 25 21:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 25 21:21 ibtmp1
drwxr-x--- 2 mysql mysql 143 Feb 25 21:21 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 25 21:21 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Feb 25 21:21 performance_schema
drwxr-x--- 2 mysql mysql 28 Feb 25 21:21 sys
-rw-r----- 1 mysql mysql 16777216 Feb 25 21:21 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 25 21:21 undo_002
-rw-r----- 1 mysql mysql 512 Feb 25 21:21 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Feb 25 21:21 xtrabackup_master_key_id
# 启动服务，并验证数据库的数据
[root@MySQL-Bak ]#service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@MySQL-Bak ]#mysql -uroot -pshone2022
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> select * from teachers;
ERROR 1046 (3D000): No database selected
mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | xiao yu | 10 | F |
| 6 | xia qingqing | 16 | F |
| 7 | Summer | 46 | F |
| 8 | SummerAAAAAAA | 36 | F |
| 9 | BBBBBBBBBB | 36 | F |
| 10 | CCCCCCCCC | 36 | F |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
mysql> quit
Bye
# 查看启动后的MySQL目录
[root@MySQL-Bak ]#ll /var/lib/mysql
total 188884
-rw-r----- 1 mysql mysql 56 Feb 25 21:22 auto.cnf
-rw-r----- 1 mysql mysql 156 Feb 25 21:22 binlog.000006
-rw-r----- 1 mysql mysql 156 Feb 25 21:22 binlog.000007
-rw-r----- 1 mysql mysql 30 Feb 25 21:22 binlog.index
-rw------- 1 mysql mysql 1680 Feb 25 21:22 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 21:22 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 21:22 client-cert.pem
-rw------- 1 mysql mysql 1680 Feb 25 21:22 client-key.pem
drwxr-x--- 2 mysql mysql 132 Feb 25 21:21 hellodb
-rw-r----- 1 mysql mysql 196608 Feb 25 21:24 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 8585216 Feb 25 21:22 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql 5498 Feb 25 21:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 25 21:23 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 25 21:24 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 25 21:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 25 21:22 ibtmp1
drwxr-x--- 2 mysql mysql 187 Feb 25 21:22 '#innodb_temp'
drwxr-x--- 2 mysql mysql 143 Feb 25 21:21 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 25 21:22 mysql.ibd
srwxrwxrwx 1 mysql mysql 0 Feb 25 21:22 mysql.sock
-rw------- 1 mysql mysql 6 Feb 25 21:22 mysql.sock.lock
srwxrwxrwx 1 mysql mysql 0 Feb 25 21:22 mysqlx.sock
-rw------- 1 mysql mysql 7 Feb 25 21:22 mysqlx.sock.lock
drwxr-x--- 2 mysql mysql 8192 Feb 25 21:21 performance_schema
-rw------- 1 mysql mysql 1680 Feb 25 21:22 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 25 21:22 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 25 21:22 server-cert.pem
-rw------- 1 mysql mysql 1676 Feb 25 21:22 server-key.pem
drwxr-x--- 2 mysql mysql 28 Feb 25 21:21 sys
-rw-r----- 1 mysql mysql 16777216 Feb 25 21:24 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 25 21:24 undo_002
-rw-r----- 1 mysql mysql 512 Feb 25 21:21 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Feb 25 21:21 xtrabackup_master_key_id
[root@MySQL-Bak ]#du -sh /var/lib/mysql
188M /var/lib/mysql
[root@MySQL-Bak ]#
# 至此全量和两次增量的备份和恢复全部完成
# 思考：本次实验都是在相对理想的环境下完成的，比如数据库是默认安装、库表相对简单、路径简洁等等，要想xtrabackup用于实际实际上次中去，还需要去研读官方文档，并结合定时备份、定时合并增量文件等脚本才能更好用于实际生产环境。
