MySQL逻辑备份的实现方法_F11 - 专业站长和开发者的学习网站

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 文件内容：

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 语句：

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","Alice","Manager","50000"

"2","Bob","Engineer","40000"

"3","Charlie","Technician","30000"

恢复:

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

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 适用于导出数据进行外部分析，格式灵活但操作稍复杂。