使用备份工具xtrabackup进行差异备份详细讲解

元气满满的热码式

于 2024-02-21 10:15:23 发布

阅读量468

点赞数 21

分类专栏：运维 mysql 文章标签： adb linux mysql 数据库运维

本文链接：https://blog.csdn.net/m0_66011019/article/details/136204579

版权

mysql 同时被 2 个专栏收录

29 篇文章 0 订阅

订阅专栏

运维

26 篇文章 0 订阅

订阅专栏

本文详细描述了使用Xtrabackup在MySQL中进行差异备份的过程，包括完整备份、增量备份以及应用和恢复策略，展示了如何在数据库变更后进行数据恢复。

摘要由CSDN通过智能技术生成

差异备份

基于第一天进行差异备份

删除之前修改的数据备份

[root@service ~]# rm -rf /data/backup/*
[root@service ~]# ls /data/backup

完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/ -uroot -pWyxbuke00. -H localhost -P 3306 --no-server-version-check
[root@service ~]# ls /data/backup/base
backup-my.cnf  binlog.index  ib_buffer_pool  mysql      performance_schema  undo_001  xtrabackup_binlog_info  xtrabackup_info     xtrabackup_tablespaces
binlog.000018  db01          ibdata1         mysql.ibd  sys                 undo_002  xtrabackup_checkpoints  xtrabackup_logfile

对数据库进行数据变更

mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| tb_user        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
|  4 | sxx     | 0    |
|  5 | wyx     | 1    |
|  6 | asd     | 1    |
+----+---------+------+
6 rows in set (0.00 sec)

mysql> insert into tb_user values (7,'lisi',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
|  4 | sxx     | 0    |
|  5 | wyx     | 1    |
|  6 | asd     | 1    |
|  7 | lisi    | 1    |
+----+---------+------+
7 rows in set (0.00 sec)

对第一次修改的数据进行备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base -uroot -pWyxbuke00. -H localhost -P 3306 --no-server-version-check

第二次对数据进行修改

mysql> insert into tb_user values (8,'zhangsan',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | Tom      | 1    |
|  2 | Trigger  | 0    |
|  3 | Dawn     | 1    |
|  4 | sxx      | 0    |
|  5 | wyx      | 1    |
|  6 | asd      | 1    |
|  7 | lisi     | 1    |
|  8 | zhangsan | 1    |
+----+----------+------+
8 rows in set (0.00 sec)

对第二次修改的数据进行备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/base -uroot -pWyxbuke00. -H localhost -P 3306 --no-server-version-check

第三次对数据库的数据进行修改

mysql> insert into tb_user values (9,'haha',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | Tom      | 1    |
|  2 | Trigger  | 0    |
|  3 | Dawn     | 1    |
|  4 | sxx      | 0    |
|  5 | wyx      | 1    |
|  6 | asd      | 1    |
|  7 | lisi     | 1    |
|  8 | zhangsan | 1    |
|  9 | haha     | 1    |
+----+----------+------+
9 rows in set (0.00 sec)

对第三次修改的数据进行备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc3 --incremental-basedir=/data/backup/base -uroot -pWyxbuke00. -H localhost -P 3306 --no-server-version-check

应用完整备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base

基于第一天和最后一天进行差异备份

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc3

停止MySQL服务，删除数据库中的所有文件

[root@service ~]# systemctl stop mysqld
[root@service ~]# rm -rf /var/lib/mysql/*
[root@service ~]# ls /var/lib/mysql

进行恢复，并进入数据库查看

[root@service ~]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/
[root@service ~]# chown -R mysql.mysql /var/lib/mysql
[root@service ~]# systemctl start mysqld
[root@service ~]# mysql -u root -p
Enter password: 
mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from tb_user;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | Tom      | 1    |
|  2 | Trigger  | 0    |
|  3 | Dawn     | 1    |
|  4 | sxx      | 0    |
|  5 | wyx      | 1    |
|  6 | asd      | 1    |
|  7 | lisi     | 1    |
|  8 | zhangsan | 1    |
|  9 | haha     | 1    |
+----+----------+------+
9 rows in set (0.00 sec)