MySQL逻辑备份

目录

一.mysqldump

基本命令：

参数选项：

示例

备份整个数据库 

备份多个数据库 

备份所有数据库 

仅备份数据库结构

仅备份特定表

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

恢复数据

恢复数据库

恢复库中的表 

使用source恢复 

注意事项

二. mysqlpump

mysqlpump 特点

基本命令：

常用选项：

示例

备份单个数据库

备份多个数据库 

备份所有数据库

并发处理

过滤表

仅备份表结构

压缩备份文件

备份数据目录

恢复数据库

注意事项

逻辑备份的优缺点

只备份表数据扩展

mysqldump -n

SELECT INTO OUTFILE

总结

---

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

一.mysqldump

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

基本命令：

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 复制。

示例

备份整个数据库 

    mysqldump -u root -p mydatabase > mydatabase_backup.sql

备份多个数据库 

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

备份所有数据库 

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

仅备份数据库结构

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

仅备份特定表

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

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

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

恢复数据

恢复数据库

语法

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

示例

    mysql -u root -p mydatabase < mydatabase_backup.sql

恢复库中的表 

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

使用source恢复 

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

source 文件路径;

例： 

source /path/to/employees_backup.sql;

注意事项

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

二. mysqlpump

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

mysqlpump 特点

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

基本命令：

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

常用选项：

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

示例

备份单个数据库

    mysqlpump -u root -p mydatabase > mydatabase_backup.sql

备份多个数据库 

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

备份所有数据库

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

并发处理

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

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

过滤表

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

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

仅备份表结构

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

压缩备份文件

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

备份数据目录

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

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

恢复数据库

解压缩备份文件

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

恢复备份

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

示例

    mysql -u root -p mydatabase < mydatabase_backup.sql

注意事项

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

逻辑备份的优缺点

优点：

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

缺点：

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

只备份表数据扩展

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

mysqldump -n

介绍

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

举例

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

2. 步骤:

进入命令行。

执行以下命令：

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 客户端，执行：

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 staffFIELDS TERMINATED BY ',' ENCLOSED BY '"'LINES TERMINATED BY '\n';

总结

1. 用途不同:

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

2. 恢复指定表:

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

3. 便捷性和灵活性:

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