关于mysql:MySQL运维实战之备份和恢复81xtrabackup全量备份

作者：俊达
xtrabackup是percona开源的mysql物理备份工具。xtrabackup 8.0反对mysql 8.0版本的备份和复原。xtrabackup 2.4反对mysql 5.7及以下版本的备份和复原。这里咱们以xtrabackup 8.0为例解说备份和复原的具体操作办法。xtrabackup 2.4版本的应用上和8.0版本相差并不大。

全量备份

本地全量备份

将整个数据库备份到本地目录。

xtrabackup --backup --slave-info  -u root -H 127.0.0.1 -P3380 -pabc123 --target-dir /data/backup/    2>/tmp/xtrabackup.log

要害参数阐明：

backup：发动全量备份
-u, -H, -P, -p：连贯mysql实例，用户名、主机IP、端口、明码
-slave-info：记录slave复制位点信息
-target-dir：备份文件的寄存门路
2>/tmp/xtrabackup.log：将备份过程中的日志重定向到/tmp/xtrabackup.log文件中

备份过程中的日志信息会输入到规范谬误中，个别能够重定向到文本文件中
备份进去的文件：

-rw-r----- 1 root root       448 6月  26 14:46 backup-my.cnf-rw-r----- 1 root root       610 6月  26 14:46 binlog.000020-rw-r----- 1 root root        36 6月  26 14:46 binlog.indexdrwxr-x--- 2 root root      4096 6月  27 11:03 demo-rw-r--r-- 1 root root      9326 6月  27 11:03 export.log-rw-r----- 1 root root      5554 6月  26 14:46 ib_buffer_pool-rw-r----- 1 root root 134217728 6月  27 11:03 ibdata1-rw-r----- 1 root root  12582912 6月  27 11:03 ibtmp1drwxr-x--- 2 root root      4096 6月  27 11:03 #innodb_redodrwxr-x--- 2 root root      4096 6月  26 14:46 mysql-rw-r----- 1 root root  25165824 6月  26 16:08 mysql.ibddrwxr-x--- 2 root root      4096 6月  26 14:46 performance_schema-rw-r--r-- 1 root root      7883 6月  26 16:08 prepare_full.log-rw-r--r-- 1 root root      9109 6月  26 16:08 prepare_full.log.2drwxr-x--- 2 root root      4096 6月  27 11:03 sys-rw-r----- 1 root root  16777216 6月  26 16:08 undo_001-rw-r----- 1 root root  16777216 6月  26 16:08 undo_002-rw-r----- 1 root root       105 6月  26 14:46 xtrabackup_binlog_info-rw-r----- 1 root root       134 6月  27 11:03 xtrabackup_checkpoints-rw-r----- 1 root root       673 6月  26 14:46 xtrabackup_info-rw-r----- 1 root root   8388608 6月  26 16:08 xtrabackup_logfile-rw-r----- 1 root root       154 6月  26 14:46 xtrabackup_slave_info-rw-r----- 1 root root        39 6月  27 11:03 xtrabackup_tablespaces

xtrabackup_logfile：innodb redo日志备份

xtrabackup_binlog_info：binlog位点信息和GTID信息。应用该备份复原后，须要从该binlog位点进行增量复原。

xtrabackup_slave_info：备份实例的slave位点信息。

xtrabackup_checkpoints

backup_type：full-prepared /

from_lsn：0示意全量备份。非0示意增量备份起始日志序列号。

to_lsn：备份完结时的checkpoint lsn。也是下一个增量备份的开始lsn。如果数据块的lsn小于to_lsn，则增量备份不须要备份这些数据块。

last_lsn：apply log时须要利用到的最大日志序列号。超过last_lsn的日志不须要利用到数据文件中

flushed_lsn：备份完结时，实例的checkpoint lsn。

各个目录：数据库和表

ibdata1, mysql.ibd：innodb表空间文件

undo_001 undo_002：undo表空间

其中xtrabackup_checkpoints记录了innodb的lsn，在增量备份和数据库复原中都会依赖这个文件。

# cat xtrabackup_checkpointsbackup_type = full-preparedfrom_lsn = 0to_lsn = 22021528last_lsn = 24406197flushed_lsn = 24210721redo_memory = 0redo_frames = 0

日志信息
xtrabackup会将备份过程中的重要信息以日志模式输入到规范谬误。倡议将这些日志重定向到文件。通过这些日志信息，能够察看到xtrabackup备份过程中的重要操作。比方何时锁表、何时解锁、何时开始备份、何时实现备份、备份是否胜利等。

2023-06-21T10:29:36.685670+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 3380, socket: not set2023-06-21T10:29:36.714220+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.322023-06-21T10:29:36.719676+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...2023-06-21T10:29:36.721377+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().2023-06-21T10:29:36.721453+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysql8.0/data/2023-06-21T10:29:36.721478+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 655362023-06-21T10:29:37.086200+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 220211702023-06-21T10:29:43.354808+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files2023-06-21T10:29:44.757598+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files2023-06-21T10:29:44.757659+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS2023-06-21T10:29:44.789922+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status2023-06-21T10:29:44.796572+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/mysql8.0/binlog/binlog.000010 to /data/backup/binlog.000010 up to position 13532023-06-21T10:29:44.796664+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/mysql8.0/binlog/binlog.000010 to /data/backup/binlog.0000102023-06-21T10:29:44.806407+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/binlog.index2023-06-21T10:29:44.806515+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/binlog.index2023-06-21T10:29:44.811951+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_slave_info2023-06-21T10:29:44.812042+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_slave_info2023-06-21T10:29:44.819173+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_binlog_info2023-06-21T10:29:44.819251+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_binlog_info2023-06-21T10:29:44.823595+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...2023-06-21T10:29:44.861004+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '22021528'2023-06-21T10:29:44.861053+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 244061972023-06-21T10:29:44.862265+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (24414089)2023-06-21T10:29:45.885625+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE2023-06-21T10:29:45.885916+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked2023-06-21T10:29:45.897933+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000010', position '1353', GTID of the last change '58224b02-09b7-11ee-90bd-fab81f64ee00:1-5827,7caa9a48-b325-11ed-8541-fab81f64ee00:1-27'2023-06-21T10:29:45.897948+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL slave binlog position: master host '172.16.121.234', purge list '58224b02-09b7-11ee-90bd-fab81f64ee00:1-5827,7caa9a48-b325-11ed-8541-fab81f64ee00:1-27', channel name: ''2023-06-21T10:29:45.898110+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/backup-my.cnf2023-06-21T10:29:45.898177+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/backup-my.cnf2023-06-21T10:29:45.906019+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_info2023-06-21T10:29:45.906100+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_info2023-06-21T10:29:46.919361+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (22021528) to (24511271) was copied.2023-06-21T10:29:47.182490+08:00 0 [Note] [MY-011825] [Xtrabackup] completed 0

备份到近程主机

xtrabackup反对流式备份，备份文件本地不落盘，间接备份到近程主机，防止单个主机故障导致数据库和备份文件同时失落。

xtrabackup --backup --slave-info  -u root -H 127.0.0.1 -P3380 \  -pabc123 --stream=xbstream --target-dir /data/backup/ \ 2>/data/backup/xtrabackup.log \| ssh root@172.16.121.236 "cat -  > /data/backup/backup.s1"

--stream=xbstream参数，备份数据输入到规范输入

通过管道，将备份数据保留到近程主机。

压缩备份文件

能够在备份服务器上进行压缩，也能够在近程服务器上进行压缩。

xtrabackup --backup --slave-info  -u root -H 127.0.0.1 -P3380 \  -pabc123 --stream=xbstream --target-dir /data/backup/ \ 2>/data/backup/xtrabackup.log \| gzip - > /data/backup/backup1.gz

通过管道将数据发送到近程服务器压缩：

./bin/xtrabackup --backup --slave-info  -u root -H 127.0.0.1 -P3380 \  -pabc123 --stream=xbstream --target-dir /data/backup/ \ 2>/data/backup/xtrabackup.log \| ssh root@172.16.121.236 "gzip - > /data/backup/backup1.gz"

压缩软件能够应用gzip，也能够应用pigz。pigz反对并行压缩和并行解压缩，能够放慢压缩和解压的速度。

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw