mysql 物理备份 MySQL 全量备份增量备份差异备份日志备份万字长文 1.3万字

注意，通常完备增备，日志（binlog)备，结合使用差异则根据具体情况选用。

此备份过程

属于公司

常用的单个数据库备份技术中的

mysql 物理备份 MySQL 全量备份增量备份差异备份日志备份

至于单个数据库备份技术中的逻辑备份

和

混合数据库及其备份技术，本人会另外开文章再进行说明

MySQL备份工具的安装

逻辑备份工具 mysqldump 自带

物理备份工具 percona-xtrabackup 需安装

MySQL数据备份方式

主要有四种：

完整备份、增量备份、差异备份和逻辑备份。它们的区别在于备份的范围和备份方式不同。

MySQL物理备份的种类及其特点

MySQL物理备份主要有三种：完整备份、增量备份和差异备份。每种备份方式都有其独特的特点和适用场景。

1. 完整备份

特点：
- 备份整个数据库的所有数据和结构。
- 备份文件较大，占用较多存储空间。
- 恢复速度最快，因为只需要恢复一个备份文件。
适用场景：
- 数据量较小的数据库。
- 需要快速恢复的场景。

2. 增量备份

特点：
- 备份自上次完整备份或增量备份以来发生变化的数据。
- 备份文件较小，节省存储空间。
- 恢复速度较慢，因为需要先恢复完整备份，然后应用所有增量备份。
适用场景：
- 数据量较大且变化频繁的数据库。
- 需要节省存储空间的场景。

3. 差异备份

特点：
- 备份自上次完整备份以来发生变化的数据。
- 备份文件大小介于完整备份和增量备份之间。
- 恢复速度较快，因为只需要恢复完整备份和一个差异备份。
适用场景：
- 数据量较大且变化频繁，但希望恢复过程不太复杂。
- 需要快速恢复且节省存储空间的场景。

总结

选择哪种备份方式取决于具体需求：

完整备份适用于数据量较小且需要快速恢复的场景。
增量备份适用于数据量较大且变化频繁，需要节省存储空间的场景。
差异备份适用于数据量较大且变化频繁，但希望恢复过程不太复杂的场景。

Percona XtraBackup 是一个开源的 MySQL 和 InnoDB 备份工具，广泛用于物理数据备份和恢复。

MySQL安装percona-xtrabackup

逻辑备份工具 mysqldump 自带

percona-xtrabackup版本这里有必要说明

参考官网如下：

Aligning Percona XtraBackup Versions with Percona Server for MySQLWe are excited to let you know about two upcoming changes to Percona XtraBackup which will align Percona XtraBackup Versions with Percona Server for MySQL. https://www.percona.com/blog/aligning-percona-xtrabackup-versions-with-percona-server-for-mysql/

翻译过来意思就是：Percona XtraBackup 的版本要超过（大于或等于）数据库的版本。

比如：当前 MySQL 数据库版本 8.0.32，若安装 XtraBackup 版本为 8.0.32-xxx

尽量和数据库的版本保持一致 为啥？如果不保持一致因为会天天输出报警信息说你的备份工具和数据库版本不匹配

查看mysql 版本：

[root@root ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
[root@root ~]#

注意v必须大写

安装与MySQL 5.7兼容的Percona XtraBackup版本。在你的情况下，MySQL版本是5.7.44，所以你应该安装Percona XtraBackup 2.4，因为这一版本与MySQL 5.7兼容。

MySQL 8.0.32 对应版本： Percona XtraBackup 8.0.32-x

则查看 XtraBackup 版本时

卸载已经安装的 XtraBackup

查找已经安装的 XtraBackup 名称

查找

[root@root soft]# yum list installed | grep -i xtrabackup

# 卸载

yum -y remove percona-xtrabackup-80.x86_64

安装percona-xtrabackup

查看mysql 版本：

[root@root ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
[root@root ~]#

安装与MySQL 5.7兼容的Percona XtraBackup版本。在你的情况下，MySQL版本是5.7.44，所以你应该安装Percona XtraBackup 2.4，因为这一版本与MySQL 5.7兼容。

MySQL 8.0.32 对应版本： Percona XtraBackup 8.0.32-x

[root@mysql-server ~]# vim /etc/yum.repos.d/Percona.repo

[percona]

name = CentOS $releasever - Percona

baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/

enabled = 1

gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

gpgcheck = 1 改为0 不然还要去官网获取密钥太麻烦

注:我的MySQL 5.7.44 是用yum安装的

推荐yum,二进制安装，切记不推荐编译，

会爆各种错，五花八门的

MySQL物理备份主要有三种：完整备份、增量备份和差异备份。每种备份方式都有其独特的特点和适用场景。

完全备份

创建备份目录：

[root@root ~]# mkdir /xtrabackup/bakwuli -p

备份之前，进入数据库，存入些数据

[root@root ~]# mysql -uroot -p'Password123$'

mysql> create database testbakwuli;

Query OK, 1 row affected (0.01 sec)

mysql> use testbakwuli

Database changed

mysql> create table t1(id int);

Query OK, 0 rows affected (0.05 sec)

mysql> exit

备份：

[root@root ~]# innobackupex --user=root --password='Password123$' /xtrabackup/bakwuli

。。。。。

。。。。。。

40815 20:43:24 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '32452021'
xtrabackup: Stopping log copying thread.
.240815 20:43:24 >> log scanned up to (32452030)

240815 20:43:24 Executing UNLOCK TABLES
240815 20:43:24 All tables unlocked
240815 20:43:24 [00] Copying ib_buffer_pool to /xtrabackup/bakwuli/2024-08-15_20-43-21/ib_buffer_pool
240815 20:43:24 [00] ...done
240815 20:43:24 Backup created in directory '/xtrabackup/bakwuli/2024-08-15_20-43-21/'
240815 20:43:24 [00] Writing /xtrabackup/bakwuli/2024-08-15_20-43-21/backup-my.cnf
240815 20:43:24 [00] ...done
240815 20:43:24 [00] Writing /xtrabackup/bakwuli/2024-08-15_20-43-21/xtrabackup_info
240815 20:43:24 [00] ...done
xtrabackup: Transaction log of lsn (32452021) to (32452030) was copied.
240815 20:43:25 completed OK!

查看:

[root@root ~]# cd /xtrabackup/bakwuli

[root@root bakwuli]# ls

2024-08-15_20-43-21

[root@root bakwuli]#

完全备份恢复

1.关闭数据库：

[root@root bakwuli]# systemctl stop mysqld

清理环境

[root@root bakwuli]# rm -rf /var/lib/mysql/*

[root@root bakwuli]# rm -rf /var/log/mysql-slow/slow.log

2.重演恢复:

[root@root bakwuli]# innobackupex --apply-log /xtrabackup/bakwuli/2024-08-15_20-43-21

。。。。。

。。。

g the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.44 started; log sequence number 32452117
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 32452136
240815 20:50:01 completed OK!

注：恢复之前需要确认配置文件内有数据库指定目录，如下，不然会随机恢复数据到某个位置

很麻烦

vim /etc/my.cnf

在命令行模式：

/datadir=/var/lib/mysq

查看里面是否有datadir=/var/lib/mysql

[mysqld]

datadir=/var/lib/mysql

：q

.恢复数据:

[root@root bakwuli]# innobackupex --copy-back /xtrabackup/bakwuli/2024-08-15_20-43-21
。。。。。。

。。。。。。

b_buffer_pool
240815 20:58:39 [01] ...done
240815 20:58:39 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
240815 20:58:39 [01] ...done
240815 20:58:39 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id
240815 20:58:39 [01] ...done
240815 20:58:39 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
240815 20:58:39 [01] ...done
240815 20:58:39 completed OK!
[root@root bakwuli]#

恢复完数据重启失败，但是给他一个权限就能重启成功

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

[root@root bakwuli]# systemctl start mysqld

[root@root bakwuli]#

测试是否成功恢复数据

[root@root bakwuli]# mysql -uroot -p"Password123$"

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testbakwuli |
| zabbix |
+--------------------+
7 rows in set (0.01 sec)

mysql> use testbakwuli;
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> show tables;
+-----------------------+
| Tables_in_testbakwuli |
+-----------------------+
| t1 | ————》》从这可看出刚刚删除的表，数据已恢复
+-----------------------+
1 row in set (0.00 sec)

mysql> exit

systemctl stop mysqld

增量备份

增量备份每次备份上一次备份到现在产生的新数据 且基于前一天的备份为目录，在企业中增量备份通常与完备配合使用比如1234567天 1用完备，2，3，4，5，6，7分别用增量 .

刚刚已创建指定备份目录/xtrabackup/bakwuli/ 和测试用的数据库testbakwuli 数据表t1

刚刚已经备份了第1天的完备

现在第2天，增备：

在数据库中插入表值， 模拟第2天产生新数据

mysql -uroot -p"Password123$"

mysql> insert into testbakwuli.t1 values(2);

Query OK, 1 row affected (0.01 sec)

mysql> exit

systemctl stop mysqld

备份上一次备份到现在产生的新数据 且基于第一天的备份为目录

[root@root bakwuli]# innobackupex -uroot -p"Password123$" --incremental /xtrabackup/bakwuli/ --incremental-basedir=//xtrabackup/bakwuli/2024-08-15_20-43-21

[root@root bakwuli]# ls

2024-08-15_20-43-21 2024-08-15_21-24-39

[root@root bakwuli]#

现在第3天，增备：

在数据库中插入表值， 模拟第3天产生新数据

mysql -uroot -p"Password123$"

mysql> insert into testbakwuli.t1 values(3);

Query OK, 1 row affected (0.01 sec)

mysql> exit

[root@root bakwuli]# ls 查看前两天的备份

2024-08-15_20-43-21 2024-08-15_21-24-39

# 备份上一次备份到现在产生的新数据且是基于第2天的备份为目录

systemctl stop mysqld

[root@root bakwuli]# innobackupex -uroot -p"Password123$" --incremental /xtrabackup/bakwuli/ --incremental-basedir=//xtrabackup/bakwuli/2024-08-15_21-24-39

[root@root bakwuli]# ls

2024-08-15_20-43-21 2024-08-15_21-24-39 2024-08-15_21-35-30

全备周一（第1天 增量周二（第2天 增量周三（第3天

增量备份恢复

1. 停止数据库

2. 清理环境

3. 依次重演回滚redo log－－> 恢复数据

恢复完整备份。依次应用所有增量备份。完成恢复过程。

将恢复的数据文件复制到 MySQL 数据目录

4. 修改权限

5. 启动数据库

测试：

[root@root bakwuli]# ls

2024-08-15_20-43-21 2024-08-15_21-24-39 2024-08-15_21-35-30

[root@root bakwuli]# systemctl stop mysqld

[root@root bakwuli]# rm -rf /var/lib/mysql/*

依次重演回滚redo log: 恢复完整备份 假设完整备份存储在 /xtrabackup/bakwuli/2024-08-15_20-43-21/ 目录中。

[root@root bakwuli]# innobackupex --apply-log --redo-only /xtrabackup/bakwuli/2024-08-15_20-43-21/

回滚周二 应用第一个增量 ：备份假设完整备份存储在/xtrabackup/bakwuli/2024-08-15_21-24-39 目录中。

[root@root bakwuli]# innobackupex --apply-log --redo-only /xtrabackup/bakwuli/2024-08-15_20-43-21/ --incremental-dir=/xtrabackup/bakwuli/2024-08-15_21-24-39

回滚周三

[root@root bakwuli]# innobackupex --apply-log --redo-only /xtrabackup/bakwuli/2024-08-15_21-24-39/ --incremental-dir=/xtrabackup/bakwuli/2024-08-15_21-35-30

完成恢复

最后一步是完成恢复过程，这次不再使用 --redo-only 选项。

[root@root bakwuli]# innobackupex --apply-log /xtrabackup/bakwuli/2024-08-15_20-43-21/

恢复数据到 MySQL 数据目录

将恢复的数据文件复制到 MySQL 数据目录中。确保 MySQL 服务已停止。

[root@root bakwuli]# cp -r /xtrabackup/bakwuli/2024-08-15_20-43-21/* /var/lib/mysql/

修改权限

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

[root@mysql-server ~]# systemctl start mysqld

查看是否恢复

[root@root bakwuli]# mysql -uroot -p"Password123$"

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testbakwuli |
| zabbix |
+--------------------+
7 rows in set (0.01 sec)

mysql> use testbakwuli;
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> show tables;

关于增量备份恢复过程，报错解决

InnoDB: Page [page id: space=0, page number=7] log sequence number 32452174 is in the future! Current system log sequence number 32452154.
InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery for information about forcing recovery.

xtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 32452145
xtrabackup: The intended lsn is 32452347

具体的错误信息如下：

Page log sequence number is in the future：
```
CopyInnoDB: Page [page id: space=0, page number=7] log sequence number 32452174 is in the future! Current system log sequence number 32452154.
```
这意味着某个页面的日志序列号（LSN）比当前系统的日志序列号还要大，表明数据不一致。

Transaction log file is corrupted：

Copyxtrabackup: error: The transaction log file is corrupted.
xtrabackup: error: The log was not applied to the intended LSN!
xtrabackup: Log applied to lsn 32452145
xtrabackup: The intended lsn is 32452347

这表示事务日志文件已损坏，日志应用到了错误的 LSN。

这些错误可能是由于以下原因导致的：

备份过程中 InnoDB 表空间和日志文件没有同步复制。
数据库在备份时发生了写入操作，导致数据不一致。
硬件故障或文件系统错误导致文件损坏。

解决方法

强制恢复：
可以尝试按照 MySQL 官方文档中的指导进行强制恢复。参考链接：Forcing InnoDB Recovery

在 my.cnf 文件中添加以下配置选项，然后重启 MySQL 服务：
```
Copy[mysqld]
innodb_force_recovery = 1
```
如果 innodb_force_recovery = 1 无法解决问题，可以尝试逐步增加值（最大为 6），但请注意，较高的值可能会导致数据丢失。
检查和修复文件权限：
确保 MySQL 服务器对所有 InnoDB 表空间和日志文件具有适当的读写权限。
重新备份和恢复：
如果可能的话，重新进行一个完整的备份，并确保备份过程没有中断或错误。
检查硬件和文件系统：
确保硬件和文件系统没有问题，检查磁盘是否有坏块，文件系统是否有错误。
咨询专业支持：
如果问题依然存在，考虑寻求专业的数据库支持服务，特别是如果这是一个生产环境。

为了避免InnoDB数据库损坏和备份过程中的问题，以下是一些最佳实践和预防措施：

1. 定期备份

频繁备份：定期进行全量和增量备份，确保在数据损坏时有可用的恢复点。
验证备份：定期验证备份文件的完整性和可恢复性，确保备份文件没有损坏。

2. 正确配置备份工具

使用最新版本：确保使用对应数据库版本的备份工具，如Percona XtraBackup，以获得最新的功能和修复。
正确配置：确保备份工具的配置正确，包括备份路径、日志文件路径等。

3. 数据库配置

启用双写缓冲区：在MySQL配置文件中启用InnoDB的双写缓冲区，以减少数据损坏的可能性。
```
Copy[mysqld]
innodb_doublewrite = 1
```
适当的缓冲池大小：配置适当的InnoDB缓冲池大小，确保数据库性能和稳定性。
```
Copy[mysqld]
innodb_buffer_pool_size = <适当的大小>
```

4. 数据库维护

定期检查和优化：定期运行CHECK TABLE和OPTIMIZE TABLE命令，确保表的一致性和性能。
监控错误日志：定期检查MySQL错误日志，及时发现和解决潜在问题。

5. 文件系统和硬件

选择可靠的文件系统：使用支持事务的文件系统，如EXT4或XFS，以减少文件系统层面的数据损坏。
硬件监控：定期检查硬件状态，包括磁盘健康状况，确保硬件没有故障。

6. 数据库操作

避免长时间运行的事务：避免长时间运行的事务，减少锁争用和潜在的数据不一致性。
适当的事务隔离级别：根据业务需求设置适当的事务隔离级别，避免不必要的锁争用。

7. 恢复测试

定期恢复测试：定期进行恢复测试，确保备份文件在实际恢复过程中可用。

8. 业务高峰期备份

避开高峰期：尽量避开业务高峰期进行备份，减少备份过程中数据写入的冲突。

9. 备份策略

多层次备份：结合全量备份、增量备份和日志备份，确保多层次的备份策略。
异地备份：将备份文件存储在异地，防止本地灾难导致数据完全丢失。

10. 监控和报警

设置监控和报警：使用监控工具（如Prometheus、Grafana）监控数据库和备份工具的运行状态，设置报警机制，及时发现和处理异常情况。

通过以上措施，可以大大减少InnoDB数据库损坏和备份过程中的问题，确保数据的安全性和可靠性。

Binlog 备份概述

MySQL 的二进制日志（binlog）记录了所有对数据库进行更改的语句（例如，INSERT、UPDATE、DELETE等）。通过备份和恢复 binlog，可以实现数据的增量恢复和灾难恢复。

Binlog 备份

启用 binlog
确保在 MySQL 配置文件（my.cnf 或 my.ini）中启用了 binlog。
```
Copy[mysqld]
log-bin=mysql-bin
server-id=1
```
重启 MySQL 服务以使配置生效。
```
Copysystemctl restart mysqld
```
查看 binlog 文件
使用以下命令查看当前的 binlog 文件列表。
```
Copymysql -uroot -p"Password123$" -e "SHOW BINARY LOGS;"
```
备份 binlog 文件
定期将 binlog 文件复制到安全的位置。例如，可以使用 rsync 或 scp 命令进行远程备份。
```
Copyrsync -av /var/lib/mysql/mysql-bin.* /path/to/backup/
```

Binlog 恢复的步骤

恢复完整备份
首先恢复最近的完整备份。假设完整备份存储在 /xtrabackup/bakwuli/2024-08-15_20-43-21/ 目录中。

Copyinnobackupex --apply-log /xtrabackup/bakwuli/2024-08-15_20-43-21/
cp -r /xtrabackup/bakwuli/2024-08-15_20-43-21/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

恢复 binlog 文件
使用 mysqlbinlog 工具将 binlog 文件应用到数据库中。

Copymysqlbinlog /path/to/backup/mysql-bin.000001 /path/to/backup/mysql-bin.000002 | mysql -uroot -p"Password123$"

如果需要应用特定的时间点，可以使用 --start-datetime 和 --stop-datetime 选项。

Copymysqlbinlog --start-datetime="2024-08-16 00:00:00" --stop-datetime="2024-08-16 23:59:59" /path/to/backup/mysql-bin.000001 | mysql -uroot -p"Password123$"

总结

通过 binlog 备份和恢复，可以实现 MySQL 数据库的增量恢复。这种方法特别适用于需要将数据库恢复到特定时间点的场景。结合完整备份和增量备份，binlog 备份提供了一种灵活且高效的数据恢复方案

差异备份概述

差异备份是备份自上次完整备份以来发生变化的数据。与增量备份不同，差异备份每次都基于上一次的完整备份，而不是基于上一次的增量备份。因此，恢复过程只需要恢复完整备份和最后一个差异备份。

差异备份

注意，通常完备增备，日志（binlog)备，结合使用差异则根据具体情况选用。

假设我已经完成了第1天的完整备份，现在将进行第2天和第3天的差异备份。

第1天：完整备份

假设完备（完整备份）已经完成，存储在 /xtrabackup/bakwuli/2024-08-15_20-43-21/ 目录中。

第2天：差异备份

插入新数据

Copymysql -uroot -p"Password123$"
mysql> insert into testbakwuli.t1 values(2);
Query OK, 1 row affected (0.01 sec)
mysql> exit

停止 MySQL 服务
```
Copysystemctl stop mysqld
```

执行差异备份

Copyinnobackupex -uroot -p"Password123$" --incremental /xtrabackup/bakwuli/ --incremental-basedir=/xtrabackup/bakwuli/2024-08-15_20-43-21

查看备份目录

Copyls /xtrabackup/bakwuli/

输出应类似：

Copy2024-08-15_20-43-21  2024-08-16_21-24-39

第3天：差异备份