使用 mysqldump (最推荐、最常用)

mysqldump 是 MySQL 官方提供的逻辑备份工具，是备份单个表、整个数据库甚至整个服务器的标准方法。

备份单个表（结构和数据）

这是最常见的需求,备份指定表的结构（CREATE TABLE 语句）和数据（INSERT 语句）。

mysqldump -u [username] -p[password] [database_name] [table_name] > [backup_file].sql

参数说明:

• -u [username]: 数据库用户名，-u root。
• -p[password]: 数据库密码，注意 -p 和密码之间没有空格，如果省略密码，命令会提示你输入，为了安全，建议在脚本中省略密码，通过交互方式输入。
• [database_name]: 数据库名。
• [table_name]: 要备份的表名。
• > [backup_file].sql: 将输出重定向到一个 .sql 文件中。

示例:

# 备份 mydb 数据库中的 users 表到 users_backup.sql 文件
# 系统会提示你输入 root 用户的密码
mysqldump -u root -p mydb users > users_backup.sql

只备份数据（不包含结构）

如果你已经有表结构,只需要数据，可以使用 --no-create-info 参数。

mysqldump -u [username] -p[password] [database_name] [table_name] --no-create-info > [data_only].sql

示例:

mysqldump -u root -p mydb users --no-create-info > users_data_only.sql

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

如果你想创建一个空表（结构和原表一样），可以使用 --no-data 参数。

mysqldump -u [username] -p[password] [database_name] [table_name] --no-data > [structure_only].sql

示例:

mysqldump -u root -p mydb users --no-data > users_structure_only.sql

备份多个表

在数据库名后列出所有要备份的表名,用空格隔开。

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

示例:

mysqldump -u root -p mydb users products > users_products_backup.sql

压缩备份文件（推荐）

对于较大的表,直接备份到 .sql 文件可能会占用很大空间，可以在命令中直接使用 gzip 进行压缩。

mysqldump -u [username] -p[password] [database_name] [table_name] | gzip > [backup_file].sql.gz

示例:

mysqldump -u root -p mydb users | gzip > users_backup.sql.gz

使用 SELECT ... INTO OUTFILE (超大数据导出)

这个方法速度非常快,因为它直接将服务器上的数据写入到服务器的文件系统中，不经过客户端，但它有一些重要的限制。

语法

SELECT * FROM [table_name] INTO OUTFILE '[server_file_path]'
FIELDS TERMINATED BY ','  -- 可选，指定字段分隔符，默认是制表符
ENCLOSED BY '"'          -- 可选，指定字符串字段的包围符
LINES TERMINATED BY '\n'; -- 可选，指定行分隔符，默认是换行符

关键步骤和限制:

1. 文件路径: 文件路径必须是服务器上的绝对路径，MySQL 服务器进程对该目录必须有写入权限，通常使用 /tmp/ 目录。
2. 文件所有权: 生成的文件所有者是运行 MySQL 服务的用户（通常是 mysql 或 root），你可能会遇到权限问题。
3. 文件必须不存在: INTO OUTFILE 会直接创建文件，如果文件已存在，命令会失败。
4. 只导出数据: 此命令不会导出表结构、索引、注释等任何元数据。

完整示例:

1. 在 MySQL 客户端中执行查询:

   -- 假设我们要备份 mydb 数据库中的 users 表
   -- 导出到服务器的 /tmp/ 目录下
   SELECT * FROM mydb.users INTO OUTFILE '/tmp/users_data.csv'
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n';

2. 从服务器下载文件: 备份完成后，/tmp/users_data.csv 文件就存在于服务器上，你需要通过 scp、rsync 或 FTP 等方式将其下载到本地。

# 使用 scp 从服务器下载文件到本地
scp user@your_server_ip:/tmp/users_data.csv ./local_users_data.csv

使用 CREATE TABLE ... SELECT (内部数据复制)

这种方法用于在同一个数据库服务器内部快速创建一个包含现有表数据的新表，新表的结构是自动根据查询结果推断的。

语法

CREATE TABLE [new_table_name] AS SELECT * FROM [old_table_name];

特点:

• 只复制数据：新表的结构是简化的，会丢失原表的索引、主键、AUTO_INCREMENT、ENGINE、COMMENT 等所有属性。
• 速度快：操作在服务器端完成，效率高。
• 过滤数据：你可以在 SELECT 语句中加入 WHERE 条件来只复制部分数据。

示例:

-- 创建一个 users_copy 表，结构和 users 表不同，但包含 users 表的所有数据
CREATE TABLE users_copy AS SELECT * FROM users;
-- 创建一个只包含活跃用户的快照表
CREATE TABLE active_users_snapshot AS SELECT id, name, email FROM users WHERE status = 'active';

如何选择？

• 日常备份、数据迁移、需要保留完整结构：首选 mysqldump，这是最可靠、最标准的方法。
• 导出超大数据文件（如日志、数据归档）：使用 SELECT ... INTO OUTFILE，因为它速度最快，但要注意处理好权限和文件下载。
• 在数据库内部快速复制一份用于测试或临时分析：使用 CREATE TABLE ... SELECT，简单快捷。

希望这份详细的指南能帮助你！