MySQL 的逻辑备份指的是使用 SQL 语句备份数据库的结构和数据，而不是直接备份数据库文件。通常使用 mysqldump 工具进行逻辑备份。

一.mysqldump

mysqldump是最常用的逻辑备份工具，能够生成SQL脚本文件，这些文件可以用来重建数据库结构和数据。

基本命令：

1	mysqldump -u [用户名] -p[密码] [数据库名] > [备份文件.sql]

参数选项：

• -u, --user=[name]：指定 MySQL 用户名。
• -p, --password[=password]：指定 MySQL 密码。如果不提供密码，mysqldump 会提示输入。
• -h, --host=[hostname]：指定 MySQL 服务器主机名。
• -P, --port=[port_num]：指定 MySQL 服务器端口号。
• --protocol=[{TCP|SOCKET|PIPE|MEMORY}]：指定连接协议。
• -r, --result-file=[file]：将输出直接写入文件，而不是通过标准输出。
• -t, --no-create-info：不写入创建表的语句，只导出数据。
• -d, --no-data：不写入数据，只导出数据库结构。
• --add-drop-table：在每个创建表语句前增加 DROP TABLE IF EXISTS 语句。
• --add-locks：在插入数据前后使用 LOCK TABLES 和 UNLOCK TABLES 语句。
• --disable-keys：在插入数据前使用 ALTER TABLE ... DISABLE KEYS，插入数据后使用 ALTER TABLE ... ENABLE KEYS。
• -B, --databases：指定要备份的数据库。如果使用该选项，所有跟在其后的数据库名都将被备份。
• -A, --all-databases：备份所有数据库。
• --tables：指定要备份的表，必须跟在数据库名之后。
• -n, --no-create-db：不写入创建数据库的语句。
• -w, --where='condition'：仅导出符合指定条件的行。
• --single-transaction：在一个事务中导出所有数据，适用于支持事务的表（如 InnoDB）。
• --lock-tables：在导出前锁定所有表，适用于不支持事务的表（如 MyISAM）。
• --master-data[=#]：在导出中加入主服务器的二进制日志位置和文件名，用于设置复制。
• --flush-logs：在导出前刷新 MySQL 服务器的日志。
• --routines：导出存储过程和函数。
• --triggers：导出触发器。
• --events：导出事件。
• --hex-blob：以十六进制格式导出二进制列。
• --set-gtid-purged=[OFF|ON|AUTO]：控制是否在备份中加入 GTID 信息，适用于 GTID 复制。

示例

备份整个数据库 

1	mysqldump -u root -p mydatabase > mydatabase_backup.sql

备份多个数据库 

1	mysqldump -u root -p --databases db1 db2 > multi_database_backup.sql

备份所有数据库 

1	mysqldump -u root -p --all-databases > all_databases_backup.sql

仅备份数据库结构

1	mysqldump -u root -p --no-data mydatabase > mydatabase_structure_backup.sql

仅备份特定表

1	mysqldump -u root -p mydatabase table1 table2 > mydatabase_tables_backup.sql

添加选项以有效处理锁表问题

1	mysqldump -u root -p --single-transaction --quick --lock-tables=false mydatabase > mydatabase_backup.sql

恢复数据

恢复数据库

语法

1	mysql -u [用户名] -p[密码] [数据库名] < [备份文件.sql]

示例

1	mysql -u root -p mydatabase < mydatabase_backup.sql

恢复库中的表 

1	mysqldump -u root -p[密码] [库名] [表名] > staff_backup.sql

使用source恢复 

首先登录到mysql中，在mysql中使用下述语法恢复

1	source 文件路径;

例： 

1	source /path/to/employees_backup.sql;

注意事项

• 权限管理：确保执行 mysqldump 和恢复命令的用户具有相应的数据库读写权限。
• 定期备份：逻辑备份通常对数据一致性和完整性要求较高，建议定期进行备份并验证备份文件的有效性。
• 存储位置：将备份文件存储在安全的位置，避免丢失或被不当修改。

二. mysqlpump

mysqlpump是MySQL 5.7引入的备份工具，支持多线程，能够更快地进行备份。它是 mysqldump 的增强版本。

mysqlpump 特点

• 并行处理：mysqlpump 支持并发地备份多个表和多数据库，极大地提高了备份速度。
• 更丰富的功能选项：支持过滤特定数据库、表和数据以及导出的同时进行压缩等。
• 增强的灵活性：提供更多参数以定制化备份操作。

基本命令：

1	mysqlpump -u [用户名] -p[密码] [数据库名] > [备份文件.sql]

常用选项：

• --exclude-databases：排除特定数据库
• --include-databases：包含特定数据库
• --default-parallelism：设置并行度
• --skip-definer：忽略DEFINER子句
• --set-gtid-purged：用于GTID的备份

示例

备份单个数据库

1	mysqlpump -u root -p mydatabase > mydatabase_backup.sql

备份多个数据库 

1	mysqlpump -u root -p --databases db1 db2 > multi_database_backup.sql

备份所有数据库

1	mysqlpump -u root -p --all-databases > all_databases_backup.sql

并发处理

1	mysqlpump -u root -p --default-parallelism=4 --databases db1 db2 > multi_database_backup.sql

使用 --default-parallelism 参数可以设定并发线程数，加快备份速度。 

过滤表

1	mysqlpump -u root -p mydatabase --exclude-tables=table1,table2 > mydatabase_backup.sql

使用 --exclude-tables 可以排除特定的表 

仅备份表结构

1	mysqlpump -u root -p mydatabase --skip-dump-data > mydatabase_structure_backup.sql

压缩备份文件

1	mysqlpump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

备份数据目录

1	mysqlpump --users --routines --databases db1 db2 > backup_with_users_routines.sql

使用 --users 备份用户账户，使用 --routines 备份存储函数和存储过程。

恢复数据库

解压缩备份文件

1	gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

恢复备份

1	mysql -u [用户名] -p [数据库名] < [备份文件.sql]

示例

1	mysql -u root -p mydatabase < mydatabase_backup.sql

注意事项

• 权限管理：确保执行 mysqlpump 和恢复命令的用户具有必要的数据库读写权限。
• 并发性：合理设定并发线程数，避免服务器负载过高。
• 定期备份：建议定期备份并验证备份文件的完整性。
• 存储位置：将备份文件存储在安全和可靠的位置，避免丢失或不当修改。

逻辑备份的优缺点

优点：

• 可读性强：备份文件是文本格式的SQL脚本，易于查看和编辑。
• 跨平台：备份文件可以在不同操作系统和不同版本的MySQL之间移植。
• 备份灵活：可以选择备份整个数据库、特定的表或结构。

缺点：

• 性能开销大：备份和恢复过程中会产生较大的CPU和I/O开销，特别是对于大数据量的数据库。
• 恢复速度慢：相对于物理备份，逻辑备份的恢复速度较慢。
• 一致性问题：如果没有正确使用事务一致性选项，可能会导致数据不一致。

只备份表数据扩展

/var/lib/mysql-files 是 MySQL 默认用于存储导出的文件的目录。这个目录通常用于 SELECT INTO OUTFILE 等操作所生成的文件。

mysqldump -n

介绍

mysqldump 是一个用于备份 MySQL 数据库的工具。-n 参数让你只备份表中数据，不包括表结构。备份得到的文件是纯 SQL 语句，可以用来恢复数据。

举例

场景: 有一个名为 employees 的数据库，其中包含 staff 表。备份 staff 表中的数据，但不包括表结构。

步骤:

进入命令行。

执行以下命令：

1	mysqldump -u root -p[密码] --no-create-info employees staff > staff_data_only.sql

3.结果: 生成的 staff_data_only.sql 文件内容：

1 2 3

INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (1, 'Alice', 'Manager', 50000); INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (2, 'Bob', 'Engineer', 40000); INSERT INTO `staff` (`id`, `name`, `position`, `salary`) VALUES (3, 'Charlie', 'Technician', 30000);

恢复:

• 在新环境中，确保已存在相同结构的 staff 表。
• 进入 MySQL 客户端，执行：

1	source /path/to/staff_data_only.sql;

SELECT INTO OUTFILE

介绍

SELECT INTO OUTFILE 是一条SQL语句，用于将查询结果导出到文件中。文件格式可以根据需要来定义（如CSV格式，也可以是xlsx结尾的格式），通常用于数据分析和传输。

举例

1.场景: 有一个名为 employees 的数据库，其中包含 staff 表。导出 staff 表中的数据作为CSV文件来分析。

2.步骤:

进入 MySQL 客户端。

执行以下 SQL 语句：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SELECT * FROM staff INTO OUTFILE '/var/lib/mysql-files/staff_data.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';   命令解读（也可以使用默认，后面就不用那么多指定要求语句了）： -- 从 staff 表中选择所有列和行 SELECT * FROM staff   -- 将查询结果写入指定路径的文件中 INTO OUTFILE '/var/lib/mysql-files/staff_data.csv'   -- 每个字段（列）的数据使用逗号分隔 FIELDS TERMINATED BY ','   -- 每个字段的值都用双引号包围 ENCLOSED BY '"'   -- 每行记录之间以换行符分隔 LINES TERMINATED BY '\n';

3.结果: 生成的 staff_data.csv 文件内容：

1 2 3

"1","Alice","Manager","50000" "2","Bob","Engineer","40000" "3","Charlie","Technician","30000"

恢复:

• 在新环境中，确保已存在相同结构的 staff 表。
• 复制 staff_data.csv 文件到服务器。
• 使用以下 SQL 语句导入数据：

1 2 3 4 5

LOAD DATA INFILE '/var/lib/mysql-files/staff_data.csv' INTO TABLE staff FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

总结

用途不同:

• mysqldump -n 主要用于备份和恢复 MySQL 数据库数据。生成 SQL 文件，便于在需要时恢复数据。
• SELECT INTO OUTFILE 主要用于数据导出用于分析或传输。生成的文件格式如CSV，可用Excel等工具打开。

恢复指定表:

• mysqldump -n 恢复数据更简单，直接源文件加载到MySQL中即可。
• SELECT INTO OUTFILE 导出的文件格式灵活，但需要额外步骤导入数据（如使用 LOAD DATA INFILE）。

便捷性和灵活性:

• mysqldump -n 适用于跨平台环境，恢复数据库较为便捷。
• SELECT INTO OUTFILE 适用于导出数据进行外部分析，格式灵活但操作稍复杂。