mysql-数据物理备份

1、安装xtrabackup工具

1、配置xtrabackup工具yum源：vim /etc/yum.repos.d/percona-original-release.repo

# This repo is managed by "percona-release" utility, do not edit!
#
[percona-release-x86_64]
name = Percona Original release/x86_64 YUM repository
baseurl = http://repo.percona.com/percona/yum/release/$releasever/RPMS/x86_64
enabled = 1
gpgcheck = 0
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY

[percona-release-noarch]
name = Percona Original release/noarch YUM repository
baseurl = http://repo.percona.com/percona/yum/release/$releasever/RPMS/noarch
enabled = 1
gpgcheck = 0
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY

[percona-release-sources]
name = Percona Original release/sources YUM repository
baseurl = http://repo.percona.com/percona/yum/release/$releasever/SRPMS
enabled = 0
gpgcheck = 1
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY

2、yum安装xtrabackup

yum -y install percona-xtrabackup-24.x86_64  #安装此版本可以兼容mysql5.7版本

2、完整备份

1、完整备份数据

[root@localhost ~]# mkdir /xtrabackup/full -p  # 创建备份数据存在的文件夹
[root@localhost ~]# innobackupex --user=root --password='your_password'  /xtrabackup/full

2、确认配置文件中有数据库目录指定  cat /etc/my.cnf

3、停止数据库、清除数据并对备份数据进行重演

[root@localhost ~]# systemctl stop mysqld       # 停止mysql软件
[root@localhost ~]# rm -rf /var/lib/mysql/*     # 清除mysql数据
[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2024-08-15_19-16-08/
  # 重演恢复

4、恢复数据

[root@localhost ~]# innobackupex  --copy-back /xtrabackup/full/2024-08-15_19-16-08/

5、修改文件权限，启动mysql

[root@localhost ~]# chown -R mysql.mysql  /var/lib/mysql
[root@localhost ~]# systemctl start mysqld

6、登录mysql验证。

3、增量备份

1、创建一个测试库test,创建一个表格beifen，在里面插入一行数据，如下

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into beifen values(1,'jack');
Query OK, 1 row affected (0.00 sec)

2、完整备份

[root@localhost ~]# innobackupex -u root -p'your_password' /xtrabackup/full

3、查看备份文件是否生成

[root@localhost ~]# ls /xtrabackup/full/
2024-08-15_15-03-48  

4、进入数据库，插入一行数据

 5、增量备份

[root@localhost ~]# innobackupex -u root -p'your_password' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2024-08-15_15-03-48/

 6、查看备份文件

[root@localhost ~]# ls /xtrabackup/full/
2024-08-15_15-03-48  2024-08-15_15-09-39

7、进入数据库再插入一条记录

mysql> insert into beifen values(3,'tom');
Query OK, 1 row affected (0.01 sec)

8、再进行一次增量备份

[root@localhost ~]# innobackupex -u root -p'your_password' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2024-08-15_15-09-39/

 9、停止数据库，并清除数据

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

10、进行数据回滚

先将完整备份进行回滚

[root@localhost ~]# innobackupex  --apply-log  --redo-only /xtrabackup/full/2024-08-15_15-03-48

 在按顺序将第一次增量备份和第二次增量备份数据回滚到完整备份数据文件夹内

[root@localhost ~]# innobackupex  --apply-log  --redo-only /xtrabackup/full/2024-08-15_15-03-48 --incremental-dir=/xtrabackup/full/2024-08-15_15-09-39

[root@localhost ~]# innobackupex  --apply-log  --redo-only /xtrabackup/full/2024-08-15_15-03-48 --incremental-dir=/xtrabackup/full/2024-08-15_15-12-22

11、恢复数据

[root@localhost ~]# innobackupex  --copy-back /xtrabackup/full/2024-08-15_15-03-48

12、修改/var/lib/mysql 属主和属组 

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql

13、启动并登录mysql，验证结果

4、差异备份

1、清理备份环境并进行完整备份

[root@localhost ~]# rm -rf /xtrabackup/full/*
[root@localhost ~]# innobackupex --user=root --password='your_password'  /xtrabackup/full/

2、在数据库表中插入一行数据

3、第一次差异化备份，基于完整备份

[root@localhost ~]# innobackupex  --user=root --password='your_password'  
--incremental /xtrabackup/full/ 
--incremental-basedir=/xtrabackup/full/2024-08-15_17-42-04/

4、在表中插入一条数据

5、第二次差异化备份，基于完整备份

[root@localhost ~]# innobackupex  --user=root --password='your_password'  
--incremental /xtrabackup/full/ 
--incremental-basedir=/xtrabackup/full/2024-08-15_17-42-04/

6、停止mysql 并且清除mysql数据

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

7、回滚完整备份数据

[root@localhost ~]# innobackupex --apply-log --redo-only 
/xtrabackup/full/2024-08-15_17-42-04

8、恢复第二次差异化备份的数据（随便恢复哪一次）

[root@localhost ~]# innobackupex --apply-log --redo-only 
/xtrabackup/full/2024-08-15_17-42-04  
--incremental-dir=/xtrabackup/full/2024-08-15_17-56-23

9、恢复数据备份

[root@localhost ~]# innobackupex --copy-back /xtrabackup/full/2024-08-15_17-42-04

10、修改文件权限并打开mysql数据库 登录验证

[root@localhost ~]# chown -R  mysql.mysql /var/lib/mysql
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -u root -p'your_password'