数据库管理与高可用-MySQL全量，增量备份与恢复-海口c网

1.1MySQL数据库备份概述

MySQL 数据库备份是指将数据库中的数据、结构、索引等关键信息导出并存储到其他介质（如磁盘、磁带、云存储等）的过程，以便在数据丢失、损坏或系统故障时恢复数据。

1.1.1数据备份的重要性

数据备份是数据安全的核心防线。

数据丢失可能源于人为失误（如误删除、误操作）、硬件故障（硬盘损坏、服务器宕机）、软件漏洞（数据库崩溃、系统升级失败）、自然灾害（火灾、洪水、地震）或恶意攻击（勒索病毒、黑客入侵）。

1.1.2数据库备份类型

（1）从物理与逻辑的角度分类

数据库备份可以分为物理备份和逻辑备份。

物理备份是对数据库操作系统物理文件（如数据文件，日志文件等）的备份。

物理备份又可以分为冷备份，热备份，温备份。

备份类型	定义	数据库状态	优势	劣势	适用场景
冷备份	在数据库停止运行时进行的完全备份	离线（服务停止）	实现简单，数据一致性强，备份完整性高	服务中断影响业务，恢复时间长	非关键系统、低可用性要求场景
热备份	在数据库正常运行时进行的在线备份	在线（读写不受限）	无需停机，适合高可用性系统，对业务无影响	实现复杂，需专业工具支持，可能影响性能	生产环境、高并发业务
温备份	在数据库允许读操作但限制写操作时进行的备份	半在线（只读或轻量级写入）	数据一致性较高，对业务影响较小	需短暂锁定表或限制写入，可能影响部分业务	可接受短暂中断的系统、报表类业务

逻辑备份是通过数据库提供的逻辑导出工具（如 MySQL 的`mysqldump`），将数据库中的数据对象及其数据内容导出为可读的文本格式或特定格式文件的过程。

（2）从数据库的备份策略角度分类

从数据库的备份策略角度，数据库的备份可分为完全备份，差异备份和增量备份。

备份类型	定义	备份内容	优势	劣势	恢复效率	存储空间占用
完全备份	每次备份均复制所有数据（即完整副本），与历史备份无关。	全部数据（文件、数据库、系统状态等）	1. 恢复简单（仅需最后一次全量备份）； 2. 备份逻辑简单，不易出错。	1. 备份时间长； 2. 存储空间占用大； 3. 对业务影响较大（尤其数据量大时）。	最快（直接恢复全量）	最大
差异备份	备份自上一次完全备份以来发生变化的数据（仅记录差异）。	自上次全量备份后所有变更的数据	1. 备份速度快于全量备份； 2. 存储空间占用低于全量备份； 3. 恢复时只需全量 + 最后一次差异备份。	1. 依赖全量备份，若全量损坏则无法恢复； 2. 每次差异备份需与全量对比，备份时间随数据变更累积增长。	较快（需全量 + 最新差异）	中等（随变更累积增加）
增量备份	备份自上一次任意备份（全量或增量）以来发生变化的数据（仅记录最新差异）。	自上次任意备份后所有变更的数据（依赖历史备份链）	1. 备份速度最快； 2. 存储空间占用最小（仅记录最新变更）。	1. 恢复需按顺序应用全量 + 所有增量备份（备份链越长，恢复越复杂）； 2. 任一备份损坏会导致后续恢复失败。	最慢（需全量 + 所有增量）	最小（仅新增差异）

1.1.3常见的备份方法

MySQL数据库的备份可以采用很多方式，如直接打包数据库文件，专用备份工具，二进制日志增量备份，第三方工具备份等。

（1）物理冷备份

物理冷备份（Physical Cold Backup）是数据库备份的一种方式，核心特点是在数据库完全关闭且静止状态下，直接复制数据库的物理文件（如数据文件、日志文件、配置文件等）。

（2）专用备份工具mysqldump

`mysqldump` 是 MySQL 官方提供的逻辑备份工具，用于将数据库中的数据、表结构、存储过程等对象导出为 SQL 脚本文件。这些脚本可在需要时通过 `mysql` 命令行工具或图形化界面重新导入数据库，实现数据恢复或迁移。

（3）通过启用二进制日志进行增量备份

MySQL 的二进制日志（Binlog）是记录数据库所有变更操作的日志文件（如 INSERT、UPDATE、DELETE 等，不包括 SELECT 等查询操作）。它以二进制格式存储，用于主从复制、数据恢复和审计追踪。通过启用 Binlog 并结合全量备份，可实现增量备份，即仅备份两次全量备份之间的变更数据，大幅减少备份时间和存储空间。

（4）第三方工具备份

第三方工具备份是指使用非数据库官方原生工具（如 MySQL 官方的 `mysqldump`、`mysqlbinlog` 等），由第三方厂商或开源社区开发的数据库备份解决方案。这类工具通常针对原生工具的局限性（如性能瓶颈、功能单一、自动化程度低等）进行优化，提供更高效、便捷、智能化的备份与恢复能力，适用于复杂业务场景或大规模数据库集群。

2.1数据库完全备份操作

2.1.1物理冷备份与恢复

物理冷备份一般用tar命令直接打包数据库文件夹，而在进行备份之前需要关闭mysqld服务。

2.1.2mysqldump备份与恢复

`mysqldump` 是 MySQL 官方提供的逻辑备份工具，通过生成 SQL 脚本文件记录数据库的结构和数据。它是中小型数据库备份的首选工具，适用于数据迁移、灾难恢复、开发测试等场景。

[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --databases test > test.sql
Enter password:
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:

[root@localhost ~]# mysql -u root -p test < mysql-user.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW TABLES FROM test;' // 验证导入结果
Enter password:
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
[root@localhost ~]# mysql -u root -p -e 'DROP DATABASE test;'

[root@localhost ~]# mysql -u root -p -e 'DROP DATABASE test;'
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW DATABASES;'
Enter password:
+----------------+
| Database |
+----------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+----------------+
[root@localhost data]# mysql -u root -p < ~/test.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'SHOW DATABASES;'
Enter password:
+----------------+
| Database |
+----------------+
| information_schema |
| test |
| mysql |
| performance_schema |
| sys |
+----------------+

[root@localhost ~]# mysql -u root -p
Enter password:
mysql> source /root/test.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

2.1.3MySQL增量备份与恢复

MySQL二进制日志对备份的意义

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin 
binlog_format = MIXED 
server-id=1 #为 mysql 
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 154 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.index

3.1制定企业备份策略的思路

前期评估

风险评估：全面排查内部系统漏洞、人员误操作等，以及外部网络攻击、自然灾害等潜在风险，明确数据面临的威胁。比如电商企业要防范黑客攻击导致用户信息泄露风险。

业务影响分析：评估业务中断对企业运营、声誉、经济等方面的冲击。如金融交易系统中断会严重影响业务开展和企业信誉，需重点保障。

明确策略要素

范围与目标：确定需备份的核心数据，如制造企业的生产工艺数据等；设定恢复时间目标（RTO）和恢复点目标（RPO），像在线支付系统 RTO 可能要求分钟级，RPO 要求秒级。

优先级：区分数据重要程度，对关键业务数据优先备份，如医院的患者病历数据。

规划备份流程

人员安排：确定备份操作、监控、管理等人员职责，如系统管理员负责执行备份任务。

工具选择：依据企业规模、数据量等选合适备份工具，如小型企业可用免费开源备份软件，大型企业可采用专业商业备份解决方案。

存储方案：考虑本地存储、异地存储或云存储等方式，结合使用保障安全，如一份数据本地存储用于快速恢复，一份存于异地防区域性灾难。

选择备份类型

完全备份：定期完整备份数据，恢复简单但耗时占空间，可每周或每月执行一次，用于奠定数据基础副本。

增量备份：备份自上次备份后变化的数据，节省时间空间，但恢复复杂，适合数据频繁变动场景，如电商订单数据。

差异备份：备份自上次完全备份后变化的数据，恢复相对简单，可在完全备份间隔期间使用。

完善恢复与迁移

恢复流程：制定详细恢复步骤，定期模拟测试，如季度性灾难恢复演练，确保能快速恢复数据。

迁移流程：规划数据在不同存储介质间迁移方法，保障业务连续性，如数据从本地迁移到云端。

设定数据管理规则

保留规则：根据法规和业务需求确定数据保留期限，如财务数据按规定保留多年。

删除规则：到期数据自动删除或迁移，节约存储资源，减轻管理负担。

4.1扩展：Mysql的GTID

4.1.1MySQL的GTID

GTID（Global Transaction ID）即全局事务 ID ，从 MySQL 5.6.5 开始引入，用于唯一标识数据库中的每个事务，旨在简化主从复制配置与维护，保障数据一致性。

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/usr/local/mysql/data/mysql-bin #启用二进制日志（Binary Log）并指定其存储路径
binlog_format = MIXED #定义二进制日志的记录格式为混合模式
server-id=1 #为 mysql 实例分配一个唯一的服务器标识符
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 154 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Jan 2 14:22 /usr/local/mysql/data/mysql-bin.index
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON # 确保事务安全性
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p
mysql> SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> reset master; 
mysql> show master status;
+------------------+----------+------------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+------------------+------------------+-------------------+
创建测试库 test，测试表 user，并导入 3 条数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table user(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into user values(1);
Query OK, 1 row affected (0.14 sec)
mysql> insert into user values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(3);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+------------------+------------------+--------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+--------------------------------------+
| mysql-bin.000001 | 1417 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-5 |
+------------------+----------+------------------+------------------+--------------------------------------+
1 row in set (0.00 sec)
[root@localhost ~]# mysqldump -u root -p --databases test >test.sql
Enter password:
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.
[root@localhost ~]# grep -i gtid test.sql
-- GTID state at the beginning of the backup
SET @@GLOBAL.GTID_PURGED=/!80000 '+'/ 'd780a5a6-055f-11f0-b6e6-000c29078b04:1-5';
[root@localhost ~]# mysql -uroot -p
mysql> use test;

Database changed
mysql> insert into user values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test.user;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+------------------+------------------+--------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+--------------------------------------+
| mysql-bin.000001 | 2007 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-7 |
+------------------+----------+------------------+------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> drop database test;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+------------------+------------------+--------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+------------------+------------------+--------------------------------------+
| mysql-bin.000001 | 2188 | | | d780a5a6-055f-11f0-b6e6-000c29078b04:1-8 |
+------------------+----------+------------------+------------------+--------------------------------------+
[root@localhost~]#mysqlbinlog
--include-gtids='d780a5a6-055f-11f0-b6e6-000c29078b04:3-7'
/usr/local/mysql/data/mysql-bin.000001 >/mysqlbak.sql
[root@localhost ~]#mysql -u root -p -e "RESET MASTER;" 
[root@localhost ~]# mysql -uroot -p <test.sql
[root@localhost ~]# mysql -uroot -p -e "select * from test.user;"
Enter password:
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
(8) 恢复增量
[root@localhost ~]# mysql -uroot -p <mysqlbak.sql
[root@localhost ~]# mysql -uroot -p -e "select * from test.user;"
Enter password:
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+