xtrabackup备份恢复Mysql数据库_xtrabackup恢复-CSDN博客

xtrabackup是物理备份，会备份data目录里的所有文件，但是不会备份/log里的文件。
并备份期间产生的redo log,并不是备份binlog来前滚。

1.解压xtrabackup软件：
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ ls
mysql percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ tar -xvf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
...
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ mv percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17 pbx
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ ls
mysql pbx percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz

2.加入环境变量：
vi .bash_profile
PATH=$PATH:$HOME/bin:/home/mysql/mysql/bin:/home/mysql/pbx/bin
source .bash_profile
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ which xtrabackup
~/pbx/bin/xtrabackup

3.了解基本命令，提供了innobackupex和xtrabackup两个备份命令，5.7一般用innobackupex，8.0用xtrabackup并废弃了innobackupex。
注意每个Mysql版本对应了一个版本的xtrabackup，只能用相对应版本的xtrabackup来备份，否则会失败。

innobackupex \
--defaults-file=$mysql_conf_file \
--parallel=2 \
--user=$user \
--password=$password \
--ftwrl-wait-timeout=60 \
--ftwrl-wait-threshold=5 \
--no-timestamp \
--slave-info \
--compress --compress-threads=2 \
$backup_dir/$backup_folder 2>$log_dir/${backup_folder}.log

xtrabackup \
--defaults-file=$mysql_conf_file \
--backup \
--parallel=2 \
--user=$user \
--password=$password \
--ftwrl-wait-timeout=60 \
--ftwrl-wait-threshold=5 \
--slave-info \
--compress --compress-threads=2 \
--target-dir=$backup_dir/$backup_folder 2>$log_dir/${backup_folder}.log

注释：
FLUSH TABLES WITH READ LOCK 期间，是把全表都锁上，不否允许dml
--ftwrl-wait-threshold=5 如果低于5秒马上报错，超过5秒会等待
--ftwrl-wait-timeout=60 如果表上有超过5秒的大事务，会等待60秒，60秒后报错。
--defaults-file=/etc/my.cnf 配置文件
--slave-info 如果备份从库需要加上备份从节点的日志点
--target-dir 备份到哪个文件夹

我们来测试下8.0版本的备份恢复步骤

xtrabackup \
--defaults-file=/etc/my.cnf \
       --backup \
--user=root \
--password=Passw0rd* \
--ftwrl-wait-timeout=60 \
--ftwrl-wait-threshold=5 \
--target-dir=/home/mysql/backup20250228 2>/home/mysql/backup.log


查看/home/mysql/backup.log       最后一行completed OK!表示成功

2025-02-28T13:21:57.361871+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

前滚日志：
xtrabackup --prepare --use-memory=4GB --target-dir=/home/mysql/backup20250228

我们把/data/mysql/data目录下所有的文件删掉，然后恢复--move-back --copy-back分别是移动回和COPY回

xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/home/mysql/backup20250228

报错：/data/mysql/log/bin/mysql-bin.000003这个文件已存在
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)
2025-02-28T13:45:51.702389+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /home/mysql/backup20250228/
2025-02-28T13:45:51.702972+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_001 to /data/mysql/data/undo_001
2025-02-28T13:45:51.753485+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_001 to /data/mysql/data/undo_001
2025-02-28T13:45:51.773233+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying undo_002 to /data/mysql/data/undo_002
2025-02-28T13:45:51.843765+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying undo_002 to /data/mysql/data/undo_002
2025-02-28T13:45:51.886248+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ibdata1 to /data/mysql/data/ibdata1
2025-02-28T13:45:51.919866+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ibdata1 to /data/mysql/data/ibdata1
xtrabackup: Can't create/write to file '/data/mysql/log/bin/mysql-bin.000003' (OS errno 17 - File exists)
2025-02-28T13:45:51.980361+08:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot open the destination stream for mysql-bin.000003
2025-02-28T13:45:51.980420+08:00 0 [ERROR] [MY-011825] [Xtrabackup] copy_file() failed.

删掉/data/mysql/log/bin/目录下所有的文件重新恢复，成功！
2025-02-28T13:48:17.787081+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

重启实例，验证恢复成功

[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ /etc/init.d/mysqld start
Starting MySQL........................... SUCCESS!

[mysql@p0-dtpoc-dtpoc-cache-redis03-ma02 ~]$ mysql -uroot -pPassw0rd*
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.36 MySQL Community Server - GPL

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.