MySQL运维实战之备份和恢复（8.4）xtrabackup恢复全量备份
恢复全量备份
恢复全量备份大致可以分成以下几步：解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例。
解压文件
如果备份时使用了xbstream，需要先解压备份文件。
我们备份时使用了--stream=xbstream和gzip压缩，先使用gunzip接解压缩，再使用xbstream将文件提取出来。
# gunzip backup_full.gz # xbstream -x -v < backup_full ibdata1 sys/sys_config.ibd demo/tb.ibd demo/taa.ibd ......
xbstream使用参数-x提取文件，加上参数-v后，会输出解压的文件列表。
应用redo日志
解压完成后，需要使用xtrabackup --prepare命令，将xtrabackup_logfile应用到备份的数据文件中，将数据库恢复到备份结束时的状态。
xtrabackup --prepare命令会根据xtrabackup_checkpoints文件中记录的last_lsn来确定需要应用哪些日志。
# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 27517178 last_lsn = 30394287 flushed_lsn = 30328603 redo_memory = 0 redo_frames = 0
执行prepare命令：
xtrabackup --prepare --target-dir . > prepare.log 2>&1
命令执行完成后，查看日志信息：
2023-06-26T10:20:01.948054+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:128M:autoextend --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 2023-06-26T10:20:01.948397+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=. 2023-06-26T10:20:02.359130+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 27517178 in redo log file ./#innodb_redo/#ib_redo0. 2023-06-26T10:20:02.359262+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 19019361 in the system tablespace does not match the log sequence number 27517178 in the redo log files! 2023-06-26T10:20:02.359283+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally! 2023-06-26T10:20:02.359296+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery. 2023-06-26T10:20:02.367962+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 27516952, whereas checkpoint_lsn = 27517178 and start_lsn = 27516928 2023-06-26T10:20:02.418985+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 30394287 2023-06-26T10:20:05.103532+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2023-06-26T10:20:05.105082+08:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN=30394340. 2023-06-26T10:20:05.105113+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files. 2023-06-26T10:20:05.105284+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0 2023-06-26T10:20:05.207953+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./#innodb_redo/#ib_redo0_tmp with file_id 0 with size 33554432 bytes 2023-06-26T10:20:05.218735+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./#innodb_redo/#ib_redo0_tmp to ./#innodb_redo/#ib_redo0 2023-06-26T10:20:05.225168+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=30394380 2023-06-26T10:20:07.118724+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK
xtrabackup prepare命令会启动一个临时的mysql实例，依赖innodb的恢复机制来应用redo文件。xtrabackup对恢复代码进行了一些改造，只应用序列号不大于last_lsn的redo日志，这一点可以从这一行日志中看出：“Doing recovery: scanned up to log sequence number 30394287”。
恢复日志最后一行显示“completed OK”，表示prepare执行成功。如果最后一行日志不是“completed OK”，说明prepare执行过程中有问题，需要根据输出的日志分析具体原因。prepare完成后，xtraback_checkpoints文件中backup_type变成了full-prepared。
# cat xtrabackup_checkpoints backup_type = full-prepared ......
xtrabackup启动的mysql临时实例buffer pool默认为100M，可以通过参数--use-memory适当增加内存，加快恢复的速度。官方文档建议将内存设置为1-2G。
xtrabackup --prepare --use-memory=2G --target-dir . > prepare.log 2>&1
将数据文件复制到数据目录
xtrabackup prepare完成后的数据库，可以用来直接启动。启动实例之前，需要将文件复制（或移动）到目标实例的数据目录中。目录需要依据目标实例的参数来确定。
这里我们将实例恢复到/data/full_restore路径下
mkdir -p /data/full_restore/{data,binlog,relaylog,log,run,tmp}my.cnf配置文件如下：
## /data/full_restore/my.cnf [mysqld] datadir=/data/full_restore/data log_bin=/data/full_restore/binlog/binlog innodb_data_file_path=ibdata1:128M:autoextend ......
复制文件时，有几点需要注意：
数据库目录复制到目标实例datadir
如果设置了innodb_data_home_dir，则需要将ibdata文件复制到对应目录。默认情况下innodb_data_home_dir和datadir一样。
如果log_bin目录和datadir不一样，需要将binlog和binlog.index文件复制到log_bin指定的目录。binlog.index中记录的binlog路径也要做相应的调整。
innodb_data_file_path参数需要和备份时实例的设置保持一致
复制文件
先查看一下恢复出来的文件：
root@172-16-121-234 full]# tree -p -L 1 . . ├── [-rw-r--r--] backup_full ├── [-rw-r--r--] backup_full.log ├── [-rw-r-----] backup-my.cnf ├── [-rw-r-----] binlog.000020 ├── [-rw-r-----] binlog.index ├── [drwxr-x---] demo ├── [-rw-r-----] ib_buffer_pool ├── [-rw-r-----] ibdata1 ├── [-rw-r-----] ibtmp1 ├── [drwxr-x---] #innodb_redo ├── [drwxr-x---] mysql ├── [-rw-r-----] mysql.ibd ├── [drwxr-x---] performance_schema ├── [-rw-r--r--] prepare.log ├── [drwxr-x---] sys ├── [-rw-r-----] undo_001 ├── [-rw-r-----] undo_002
## 复制文件 cd /data/backup/full cp -r * /data/full_restore/data cp binlog.* /data/full_restore/binlog/ ## 修改binlog.index # cat /data/full_restore/binlog/binlog.index /data/mysql8.0/binlog/binlog.000020 # sed -i -e 's/mysql8.0/full_restore/' /data/full_restore/binlog/binlog.index ## 移除无用的binlog相关文件 rm /data/full_restore/data/binlog.*
启动实例
启动实例之前，需要先修改恢复出来的文件owner：
chown -R mysql:mysql /data/full_restore/
启动实例：
# mysqld_safe --defaults-file=/data/full_restore/my.cnf & [1] 13010 # 2023-06-26T03:19:12.376984Z mysqld_safe error: log-error set to '/data/full_restore/log/alert.log', however file don't exists. Create writable for user 'mysql'.
如果启动时报日志文件不存在，先创建文件再启动：
touch /data/full_restore/log/alert.log chown mysql:mysql /data/full_restore/log/alert.log # mysqld_safe --defaults-file=/data/full_restore/my.cnf & [1] 13404 2023-06-26T03:21:35.299596Z mysqld_safe Logging to '/data/full_restore/log/alert.log'. 2023-06-26T03:21:35.340097Z mysqld_safe Starting mysqld daemon with databases from /data/full_restore/data
查看mysql日志：
[root@172-16-121-234 full]# tail /data/full_restore/log/alert.log 2023-06-26T03:21:38.109286Z 0 [System] [MY-010229] [Server] Starting XA crash recovery... 2023-06-26T03:21:38.152446Z 0 [System] [MY-010232] [Server] XA crash recovery finished. 2023-06-26T03:21:38.414645Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2023-06-26T03:21:38.414702Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2023-06-26T03:21:38.491284Z 0 [ERROR] [MY-010544] [Repl] Failed to open the relay log '/data/mysql8.0/relaylog/relaylog.000006' (relay_log_pos 407). 2023-06-26T03:21:38.491320Z 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file '/data/full_restore/relaylog/relaylog.index' during relay log initialization. 2023-06-26T03:21:38.495637Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it. 2023-06-26T03:21:38.495677Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories. 2023-06-26T03:21:38.495730Z 0 [Warning] [MY-010530] [Repl] Detected misconfiguration: replication channel '' was configured with AUTO_POSITION = 1, but the server was started with --gtid-mode=off. Either reconfigure replication using CHANGE MASTER TO MASTER_AUTO_POSITION = 0 FOR CHANNEL '', or change GTID_MODE to some value other than OFF, before starting the slave receiver thread. 2023-06-26T03:21:38.499008Z 0 [System] [MY-010931] [Server] /opt/mysql/bin/mysqld: ready for connections. Version: '8.0.32' socket: '/data/full_restore/run/mysql.sock' port: 6380 MySQL Community Server - GPL.
从日志中可以看到，实例已经启动，但是有一些复制相关的报错。这是由于mysql复制信息存储在表中(relay_log_info_repository=TABLE)，恢复时将复制信息也恢复出来了。 执行reset slave all清理掉就可以了。
至此，我们已经成功将xtrabackup的全量备份恢复出来。
