MySQL - 数据库导出

MySQL - 数据库导出与导入

为什么需要导出和导入数据？

导出和导入数据是基本的数据库管理任务。常见原因包括：

• 备份：创建数据的逻辑备份，以便在数据丢失或损坏时可以恢复。
• 迁移：将数据库从一个服务器移动到另一个服务器（例如，从开发环境到生产环境，或从本地服务器到 AWS RDS 等云服务提供商）。
• 克隆：创建数据库副本用于测试、开发或分析。

MySQL 中用于创建逻辑备份的最常用命令行工具是 mysqldump。

使用 mysqldump 导出

mysqldump 是一个客户端程序，它生成一个 SQL 脚本文件（一个 ‘dump’ 文件），其中包含用于重建数据库对象的 CREATE 语句和用于重新填充数据的 INSERT 语句。

导出单个数据库

导出整个数据库的基本语法：

mysqldump -u [username] -p [database_name] > backup.sql

系统将提示您输入 [username] 的密码。> 符号将输出重定向到名为 backup.sql 的文件。

示例

# 此命令将把 'TUTORIALS' 数据库导出到 'tutorials_backup.sql'
$ mysqldump -u root -p TUTORIALS > tutorials_backup.sql
Enter password: ********

导出特定表

要只导出一个或多个特定表，请在数据库名称后面列出它们。

mysqldump -u [username] -p [database_name] [table1] [table2] > tables_backup.sql

示例

# 仅导出 'TUTORIALS' 数据库中的 'Customers' 和 'Products' 表
$ mysqldump -u root -p TUTORIALS Customers Products > customer_products_backup.sql

导出所有数据库

要备份用户有权访问的 MySQL 服务器上的所有数据库，请使用 --all-databases 标志。

mysqldump -u [username] -p --all-databases > all_databases_backup.sql

现代使用 mysqldump 的关键选项

为了可靠备份，尤其是在生产环境中，您应该使用以下额外标志：

• --single-transaction：创建 InnoDB 表的一致快照，而无需锁定它们。这对于在不中断服务的情况下备份实时数据库至关重要。它会启动一个事务，并从该一致状态导出数据。
• --routines：在导出中包含存储过程和函数。
• --events：在导出中包含计划事件。
• --triggers：在导出中包含触发器（此项默认开启，但明确指定是良好实践）。
• --no-data：创建一个仅包含模式（表结构、视图等）而不包含任何行数据的导出文件。适用于创建数据库结构的空白副本。
• --where='condition'：仅导出满足 WHERE 条件的行。例如，--where='is_active=1'。

推荐的生产环境备份命令

mysqldump -u [username] -p \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  [database_name] > full_backup_$(date +%F).sql

此命令创建了一个全面的备份，文件名中带有时间戳。

使用 mysql 客户端导入数据库

要从 .sql 导出文件恢复数据库，您可以使用标准的 mysql 命令行客户端。

首先，您通常需要创建一个空数据库用于导入数据。

# 连接到 MySQL
$ mysql -u root -p

# 在 MySQL shell 中创建一个新数据库
mysql> CREATE DATABASE new_tutorials;
mysql> exit;

现在，将备份文件中的数据导入到新创建的数据库中。

mysql -u [username] -p [new_database_name] < backup.sql

示例

# 将 'tutorials_backup.sql' 文件导入到 'new_tutorials' 数据库中
$ mysql -u root -p new_tutorials < tutorials_backup.sql
Enter password: ********

安全考量

• 不要在命令行中直接输入密码：避免直接使用 -pMyPassword。始终单独使用 -p 以安全地提示输入密码。
• 使用专用备份用户：为备份创建一个专门的 MySQL 用户，只授予必要的权限（SELECT、LOCK TABLES、SHOW VIEW、EVENT、TRIGGER）。
• 保护备份文件安全：.sql 导出文件包含所有明文数据。请将其存储在安全、受访问控制的位置，并考虑加密它（例如，使用 gpg）。

自动化和实际场景

在实际项目中，您绝不会手动运行备份。您会使用 Linux 上的 cron 或 Windows 上的任务计划程序等调度程序来自动化此过程。

示例：每晚备份的基本 Cron 任务

您可以将一行添加到 crontab 中（crontab -e），以便每晚运行备份脚本。

/etc/cron.d/mysql-backup

# 每天凌晨 2:00 运行
0 2 * * * root /path/to/backup_script.sh

backup_script.sh 将包含你的 mysqldump 命令，以及可能额外的逻辑，用于压缩备份、将其上传到云存储（如 Amazon S3）和删除旧备份。