MYSQL 8 物理全量恢复2

祖仙教小凡仙海鲨数据库架构师

不知道怎么回事，现在才发现前面那篇发表的时候居然才半篇内容！

Percona XtraBackup的工作原理：

1.XtraBackup复制InnoDB数据文件，这会导致内部不一致的数据，但是它会对文件执行崩溃恢复，以使其再次成为一个一致的可用数据库

2.这样做的可行性是因为InnoDB维护一个REDO日志，也称为事务日志。REDO日志包含了InnoDB数据每次更改的记录。当InnoDB启动时，REDO日志会检查数据文件和事务日志，并执行两个步骤。它将已提交的事务日志条目应用于数据文件，并对任何修改了数据但未提交的事务执行undo操作

3.Percona XtraBackup会在启动时记住日志序列号（LSN）,然后复制数据文件。这需要一些时间来完成，如果文件正在改变，那么它会在不同的时间点反映数据库的状态。同时，Percona XtraBackup运行一个后台进程，用于监视事务日志文件，并从中复制更改。Percona XtraBackup需要持续这样做，因为事务日志是以循环方式写入的，并且可以在一段时间后重新使用。Percona XtraBackup开始执行后，需要复制每次数据文件更改对应的事务日志记录

这里我们看到普通全量备份 UNDO大小为10MB

 
[root@localhost backup]# ll -h
总用量 57M
-rw-r----- 1 root root  475 1月  27 23:12 backup-my.cnf
-rw-r----- 1 root root  156 1月  27 23:12 binlog.000027
-rw-r----- 1 root root   16 1月  27 23:12 binlog.index
drwxr-x--- 2 root root 4.0K 1月  27 23:12 bookstore
drwxr-x--- 6 root root 4.0K 1月  28 23:51 Full_mysql8020_2021-01-28-23-51-49
-rw-r----- 1 root root 3.5K 1月  27 23:12 ib_buffer_pool
-rw-r----- 1 root root  12M 1月  27 23:12 ibdata1
drwxr-x--- 2 root root 4.0K 1月  27 23:12 mysql
-rw-r----- 1 root root  24M 1月  27 23:12 mysql.ibd
drwxr-x--- 2 root root 4.0K 1月  27 23:12 performance_schema
drwxr-x--- 2 root root 4.0K 1月  27 23:12 sys
-rw-r----- 1 root root  10M 1月  27 23:12 undo_001
-rw-r----- 1 root root  10M 1月  27 23:12 undo_002
-rw-r----- 1 root root   18 1月  27 23:12 xtrabackup_binlog_info
-rw-r----- 1 root root   95 1月  27 23:12 xtrabackup_checkpoints
-rw-r----- 1 root root  547 1月  27 23:12 xtrabackup_info
-rw-r----- 1 root root 2.5K 1月  27 23:12 xtrabackup_logfile
-rw-r----- 1 root root   39 1月  27 23:12 xtrabackup_tablespaces

全量压缩备份

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# ll -h
总用量 2.8M
-rw-r----- 1 root root  459 1月  28 23:51 backup-my.cnf.qp
-rw-r----- 1 root root  190 1月  28 23:51 binlog.000032.qp
-rw-r----- 1 root root   93 1月  28 23:51 binlog.index.qp
drwxr-x--- 2 root root 4.0K 1月  28 23:51 bookstore
-rw-r----- 1 root root  988 1月  28 23:51 ib_buffer_pool.qp
-rw-r----- 1 root root 164K 1月  28 23:51 ibdata1.qp
drwxr-x--- 2 root root 4.0K 1月  28 23:51 mysql
-rw-r----- 1 root root 2.1M 1月  28 23:51 mysql.ibd.qp
drwxr-x--- 2 root root 4.0K 1月  28 23:51 performance_schema
drwxr-x--- 2 root root 4.0K 1月  28 23:51 sys
-rw-r----- 1 root root 219K 1月  28 23:51 undo_001.qp
-rw-r----- 1 root root 207K 1月  28 23:51 undo_002.qp
-rw-r----- 1 root root  105 1月  28 23:51 xtrabackup_binlog_info.qp
-rw-r----- 1 root root   95 1月  28 23:51 xtrabackup_checkpoints
-rw-r----- 1 root root  540 1月  28 23:51 xtrabackup_info.qp
-rw-r----- 1 root root  509 1月  28 23:51 xtrabackup_logfile.qp
-rw-r----- 1 root root  130 1月  28 23:51 xtrabackup_tablespaces.qp

UNDO大小才219K

比较具体表大小

 
[root@localhost bookstore]# ll -h
总用量 288K
-rw-r----- 1 root root 128K 1月  27 23:12 books2.ibd
-rw-r----- 1 root root 160K 1月  27 23:12 books.ibd
[root@localhost bookstore]# cd ..
[root@localhost backup]# cd Full_mysql8020_2021-01-28-23-51-49/bookstore/
[root@localhost bookstore]# ll -h
总用量 24K
-rw-r----- 1 root root 5.6K 1月  28 23:51 books2.ibd.qp
-rw-r----- 1 root root 4.0K 1月  28 23:51 books3.ibd.qp
-rw-r----- 1 root root 4.0K 1月  28 23:51 books4.ibd.qp
-rw-r----- 1 root root 6.9K 1月  28 23:51 books.ibd.qp

books.ibd 160K 压缩后的books.ibd.qp 6.9K

全量恢复启动数据库并给BOOKS表插入一条记录

 
[root@localhost mysql8020debug]# ./log_mysqlc_by_root.sh 
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.20-debug Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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           |
+--------------------+
| bookstore          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.11 sec)

mysql> use bookstore
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> show tables;
+---------------------+
| Tables_in_bookstore |
+---------------------+
| books               |
| books2              |
| books3              |
| books4              |
+---------------------+
4 rows in set (0.01 sec)

mysql> select * from books;
+----+------------------------+-------+---------------------+
| id | title                  | price | publishDate         |
+----+------------------------+-------+---------------------+
|  1 | Java编程思想           | 98.50 | 2005-01-02 0 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09 0 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23 0 |
|  4 | C++编程思想            | 88.50 | 2004-01-09 0 |
|  5 | HeadFirst Java         | 55.70 | 2013-12-17 0 |
|  6 | 疯狂Android            | 19.50 | 2014-07-31 0 |
+----+------------------------+-------+---------------------+
6 rows in set (0.02 sec)

 
mysql> insert into books(title,price,publishDate) values('数据库架构师修炼',102.4,'2021-01-18 00:14:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+--------------------------+--------+---------------------+
| id | title                    | price  | publishDate         |
+----+--------------------------+--------+---------------------+
|  1 | Java编程思想             |  98.50 | 2005-01-02 0 |
|  2 | HeadFirst设计模式        |  55.70 | 2010-11-09 0 |
|  3 | 第一行Android代码        |  69.90 | 2015-06-23 0 |
|  4 | C++编程思想              |  88.50 | 2004-01-09 0 |
|  5 | HeadFirst Java           |  55.70 | 2013-12-17 0 |
|  6 | 疯狂Android              |  19.50 | 2014-07-31 0 |
|  7 | 数据库架构师修炼         | 102.40 | 2021-01-18 00:14:00 |
+----+--------------------------+--------+---------------------+
7 rows in set (0.00 sec)

我们准备使用全量压缩备份来做个全量恢复

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# pwd
/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49

[root@localhost Full_mysql8020_2021-01-28-23-51-49]# ll
总用量 2780
-rw-r----- 1 root root     459 1月  28 23:51 backup-my.cnf.qp
-rw-r----- 1 root root     190 1月  28 23:51 binlog.000032.qp
-rw-r----- 1 root root      93 1月  28 23:51 binlog.index.qp
drwxr-x--- 2 root root    4096 1月  28 23:51 bookstore
-rw-r----- 1 root root     988 1月  28 23:51 ib_buffer_pool.qp
-rw-r----- 1 root root  167011 1月  28 23:51 ibdata1.qp
drwxr-x--- 2 root root    4096 1月  28 23:51 mysql
-rw-r----- 1 root root 2183662 1月  28 23:51 mysql.ibd.qp
drwxr-x--- 2 root root    4096 1月  28 23:51 performance_schema
drwxr-x--- 2 root root    4096 1月  28 23:51 sys
-rw-r----- 1 root root  223243 1月  28 23:51 undo_001.qp
-rw-r----- 1 root root  211918 1月  28 23:51 undo_002.qp
-rw-r----- 1 root root     105 1月  28 23:51 xtrabackup_binlog_info.qp
-rw-r----- 1 root root      95 1月  28 23:51 xtrabackup_checkpoints
-rw-r----- 1 root root     540 1月  28 23:51 xtrabackup_info.qp
-rw-r----- 1 root root     509 1月  28 23:51 xtrabackup_logfile.qp
-rw-r----- 1 root root     130 1月  28 23:51 xtrabackup_tablespaces.qp

[root@localhost backup]# ll
总用量 57400
-rw-r----- 1 root root      475 1月  27 23:12 backup-my.cnf
-rw-r----- 1 root root      156 1月  27 23:12 binlog.000027
-rw-r----- 1 root root       16 1月  27 23:12 binlog.index
drwxr-x--- 2 root root     4096 1月  27 23:12 bookstore
drwxr-x--- 6 root root     4096 1月  28 23:51 Full_mysql8020_2021-01-28-23-51-49
-rw-r----- 1 root root     3578 1月  27 23:12 ib_buffer_pool
-rw-r----- 1 root root 12582912 1月  27 23:12 ibdata1
drwxr-x--- 2 root root     4096 1月  27 23:12 mysql
-rw-r----- 1 root root 25165824 1月  27 23:12 mysql.ibd
drwxr-x--- 2 root root     4096 1月  27 23:12 performance_schema
drwxr-x--- 2 root root     4096 1月  27 23:12 sys
-rw-r----- 1 root root 10485760 1月  27 23:12 undo_001
-rw-r----- 1 root root 10485760 1月  27 23:12 undo_002
-rw-r----- 1 root root       18 1月  27 23:12 xtrabackup_binlog_info
-rw-r----- 1 root root       95 1月  27 23:12 xtrabackup_checkpoints
-rw-r----- 1 root root      547 1月  27 23:12 xtrabackup_info
-rw-r----- 1 root root     2560 1月  27 23:12 xtrabackup_logfile
-rw-r----- 1 root root       39 1月  27 23:12 xtrabackup_tablespaces

1 停服务

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# service mysqld stop
Shutting down MySQL.... SUCCESS!

2 准备恢复

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf
--host=localhost --user=root --password=123456 --port=3306 
--prepare --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: recognized client arguments: --host=localhost --user=root --password=* --port=3306 --prepare=1 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
xtrabackup: cd to /u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
Operating system error number 2 in a file operation.
The error means the system cannot find the path specified.
xtrabackup: Warning: cannot open ./xtrabackup_logfile. will try to find.
Operating system error number 2 in a file operation.
The error means the system cannot find the path specified.
  xtrabackup: Fatal error: cannot find ./xtrabackup_logfile.

大意是没有找到该文件，进目录查看一下发现有该文件，不过文件结尾是。GP。需要解压查看帮助中的解压命令

 
  [root@localhost ~]# xtrabackup -help
                        Force rollback prepared InnoDB transactions.
  --decompress        Decompresses all files with the .qp extension in a backup
                      previously made with the --compress option.

带上解压命令还是报错

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --prepare --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ --decompress
xtrabackup: recognized client arguments: --host=localhost --user=root --password=* --port=3306 --prepare=1 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ --decompress=1 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
Error: --decompress and --apply-log are mutually exclusive

意思是说应用日志命令和解压命令是互扯的命令！看来--prepare默认带--apply-log 那就分开来执行

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]#  xtrabackup 
--decompress --remove-original --parallel=4 
--target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --parallel=4 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
210206 02:08:52 [01] decompressing ./mysql.ibd.qp
210206 02:08:52 [02] decompressing ./backup-my.cnf.qp
210206 02:08:52 [03] decompressing ./mysql/slow_log.CSV.qp
210206 02:08:52 [04] decompressing ./mysql/slow_log.CSM.qp
sh: qpress: 未找到命令
sh: qpress: 未找到命令
cat: 写入错误sh: qpress: 未找到命令
: 断开的管道
sh: qpress: 未找到命令
cat: 写入错误: 断开的管道
cat: 写入错误: 断开的管道
cat: 写入错误: 断开的管道
Error: decrypt and decompress thread 0 failed.
Error: decrypt and decompress thread 1 failed.
Error: decrypt and decompress thread 2 failed.
Error: decrypt and decompress thread 3 failed.

居然要调用系统命令QPRESS ？那就下载安装

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
--2021-02-06 02:12:37--  https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
正在解析主机 repo.percona.com (repo.percona.com)... 167.71.118.3, 167.99.233.229, 157.245.119.64
正在连接 repo.percona.com (repo.percona.com)|167.71.118.3|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：32624 (32K) [application/x-redhat-package-manager]
正在保存至: “qpress-11-1.el7.x86_64.rpm”
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# rpm -ivh qpress-11-1.el7.x86_64.rpm 
警告：qpress-11-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 8507efa5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:qpress-11-1.el7                  ################################# [100%]

继续执行解压

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# xtrabackup --decompress --remove-original --parallel=4 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/xtrabackup: recognized client arguments: --decompress=1 --remove-original=1 --parallel=4 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
210206 02:13:33 [01] decompressing ./mysql.ibd.qp
210206 02:13:33 [04] decompressing ./mysql/slow_log.CSM.qp
210206 02:13:33 [03] decompressing ./mysql/slow_log.CSV.qp
210206 02:13:33 [02] decompressing ./backup-my.cnf.qp
.....
210206 02:13:34 [01] removing ./performance_schema/replication_conn_157.sdi.qp
210206 02:13:34 [02] removing ./performance_schema/events_transacti_130.sdi.qp
210206 02:13:34 completed OK!

已经看到解压的文件

 
[root@localhost Full_mysql8020_2021-01-28-23-51-49]# ll -h
总用量 57M
-rw-r--r-- 1 root root  475 2月   6 02:13 backup-my.cnf
-rw-r--r-- 1 root root  156 2月   6 02:13 binlog.000032
-rw-r--r-- 1 root root   16 2月   6 02:13 binlog.index
drwxr-x--- 2 root root 4.0K 2月   6 02:13 bookstore
-rw-r--r-- 1 root root 3.6K 2月   6 02:13 ib_buffer_pool
-rw-r--r-- 1 root root  12M 2月   6 02:13 ibdata1
drwxr-x--- 2 root root 4.0K 2月   6 02:13 mysql
-rw-r--r-- 1 root root  24M 2月   6 02:13 mysql.ibd
drwxr-x--- 2 root root 4.0K 2月   6 02:13 performance_schema
drwxr-x--- 2 root root 4.0K 2月   6 02:13 sys
-rw-r--r-- 1 root root  10M 2月   6 02:13 undo_001
-rw-r--r-- 1 root root  10M 2月   6 02:13 undo_002
-rw-r--r-- 1 root root   18 2月   6 02:13 xtrabackup_binlog_info
-rw-r----- 1 root root   95 1月  28 23:51 xtrabackup_checkpoints
-rw-r--r-- 1 root root  602 2月   6 02:13 xtrabackup_info
-rw-r--r-- 1 root root 2.5K 2月   6 02:13 xtrabackup_logfile
-rw-r--r-- 1 root root   39 2月   6 02:13 xtrabackup_tablespaces

准备应用日志

[root@localhost Full_mysql8020_2021-01-28-23-51-49]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --prepare --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
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: --host=localhost --user=root --password=* --port=3306 --prepare=1 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup version 8.0.14 based on MySQL server 8.0.21 Linux (x86_64) (revision id: 113f3d7)
xtrabackup: cd to /u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: This target seems to be not prepared yet.
Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(18284392)
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
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 = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Not using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 8 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
The log sequence number 18216270 in the system tablespace does not match the log sequence number 18284392 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 18284082, whereas checkpoint_lsn = 18284392
Doing recovery: scanned up to log sequence number 18284422
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 11316
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.21 started; log sequence number 18284422
Allocated tablespace ID 5 for bookstore/books3, old maximum was 0
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 18284422
Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
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
PUNCH HOLE support available
Mutexes and rw_locks use GCC atomic builtins
Uses event mutexes
GCC builtin __atomic_thread_fence() is used for memory barrier
Compressed tables use zlib 1.2.7
Number of pools: 1
Not using CPU crc32 instructions
Directories to scan './'
Scanning './'
Completed space ID check of 8 files.
Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile101
fallocate() failed with errno 95 - falling back to writing NULLs.
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Creating log file ./ib_logfile1
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=18284556
Starting to parse redo log at lsn = 18284556, whereas checkpoint_lsn = 18284556
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
GTID recovery trx_no: 11316
Removed temporary tablespace data file: "ibtmp1"
Creating shared tablespace for temporary tables
Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
File './ibtmp1' size is now 12 MB.
Scanning temp tablespace dir:'./#innodb_temp/'
Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active.
8.0.21 started; log sequence number 18284556
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Trying to access missing tablespace 4294967294
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 18284556
210206 02:15:30 completed OK!
[root@localhost Full_mysql8020_2021-01-28-23-51-49]#

复制文件

 
[root@localhost data]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --host=localhost --user=root --password=* --port=3306 --copy-back=1 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
Original data directory /u01/mysql/mysql8020debug/data is not empty!

要清空目标目录

 
[root@localhost data]# rm -f *.*
[root@localhost data]# ll
总用量 131096
drwxr-x--- 2 mysql mysql     4096 1月  28 23:36 bookstore
-rw-r----- 1 mysql mysql     3431 2月   6 01:42 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 2月   6 01:42 ibdata1
-rw-r----- 1 mysql mysql 50331648 2月   6 01:42 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 7月  19 2020 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 2月   6 01:42 #innodb_temp
drwxr-x--- 2 mysql mysql     4096 7月  19 2020 mysql
drwxr-x--- 2 mysql mysql     4096 7月  19 2020 performance_schema
drwxr-x--- 2 mysql mysql     4096 7月  19 2020 sys
-rw-r----- 1 mysql mysql 10485760 2月   6 01:42 undo_001
-rw-r----- 1 mysql mysql 10485760 2月   6 01:42 undo_002
[root@localhost data]# rm -f *
rm: 无法删除"bookstore": 是一个目录
rm: 无法删除"#innodb_temp": 是一个目录
rm: 无法删除"mysql": 是一个目录
rm: 无法删除"performance_schema": 是一个目录
rm: 无法删除"sys": 是一个目录
[root@localhost data]# ll
总用量 20
drwxr-x--- 2 mysql mysql 4096 1月  28 23:36 bookstore
drwxr-x--- 2 mysql mysql 4096 2月   6 01:42 #innodb_temp
drwxr-x--- 2 mysql mysql 4096 7月  19 2020 mysql
drwxr-x--- 2 mysql mysql 4096 7月  19 2020 performance_schema
drwxr-x--- 2 mysql mysql 4096 7月  19 2020 sys
[root@localhost data]# rm -rf bookstore/ mysql/ performance_schema/ sys/ \#innodb_temp/
[root@localhost data]# ll
总用量 0

再次执行拷贝

 
[root@localhost data]# xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf --host=localhost --user=root --password=123456 --port=3306 --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/
xtrabackup: recognized server arguments: --datadir=/u01/mysql/mysql8020debug/data 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --host=localhost --user=root --password=* --port=3306 --copy-back=1 --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/ 
xtrabackup version 8.0.14 based on MySQL server 8.0.21  Linux  (x86_64) (revision id: 113f3d7)
210206 02:21:04 [01] Copying undo_001 to /u01/mysql/mysql8020debug/data/undo_001
210206 02:21:04 [01]        ...done
210206 02:21:05 [01] Copying undo_002 to /u01/mysql/mysql8020debug/data/undo_002
210206 02:21:05 [01]        ...done
210206 02:21:05 [01] Copying ib_logfile0 to /u01/mysql/mysql8020debug/data/ib_logfile0
210206 02:21:05 [01]        ...done
210206 02:21:05 [01] Copying ib_logfile1 to /u01/mysql/mysql8020debug/data/ib_logfile1
210206 02:21:06 [01]        ...done
210206 02:21:06 [01] Copying ibdata1 to /u01/mysql/mysql8020debug/data/ibdata1
210206 02:21:06 [01]        ...done
210206 02:21:06 [01] Copying binlog.000032 to /u01/mysql/mysql8020debug/data/binlog.000032
210206 02:21:06 [01]        ...done
210206 02:21:06 [01] Copying binlog.index to /u01/mysql/mysql8020debug/data/binlog.index
210206 02:21:06 [01]        ...done
210206 02:21:06 [01] Copying ./xtrabackup_info to /u01/mysql/mysql8020debug/data/xtrabackup_info
210206 02:21:06 [01]        ...done
...............................
210206 02:21:08 [01] Copying ./performance_schema/setup_consumers_96.sdi to /u01/mysql/mysql8020debug/data/performance_schema/setup_consumers_96.sdi
210206 02:21:08 [01]        ...done
210206 02:21:08 completed OK!

目标数据目录已有了文件

 
[root@localhost data]# ll -h
总用量 165M
-rw-r----- 1 root root  156 2月   6 02:21 binlog.000032
-rw-r----- 1 root root   14 2月   6 02:21 binlog.index
drwxr-x--- 2 root root 4.0K 2月   6 02:21 bookstore
-rw-r----- 1 root root 3.6K 2月   6 02:21 ib_buffer_pool
-rw-r----- 1 root root  12M 2月   6 02:21 ibdata1
-rw-r----- 1 root root  48M 2月   6 02:21 ib_logfile0
-rw-r----- 1 root root  48M 2月   6 02:21 ib_logfile1
-rw-r----- 1 root root  12M 2月   6 02:21 ibtmp1
drwxr-x--- 2 root root 4.0K 2月   6 02:21 mysql
-rw-r----- 1 root root  24M 2月   6 02:21 mysql.ibd
drwxr-x--- 2 root root 4.0K 2月   6 02:21 performance_schema
drwxr-x--- 2 root root 4.0K 2月   6 02:21 sys
-rw-r----- 1 root root  10M 2月   6 02:21 undo_001
-rw-r----- 1 root root  10M 2月   6 02:21 undo_002
-rw-r----- 1 root root  602 2月   6 02:21 xtrabackup_info
-rw-r----- 1 root root    1 2月   6 02:21 xtrabackup_master_key_id

修改目录启动服务

 
chown -R mysql:mysql /u01/mysql/mysql8020debug/data
chmod -R 755 /u01/mysql/mysql8020debug/data

[root@localhost data]# service mysqld start
Starting MySQL... SUCCESS!

检查数据数据库架构师不在了

 
mysql> select * from books;
+----+--------------------------+--------+---------------------+
| id | title                    | price  | publishDate         |
+----+--------------------------+--------+---------------------+
|  1 | Java编程思想             |  98.50 | 2005-01-02 0 |
|  2 | HeadFirst设计模式        |  55.70 | 2010-11-09 0 |
|  3 | 第一行Android代码        |  69.90 | 2015-06-23 0 |
|  4 | C++编程思想              |  88.50 | 2004-01-09 0 |
|  5 | HeadFirst Java           |  55.70 | 2013-12-17 0 |
|  6 | 疯狂Android              |  19.50 | 2014-07-31 0 |
|  7 | 数据库架构师修炼         | 102.40 | 2021-01-18 00:14:00 |
+----+--------------------------+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from books;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: bookstore

+----+------------------------+-------+---------------------+
| id | title                  | price | publishDate         |
+----+------------------------+-------+---------------------+
|  1 | Java编程思想           | 98.50 | 2005-01-02 0 |
|  2 | HeadFirst设计模式      | 55.70 | 2010-11-09 0 |
|  3 | 第一行Android代码      | 69.90 | 2015-06-23 0 |
|  4 | C++编程思想            | 88.50 | 2004-01-09 0 |
|  5 | HeadFirst Java         | 55.70 | 2013-12-17 0 |
|  6 | 疯狂Android            | 19.50 | 2014-07-31 0 |
+----+------------------------+-------+---------------------+
6 rows in set (0.12 sec)

总结全量压缩恢复流程

全量恢复
1、 安装解压算法的RPM,如果没有安装的话
 rpm -qa|grep qpress 
 wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
 rpm -ivh qpress-11-1.el7.x86_64.rpm 

2、停止掉运行的数据库实例：
systemctl stop mysqld
service mysql stop

3、删除数据目录 注意里面包含日志
   rm -rf data/

4、解压：
   xtrabackup --decompress --remove-original --parallel=4 
     --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/

5、准备(应用日志)   
   xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf 
   --host=localhost --user=root --password=123456 --port=3306 
    --prepare --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/

6、拷回数据：
    xtrabackup --defaults-file=/u01/mysql/mysql8020debug/my.cnf 
    --host=localhost --user=root --password=123456 --port=3306 
     --copy-back --target-dir=/u01/mysql/mysql8020debug/backup/Full_mysql8020_2021-01-28-23-51-49/

7、修改目录属性启动数据库：
  chown -R mysql:mysql /u01/mysql/mysql8020debug/data
  chmod -R 755 /u01/mysql/mysql8020debug/data

8、启动数据库实例:

 systemctl start mysqld
 service start mysqld

查看更多关于MYSQL 8 物理全量恢复2的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did162076

更新时间：2022-12-13 阅读：30次