MySQL运维实战之备份和恢复(8.1)xtrabackup全量备份
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.index drwxr-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 ibtmp1 drwxr-x--- 2 root root 4096 6月 27 11:03 #innodb_redo drwxr-x--- 2 root root 4096 6月 26 14:46 mysql -rw-r----- 1 root root 25165824 6月 26 16:08 mysql.ibd drwxr-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.2 drwxr-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_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 22021528 last_lsn = 24406197 flushed_lsn = 24210721 redo_memory = 0 redo_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 set 2023-06-21T10:29:36.714220+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.32 2023-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 65536 2023-06-21T10:29:37.086200+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 22021170 2023-06-21T10:29:43.354808+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files 2023-06-21T10:29:44.757598+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files 2023-06-21T10:29:44.757659+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 2023-06-21T10:29:44.789922+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status 2023-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 1353 2023-06-21T10:29:44.796664+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/mysql8.0/binlog/binlog.000010 to /data/backup/binlog.000010 2023-06-21T10:29:44.806407+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/binlog.index 2023-06-21T10:29:44.806515+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/binlog.index 2023-06-21T10:29:44.811951+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_slave_info 2023-06-21T10:29:44.812042+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_slave_info 2023-06-21T10:29:44.819173+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_binlog_info 2023-06-21T10:29:44.819251+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_binlog_info 2023-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 24406197 2023-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 INSTANCE 2023-06-21T10:29:45.885916+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked 2023-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-fab81f64e e00:1-27', channel name: '' 2023-06-21T10:29:45.898110+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/backup-my.cnf 2023-06-21T10:29:45.898177+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/backup-my.cnf 2023-06-21T10:29:45.906019+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/xtrabackup_info 2023-06-21T10:29:45.906100+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/xtrabackup_info 2023-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 O
备份到远程主机
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支持并行压缩和并行解压缩,可以加快压缩和解压的速度。