Mysql高级篇（下）——数据库备份与恢复

一、物理备份与逻辑备份

1、物理备份

物理备份 是对数据库的物理文件进行备份，主要包括数据文件、日志文件、表空间等。它 不涉及数据 的具体内容，只是对数据库的底层存储文件进行复制。

特点：

• 速度快：物理备份直接复制数据库文件，相对于逻辑备份，它通常执行得更快，特别是对大数据库。
• 一致性强：在正确的配置下（如使用锁定机制或结合事务日志），物理备份能够保证数据的完整性和一致性。
• 备份大小较小：物理备份通常不需要额外的空间来存储表结构，因此备份文件相对较小。
• 还原速度快：还原时直接复制文件，不需要解析和重建数据，因此还原速度也较快。

常用工具：

• XtraBackup：开源的物理备份工具，支持热备份，不会锁表，适用于MySQL和MariaDB。
• MySQL Enterprise Backup：MySQL官方的企业级物理备份工具，提供更多企业级功能。

使用场景：

• 适用于大规模数据库或需要快速备份和还原的场景。
• 需要保证备份数据的一致性时。

---

2、逻辑备份

逻辑备份 是将数据库中的结构和数据以SQL脚本或其他格式导出，导出的文件包含SQL语句，如CREATE、 TABLE和INSERT等。

特点：

• 备份灵活：可以选择备份某些表或某些数据库，灵活性较高。
• 跨平台性强：逻辑备份导出的文件是SQL语句，可以方便地在不同版本或不同架构的MySQL实例间迁移。
• 备份文件较大：因为包含了大量的SQL语句，逻辑备份生成的文件通常比物理备份大。
• 速度较慢：备份和还原时需要处理大量的SQL语句，速度相对较慢，尤其是数据量较大的情况下。
• 易于编辑：备份后的文件是文本文件，可以方便地进行修改。

常用工具：

• mysqldump：MySQL内置的逻辑备份工具，能导出表结构和数据。
• mysqlpump：MySQL官方提供的更高效的逻辑备份工具，支持并发导出。

使用场景：

• 小规模数据库或不需要频繁备份的大型数据库。
• 跨平台、跨版本的数据库迁移场景。
• 备份某些特定的表或数据库时。

---

3、对比

特性	物理备份	逻辑备份
备份速度	快	慢
还原速度	快	慢
备份大小	较小	较大
灵活性	较低	较高
数据一致性	强	需要额外配置
备份文件格式	数据文件	SQL文件或文本文件
适用场景	大型数据库、频繁备份需求	小型数据库、跨平台迁移场景

4、总结

• 物理备份 更适合大型生产环境，特别是在需要快速备份和恢复的场景中，如企业级部署中。
• 逻辑备份 则适用于小规模数据库，或需要在不同环境间进行迁移的场景。

二、mysqldump实现逻辑备份

1、mysqldump 常用选项

mysqldump 是一个强大的 MySQL 备份工具，它提供了很多选项来定制备份操作。以下是一些常用的 mysqldump 选项及其作用：

---

2、mysqldump 逻辑备份语法

mysqldump 是一个灵活的 MySQL 备份工具，可以通过不同的选项来备份整个数据库、多个数据库、部分表或只备份特定表的部分数据。下面我将详细介绍如何使用 mysqldump 备份数据库的不同部分，给出每种情况的完整命令示例。

（1）备份一个数据库

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 待备份数据库名 > 备份文件名.sql

• 备份文件后缀并非必须以.sql结尾，后缀名为.txt也是可以的

---

（2）备份部分数据库（多个数据库）

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --databases  db1 db2 db3 > 备份文件名.sql

---

（3）备份全部数据库

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --all-databases > 备份文件名.sql

---

（4）备份部分表

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 db tb1 tb2 > 备份文件名.sql

• 备份数据库 db 中的表 tb1 和 tb2

---

（5）备份单表的部分数据

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 db tb --where="id < 100" > 备份文件名.sql

• 使用 --where 选项可以指定 WHERE 条件，只导出符合条件的数据。例如，备份 tb 表中 id 小于 100 的数据：

---

（6）排除某些表的备份

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --ignore-table=db.tb db > 备份文件名.sql

• 如果想备份整个数据库，但排除特定的表，可以使用 --ignore-table 选项。例如，要备份 db，但排除 db 中的 tb 表：

---

（7）只备份表结构（不包括数据）

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --no-data db > 备份文件名.sql

• 使用 --no-data 选项，只备份表结构，而不备份表数据。例如，要备份 db 的表结构：

---

（8）只备份数据（不包括表结构）

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --no-create-info db > 备份文件名.sql

• 使用 --no-create-info 选项，只备份表数据，而不备份 CREATE TABLE 语句。例如，要备份 db 的表数据：

---

---

（9）备份带有存储过程、触发器和事件的数据库

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --routines --triggers --events db > 备份文件名.sql

• --routines：包含存储过程和函数。
• --triggers：包含表上的触发器。
• --events：包含事件调度器。

---

（10）使用 --single-transaction 选项备份

如果你正在备份的是一个 支持事务 的 InnoDB 数据库，并且希望在备份时不锁定表（减少对运行中的应用的影响），可以使用 --single-transaction 选项：

--single-transaction 会在备份时启动一个事务，从而确保备份数据的一致性而不需要锁表。

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 --single-transaction db > 备份文件名.sql

---

（11）压缩备份文件

如果希望 备份文件体积更小，可以直接将输出通过 gzip 压缩,这样备份文件将会生成压缩文件，体积较小，便于存储和传输。：

mysqldump -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】  db | gzip > 备份文件名.sql.gz

---

三、mysql命令恢复数据

在 MySQL 中恢复数据通常使用的是 mysql 命令来执行之前使用 mysqldump 导出的 SQL 文件。

（1）单库备份中恢复单库

如果备份文件中 包含创建数据库的语句，则恢复的时候 不需要 指定数据库的名称，否则 要指定数据库名称

# 不指定数据库名称
mysql -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 < 备份文件名.sql

# 指定数据库名称 db

mysql -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 db < 备份文件名.sql

---

（2）全量备份恢复

如果使用了 --all-databases参数备份了所有数据库，那么恢复时不需要指定数据库

# 不指定数据库名称
mysql -u 用户名 【-h 主机名 -P 端口号】  -p 【密码】 < 备份文件名.sql

---

（3）从全量备份中恢复单库

从全量备份文件（即备份了多个数据库的 SQL 文件）中恢复指定的数据库

• 方法一：使用 sed 提取指定数据库的内容

1. 检查备份文件：首先，你可以检查备份文件，确认它是否包含多个数据库的内容，并确认你要恢复的数据库的名称。

head -n 50 all_databases_backup.sql

2. 提取指定数据库： 使用 sed 提取指定数据库的内容。例如，假设备份文件中数据库的名称是 mydatabase，可以用以下命令提取该数据库的 SQL 语句到单独的文件中：

sed -n '/^-- Current Database: `mydatabase`/,/^-- Current Database: `/p' all_databases_backup.sql > mydatabase_backup.sql

• 这条命令的作用是从备份文件 all_databases_backup.sql 中找到标识数据库 mydatabase 的部分，并将其提取到 mydatabase_backup.sql 文件中。

3. 恢复提取后的数据库： 提取完成后，可以像正常备份文件一样恢复该数据库：

mysql -u root -p mydatabase < mydatabase_backup.sql

---

• 方法二：使用 mysql 客户端中的 --one-database 选项

MySQL 提供了 --one-database 选项，可以 直接 从包含多个数据库的备份文件中恢复指定的数据库。这是更为直接的方法。

mysql -u root -p --one-database mydatabase < all_databases_backup.sql

---

（4）从单量备份中恢复单表

1. 提取表结构：使用 sed 从备份文件中提取 table 表的创建语句。
2. 提取表数据：使用 grep 提取 table 表的所有插入数据的语句。
3. 恢复表结构和数据：在 MySQL 中先执行 table_structure.sql 来创建表，然后执行 table_data.sql 来插入数据。

# 1. 提取 table 表的表结构
cat db.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE class/!d;q' > table_structure.sql  


# 2. 提取 table 表的数据
cat db.sql | grep --ignore-case 'insert into table' > table_data.sql 


# 3. 恢复表结构和数据
use db;

mysql> source table_structure.sql;

mysql> source table_data.sql;

命令解析：

---

四、（了解）物理备份：直接复制整个数据库

物理备份是指 直接复制数据库文件和相关数据存储文件，作为一种备份策略，它与逻辑备份（导出SQL语句等）有所不同。物理备份通过操作系统级别的文件操作，复制数据库数据文件、日志文件和配置文件，能更快地备份和恢复数据，因为它避免了数据库引擎对数据的解析。

---

---

示例：

你在一台 Linux 服务器上运行 MySQL，数据库的目录位于 /var/lib/mysql/，你需要对数据库进行备份，同时保证数据库在备份时的一致性。

备份步骤举例：

1、找到 MySQL 数据库文件目录
首先，你确认数据库的存放路径是 /var/lib/mysql/。

2、执行 FLUSH TABLES WITH READ LOCK
假设你的数据库正在运行，无法停止 MySQL 服务。你可以选择使用 FLUSH TABLES WITH READ LOCK 命令来锁定表，以防止其他客户端在备份时修改数据。

登录 MySQL 客户端：
mysql -u root -p

输入密码后，执行命令：
FLUSH TABLES WITH READ LOCK;
此命令会将所有表的写入操作锁定，并把活跃的索引数据写入磁盘。

3、备份数据库文件
在一个新终端中（因为 FLUSH TABLES WITH READ LOCK 会锁定当前会话，不能关闭），使用 cp 命令复制整个数据库目录：
cp -r /var/lib/mysql /path/to/backup/
这一步是将数据库文件复制到备份位置。需要确保目标位置有足够的存储空间。


4、解锁表
文件备份完成后，回到之前的 MySQL 会话中，执行 UNLOCK TABLES 命令解锁表：
UNLOCK TABLES;
这一步会允许其他客户端继续对数据库表进行写操作。

5、验证备份是否成功
你可以通过检查 /path/to/backup/ 目录下的文件，确保所有数据文件已经被完整地复制。




恢复步骤（可选）：

在需要恢复备份时，可以将备份的文件复制回原始的数据库目录，或在新服务器上安装同样版本的 MySQL，并将备份的文件复制到新的数据库目录下。

恢复的步骤为：
cp -r /path/to/backup/mysql /var/lib/mysql/
确保文件权限和用户与 MySQL 服务匹配，然后重新启动 MySQL 服务。





总结：
通过 FLUSH TABLES WITH READ LOCK 确保数据在备份时保持一致性，然后复制数据库文件，最后解锁表。
整个过程不需要停止服务，保证了系统的高可用性。

---

1. 冷备份例子

冷备份就是在数据库关闭时复制整个数据库文件，步骤如下：

步骤 1：关闭 MySQL 服务，确保没有活动的读写操作。
sudo systemctl stop mysql


步骤 2：找到 MySQL 数据库的存储位置。通常在 MySQL 的配置文件 my.cnf 中会有 datadir 指定数据库文件的存储路径，比如：
datadir=/var/lib/mysql


步骤 3：使用 cp 命令直接复制整个数据库文件夹到备份目录。
sudo cp -r /var/lib/mysql /backup/mysql_backup_2024-10-07


步骤 4：重新启动 MySQL 服务。
sudo systemctl start mysql



恢复过程：如果需要恢复，只需关闭 MySQL 服务，然后将备份文件复制回原来的路径，并重新启动 MySQL 服务即可。
sudo systemctl stop mysql
sudo cp -r /backup/mysql_backup_2024-10-07 /var/lib/mysql
sudo systemctl start mysql

2. 热备份例子

热备份允许你在数据库运行时进行备份，常见的工具是 Percona XtraBackup，它可以对 MySQL 数据库进行热备份。以下是一个使用 Percona XtraBackup 工具进行物理备份的例子。

步骤 1：安装 Percona XtraBackup（以 Ubuntu 为例）。
sudo apt-get install percona-xtrabackup-80

步骤 2：运行备份命令，在数据库运行的情况下备份数据文件到指定目录。
xtrabackup --backup --target-dir=/backup/mysql_backup_2024-10-07 --datadir=/var/lib/mysql

步骤 3：XtraBackup 生成的备份是非一致性状态的，需要应用日志文件以确保数据一致性。
xtrabackup --prepare --target-dir=/backup/mysql_backup_2024-10-07


恢复过程：停止 MySQL 服务后，将备份文件恢复到 MySQL 的数据目录中。
sudo systemctl stop mysql
sudo cp -r /backup/mysql_backup_2024-10-07 /var/lib/mysql
sudo systemctl start mysql

总结
冷备份：简单直接，通过文件系统复制数据库文件进行备份，适用于小型系统，但需要停机。
热备份：适用于运行中的大型系统，保证备份时数据的一致性，不需要停机，恢复速度也很快。

---

五、（了解）物理恢复：直接复制到数据库目录

演示过程

假设有一个备份的 MySQL 数据库，文件名为 mydb_backup，你需要恢复到 /var/lib/mysql/ 目录下。

1、停止 MySQL 服务： 在恢复数据之前，需要先停止 MySQL 服务以确保数据不会被访问或修改：
sudo systemctl stop mysql

2、清理旧数据： 如果已经存在相同数据库的旧数据，你可以删除旧的数据库文件：
sudo rm -rf /var/lib/mysql/mydb

3、复制备份文件： 将备份的数据库文件复制到 MySQL 数据目录下：
sudo cp -r /path/to/mydb_backup /var/lib/mysql/mydb

4、设置文件权限： 使用 chown 命令修改文件权限，确保 MySQL 能够正确访问数据库文件：
sudo chown -R mysql:mysql /var/lib/mysql/mydb

5、启动 MySQL 服务： 恢复完成后，重新启动 MySQL 服务：
sudo systemctl start mysql

6、验证恢复结果： 登录 MySQL 查看是否能够访问和使用恢复的数据库：
mysql -u root -p
SHOW DATABASES;


通过这几个步骤，你就完成了 MySQL 数据库的物理文件恢复操作。
在这个过程中，最重要的是确保备份与恢复环境的 MySQL 版本号一致，并正确设置文件权限。

六、表的导入与导出

1、表的导出方法

1. 使用 SELECT... INTO OUTFILE 导出文本文件：

• 该方法将查询结果直接写入服务器的一个文件中。
• 示例：

SELECT * FROM your_table
INTO OUTFILE '/path/to/output/file.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

这种方法生成的文件保存在数据库服务器上，注意使用相应的文件路径和权限。

---

2. 使用 mysqldump 命令导出文本文件：

• mysqldump 是一个实用工具，用于导出数据库的结构和数据到文件。
• 示例：

mysqldump -u root -p your_database your_table > /path/to/output/file.sql

该命令会将 your_table 导出到指定的 .sql 文件中，通常用于备份。

---

3. 使用 mysql 命令导出文本文件：

• 这个方法常常结合导出查询结果为文本文件。
• 示例：

mysql -u root -p -e "SELECT * FROM your_table" > /path/to/output/file.txt

通过这种方式，你可以使用 mysql 命令直接输出查询结果到指定文件。

---

2、表的导入方法

1. 使用 LOAD DATA INFILE 导入文本文件：

• 该命令将文本文件的数据直接加载到表中。
• 示例：

LOAD DATA INFILE '/path/to/input/file.txt'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

这种方式要求导入的文件与表的结构相匹配，并且文件必须位于数据库服务器上。

---

2. 使用 mysqlimport 方式导入文本文件：

• mysqlimport 是导入数据到表的命令行工具。
• 示例：

mysqlimport --local -u root -p your_database /path/to/input/file.txt

这条命令会将文件中的数据导入到表中，要求文件格式与表结构相符。

---

总结
这些方法在数据导入和导出时各有不同的应用场景，像 SELECT INTO OUTFILE 适合于 简单的数据导出，而 mysqldump 和 mysqlimport 更适合 做备份和还原。