免费网站的建设,温州做网站哪家比较好,建筑设计作品展示网站,企业管理咨询服务机构作者#xff1a;俊达 恢复全量备份大致可以分成以下几步#xff1a;解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例。
解压文件
如果备份时使用了xbstream#xff0c;需要先解压备份文件。 我们备份时使用了–streamxbstream和gzip压缩俊达 恢复全量备份大致可以分成以下几步解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例。
解压文件
如果备份时使用了xbstream需要先解压备份文件。 我们备份时使用了–streamxbstream和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 21命令执行完成后查看日志信息
2023-06-26T10:20:01.94805408:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithmcrc32 --innodb_log_checksums1 --innodb_data_file_pathibdata1:128M:autoextend --innodb_log_file_size50331648 --innodb_page_size16384 --innodb_undo_directory./ --innodb_undo_tablespaces2 --server-id0 --innodb_log_checksumsON --innodb_redo_log_encrypt0 --innodb_undo_log_encrypt0
2023-06-26T10:20:01.94839708:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare1 --target-dir.2023-06-26T10:20:02.35913008: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.35926208: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.35928308:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally!
2023-06-26T10:20:02.35929608:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery.
2023-06-26T10:20:02.36796208: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.41898508:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 303942872023-06-26T10:20:05.10353208:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2023-06-26T10:20:05.10508208:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN30394340.
2023-06-26T10:20:05.10511308:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files.
2023-06-26T10:20:05.10528408:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./#innodb_redo/#ib_redo0
2023-06-26T10:20:05.20795308: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.21873508: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.22516808:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN303943802023-06-26T10:20:07.11872408:00 0 [Note] [MY-011825] [Xtrabackup] completed OKxtrabackup 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-memory2G --target-dir . prepare.log 21将数据文件复制到数据目录
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/binloginnodb_data_file_pathibdata1: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参数需要和备份时实例的设置保持一致
复制文件 先查看一下恢复出来的文件
root172-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 dont 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] 134042023-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日志
[root172-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-modeoff. 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_repositoryTABLE)恢复时将复制信息也恢复出来了。 执行reset slave all清理掉就可以了。 至此我们已经成功将xtrabackup的全量备份恢复出来。
更多技术信息请查看云掣官网https://yunche.pro/?tyrgw
