【技术干货使用xtrabackup备份工具完全恢复MySQL数据库-每日运维

一、xtrabackup介绍

xtrabackup是由Percona公司开发的一个用于MySQL数据库物理热备的工具，开源免费。目前最新的xtrabbackup 8.3版本可以备份MySQL 8.3 servers上的InnoDB, XtraDB, MyISAM, MyRocks表，Percona Server for MySQL with XtraDB, Percona Server for MySQL 8.3，Percona XtraDB Cluster 8.3。xtrabackup工具执行InnoDB和XtraDB数据库的非阻塞备份。它具有如下优点：
（1）备份速度快，物理备份可靠
（2）在备份期间不间断地处理事务
（3）支持备份压缩，节约磁盘空间和网络带宽
（4）自动校验备份
xtrabackup支持流式输出、压缩、增量备份、加密，是目前各个厂商普遍使用的MySQL备份工具。

二、xtrabackup备份原理

1、xtrabackup开始，记录lsn ，同时启动后台进程监控redo日志的变化，且将变化实时记录到xtrabackup_logfile中
2、复制ibdata1，.ibd数据文件
3、执行LOCK INSTANCE FOR BACKUP（8.0取代了 FLUSH TABLES WITH READ LOCK）
4、复制非InnoDB的表和文件
5、获取binlog位置信息
6、停止复制redo log，即停止记录xtrabackup_logfile
7、执行UNLOCK INSTANCE释放锁
8、备份完成

三、xtrabackup下载

下载网址：https://www.percona.com/downloads
选择和数据库相匹配的xtrabackup版本下载，我这里数据库版本是8.0.33，下载界面如下：

四、xtrabackup安装

1、安装依赖包

# yum install -y libaio libaio-devel zstd libev perl-DBD-MySQL

2、安装xtrabackup包

# rpm -ivh percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm 安装过程如下： [root@node1 opt]# rpm -ivh percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm warning: percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:percona-xtrabackup-80-8.0.33-28.1################################# [100%]

3、查看xtrabackup版本

# xtrabackup --version 2024-05-18T14:53:54.164259+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=33062 --datadir=/data/data --open_files_limit=65535 --log_bin=/data/data/mybinlog --innodb_buffer_pool_size=2048M --innodb_data_file_path=ibdata1:12M:autoextend --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=1G --innodb_log_files_in_group=3 --innodb_io_capacity=4000 --innodb_open_files=65535 --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0 --innodb_adaptive_hash_index=0 xtrabackup version 8.0.33-28 based on MySQL server 8.0.33 Linux (x86_64) (revision id: b3a3c3dd)

五、使用xtrabackup恢复数据库

1、创建备份目录并授权

mkdir /data/backup chown -R mysql:mysql /data/backup/

2、全备数据库

1、数据库全备 xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/db_`date +%F` --user=root --password=root --socket=/data/data/mysql.sock>/data/backup/db_full_`date +%F`.log 2>&1

部分备份日志如下：

备份过程如下： ```language [root@node1 backup]# cat db_full_2024-05-18.log --- 2024-05-18T15:06:24.060430+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /data/data/mysql.sock 2024-05-18T15:06:24.081369+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.33 2024-05-18T15:06:24.086956+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ... ... 2024-05-18T15:06:24.533828+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19730331) 2024-05-18T15:06:24.592403+08:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads. 2024-05-18T15:06:24.798005+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces 2024-05-18T15:06:24.798132+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './' 2024-05-18T15:06:24.803212+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files. 2024-05-18T15:06:24.809533+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 2 for testdb/testtab, old maximum was 0 2024-05-18T15:06:24.810468+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'. 2024-05-18T15:06:24.815650+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'. 2024-05-18T15:06:24.820294+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces. 2024-05-18T15:06:24.821113+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./ibdata1 to /data/backup/db_2024-05-18/ibdata1 2024-05-18T15:06:24.990789+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./ibdata1 to /data/backup/db_2024-05-18/ibdata1 2024-05-18T15:06:24.991996+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./sys/sys_config.ibd to /data/backup/db_2024-05-18/sys/sys_config.ibd 2024-05-18T15:06:24.993218+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./sys/sys_config.ibd to /data/backup/db_2024-05-18/sys/sys_config.ibd 2024-05-18T15:06:24.994635+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./testdb/testtab.ibd to /data/backup/db_2024-05-18/testdb/testtab.ibd 2024-05-18T15:06:24.995276+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./testdb/testtab.ibd to /data/backup/db_2024-05-18/testdb/testtab.ibd 2024-05-18T15:06:24.996214+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./mysql.ibd to /data/backup/db_2024-05-18/mysql.ibd 2024-05-18T15:06:25.147866+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./mysql.ibd to /data/backup/db_2024-05-18/mysql.ibd 2024-05-18T15:06:25.148829+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_002 to /data/backup/db_2024-05-18/undo_002 2024-05-18T15:06:25.201926+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_002 to /data/backup/db_2024-05-18/undo_002 2024-05-18T15:06:25.202414+08:00 2 [Note] [MY-011825] [Xtrabackup] Copying ./undo_001 to /data/backup/db_2024-05-18/undo_001 2024-05-18T15:06:25.255174+08:00 2 [Note] [MY-011825] [Xtrabackup] Done: Copying ./undo_001 to /data/backup/db_2024-05-18/undo_001 2024-05-18T15:06:25.534302+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (19730331) 2024-05-18T15:06:25.820806+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to backup non-InnoDB tables and files 2024-05-18T15:06:25.821785+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log_213.sdi to /data/backup/db_2024-05-18/mysql/general_log_213.sdi 2024-05-18T15:06:25.824461+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log_213.sdi to /data/backup/db_2024-05-18/mysql/general_log_213.sdi 2024-05-18T15:06:25.825196+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSM to /data/backup/db_2024-05-18/mysql/general_log.CSM 2024-05-18T15:06:25.825535+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSM to /data/backup/db_2024-05-18/mysql/general_log.CSM 2024-05-18T15:06:25.827105+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/general_log.CSV to /data/backup/db_2024-05-18/mysql/general_log.CSV 2024-05-18T15:06:25.827236+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/general_log.CSV to /data/backup/db_2024-05-18/mysql/general_log.CSV 2024-05-18T15:06:25.828050+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log_214.sdi to /data/backup/db_2024-05-18/mysql/slow_log_214.sdi 2024-05-18T15:06:25.829828+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log_214.sdi to /data/backup/db_2024-05-18/mysql/slow_log_214.sdi 2024-05-18T15:06:25.830837+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSM to /data/backup/db_2024-05-18/mysql/slow_log.CSM 2024-05-18T15:06:25.831838+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSM to /data/backup/db_2024-05-18/mysql/slow_log.CSM 2024-05-18T15:06:25.832810+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying mysql/slow_log.CSV to /data/backup/db_2024-05-18/mysql/slow_log.CSV 2024-05-18T15:06:25.832857+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying mysql/slow_log.CSV to /data/backup/db_2024-05-18/mysql/slow_log.CSV 2024-05-18T15:06:25.833627+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/cond_instances_82.sdi to /data/backup/db_2024-05-18/performance_schema/cond_instances_82.sdi 2024-05-18T15:06:25.834642+08:00 3 [Note] [MY-011825] [Xtrabackup] Done: Copying performance_schema/cond_instances_82.sdi to /data/backup/db_2024-05-18/performance_schema/cond_instances_82.sdi 2024-05-18T15:06:25.835810+08:00 3 [Note] [MY-011825] [Xtrabackup] Copying performance_schema/error_log_83.sdi to /data/backup/db_2024-05-18/performance_schema/error_log_83.sdi ... 2024-05-18T15:06:26.023267+08:00 0 [Note] [MY-011825] [Xtrabackup] Finished backing up non-InnoDB tables and files 2024-05-18T15:06:26.023347+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS 2024-05-18T15:06:26.040899+08:00 0 [Note] [MY-011825] [Xtrabackup] Selecting LSN and binary log position from p_s.log_status 2024-05-18T15:06:26.054285+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying /data/data/mybinlog.000004 to /data/backup/db_2024-05-18/mybinlog.000004 up to position 237 2024-05-18T15:06:26.054671+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying /data/data/mybinlog.000004 to /data/backup/db_2024-05-18/mybinlog.000004 2024-05-18T15:06:26.055494+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/mybinlog.index 2024-05-18T15:06:26.055680+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/mybinlog.index 2024-05-18T15:06:26.059364+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/xtrabackup_binlog_info 2024-05-18T15:06:26.059562+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/xtrabackup_binlog_info 2024-05-18T15:06:26.060911+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 2024-05-18T15:06:26.093716+08:00 0 [Note] [MY-011825] [Xtrabackup] The latest check point (for incremental): '19730331' 2024-05-18T15:06:26.093745+08:00 0 [Note] [MY-011825] [Xtrabackup] Stopping log copying thread at LSN 19730331 2024-05-18T15:06:26.093976+08:00 1 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 19729933 2024-05-18T15:06:26.094655+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE 2024-05-18T15:06:26.094812+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked 2024-05-18T15:06:26.094847+08:00 0 [Note] [MY-011825] [Xtrabackup] Copying ib_buffer_pool to /data/backup/db_2024-05-18/ib_buffer_pool 2024-05-18T15:06:26.095270+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Copying ib_buffer_pool to /data/backup/db_2024-05-18/ib_buffer_pool 2024-05-18T15:06:26.095576+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/data/backup/db_2024-05-18/' 2024-05-18T15:06:26.095588+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mybinlog.000004', position '237', GTID of the last change '6e7ba49a-1010-11ef-915f-00505623430f:1-11,70d72f44-1012-11ef-b21a-0050563f9c81:1-11' 2024-05-18T15:06:26.095630+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/backup-my.cnf 2024-05-18T15:06:26.095685+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/backup-my.cnf 2024-05-18T15:06:26.099996+08:00 0 [Note] [MY-011825] [Xtrabackup] Writing /data/backup/db_2024-05-18/xtrabackup_info 2024-05-18T15:06:26.100295+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Writing file /data/backup/db_2024-05-18/xtrabackup_info 2024-05-18T15:06:27.100965+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (19730331) to (19730341) was copied. 2024-05-18T15:06:27.324850+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!


## 3、创建测试表tab1，并插入5条数据

```language

root@node1 15:52:  [(none)]> use testdb

Database changed

root@node1 15:52:  [testdb]> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| testtab          |

+------------------+

1 row in set (0.02 sec)
root@node1 15:52:  [testdb]> create table tab1(id int);

Query OK, 0 rows affected (0.06 sec)
root@node1 15:52:  [testdb]> insert into tab1 values(1);

Query OK, 1 row affected (0.02 sec)
root@node1 15:52:  [testdb]> insert into tab1 values(2);

Query OK, 1 row affected (0.02 sec)
root@node1 15:52:  [testdb]> insert into tab1 values(3);

Query OK, 1 row affected (0.01 sec)
root@node1 15:53:  [testdb]> insert into tab1 values(4);

Query OK, 1 row affected (0.02 sec)

root@node1 15:53: [testdb]> insert into tab1 values(5); Query OK, 1 row affected (0.02 sec) root@node1 15:54: [testdb]> select * from tab1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.01 sec)

4、模拟数据库故障

--mv数据文件目录 [root@node1 backup]# cd /data [root@node1 data]# mv data data.bak

5、恢复数据库

# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db_2024-05-18 # xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/db_2024-05-18

6、启动数据库

--修改数据文件目录权限 # chown -R mysql:mysql /data/data


--启动数据库

[root@node1 data]# systemctl start mysqld

[root@node1 data]# systemctl status mysqld

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active:active (running) since Sat 2024-05-18 16:22:09 CST; 6s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 41145 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 41175 (mysqld)

   Status: "Server is operational"

    Tasks: 41

   CGroup: /system.slice/mysqld.service

           └─41175 /usr/sbin/mysqld

May 18 16:22:04 node1 systemd[1]: Starting MySQL Server... May 18 16:22:09 node1 systemd[1]: Started MySQL Server.

7、初次验证数据

因为是在全备份之后创建的tab1表，所有备份恢复回来是没有tab1表的。

[root@node1 data]# mysql -uroot -p'root' testdb 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.33 MySQL Community Server - GPL


Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.

root@node1 16:23: [testdb]> show tables; +------------------+ | Tables_in_testdb | +------------------+ | testtab | +------------------+ 1 row in set (0.00 sec)

8、解析binlog

--查看备份是的binlog位点 [root@node1 db_2024-05-18]# cat xtrabackup_binlog_info mybinlog.000004 237 6e7ba49a-1010-11ef-915f-00505623430f:1-11,70d72f44-1012-11ef-b21a-0050563f9c81:1-11 --解析mv之前的binlog [root@node1 data]# mysqlbinlog -vv --start-position=237 /data/data.bak/mybinlog.000004 > recoverdata.sql

9、使用binlog恢复数据

[root@node1 data]# mysql -uroot -p'root'< recoverdata.sql mysql: [Warning] Using a password on the command line interface can be insecure.

10、再次验证数据

[root@node1 data]# mysql -uroot -p'root' testdb 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 10 Server version: 8.0.33 MySQL Community Server - GPL


Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
root@node1 16:36:  [testdb]> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| tab1             |

| testtab          |

+------------------+

2 rows in set (0.02 sec)

root@node1 16:36: [testdb]> select * from tab1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec)

恭喜，数据全部恢复回来了。

六、碰到的问题

安装perl-DBD-mysql包报错

# rpm -ivh perl-DBD-mysql ... --> Running transaction check ---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed Removing mariadb-libs.x86_64 1:5.5.68-1.el7 - u due to obsoletes from installed mysql-community-libs-8.0.33-1.el7.x86_64 --> Restarting Dependency Resolution with new changes. --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64 --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be installed Removing mariadb-libs.x86_64 1:5.5.68-1.el7 - u due to obsoletes from installed mysql-community-libs-8.0.33-1.el7.x86_64 ... 解决办法：rpm -ivh mysql-community-libs-compat-8.0.33-1.el7.x86_64.rpm

【技术干货使用xtrabackup备份工具完全恢复MySQL数据库

三、xtrabackup下载

下载网址：https://www.percona.com/downloads
选择和数据库相匹配的xtrabackup版本下载，我这里数据库版本是8.0.33，下载界面如下：

四、xtrabackup安装

1、安装依赖包

2、安装xtrabackup包

3、查看xtrabackup版本

五、使用xtrabackup恢复数据库

1、创建备份目录并授权

2、全备数据库

4、模拟数据库故障

5、恢复数据库

6、启动数据库

7、初次验证数据

8、解析binlog

9、使用binlog恢复数据

10、再次验证数据

六、碰到的问题

安装perl-DBD-mysql包报错

相关文章

发布评论取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

【技术干货使用xtrabackup备份工具完全恢复MySQL数据库

三、xtrabackup下载 下载网址：https://www.percona.com/downloads 选择和数据库相匹配的xtrabackup版本下载，我这里数据库版本是8.0.33，下载界面如下：

四、xtrabackup安装

1、安装依赖包

2、安装xtrabackup包

3、查看xtrabackup版本

五、使用xtrabackup恢复数据库

1、创建备份目录并授权

2、全备数据库

4、模拟数据库故障

5、恢复数据库

6、启动数据库

7、初次验证数据

8、解析binlog

9、使用binlog恢复数据

10、再次验证数据

六、碰到的问题

安装perl-DBD-mysql包报错

相关文章

发布评论 取消回复

贤蛋大眼萌

Oracle如何使用授予和撤销权限的语法和示例

产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像

i.MX8MP开发板AI项目实操

三、xtrabackup下载

下载网址：https://www.percona.com/downloads
选择和数据库相匹配的xtrabackup版本下载，我这里数据库版本是8.0.33，下载界面如下：

发布评论取消回复