目录
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 特点
- 并行处理：
mysqlpump支持并发地备份多个表和多数据库，极大地提高了备份速度。 - 更丰富的功能选项：支持过滤特定数据库、表和数据以及导出的同时进行压缩等。
- 增强的灵活性：提供更多参数以定制化备份操作。
基本命令：
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
注意事项
- 权限管理：确保执行
mysqlpump和恢复命令的用户具有必要的数据库读写权限。 - 并发性：合理设定并发线程数，避免服务器负载过高。
- 定期备份：建议定期备份并验证备份文件的完整性。
- 存储位置：将备份文件存储在安全和可靠的位置，避免丢失或不当修改。
逻辑备份的优缺点
优点：
- 可读性强：备份文件是文本格式的SQL脚本，易于查看和编辑。
- 跨平台：备份文件可以在不同操作系统和不同版本的MySQL之间移植。
- 备份灵活：可以选择备份整个数据库、特定的表或结构。
缺点：
- 性能开销大：备份和恢复过程中会产生较大的CPU和I/O开销，特别是对于大数据量的数据库。
- 恢复速度慢：相对于物理备份，逻辑备份的恢复速度较慢。
- 一致性问题：如果没有正确使用事务一致性选项，可能会导致数据不一致。