【MySQL数据库备份与恢复2】备份的三种常用方法
mysqldump客户端程序可以执行逻辑备份并生成一组SQL语句，其中包含原始数据库和表的定义以及表中的数据，以便实现对数据库的简单备份或复制。mysqldump命令可以生成CSV、或XML格式的文件。简单灵活：是一个命令行工具，使用相对简单不需要复杂的配置，提供了各种选项和参数，可以按需备份数据库的结构和数据，包括表格结构、数据、触发器、存储过程等。跨平台支持：适用于多个操作系统，包括Windo
目录
1.2.7 增量备份（Incremental Backup）
1.2.8 差异备份（Differential Backup）
一.备份
1.1 作用
数据库备份是数据管理和维护中的一个重要步骤，具体的作用如下：
- 1. 数据恢复：在硬件故障、软件错误、数据损坏或人为失误的情况下，备份可以帮助快速恢复数据，减少业务中断的影响。
- 2. 灾难恢复：在发生自然灾害（如火灾、洪水、地震）或其他灾难性事件时，备份可以确保数据不会丢失。
- 3. 数据完整性：定期备份有助于保持数据的完整性和一致性，确保数据的准确性和可靠性。
- 4. 数据迁移：在系统升级或迁移到新的数据库平台时，备份可以确保数据的平滑过渡。
- 5. 审计和报告：备份可以用于审计目的，提供历史数据以供分析和报告。
- 6. 测试和开发：备份可以用来创建开发和测试环境，而不需要复制整个生产数据库。
1.2 分类
数据库备份主要分为：逻辑备份，物理备份，冷备份，热备份，温备份，全量备份，增量备份，差异备份等
MySQL 备份分类表
| 备份类型 | 备份方式 | 特点 | 适用场景 |
|---|---|---|---|
| 逻辑备份 | 生成 SQL 语句（如 mysqldump） |
可读性强，便于迁移和恢复；速度慢，占用资源少 | 小数据量、跨版本迁移、逻辑恢复 |
| 物理备份 | 直接复制数据库文件（如 cp、rsync、xtrabackup） |
速度快，恢复快；不可读，依赖文件系统一致性 | 大数据量、快速恢复、整库备份 |
| 冷备份 | 数据库完全关闭后进行备份 | 数据一致性最好；服务不可用 | 维护窗口期、可停机环境 |
| 热备份 | 数据库运行状态下进行备份 | 不影响服务；需工具支持（如 InnoDB 引擎 + xtrabackup） |
高可用系统、7×24 运行环境 |
| 温备份 | 数据库部分可读写或只读状态备份 | 折中方案；部分操作受限 | 中等可用性要求，允许短暂锁表 |
| 全量备份 | 备份所有数据 | 完整性强，恢复简单；占用空间大，耗时长 | 定期完整备份，作为增量/差异备份的基础 |
| 增量备份 | 备份自上次备份以来的变更 | 节省空间和时间；恢复时依赖前一次备份 | 频繁备份、节省存储资源 |
| 差异备份 | 备份自上次全量备份以来的变更 | 恢复比增量快，但比全量节省空间 | 折中备份策略，平衡恢复速度与存储开销 |
1.2.1 逻辑备份 (Logical Backup)
定义：逻辑备份指的是备份数据库的逻辑结构和数据，通常是通过导出数据库中的数据和对象（如表结构、视图、存储过程等）来实现的。生成的是数据库可以识别的格式，如SQL语句。
特点：
- 备份文件中包含SQL语句，稍加修改就可以在不同的数据库系统上执行。
- 可以备份整个数据库或特定的数据库对象。
- 备份和恢复的速度比物理备份慢
1.2.2 物理备份（Physical Backup）
定义：物理备份是指直接复制数据库文件，包括数据文件、日志文件等。这种备份方式不涉及数据库的逻辑结构，而是直接在文件系统层面上复制数据库的存储结构。
特点：
- 备份文件是数据库文件的副本，不包含SQL语句。
- 通常不能跨数据库厂商进行备份与恢复，比如MySQL的备份不能在Oracle中恢复。
- 可以非常快速地备份和恢复大型数据库，因为不需要解析SQL语句，直接复制文件即可。
逻辑备份和物理备份优势和适用场景
- 逻辑备份适合于需要跨平台迁移、灵活备份和恢复的场景
- 物理备份适合于需要快速恢复、对存储空间要求不高的场景
- 在实际应用中，根据数据库的大小、业务需求、恢复时间目标等因素，可能会选择一种或结合使用两种备份方式。
1.2.3 冷备份（Cold Backup）
定义：冷备份是指在数据库完全关闭的情况下进行的备份。在备份过程中，数据库服务不可用。
特点：
- 简单易行，因为不需要考虑数据一致性问题。
- 备份过程中不会对数据库性能产生影响。
- 备份窗口期间数据库不可用，可能影响业务连续性。
1.2.4 热备份（Hot Backup）
定义：热备份是指在数据库正常运行的情况下进行的备份，备份过程中数据库服务仍然可用。
特点：
- 可以在数据库运行时进行备份，所以备份的数据是系统当前最新的。
- 不需要停止数据库服务，对业务影响最小。
- 在高负载情况下，可能会对数据库性能产生一定影响。
- 技术实现复杂，需要考虑数据一致性和并发控制。
1.2.5 温备份（Warm Backup）
定义： 温备份是介于冷备份和热备份之间的一种备份方式，数据库在备份过程中部分可用或处于只读模式。
特点：
- 可以在数据库运行时进行备份，减少业务中断时间。
- 需要数据库支持部分备份功能，如只读模式。
- 相对于热备份，技术实现简单一些，因为不需要处理所有的并发写入操作。
冷备份、热备份和温备份是数据库备份策略中的三种不同类型，根据在备份过程中数据库是否可用来区分
1.2.6 全量备份（Full Backup）
定义：全量备份是指备份整个数据库或文件系统的所有数据，包括所有的文件、数据库表、配置文件等。它不依赖于其他备份，可以独立恢复数据。
特点：
- 恢复简单，一个全量备份就可以恢复所有数据。
- 易于理解和管理。
- 备份时间较长，因为需要备份所有数据。
- 占用的存储空间较大。
- 如果数据量很大，可能会影响业务系统的性能。
1.2.7 增量备份（Incremental Backup）
定义：增量备份是指只备份自上次备份以来发生变化的数据。可以是上次全量备份以后，也可以是上次增量备份以后。
特点：
- 备份速度快，只需要备份变化的数据。
- 占用的存储空间较小。
- 可以节省时间和资源，特别是对于大型数据库或文件系统。
- 恢复复杂，需要多个备份文件
- 管理较为复杂，需要跟踪多个备份文件。
1.2.8 差异备份（Differential Backup）
定义：差异备份是指只备份自上次全量备份以来所有变化的数据。
优点：
- 恢复速度比增量备份快，只需要最后一次全量备份和最后一次差异备份。
- 管理相对简单。
- 随着时间的推移，备份的数据量可能会增加，导致备份时间变长和存储空间需求增加。
- 全量备份、增量备份和差异备份是数据备份策略中的基本类型，它们在备份数据的范围和频率上有所不同
- 全量备份、增量备份和差异备份描述的是备份内容的范围，物理备份和逻辑备份描述的是备份的方式
二. 备份方法一：Mysqldump工具
这个mysqldump是MySQL官方提供的数据导出工具。导出的是SQL语句，也就是说Mysqldump是逻辑备份。
2.1 介绍
mysqldump客户端程序可以执行逻辑备份并生成一组SQL语句，其中包含原始数据库和表的定义以及表中的数据，以便实现对数据库的简单备份或复制。
mysqldump命令可以生成CSV、或XML格式的文件。
- 简单灵活：是一个命令行工具，使用相对简单不需要复杂的配置，提供了各种选项和参数，可以按需备份数据库的结构和数据，包括表格结构、数据、触发器、存储过程等。
- 跨平台支持：适用于多个操作系统，包括Windows、Linux和Mac等。
- 兼容性好：SQL文件是纯文本格式，易于编辑和传输，支持几乎所有的MySQL版本。
- 易于恢复：导出的SQL文件可以直接用于恢复数据库，通过简单的命令即可重新导入数据。
- 数据恢复缓慢：由于恢复时是将备份产生的SQL语句逐条执行，对于大型数据库、高频率备份和快速恢复等需求不太合适
- 无增量备份：不支持增量备份，每次备份都需要导出整个数据库。
2.2 应用场景
- 数据库版本升级
- 只备份表格构成小于10GB以下的数据库
- 跨数据库类型迁移，比如MySQL升级到Oracle
- 云平台之间的迁移，比如阿里云迁移到腾讯云
2.3 语法
介绍
mysqldump的方法通常有以下使用，可以转储一个或多个表或数据库，如下所示：
# 导出
mysqldump [options] > dump.sql
# 导⼊
mysql [options] < backup-file.sql常用选项
mysqldump有如下常用选项，可以在命令行中指定，也可以在选项文件中通过[mysqldump]和[client]组进行指定
在选项之后的所有名称参数都被视为表名。
| 选项 | 说明 |
|---|---|
--add-drop-database |
在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句 |
--add-drop-table |
在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句 |
--add-drop-trigger |
在每个 CREATE TRIGGER 语句之前添加 DROP TRIGGER 语句 |
--add-locks |
用 LOCK TABLES 和 UNLOCK TABLES 语句包裹每个表转储 |
--all-databases, -A |
转储所有数据库中的所有表 |
--databases, -B |
--databases=db_name，转储部分数据库中的所有表，多个数据库名用空格隔开；将参数解释为数据库名称并转储所有表 |
--comments, -i |
添加注释到转储文件 |
--complete-insert, -c |
使用包含列名的完整 INSERT 语句 |
--events, -E |
从转储数据库中转储事件 |
--extended-insert, -e |
使用多行 INSERT 语法 |
--flush-logs, -F |
在开始转储前刷新日志 |
--force, -f |
转储期间发生了 SQL 错误，也要继续 |
--ignore-table |
--ignore-table=db_name.table_name，多个表用空格隔开；不转储给定的表 |
--lock-all-tables, -x |
锁定所有数据库中的所有表 |
--lock-tables, -l |
在转储之前锁定指定要转储的表 |
--no-autocommit |
将每个转储表的 INSERT 语句包含在 SET autocommit = 0 和 COMMIT 语句中 |
--no-create-db, -n |
不要生成 CREATE DATABASE 语句 |
--no-create-info, -t |
不要为每个转储的表生成 CREATE TABLE 语句 |
--no-data, -d |
不转储表内容 |
--routines |
在输出中转储数据库的存储过程和函数 |
--skip-add-drop-table |
在每个 CREATE TABLE 语句之前不添加 DROP TABLE 语句 |
--skip-add-locks |
不要添加锁 |
--skip-comments |
转储文件中不添加注释 |
--skip-triggers |
不转储触发器 |
| --tables | --tables=table_name ，多个表名用空格空开，在选项中后的所有名称参数都被视为表名 |
--triggers |
转储每个表中的触发器 |
2.4 注意事项
- 转储表时必须要有 SELECT 权限；
- 转储视图时必须要有 SHOW VIEW 权限；
- 转储触发器时必须要有 TRIGGER 权限；
- 如果没有使用 --single-transaction 选项时必须要有 LOCK TABLES 权限；
- 如果没有使用 --no-tablespaces 选项时必须要有 PROCESS 权限；
- 重新导入转储文件时，也需要有相应的权限；
- 由于mysqldump是逐行转储数据，所以不适用于大数据的转储与导入，一般适用于50G以下的数据备份，50G以上使用物理备份；
- 默认不备份 INFORMATION_SCHEMA, performance_schema, sys, 需要备份时显示指定；
- mysqldump是单线程，数据量大时备份时间长，在备份过程中会对非事务表（MyISAM）长时间锁定，可能会对业务造成影响；
- 备份结果是SQL形式，数据恢复时间也相对较长，大概是备份时间的5-10倍。
2.5 简单备份示例
2.5.1.简单备份（对单个数据库进行备份）
目标：对testdb数据库进行全量备份
查看原始数据
简单导出示例+查看导出文件内容
# 导出本地testdb数据库中的所有数据到磁盘
mysqldump -uroot -p -h127.0.0.1 -P3306 -B testdb > /home/zs_108/dump.sql 将本机 testdb 数据库的所有内容（表结构+数据）导出到 /home/zs_108/dump.sql 文件。
我们可以进去dump.sql看看
- 1. 导入数据时为当前表加锁
- 2. 并生成INSERT 语句
- 3. 释放锁
如果说我的备份文件是按年月日来命名的，我们可以像下面这样子搞
# 备份文件按当前时间命名
mysqldump -uroot -p -h127.0.0.1 -P3306 -B testdb > /home/zs_108/`date +%Y%m%d`.sql| 特殊语法 | 说明 |
|---|---|
`date +%Y%m%d` |
反引号执行命令：生成当前日期字符串%Y=年（2023），%m=月（07），%d=日（25）→ 输出 20230725 |
| 最终路径 | /backup/mysql/20230725.sql（示例文件名） |
我们也进去看看
其实内容还是一样的
恢复
目标：根据备份文件把数据恢复到当前服务器上的数据库
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /home/zs_108/dump.sql
# 方式二：登录mysql客户端导入SQL文件
mysql> source /home/zs_108/dump.sql这两种方法都是可以的，我们只演示方式一。
首先我们需要回去数据库里面把testdb数据库删除掉
接着我们就可以恢复我们的数据库testdb了
我们回去mysql看看
恢复完成后与备份库中的数据一致。
2.5.2.mysqldump的潜在问题
mysqldump备份执行流程（默认模式）
-
建立数据库连接
通过指定用户名、密码及服务器地址端口，与 MySQL 服务端建立网络连接，完成身份认证和权限校验。 -
确定备份范围
查询数据库元信息，识别需要备份的数据库对象，包括表结构、视图、存储过程等，并根据参数过滤特定对象（如排除指定表）。 -
施加全局读锁
执行全局读锁定操作，阻塞所有数据写入请求（INSERT/UPDATE/DELETE），确保备份开始时数据库处于静止状态。此操作会导致业务写操作暂停。 -
循环导出数据
-
表结构导出：为每张表生成对应的
CREATE TABLE建表语句。 -
表数据导出：逐表执行全表扫描，将数据行转换为 INSERT 语句写入备份文件。
此过程按表顺序处理，直至所有表备份完成。
-
-
释放锁并收尾
解除全局读锁恢复数据库写入能力，关闭数据库连接，刷新文件缓冲区确保备份数据完整落盘。
存在问题——一致性问题
上述的备份流程在备份之前会对所有表进行加锁，也就意味着，在备份完成之前，正常业务产生的相关插入、删除、truncate操作都在处于锁等待状态，会对业务造成严重的影响，不建议在生产环境使用。
同时由于备份过程中会产生数据不一致问题：
- 数据可能已经改变，但是这些改变尚未提交，因此备份可能包含未提交的事务中的数据更改。
- 如果备份期间发生了事务回滚，备份中可能包含已经删除或修改了的数据，这些数据在恢复后可能不存在。
- 如果备份期间有新的数据插入，那么这些新数据可能不会被备份。
解决备份过程中的数据一致性问题
在数据库备份过程中，一个核心挑战是确保备份数据在某一特定时间点的一致性。这意味着备份应该反映数据库在备份开始那一刻的完整状态，即使备份期间数据库仍在不断更新。
如果备份工具在读取数据时，数据库中的数据正在被修改（例如，一个事务只提交了部分更改），就可能导致备份文件内部数据逻辑不一致（例如，引用完整性被破坏、部分更新可见等）。
解决方案：使用 --single-transaction 选项（针对 InnoDB）
mysqldump 工具提供的 --single-transaction 选项是解决 InnoDB 存储引擎表备份一致性问题的有效方法。
其核心原理是利用 InnoDB 的多版本并发控制（MVCC）机制，在备份操作期间创建一个可重复读（Repeatable Read） 隔离级别的事务视图。这个视图保证了在事务（即备份过程）内部看到的数据状态，始终是备份开始时（事务开始时）的快照状态，不受备份期间其他并发事务提交的影响。
详细执行流程与机制解析：
1.建立连接 (Connect):
-
备份工具（如
mysqldump）首先建立到 MySQL 数据库实例的标准连接。
2.刷新表 (FLUSH TABLES):
-
目的： 关闭当前数据库实例上所有已打开的非系统表。这是一个准备步骤。
-
原因： 确保下一步
FLUSH TABLES WITH READ LOCK能够顺利执行，避免因存在长时间运行的查询或事务导致表无法立即关闭和锁定。
3.全局读锁 (FLUSH TABLES WITH READ LOCK - FTWRL):
-
目的： 获取一个短暂的全局读锁。
-
动作： 关闭所有打开的表，并为所有表加上全局读锁。
-
影响：
-
阻塞 DML/DDL： 所有试图修改数据的操作（INSERT, UPDATE, DELETE）和修改表结构的操作（ALTER TABLE, DROP TABLE 等）都会被阻塞。
-
阻塞 COMMIT： 此时，即使是已经执行完但尚未提交的事务也无法完成提交。这是关键点，它确保了在获取一致性视图之前，没有“悬而未决”的更改。
-
允许只读查询： 已经开始的只读 SELECT 查询（没有 FOR UPDATE/SHARE）通常可以继续运行完成，但新的只读查询也会被阻塞直到锁释放（在下一步之后很快发生）。
-
关键作用： 这步操作冻结了整个数据库实例在 那一刻 的逻辑状态，特别是确保了所有未提交的事务要么回滚，要么暂时无法提交，为下一步设置稳定的事务视图奠定了基础。这是保证 所有存储引擎（不仅仅是 InnoDB）在 那一刻 状态一致的关键瞬间。
4.设置会话隔离级别 (SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ):
-
目的： 将当前备份连接会话的事务隔离级别显式设置为 REPEATABLE READ。
-
在REPEATABLE READ隔离级别下面，在事务中的第一个查询时创建一个Readview，以后所有事务中所有的查询都是通过这个Redview获取数据，保证了数据的一致性。
-
原因： InnoDB 在 REPEATABLE READ 隔离级别下，通过 MVCC 机制为事务提供一致性视图。设置此级别确保了即将开启的备份事务能看到一个稳定的、不受后续修改影响的快照。
5.开启备份事务 (START TRANSACTION):
-
目的： 显式开启一个长时间运行的用于备份的事务。
--single-transaction选项的核心在此生效。 -
机制： 此时，在 REPEATABLE READ 隔离级别下，InnoDB 引擎会为这个新事务创建一个一致性读视图（Consistent Read View）。这个视图是基于 步骤 3 (FTWRL) 成功完成那一刻 的数据库状态（因为 FTWRL 阻止了任何新提交）。该视图保证了在此事务（备份过程）的生命周期内，所有基于 InnoDB 的表的 SELECT 查询都将看到这个一致的快照，无论之后其他事务如何修改数据。
6.释放全局读锁 (UNLOCK TABLES):
-
目的： 释放步骤 3 中获取的全局读锁 (
FLUSH TABLES WITH READ LOCK)。 -
由于当前的备份是在事务中进行的，不会和其他的操作相互影响。
-
影响：
-
所有被阻塞的 DML (INSERT, UPDATE, DELETE) 和 DDL (ALTER, DROP 等) 操作现在可以继续执行。
-
数据库实例恢复正常的读写操作。
-
-
关键点： 这个锁的持有时间非常短暂（仅用于设置事务隔离级别、开启事务和记录 binlog 位置）。一旦锁释放，对 InnoDB 表的后续备份操作（SELECT）完全依赖于步骤 5 开启的事务及其 MVCC 视图来保证一致性，不再需要任何锁。这大大减少了对生产业务的影响。
7.执行数据备份 (SELECT ... FROM tbl):
-
目的： 备份工具（如
mysqldump）通过执行类似SELECT /*!40001 SQL_NO_CACHE */ * FROMtable_name`` 的查询语句逐表读取数据。 -
这一步是查询数据并备份，具体的备份过程。
-
一致性保证机制：
-
对于 InnoDB 表： 所有 SELECT 查询都在步骤 5 开启的事务内执行，利用 REPEATABLE READ 隔离级别的 MVCC 机制。因此，读取到的数据始终是备份开始时（事务开始时，即 FTWRL 成功那一刻）的快照数据，完美保证了一致性，且不会阻塞其他会话的读写操作（除了可能的元数据锁冲突）。
-
对于非 InnoDB 表 (如 MyISAM)： 无法保证一致性！ 因为 MyISAM 等存储引擎不支持事务和 MVCC。在
UNLOCK TABLES释放锁后，对这些表的 SELECT 操作会读取到实时的、最新的数据。如果备份过程中有对这些表的写入操作，备份文件可能包含不一致的数据（例如，部分更新）。因此，--single-transaction主要适用于或要求数据库表全部（或需要一致备份的部分）使用 InnoDB 存储引擎。对于混合引擎，需要额外措施（如短暂锁表或使用其他工具）。
-
8.提交事务 (COMMIT):
-
目的： 完成所有数据读取后，提交步骤 5 开启的备份事务。
-
影响： 结束该事务，释放其占用的资源（如撤销日志空间）。此时，备份过程对数据库的一致性视图依赖结束。DDL 操作在事务执行期间也可能被阻塞（InnoDB 的元数据锁机制），提交后它们可以正常执行。
这个我们就作了解即可，对于数据的大表，我们也不会使用mysqldump来备份，那么备份小表的时候，我们记得加上--single-transaction选项即可。
2.6 完整实例
2.6.1 收集信息
在对数据库进行完全备份前，需要收集数据库相关信息，如：存储引擎、字符集等，确保备份内容完整。
具体过程如下所示：
查看数据库和表信息
-- 查看数据库和表信息
SELECT
table_schema,
table_name,
table_collation,
ENGINE,
table_rows
FROM
information_schema.TABLES
WHERE
table_schema NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' );
-- 查看是否存在存储过程
SELECT count(*) FROM information_schema.routines;
-- 查看是否存在触发器、调度事件
SELECT count(*) FROM information_schema.TRIGGERS;
-- 查看是否存在调度事件
SELECT count(*) FROM information_schema.EVENTS;
-- 查看字符集
show variables like 'character%';
查看限制信息
限制信息是MySQL服务器的限制，按备份时的需要进行修改
建议调大，如果设置过小且导入导出时间过长，会导致数据还没有处理完就触发了连接超时，从而任务造成任务失败
-- 查看数据库连接超时时间
show variables like 'wait_timeout%'; 
28800秒默认就是8小时，如果我们8小时内备份不完，就得将其调大一些。至于调大多少，这需要根据我们的数据量。
-- 查看交互式连接超时时间
show variables like 'interactive_timeout'; -- 默认8小时
注意这个交互式连接超时时间应该设置为和上面的数据库连接超时时间一样。
-- 查看数据包大小限制，建议128MB或256M
show variables like 'max_allowed_packet'; -- # 防止包过大而失败，默认64MB，最大1GB
MySQL数据库在备份数据库的时候发送数据包的大小，如果超过这个限制，则将拒绝发送。
备份的时候 一般需要将数据包的大小调大到1GB。
-- 查看事务是否自动提交
show variables like 'autocommit';
这个必须设置为ON。
2.6.2 创建备份用户
MySQL8.0版本的
-- ======================= MySQL 8.0 配置 ======================= --
-- 创建备份专用用户（使用传统密码插件确保兼容性）
CREATE USER 'backup_user'@'localhost'
IDENTIFIED WITH mysql_native_password -- 使用传统密码认证插件（兼容旧版工具）
BY '123456';
-- 授予备份所需权限
GRANT
BACKUP_ADMIN, -- 允许执行LOCK INSTANCE FOR BACKUP（物理备份必需）
PROCESS, -- 查看活动进程状态（SHOW PROCESSLIST）
SELECT, -- 读取表数据（核心备份权限）
RELOAD, -- 执行FLUSH操作（刷新表/日志）
LOCK TABLES, -- 锁定表（MyISAM等非事务引擎备份需要）
REPLICATION CLIENT, -- 获取二进制日志位置（SHOW MASTER/SLAVE STATUS）
EVENT, -- 备份事件调度器
TRIGGER, -- 备份触发器
SHOW VIEW -- 备份视图定义
ON *.* TO 'backup_user'@'localhost';
-- 注意：8.0不再需要SUPER权限（由BACKUP_ADMIN替代）注意：一个MySQL用户由登录名和主机两部分组成，用户名和主机之间使用@隔开，并且用户名与主机必须分别用单引号引起来。
- *.*：表示所有库下的所有表
- 库名.* ：表示指定数据库下的所有表
- 库名.表名 ：表示指定数据库下的指定表
MySQL5.7版本的
-- ======================= MySQL 5.7 配置 ======================= --
-- 创建备份专用用户
CREATE USER 'backup_user'@'localhost'
IDENTIFIED BY 'StrongPassword123!'; -- 5.7默认使用mysql_native_password
-- 授予备份所需权限
GRANT
RELOAD, -- 执行FLUSH操作
LOCK TABLES, -- 锁定表
REPLICATION CLIENT, -- 获取二进制日志位置
CREATE TABLESPACE, -- 表空间操作（物理恢复需要）
PROCESS, -- 查看活动进程
SUPER, -- 管理操作（5.7必需）
SELECT, -- 读取表数据
EVENT, -- 备份事件
TRIGGER, -- 备份触发器
SHOW VIEW -- 备份视图
ON *.* TO 'backup_user'@'localhost';我们这里是MySQL8.0版本，所以我们创建一下
我们也可以去查看一下我们有没有创建成功
2.6.3 创建备份目录
mkdir -p /backup/mysql 
2.6.4 生产环境单库备份（为指定数据库进行备份）
我们这里需要备份整个testdb库。
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
-B testdb \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
> /backup/mysql/$(date +%Y%m%d)_0.sql| 参数 | 说明 |
|---|---|
-ubackup_user |
指定连接 MySQL 的用户名 |
-p |
提示输入密码（不建议在脚本中明文写密码） |
-h127.0.0.1 |
指定 MySQL 服务器地址 |
-P3306 |
指定 MySQL 服务端口 |
-B testdb |
指定要备份的数据库，支持多个数据库名（空格分隔） |
--default-character-set=utf8mb4 |
设置默认字符集为 utf8mb4，支持中文和表情符号 |
--single-transaction |
保证一致性备份，适用于 InnoDB 存储引擎。我们这里一定需要添加--single-transaction选项，具体原因看2.5.2.mysqldump的潜在问题。 |
--source-data=2 |
添加 CHANGE MASTER 语句，用于主从复制搭建（2 表示同时写入注释和语句） |
--triggers |
备份每个表的触发器 |
--events |
备份数据库的事件调度器 |
--routines |
备份存储过程和函数 |
--set-gtid-purged=OFF |
不写入 GTID 清除信息，适用于非 GTID 环境或避免冲突 |
> /backup/mysql/... |
将备份结果输出到指定路径的 SQL 文件中，文件名包含当前日期 |
我们可以进去看看
恢复就是使用下面这两个任意一个即可。
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/20250725_0.sql
# 方式二：登录mysql客户端导入SQL文件
mysql> source /backup/mysql/20250725_0.sql我们来看看吧。
首先我们看看testdb原来的样子
然后我们把testdb删除掉
接下来我们就恢复一下
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/20250725_0.sql
我们回去mysql里看看
怎么样？很好吧
2.6.5 完整实例备份(对所有数据库进行备份——不推荐)
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
--all-databases --default-character-set=utf8mb4 \
--single-transaction --source-data=2 \
--triggers --events --routines \
--set-gtid-purged=OFF --flush-logs \
> /backup/mysql/`date +%Y%m%d`_1.sql- --all-databases：备份所有数据库
- --flush-logs：备份完刷新日志，以便后面做增量备份
不推荐使用，有的版本存在bug，可能会导致某些系统表无法使用，比如mysql.proc。
我们一般是不使用--all-databases选项的
2.6.6 单表多表备份
首先我们看看里面有啥表？
如果我们只想备份topic01里面的class和student表，应该像下面这样子
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
-B topic01 --tables class student \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
> /backup/mysql/dump2.sql由于class和student表存在主外键关系，所以需要先导出class表，再导出student表
--tables：备份指定库中的表，多个表用空格隔开
| 参数 | 说明 |
|---|---|
-B topic01 |
指定要备份的数据库 |
--tables class student |
指定只备份 class 和 student 表 |
--default-character-set=utf8mb4 |
使用 utf8mb4 字符集 |
--single-transaction |
保证一致性备份（适用于 InnoDB） |
--source-data=2 |
添加 CHANGE MASTER 信息，用于搭建复制 |
--triggers |
备份触发器 |
--events |
备份事件 |
--routines |
备份存储过程和函数 |
--set-gtid-purged=OFF |
不记录 GTID 信息 |
> /backup/mysql/dump2.sql |
输出到指定文件 |
我们进去dump2.sql看看
恢复就是使用下面这两个任意一个即可。
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/dump2.sql
# 方式二：登录mysql客户端导入SQL文件
mysql> source /backup/mysql/dump2.sql我不演示了
2.6.7 按条件备份
需要对满足一定条件的记录进行备份，比如按id范围备份，按日期区间备份等
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
-B topic01 \
--tables class student \
--where='id < 2' \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
> /backup/mysql/dump3.sql| 参数 | 说明 |
|---|---|
-B topic01 |
指定数据库 |
--tables class --where='id < 2' |
备份 class 表，并只导出 id < 2 的数据 |
student --where='id < 2' |
同上，针对 student 表 |
--default-character-set=utf8mb4 |
使用 utf8mb4 字符集 |
--single-transaction |
基于事务的一致性备份（适用于 InnoDB） |
--source-data=2 |
添加 CHANGE MASTER 信息，用于复制 |
--triggers |
导出触发器 |
--events |
导出事件调度器 |
--routines |
导出存储过程和函数 |
--set-gtid-purged=OFF |
不写入 GTID 信息 |
> /backup/mysql/dump3.sql |
输出到指定 SQL 文件 |
--where='id<2'：备份条件，如果备份多张表，备份的所有表中必须都包含条件中的列名，类型不匹配会报错。
只要符合SQL的规则，所有的条件都可以写在选项中，条件会应用到所有的表
- 如果表中符合对应的条件，则按条件进行导出
- 如果表中没有符合台哦就则跳过
上面那个SQL语句会将下面这两个表里的id列分别小于3的都导出来
我们来看看
进去看看
恢复就是使用下面这两个任意一个即可。
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/dump3.sql
# 方式二：登录mysql客户端导入SQL文件
mysql> source /backup/mysql/dump3.sql我不想演示了。
--where='id<2'：备份条件，如果备份多张表，备份的所有表中必须都包含条件中的列名，类型不匹配会报错。
我们来看看
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
-B topic01 \
--tables class student \
--where='class_id < 2' \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
> /backup/mysql/dump3.sql 我们一执行，发现报错了
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `class` WHERE class_id < 2': Unknown column 'class_id' in 'where clause' (1054)
这个是因为 class_id只存在于student表里面，class表里面没有这一列，条件不能满足所有的表。所以会报错。
2.6.8 导出表结构
只需要导出表结构，比如新功能测试通过，导出表结构后移交到生产环境进行部署
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
-B testdb \
-d \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
> /backup/mysql/dump4.sql| 参数 | 说明 |
|---|---|
-u backup_user |
指定连接 MySQL 的用户名 |
-p |
提示输入密码（安全，适合交互式操作） |
-h 127.0.0.1 |
MySQL 服务器地址 |
-P 3306 |
MySQL 端口号 |
-B testdb |
备份数据库 testdb（等价于 --databases testdb） |
-d |
仅导出表结构（不包含数据）（⚠️ 关键点） |
--default-character-set=utf8mb4 |
使用 utf8mb4 字符集 |
--single-transaction |
使用一致性事务快照（适用于 InnoDB） |
--source-data=2 |
添加 CHANGE MASTER 语句，用于主从复制 |
--triggers |
导出触发器 |
--events |
导出事件调度器 |
--routines |
导出存储过程和函数 |
--set-gtid-purged=OFF |
不写入 GTID 信息 |
> /backup/mysql/dump4.sql |
输出到指定 SQL 文件 |
--no-data, -d: 不转储数据
我们看看
进去看看
发现只有上面那个关于表结构的信息啊。
2.6.9 只备份数据
只对备份数据，备份文件中不包含创建表的语句
mysqldump -ubackup_user -p -h127.0.0.1 -P3306 \
--databases testdb \
--hex-blob \
--no-create-db \
--no-create-info \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
--flush-logs \
> /backup/mysql/dump5.sql| 参数 | 说明 |
|---|---|
--databases testdb |
指定要备份的数据库（可加多个） |
--hex-blob |
将 BLOB 类型字段（如 BINARY, BLOB, BIT）以十六进制方式导出，避免乱码或二进制数据丢失 |
--no-create-db |
不导出 CREATE DATABASE 语句 |
--no-create-info |
不导出 CREATE TABLE 语句（只导出数据） |
--default-character-set=utf8mb4 |
使用 utf8mb4 字符集 |
--single-transaction |
使用一致性事务快照，适用于 InnoDB |
--source-data=2 |
添加 CHANGE MASTER 信息，用于主从复制（2 表示写入注释和语句） |
--triggers |
导出触发器 |
--events |
导出事件调度器 |
--routines |
导出存储过程和函数 |
--set-gtid-purged=OFF |
不写入 GTID 信息（适用于非 GTID 环境） |
--flush-logs |
备份前刷新二进制日志，便于后续做增量备份 |
> /backup/mysql/dump5.sql |
输出到指定 SQL 文件 |
- --hex-blob: 使用十六进制表示法转储二进制列
- --no-create-db: 不要生成 CREATE DATABASE 语句
- --no-create-info: 不要为每个转储的表生成 CREATE TABLE 语句
我们去看看
进去看看
里面只有关于插入数据这一点啊。
2.6.10 数据导入的3种方法
有三种方法
- 命令行导入
- 交互式导入
- 流式导入
我们来讲讲。
方式一：命令行导入
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/dump.sql这是一个输入重定向。
首先我们来看看testdb原来的样子
然后我们把testdb删除掉
接下来我们就恢复一下
# 方式一：在命令行通过mysql客户工具直接恢复
mysql -uroot -p < /backup/mysql/20250725_0.sql
我们回去mysql里看看
怎么样？很好吧
方式二：交互式导入
# 方式二：登录mysql客户端导入SQL文件
mysql -uroot -p
password:
mysql> source /backup/mysql/dump.sql
或者
mysql> /backup/mysql/20250725_0.sql看看例子
首先我们来看看testdb原来的样子
然后我们把testdb删除掉
接下来我们就恢复一下
source /backup/mysql/20250725_0.sql
或者
\. /backup/mysql/20250725_0.sql
很完美啊！！
方式三：流式导入
mysqldump -ubackup_user -p123456 -h127.0.0.1 -P3306 \
-B testdb \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
| mysql -uroot -p123456 -h117.72.80.239 -P3306管道符 | 的作用就是把管道符左边的输层作为管道符右边的输出
这条语句的意思就是将 mysqldump 从本地数据库导出的SQL脚本直接通过网络传输并导入到远程数据库服务器中。
我们来试试看
我这里有两个角色
- Windows端（本地端）
- Linux端（117.72.80.239远程端）
首先我们需要明白，我们是需要远程登陆117.72.80.239的服务器的。我们需要在远程Linux服务器上查询一下有没有支持远程访问的用户
虽然Linux远程服务器这里是有2个用户（binlogrep，federated）是支持远程登陆的，但是我们毕竟是在学东西，我们还是创建一个新用户来理解全过程
-- 创建用户
CREATE USER 'liushi'@'%' IDENTIFIED BY '123456';
-- 授予所有权限
GRANT all ON *.* TO 'liushi'@'%';
-- 刷新权限
FLUSH PRIVILEGES; 
很好，我们现在就尝试一下在Windows本地端远程登陆Linux远程服务器这个用户
mysql -h117.72.80.239 -P3306 -uliushi -p
没有任何问题啊！！！
我们看看Windows本地端的数据库啊。
我们也看看远程Linux的情况
接下来我就将执行流式导入
mysqldump -uroot -p123456 -h127.0.0.1 -P3306 \
-B windows \
--default-character-set=utf8mb4 \
--single-transaction \
--source-data=2 \
--triggers \
--events \
--routines \
--set-gtid-purged=OFF \
| mysql -h117.72.80.239 -P3306 -uliushi -p| 参数 | 说明 |
|---|---|
-ubackup_user -p123456 |
使用用户名 backup_user 和密码 123456 登录本地 MySQL |
-h127.0.0.1 -P3306 |
本地 MySQL 地址和端口 |
-B windows |
指定要备份的数据库 |
--default-character-set=utf8mb4 |
使用 utf8mb4 字符集 |
--single-transaction |
使用一致性事务快照导出（适用于 InnoDB） |
--source-data=2 |
添加 CHANGE MASTER 信息（用于复制） |
--triggers |
导出触发器 |
--events |
导出事件调度器 |
--routines |
导出存储过程和函数 |
--set-gtid-purged=OFF |
不写入 GTID 信息（适用于非 GTID 环境） |
mysqldump -uroot -p123456 -h127.0.0.1 -P3306 -B windows --default-character-set=utf8mb4 --single-transaction --source-data=2 --triggers --events --routines --set-gtid-purged=OFF | mysql -uliushi -p123456 -h117.72.80.239 -P3306
这样子就执行成功了。
我们去远程的Linux端看看
很完美了！！！
2.6.11 使用pv工具监控恢复进度
使用pv工具显示数据库导入的进度
# 安装pv工具
# Debian/Ubuntu 系统
sudo apt-get install pv
# Red Hat/CentOS 系统
sudo yum install pv我们安装一下看看
我们看看怎么使用这个pv工具啊
- 示例一
pv -L1000 /backup/mysql/dump6.sql | mysql -uroot -p123456 -h192.168.100.237 -P3306使用限速1000KB/s将SQL备份文件导入到IP为192.168.100.237的远程MySQL服务器
| 组件 | 作用 | 示例值说明 |
|---|---|---|
| pv -L1000 | 限速传输工具 | -L1000=限制1000KB/s(约1MB/s) |
- 示例二
pv -L1000 /backup/mysql/dump6.sql | mysql -uroot -p123456 -h127.0.0.1 -P3306使用限速1000KB/s将SQL备份文件导入到本地MySQL服务器
| 组件 | 作用 | 示例值说明 |
|---|---|---|
| pv -L1000 | 限速传输工具 | -L1000=限制1000KB/s(约1MB/s) |
多说无益，我们来看看例子
使用示例
我们把这个testdb删除了
然后我们执行下面这个
pv -L1000 /backup/mysql/20250725_0.sql | mysql -uroot -p123456 -h127.0.0.1 -P3306
它会有一个类似进度条的东西啊！！
我们回去数据看看
这很好了。
三. 备份方法二：SQL语句导出导入
3.1 介绍
MySQL中可以通过SQL语句把查询出来的数据导出到服务器的文件中，也可以从导出文件加载数据到指定的数据库和表，同时提供了一个导入工具方便在命令行中完成导入操作
- 导出: select..into outfile
- 导入: load data
- 导入工具：mysqlimport
3.2 特点
- 根据查询结果导出，定制性强，操作方便
- 高效读取文本数据到指定的表
- 可以灵活定制，适合异构迁移
3.3 使用场景
- 简单的数据备份和数据迁移
- 将数据导出到外部应用程序进行进一步处理
3.4 语法
导出
-- SELECT 语句的基本结构
SELECT
select_expr [, select_expr] ... -- 指定要查询的列或表达式，多个列之间用逗号分隔
FROM
table_references -- 指定查询的数据来源，可以是一个或多个表
-- INTO 子句的选项（用于将查询结果导出到文件或变量）
into_option: {
-- 将查询结果导出到一个文件中（适合大数据量）
INTO OUTFILE 'file_name' -- 指定导出文件的路径和名称
[CHARACTER SET charset_name] -- 可选参数，指定导出文件使用的字符集
-- 导出文件的格式选项（可选）
export_options:
[{FIELDS | COLUMNS} -- 指定字段或列的格式
[TERMINATED BY 'string'] -- 指定字段之间的分隔符（例如逗号）
[[OPTIONALLY] ENCLOSED BY 'char'] -- 指定字段值的包围符号（例如双引号）
[ESCAPED BY 'char'] -- 指定转义字符（例如反斜杠）
]
[LINES -- 指定行的格式
[STARTING BY 'string'] -- 指定每行的起始标记
[TERMINATED BY 'string'] -- 指定每行的结束标记（例如换行符）
]
| INTO DUMPFILE 'file_name' -- 将查询结果导出到一个文件中（适合小数据量，仅保存数据）
| INTO var_name [, var_name] ... -- 将查询结果存储到变量中（适用于单行查询）
}它的核心思想是：执行一个普通的 SELECT 查询，但不把结果返回给客户端（比如你的命令行工具或应用程序），而是将结果保存到其他地方。这个“其他地方”就是 INTO 子句指定的目标。
这个看起来有点复杂，不过没有关系，我帮大家简化 成下面这样子了
-- 简单示例
SELECT * FROM 数据库名.表名 into outfile '要导出的文件名';注意
- 该文件将在服务器主机上创建，
- 必须具有 file 权限才能使用此语法。
- file_name不能是一个已经存在的文件
我们看几个例子
示例 1：导出为 CSV 格式（最常用）
SELECT id, name, email
FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';说明：
将 users 表中的 id, name, email 字段导出到 /tmp/users.csv 文件。
-
字段用逗号
,分隔（TERMINATED BY ','） -
每个字段用双引号包围（
ENCLOSED BY '"'，防止内容含逗号导致错位） -
每行以换行符
\n结尾（LINES TERMINATED BY '\n'）
效果：生成标准 CSV 文件，可直接用 Excel 打开。
示例 2：导出为竖线分隔的文本
SELECT *
FROM orders
INTO OUTFILE '/tmp/orders.txt'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n';说明：
导出 orders 表所有数据到 /tmp/orders.txt。
-
字段用竖线
|分隔（TERMINATED BY '|'） -
每行以 Windows 换行符
\r\n结尾（兼容记事本）
效果：生成管道符分隔的文本文件，适用于数据库迁移或日志。
示例 3：导出带转义符的数据
SELECT product_name, price
FROM products
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\''
ESCAPED BY '\\'
LINES TERMINATED BY '\n';说明：
导出产品名称和价格到 /tmp/products.csv。
-
字段用分号
;分隔 -
文本字段用单引号
'包围（OPTIONALLY ENCLOSED BY表示仅包围字符串类型） -
特殊字符用反斜杠
\转义（如O\'Reilly）
效果：安全处理含特殊符号（如单引号）的数据。
导入的第一种方法—— LOAD DATA INFILE语句
-- LOAD DATA INFILE：从文件中加载数据到数据库表中
LOAD DATA
[LOW_PRIORITY | CONCURRENT] -- 可选：控制并发行为（LOW_PRIORITY：低优先级；CONCURRENT：并发加载）
[LOCAL] -- 可选：如果指定 LOCAL，表示文件在客户端机器上；否则文件应在服务器上
INFILE 'file_name' -- 指定要加载的数据文件路径和文件名
[REPLACE | IGNORE] -- 可选：处理冲突数据的方式
-- REPLACE：替换已有记录；IGNORE：忽略冲突记录（如主键/唯一键冲突）
INTO TABLE tbl_name -- 指定目标表名，数据将被导入到这个表中
[CHARACTER SET charset_name] -- 可选：指定文件的字符集（如 utf8mb4）
-- 定义字段（列）的格式
[[FIELDS | COLUMNS] -- FIELDS 和 COLUMNS 是等价的，用于定义字段的格式
[TERMINATED BY 'string'] -- 字段之间的分隔符，如逗号 ','、制表符 '\t'
[[OPTIONALLY] ENCLOSED BY 'char'] -- 字段值是否被包围，如双引号 " 或单引号 '，OPTIONALLY 表示可选
[ESCAPED BY 'char'] -- 转义字符，如反斜杠 '\'
]
-- 定义行的格式
[LINES
[STARTING BY 'string'] -- 每行的起始标识符（可选）
[TERMINATED BY 'string'] -- 行结束符，如换行 '\n'
]
-- 忽略开头的若干行（常用于跳过文件头）
[IGNORE number [LINES | ROWS]] -- 忽略文件中的前 number 行（或行数）
-- 指定列的映射关系（可选）
[(col_name_or_user_var -- 可以将文件中的列对应到表字段，或临时变量
[, col_name_or_user_var] ...)] -- 多个字段或变量用逗号分隔上面那个看起来很复杂，没关系，我们记住下面这个就好了
-- 简单示例
LOAD DATA infile '之前导出的文件名' into table 要导入的表名;常用关键字
| 关键字 | 描述 |
|---|---|
| LOW_PRIORITY | LOAD DATA语句的执行可能会被阻塞，直到没有其他客户端读取表中的数据为止。只对只支持表级锁的存储引擎有效，比如MyISAM |
| CONCURRENT | 满足并发插入条件的MyISAM表（即表中不包含空闲块），其他线程可以在执行LOAD DATA时从表中检索数据，会对性能有影响 |
| REPLACE | 当插入的数据行与表中现有唯一字段重复时，新行将替换现有行 |
| IGNORE | 当插入的数据行与表中现有唯一字段重复时则忽略 |
| LOCAL | 指定该关键字表示文件位于客户端的机器上，文件由客户端读取并通过网络发送到MySQL服务器；不指定则表示文件在MySQL服务器，由服务器读取 |
| CHARACTER SET | 指定字符集，如果不指定则使用 character_set_database 变量对应的字符集 |
| FIELDS TERMINATED BY | 字段的分隔符，默认为 '\t' |
| FIELDS ENCLOSED BY | 字段的包裹符，默认为 ' ' |
| FIELDS ESCAPED BY | 字段值的转义字符，默认为 '\\' |
| IGNORE | 忽略开头N行 |
| LINES TERMINATED BY | 行分隔符，默认为 '\n' |
| LINES STARTING BY | 忽略指定的前缀，若指定该值为'xxx'，则MySQL会自动去掉xxx及其前面的字符，若不指定默认为 ' '，如下所示 |
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY '\n' LINES STARTING BY 'xxx';
something xxx"def",2 --> "def",2
xxx"abc",1 --> "abc",1
"ghi",3 --> "ghi",3一般而言，我们都使用默认的选项。不必了解太多
导入的第二种方法——mysqlimport
mysqlimport是 LOAD DATA INFILE SQL语句的工具化，由 SELECT INTO OUTFILE 导出的文件，也可以通过 mysqlimport 导入。
这个可以在命令行里面执行，不必在mysql客户端使用。
使用方法
mysqlimport -uroot -pPassword [--local] dbname filename.txt [OPTION]常用选项
| 选项 | 说明 |
|---|---|
-local |
从客户端本地文件系统加载文件（即指定路径的文件位于客户端机器上） |
--fields-terminated-by |
指定字段（列）之间的分隔符，默认为 \t（制表符） |
--fields-optionally-enclosed-by |
指定字段值的可选包裹符号，如双引号 " 或单引号 ' |
--fields-escaped-by |
指定字段值中的转义字符，默认为 \（反斜杠） |
--lines-terminated-by |
指定每一行的结束符，默认为 \n（换行符） |
--ignore-lines |
忽略文件开头的前 N 行，常用于跳过标题行 |
3.5.示例
创建原始数据
use testb;
CREATE TABLE student (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID，主键',
name VARCHAR(50) NOT NULL COMMENT '学生姓名',
gender ENUM('男', '女') NOT NULL COMMENT '性别',
age INT NOT NULL COMMENT '年龄',
email VARCHAR(100) COMMENT '邮箱',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT='学生信息表';
INSERT INTO student (name, gender, age, email) VALUES
('张三', '男', 20, 'zhangsan@example.com'),
('李四', '女', 22, 'lisi@example.com'),
('王五', '男', 21, 'wangwu@example.com'),
('赵六', '女', 19, 'zhaoliu@example.com'),
('孙七', '男', 23, 'sunqi@example.com');
SELECT * FROM student; 
执行导出操作
MySQL在系统中的权限不允许导出到任意目录，它只能导出到指定目录
-- 查看全局只读参数secure_file_priv的值(允许导出的目录)
show variables like 'secure_file_priv';
-- 导出到授权的路径下
select * from student into outfile '/var/lib/mysql-files/student.txt';
查看导出结果
-- 查看导出结果
cat /var/lib/mysql-files/student.txt
字段之间默认使用\t进行分割。
如果我们导出到非授权目录下，这会报错！！
-- 导出到非授权目录下会报错
select * from student into outfile '/backup/mysql/student.txt';
指定任意路径时导出失败，是由于MySQL不允许随意导出到系统中的任意路径，可以通过在选项文件中为--secure-file-priv选项指定一个目录
执行导入操作
-- 截断现有的student表
truncate student;
-- 查询现有表为空
select * from student;
-- 导入
load data infile '/var/lib/mysql-files/student.txt' into table student;
-- 查询导入成功
select * from student; -- 导入
load data infile '/var/lib/mysql-files/student.txt' into table student;里面这个 student表必须和我们导出的表的结构相同
在导入导出时设置字段顺序
导出
-- 导出时指定列的顺序
select id, email, gender from student into outfile '/var/lib/mysql-files/student1.txt';
查看导出结果
cat /var/lib/mysql-files/student1.txt
导入
-- 截断表
truncate student;
-- 查看表
select * from student;
-- 导入时指定列的顺序
load data infile '/var/lib/mysql-files/student1.txt' into table student (id, email, gender);
-- 查看结果，成功
select * from student;查看导入结果
使用工具mysqlimport来进行导入
接下来我们看看使用工具mysqlimport来进行导入
-- 截断表
truncate student;
-- 查看表
select * from student;
# 在命令行中进行导入
mysqlimport -uroot -p123456 testdb /var/lib/mysql-files/student.txt
-- 查看结果，成功
select * from student; 
mysqlimport工具在导入的时候，以文件名作为表名。
我们可以看看
-- 截断表
truncate student;
-- 查看表
select * from student;
# 在命令行中进行导入
mysqlimport -uroot -p123456 testdb /var/lib/mysql-files/student1.txt
四. 备份方法三：物理备份工具-Xtrabackup
4.1 介绍
Xtrabackup是由Percona公司开发的一款用于MySQL数据库的物理热备份工具，开源免费。
它是MySQL社区唯一一款开源物理热备工具，深受用户喜爱，是MySQL开源社区的主流备份工具之一。
4.2 特点
- 备份类型：支持全量备份和增量备份。
- 快速可靠：使用物理备份，备份速度快且可靠，同时会对备份的数据进行自动校验，确保备份数据的完整性，恢复过程相对简单。
- 性能影响小：在备份过程中，Xtrabackup对数据库的性能影响较小，不会增加太多的性能压力。
- 压缩和加密：支持备份压缩和加密，可以节约磁盘空间和网络带宽，同时会对备份的数据进行自动校验，确保备份数据的完整性。
- 支持多种数据库：Xtrabackup 支持 MySQL、Percona Server 和 MariaDB，是目前较为受欢迎的主流备份工具。
4.3 应用场景
- 大型数据库备份：适合处理大型数据库的备份需求，可以节省存储空间和备份时间。
- 高可用环境：可以在不影响数据库服务的情况下进行在线热备份，适用于高可用环境的备份需求。
- 全量和增量备份：需要定期进行增量备份以节省存储空间和备份时间的场景
- 全量和增量恢复：可以进行全量恢复和增量恢复，甚至可以进行部分恢复和时间点恢复。
- 非阻塞备份：XtraBackup 支持对 InnoDB 和 XtraDB 存储引擎的数据库进行非阻塞备份，这对于要求不影响线上服务的数据备份和恢复场景非常适合。
- Xtrabackup常用于日常备份及快速构建主从。
4.4 官方网站
官方网站：Percona XtraBackup for MySQL - Top MySQL Backup Solution
4.5 下载安装
版本支持
XtraBackup 支持 MySQL 5.1至 8.0版本的备份和恢复。
不同版本的 XtraBackup 对 MySQL 版本的支持情况如下：
- XtraBackup 2.4版本：可以备份和恢复 MySQL 5.1、5.5、5.6和 5.7版本的数据库，但不支持 MySQL 8.0及以上版本。
- XtraBackup 8.0版本：专门用于备份和恢复 MySQL 8.0版本的数据库。需要注意的是，xtrabackup 8.0.12 以前版本不支持 MySQL 8.0.20 及以后版本的备份，因此如果需要备份更新版本的 MySQL 8.0，建议使用更新的 XtraBackup 8.0版本。
安装过程
查看系统版本
查看 MySQL 版本
select version();
接下来我们就知道了我们必须安装Xtrabackup 8.0.12之后的版本
我们去官网：Percona XtraBackup for MySQL - Top MySQL Backup Solution
点进去之后我们往下翻，找到下面这个
然后根据自己的CPU架构下载对应的.deb文件，可以通过uname -m 命令查看，x86_64则下载
amd64.deb
下载速度其实是有点慢的
下载完成之后，我们需要将其把.deb文件上传到我们的服务器里面去
首先我们需要切换成root用户，我们看看我们的家目录
我们可以直接将我们下载好的文件通过rz命令来进行上传到我们的Linux服务器里面啊。
# 安装软件源
dpkg -i percona-xtrabackup-80_8.0.35-33-1.focal_amd64.deb
我们发现安装时会有一些错误，这些是因为没有一些依赖导致的。现在我们来安装一下这些依赖
# 如果提示缺少依赖运行以下命令安装
apt-get install -f 
然后我们重新执行下面这个
# 安装软件源
dpkg -i percona-xtrabackup-80_8.0.35-33-1.focal_amd64.deb
# 更新源
apt update 
# 安装xtrabackup
apt install percona-xtrabackup-80
# 验证是否成功
xtrabackup --version
我们就安装好了
# 如果之前修改过MySQL的配置文件会有以下提示，保留当前的配置选择N
Configuration file '/etc/mysql/mysql.cnf'
==> Modified (by you or by a script) since installation.
==> Package distributor has shipped an updated version.
What would you like to do about it ? Your options are:
Y or I : install the package maintainer's version
N or O : keep your currently-installed version
D : show the differences between the versions
Z : start a shell to examine the situation
The default action is to keep your current version.
** mysql.cnf (Y/I/N/O/D/Z) [default=N] ? N
apt update
# 重新安装xtrabackup
apt install percona-xtrabackup-80
# 验证是否成功
xtrabackup --version
2024-11-27T18:32:13.374176+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/binlog
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)4.6.Xtrabackup常用选项
| 类别 | 选项 | 说明 | 常用值/示例 |
|---|---|---|---|
| 备份操作 | --backup |
执行备份操作 | 必须指定 |
--target-dir |
备份存储目录 | /path/to/backup |
|
| 连接认证 | --user |
MySQL 用户名 | backup_user |
--password |
MySQL 密码 | 'YourPass123!' |
|
--host |
MySQL 主机地址 | 10.0.0.100 |
|
--port |
MySQL 端口号 | 3306 |
|
| 性能优化 | --parallel |
并行线程数（备份/恢复） | 4 (根据 CPU 核心数) |
--compress |
压缩算法 | zstd (推荐), lz4, quicklz |
|
--compress-threads |
压缩线程数 | 2-4 |
|
--throttle |
限制 IOPS（每秒 I/O 操作次数） | 100 (生产环境限流) |
|
| 增量备份 | --incremental |
执行增量备份 | 需配合 --backup 使用 |
--incremental-basedir |
基础备份目录 | /backups/full |
|
--incremental-lsn |
指定起始 LSN 位置 | 12345678 |
|
| 恢复操作 | --prepare |
准备备份文件 | --target-dir=/backups/full |
--apply-log-only |
仅应用日志（增量备份准备） | 用于增量链中间步骤 | |
--copy-back |
复制备份到数据目录 | 需停止 MySQL 服务 | |
--move-back |
移动备份文件（更快速） | 会删除源备份文件 | |
| 高级配置 | --defaults-file |
指定配置文件路径 | /etc/mysql/my.cnf |
--slave-info |
备份从库复制信息 | 用于搭建新从库 | |
--encrypt |
加密算法 | AES256, AES128 |
|
--encrypt-key |
加密密钥 | "MySecretKey123" |
|
| 特殊处理 | --use-memory |
准备阶段内存分配 | 2G (默认 100MB) |
--export |
导出表空间（单表恢复） | 配合 --prepare 使用 |
|
--force-non-empty-directories |
强制覆盖非空目录 | 恢复时使用 | |
| 诊断信息 | --version |
显示版本信息 | xtrabackup --version |
--stats |
显示文件统计信息 | 备份前检查 | |
--generate-xb-log |
生成详细日志 | 调试使用 |
4.7 全量备份示例
接下来我们将使用Xtrabackup进行全量备份。
本次示例是对数据目录进行物理拷贝，把数据目录的所有内容复制到备份目录。
我们需要确认以下事项
- 选项文件（配置文件）：需要确定数据目录
- 确定备份目录
我们先去选项文件里面看看我们配置了数据目录吗？
vim /etc/mysql/my.cnf
进去仔细一看，好像我们没有配置数据目录，但是我们不要忘记了这两个
我们可以进去看看
vim /etc/mysql/conf.d/mysql.cnf
这里什么都没有。我们去另外一个地方看看
vim /etc/mysql/mysql.conf.d/mysqld.cnf
这里有datadir看到了吗？就是/var/lib/mysql，这个就是我们的数据目录
好了，我们现在就能来备份我们的数据目录了
xtrabackup \
--defaults-file=/etc/mysql/my.cnf \ # 指定 MySQL 的配置文件路径，用于读取默认参数
--host=localhost \ # 指定 MySQL 数据库的主机地址（这里是本地）
--port=3306 \ # 指定 MySQL 的端口号（默认为 3306）
--user=backup_user \ # 指定连接 MySQL 的用户名（需要有备份权限）
--password=123456 \ # 指定用户的密码（注意：密码直接写在命令行中存在安全风险）
--use-memory=16 \ # 指定 xtrabackup 使用的内存大小（单位是 GB），用于加速备份过程
--parallel=2 \ # 指定并行线程数为 2，用于并行复制数据文件，提高备份速度
--backup \ # 表示执行一次备份操作（这是执行物理备份的关键参数）
--target-dir=/backup/mysql/full # 指定备份文件保存的目标目录，备份数据将写入该路径注意： 我们需要提前创建/backup/mysql/full
mkdir -p /backup/mysql/full 
我们在命令行里面执行下面这个
xtrabackup \
--defaults-file=/etc/mysql/my.cnf \
--host=localhost \
--port=3306 \
--user=backup_user \
--password=123456 \
--use-memory=16G \
--parallel=2 \
--backup \
--target-dir=/backup/mysql/full等待完成
进入备份目录查看结果
备份目录下生成的主要文件
- xtrabackup_binlog_info：记录备份时binlog日志和POS信息
- xtrabackup_checkpoints：备份类型和备份LSN信息,增量备份时依赖文件中的LSN
- xtrabackup_info：备份信息
恢复
确保 /etc/mysql/my.cnf 已经在配置文件里面配置好
恢复之前可以把原来数据库的数据目录删除，再创建一个同名的空目录
systemctl stop mysql
# 查看MySQL服务是否停止
systemctl status mysql
#移动或删除原来的数据目录
mv /var/lib/mysql /var/lib/mysql-old
# 创建数据目录同名的空目录
mkdir -p /var/lib/mysql
ls -l /var/lib/mysql
接下来来到至关重要的两步
- 数据准备
- 恢复
我们先看数据准备
# 准备
xtrabackup --prepare --target-dir=/backup/mysql/full作用：将原始备份文件转换成可恢复的数据库状态
核心过程：
-
应用重做日志：把备份期间未提交的事务日志（redo log）应用到数据文件
-
回滚未提交事务：撤销所有未完成的事务操作
-
同步检查点：确保所有数据文件处于一致的时间点
类比：相当于把相机拍摄的"RAW格式照片"转换成可用的"JPG图片"
关键要点：
-
必须执行此操作后才能用于恢复
-
完成后备份目录会产生
xtrabackup_checkpoints文件 -
输出日志最后应有
completed OK!提示
接着我们看恢复
# 恢复数据
xtrabackup --defaults-file=/etc/mysql/my.cnf \
--copy-back --parallel=2 \
--target-dir=/backup/mysql/full作用：将准备好的备份完整复制到MySQL数据目录
核心过程：
-
自动读取
my.cnf中的datadir配置（如/var/lib/mysql） -
使用2个线程(
--parallel=2)并行复制文件 -
重建原始目录结构（包括系统表空间、用户表空间等）
关键要点：
-
必须停止MySQL服务后才能执行
-
目标数据目录必须为空（建议先清空或迁移旧数据）
-
--defaults-file确保恢复路径与原始配置一致
完成后上面这步之后必须手动设置文件权限：
# 为恢复目录授权
chown -R mysql:mysql /var/lib/mysql
# 启动MySQL服务
systemctl start mysql
# 查看MySQL服务
systemctl status mysql
登录数据库进行验证
此时数据已经恢复到上次全备的状态
4.8.增量备份示例
Xtrabackup支持增量备份，备份时只复制自上次备份之后更改的所有数据。
在每次完全备份之间可以执行多次增量备份，例如每周进行一次完全备份，每天进行一次增量备份，或者每天进行一次完全备份，每小时进行一次增量备份。
InnoDB存储引擎中的每个数据页都包含一个日志序列号（LSN），增量备份是通过查找上次备份之后的LSN对应的所有数据页，并对这些数据页进行增备。
接下来我们来增量备份需要先进行一次完全备份，全备的过程与之前的一样。
注意： 我们需要提前创建/backup/mysql/full
mkdir -p /backup/mysql/full2 
我们在命令行里面执行下面这个
xtrabackup \
--defaults-file=/etc/mysql/my.cnf \
--host=localhost \
--port=3306 \
--user=backup_user \
--password=123456 \
--use-memory=16G \
--parallel=2 \
--backup \
--target-dir=/backup/mysql/full2
查看检查点信息
修改数据库
-- 查询
select * from student;
-- 插入第 9 位学生
INSERT INTO student (name, sno, age, gender, enroll_date, class_id)
VALUES ('林黛玉', 200005, 17, 2, '2001-09-01', 2);
-- 查询
select * from student;
第一次增量备份
# 为当前增量备份指定保存备份的目录，以及全量备份的目录
# 使用 xtrabackup 执行一次 **增量备份**
# 增量备份基于之前的全量备份（full），只备份从那之后发生更改的数据
xtrabackup \
--host=localhost \ # 指定 MySQL 主机地址（本地）
--port=3306 \ # 指定 MySQL 端口号（默认为 3306）
--user=backup_user \ # 指定用于连接 MySQL 的用户名
--password=123456 \ # 指定用户的密码（注意：明文密码存在安全风险）
--backup \ # 表示执行一次备份操作（备份模式）
--target-dir=/backup/mysql/inc1 \ # 指定本次增量备份保存的目录（第一个增量备份）
--incremental-basedir=/backup/mysql/full2 # 指定增量备份的基准目录（即全量备份的目录）关键选项：
- --target-dir：目标目录是专门保存第一次增量备份的目录
- --increamental-basedir：基于哪一次备份进行的增量备份，目前这个命令是基于全量备份的
我们在命令行执行下面这个
xtrabackup --host=localhost --port=3306 --user=backup_user --password=123456 --backup --target-dir=/backup/mysql/inc1 --incremental-basedir=/backup/mysql/full2
# 查看检查点信息
cat /backup/mysql/inc1/xtrabackup_checkpoints
这个时候我们再去修改一下我们的数据
修改数据库
-- 创建新表
create table city (id bigint primary key auto_increment, name varchar(20));
-- 写入数据
insert into city (name) values ('北京'), ('上海'), ('深圳'), ('西安'), ('成都');
-- 查询结果
select * from city;
接下来我们将进行第二次增量备份
# 为当前增量备份指定保存备份的目录，以及上一次增备的目录
# 使用 xtrabackup 执行一次增量备份
# 本次增量备份基于上一次的增量备份（inc1），继续备份自上次以来更改的数据
xtrabackup \
--host=localhost \ # 指定 MySQL 主机地址（本地）
--port=3306 \ # 指定 MySQL 端口号（默认为 3306）
--user=backup_user \ # 指定用于连接 MySQL 的用户名
--password=123456 \ # 指定用户的密码（注意：明文密码存在安全风险）
--backup \ # 表示执行一次备份操作（备份模式）
--target-dir=/backup/mysql/inc2 \ # 指定本次增量备份保存的目录（第二个增量备份）
--incremental-basedir=/backup/mysql/inc1 # 指定增量备份的基准目录（即上一次增量备份的目录）关键选项：
- --target-dir：目标目录是专门保存第二次增量备份的目录
- --increamental-basedir：这个是指定基于哪一次备份进行的增量备份，目前这个命令是基于上一次增量备份的
xtrabackup --host=localhost --port=3306 --user=backup_user --password=123456 --backup --target-dir=/backup/mysql/inc2 --incremental-basedir=/backup/mysql/inc1
查看检查点信息
cat /backup/mysql/inc2/xtrabackup_checkpoints
我们仔细对比一下三次备份的情况
恢复
确保 /etc/mysql/my.cnf 已配置好
恢复之前可以把原来数据库的数据目录删掉，再创建一个同名的空目录
# 停止MySQL服务
systemctl stop mysql
# 查看MySQL服务是否停止
systemctl status mysql
# 移动或删除原来的数据目录
mv /var/lib/mysql /var/lib/mysql-old
# 创建数据目录同名的空目录
mkdir -p /var/lib/mysql
ls -l /var/lib/mysql 
接下来来到至关重要的两步
- 数据准备
- 恢复
数据准备
我们先看数据准备，增量恢复准备需要加 --apply-log-only 选项，增量恢复是通过 RedoLog进行重放事务完成数据恢复，这个选项用来跳过备份时还没有提交的事务；
注意：最后一次增量在准备时不需要使用该选项。
有以下三个目录需要进行恢复准备
- 全量备份目录：/backup/mysql/full2
- 第一次增量备份目录：/backup/mysql/inc1
- 第二次增量备份目录：/backup/mysql/inc2
从全量备份开始依次执行 prepare 操作
# ========================
# 步骤 1：准备全量备份
# 仅应用重做日志（redo log），不回滚未提交事务
# --apply-log-only 必须用于除最后一次外的所有 prepare 步骤
# 保证全量备份处于“可接受增量”的状态
# ========================
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full2
# ========================
# 步骤 2：合并第一次增量备份 (inc1)
# 将 inc1 的更改应用到全量备份中
# 注意：--incremental-dir 指向 inc1 目录
# 仍使用 --apply-log-only，因为还有后续增量
# ========================
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full2 \
--incremental-dir=/backup/mysql/inc1
# ========================
# 步骤 3：合并第二次增量备份 (inc2)
# 将 inc2 的更改应用到已合并 inc1 的全量备份中
# 这是最后一个增量，因此**不加** --apply-log-only
# 此步会执行回滚未提交事务等最终准备操作
# 完成后，full2 目录即可用于恢复
# ========================
xtrabackup --prepare --target-dir=/backup/mysql/full2 \
--incremental-dir=/backup/mysql/inc2大家复制下面这个即可
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full2
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full2 --incremental-dir=/backup/mysql/inc1
xtrabackup --prepare --target-dir=/backup/mysql/full2 --incremental-dir=/backup/mysql/inc2 
恢复数据
接下来我们只需要执行下面这一句即可恢复数据
# 恢复数据
xtrabackup --copy-back --parallel=2 --target-dir=/backup/mysql/full2
为什么只需这一条命令就能恢复数据？
因为在这之前，你已经完成了 Prepare（预处理）阶段，即：
- 全量备份的准备（apply log）
- 所有增量备份的合并（apply incremental）
这些操作已经将备份目录中的数据恢复到一个一致性状态（就像数据库刚刚正常关闭时的状态），所以只需要：将一致性数据复制到 MySQL 的数据目录中即可完成恢复。
接下来这一步非常关键，完成后上面这步之后必须手动设置文件权限，要不然MySQL启动不了！！！
# 为恢复目录授权
chown -R mysql:mysql /var/lib/mysql
# 启动MySQL服务
systemctl start mysql
# 查看MySQL服务
systemctl status mysql
接下来我们登陆数据库看看有没有恢复成功啊
一站式 AI 云服务平台
更多推荐
20
0- 0
已为社区贡献3条内容
所有评论(0)