MySQL逻辑备份

目录

一.mysqldump

基本命令：

常用选项：

示例

备份整个数据库 

备份多个数据库 

备份所有数据库 

仅备份数据库结构

仅备份特定表

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

恢复数据库

从逻辑备份文件恢复

注意事项

二. mysqlpump

mysqlpump 特点

基本命令：

常用选项：

示例

备份单个数据库

备份多个数据库 

备份所有数据库

并发处理

过滤表

仅备份表结构

压缩备份文件

备份数据目录

恢复数据库

注意事项

逻辑备份的优缺点

---

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

二. 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开销，特别是对于大数据量的数据库。
• 恢复速度慢：相对于物理备份，逻辑备份的恢复速度较慢。
• 一致性问题：如果没有正确使用事务一致性选项，可能会导致数据不一致。