揭秘MySQL 8.4新版备份利器：全面解读Mysqldump参数与实战技巧

简介

  MysqlDump是MySQL数据库管理系统提供的一个实用工具，用于创建数据库的逻辑备份。它通过生成 SQL 脚本文件，将数据库中的数据和结构导出，以便进行备份和恢复。这个工具非常适用于需要迁移数据库、备份数据、或者将数据库内容导出为 SQL 文件的场景。

工作原理

MysqlDump 的工作原理如下：

1. 连接数据库：mysqldump 通过指定的用户凭证连接到 MySQL 服务器。

2. 锁定表：为了保证数据一致性，mysqldump 在导出数据之前会锁定表或使用事务（具体取决于使用的选项，如 --lock-tables 或 --single-transaction）。

3. 导出数据和结构：mysqldump 生成包含 CREATE TABLE 和 INSERT INTO 语句的 SQL 文件，这些语句可以重建表的结构并重新插入数据。

4. 写入文件：最终生成的 SQL 脚本文件被写入到指定的输出文件中，或者直接输出到标准输出（如控制台）。

优点

• 简单易用：mysqldump 提供了多种选项，可以灵活地导出数据和表结构。

• 广泛支持：它支持几乎所有的 MySQL 和 MariaDB 版本。

• 易于恢复：导出的 SQL 文件可以直接用于恢复数据库，通过简单的 mysql 命令即可重新导入数据。

• 兼容性好：SQL 文件是纯文本格式，易于编辑和传输。

缺点

• 性能问题：对于大型数据库，mysqldump 的备份和恢复速度可能较慢，尤其是在网络较慢或磁盘性能不佳的环境下。

• 数据一致性问题：在备份过程中，如果没有正确使用事务或锁定表，可能会导致数据不一致。

• 无增量备份：mysqldump 不支持增量备份，每次备份都需要导出整个数据库。

使用场景

• 小型数据库备份：对于小型数据库，mysqldump 是一个简单且高效的备份工具。

• 数据库迁移：在将数据库从一个服务器迁移到另一个服务器时，mysqldump 可以生成完整的 SQL 脚本，方便在新服务器上重建数据库。

• 开发和测试：在开发和测试环境中，可以使用 mysqldump 创建数据库的快照，以便随时恢复到某个特定状态。

• 数据导出：将数据库数据导出为 SQL 文件，以便进行数据分析、归档或传输。

备份示例

备份整个数据库带备份点：
mysqldump -u username -p password --single-transaction --source-data=2 --all-databases > all_databases.sql
备份整个数据库带备份点、GTID点位：
mysqldump -u username -p password --single-transaction --source-data=2 --set-gtid-purged=on  --all-databases > all_databases.sql
备份整个数据库将行合并一起批量 INSERT：
mysqldump -u username -p password --single-transaction --extended-insert --all-databases > all_databases.sql
备份整个数据库将每行数据拆分成一个INSERT：
mysqldump -u username -p password --single-transaction --skip-extended-insert --all-databases > all_databases.sql
备份整个数据库使用完整的insert语句(包含列名称)：
mysqldump -u username -p password --single-transaction --complete-insert --all-databases > all_databases.sql
备份整个数据库包括触发器、存储过程、事件：
mysqldump -u username -p password --single-transaction --triggers  --routines  --events --all-databases > all_databases.sql
备份整个数据库：
mysqldump -u username -p password --single-transaction --all-databases > all_databases.sql
备份单个数据库：
mysqldump -u username -p password --single-transaction database_name > database_name.sql
备份多个指定的数据库：
mysqldump -u username -p password --single-transaction --databases database1 database2 > databases.sql
备份单个表：
mysqldump -u username -p password --single-transaction database_name table_name > table_name.sql
备份多个指定的表：
mysqldump -u username -p password --single-transaction database_name table1 table2 > tables.sql
仅备份表结构：
mysqldump -u username -p password --single-transaction --no-data database_name > schema.sql
仅备份数据：
mysqldump -u username -p password --single-transaction --no-create-info database_name > data.sql
使用 gzip 压缩备份文件：
mysqldump -u username -p password --single-transaction database_name | gzip > database_name.sql.gz
备份并排除某些表：
mysqldump -u username -p password --single-transaction --ignore-table=database_name.table_name database_name > database_name.sql
备份时不包含 CREATE DATABASE 语句：
mysqldump -u username -p password --single-transaction --no-create-db database_name > database_name.sql
备份过程中添加清除表的语句：
mysqldump -u username -p password --single-transaction --add-drop-table database_name > database_name.sql
备份时生成带有时间戳的文件名：
mysqldump -u username -p password --single-transaction database_name > database_name_`date +%Y%m%d_%H%M%S`.sql
备份带where条件关闭GTID点位
mysqldump username -p password -t --single-transaction   --set-gtid-purged=OFF    --where="id>=2 and id <=8" database_name table_name >t1.sql
备份整个数据库将INSERT替换为REPLACE INTO：
mysqldump -u username -p password --single-transaction --replace --all-databases > all_databases.sql
备份整个数据库避免备份语句写入slowlog：
mysqldump -u username -p password --single-transaction --mysqld-long-query-time=30s --all-databases > all_databases.sql

参数详解

--print-defaults
打印程序参数列表并退出。
--no-defaults
不从任何选项文件中读取默认选项，除了登录文件。
--defaults-file=#
仅从给定文件#读取默认选项。
--defaults-extra-file=#
在全局文件读取后再读取这个文件#。
--defaults-group-suffix=#
也读取group与suffix拼接的组。
--login-path=#
从登录文件中读取此路径。
--no-login-paths
不从登录路径文件中读取登录路径。
-A, --all-databases
倒所有数据库。这与--databases选项相同，只是选择了所有数据库。
-Y, --all-tablespaces
倒所有表空间。
-y, --no-tablespaces
不倒任何表空间信息。
--add-drop-database
在每个CREATE之前添加一个DROP DATABASE。
--add-drop-table
在每个CREATE之前添加一个DROP TABLE。 (默认为开启；使用--skip-add-drop-table禁用。)
--add-drop-trigger
在每个CREATE之前添加一个DROP TRIGGER。
--add-locks
在INSERT语句周围添加锁定。 (默认为开启；使用--skip-add-locks禁用。)
--allow-keywords
允许创建关键字列名。
--apply-replica-statements
在“CHANGE REPLICATION SOURCE”之前添加“STOP REPLICA”，并在转储底部添加“START REPLICA”。 使用--output-as-version切换为旧术语。
--apply-slave-statements
此选项已弃用，将在将来的版本中删除。请改用apply-replica-statements。
--bind-address=name
绑定到的IP地址。
--character-sets-dir=name
字符集文件目录。
--column-statistics
添加一个ANALYZE TABLE语句以重新生成任何现有的列统计信息。 (默认为开启；使用--skip-column-statistics禁用。)
-i, --comments
写入额外信息。 (默认为开启；使用--skip-comments禁用。)
--compatible=name
更改转储以与给定模式兼容。默认情况下，表以针对MySQL优化的格式转储。唯一合法的模式是ANSI。需要MySQL服务器版本4.1.0或更高。
--compact
提供更少冗长的输出（用于调试）。禁用结构注释和页眉/页脚构造。启用选项--skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys、--skip-set-charset。
-c, --complete-insert
使用完整的INSERT语句。
-C, --compress
在服务器/客户端协议中使用压缩。
-a, --create-options
包括所有MySQL特定的创建选项。 (默认为开启；使用--skip-create-options禁用。)
-B, --databases
转储多个数据库。注意在用法上的不同；在这种情况下不给出表。所有名称参数都被视为数据库名称。将包括“USE db_name;”在输出中。
-#, --debug[=#]
这是非调试版本。捕获此错误并退出。
--debug-check
这是非调试版本。捕获此错误并退出。
--debug-info
这是非调试版本。捕获此错误并退出。
--default-character-set=name
设置默认字符集。
--delete-source-logs
在备份之前轮换日志，等同于FLUSH LOGS，并在备份后清除所有旧的二进制日志，等同于PURGE LOGS。这将自动启用--source-data。
--delete-master-logs
此选项已弃用，将在将来的版本中删除。请改用delete-source-logs。
-K, --disable-keys
将“/*!40000 ALTER TABLE tb_name DISABLE KEYS */;”和“/*!40000 ALTER TABLE tb_name ENABLE KEYS */;”放入输出中。 (默认为开启；使用--skip-disable-keys禁用。)
--dump-replica[=#]
这导致源的二进制日志位置和文件名附加到转储的数据输出中。将值设置为1，将其打印为CHANGE REPLICATION SOURCE命令在转储的数据输出中；如果等于2，那个命令将以注释符号前缀。此选项将打开--lock-all-tables，除非还指定了--single-transaction（在这种情况下，全局读锁仅在转储开始时短暂持有 - 不要忘记阅读有关--single-transaction的内容）。在所有情况下，日志上的任何操作都将在转储的确切时刻发生。选项会自动关闭--lock-tables。
--dump-slave[=#]
此选项已弃用，将在将来的版本中删除。请改用dump-replica。
-E, --events
转储事件。
-e, --extended-insert
使用包含多个VALUES列表的多行INSERT语法。 (默认为开启；使用--skip-extended-insert禁用。)
--fields-terminated-by=name
输出文件中的字段以给定字符串终止。
--fields-enclosed-by=name
输出文件中的字段以给定字符封闭。
--fields-optionally-enclosed-by=name
输出文件中的字段可以选择以给定字符封闭。
--fields-escaped-by=name
输出文件中的字段以给定字符转义。
-F, --flush-logs
在开始转储之前在服务器中刷新日志文件。请注意，如果一次性转储多个数据库（使用--databases=或--all-databases选项），则将为每个转储的数据库刷新日志。例外情况是使用--lock-all-tables或--source-data：在这种情况下，日志将仅刷新一次，对应于所有表被锁定的时刻。因此，如果您希望您的转储和日志刷新在完全相同的时刻发生，您应该使用--lock-all-tables或--source-data与--flush-logs。
--flush-privileges
在转储mysql数据库后发出FLUSH PRIVILEGES语句。应在转储包含mysql数据库及任何其他依赖于mysql数据库数据的数据库时使用。
-f, --force
即使遇到SQL错误也继续。
-?, --help
显示此帮助消息并退出。
--hex-blob
以十六进制格式转储二进制字符串（BINARY、VARBINARY、BLOB）。
-h, --host=name
连接的主机。
--ignore-error=name
遇到时要忽略的错误号列表，用逗号分隔。
--ignore-table=name
不转储指定表。要忽略多个表，多次使用此指令，每次指定数据库和表名，例如--ignore-table=database.table。
--include-source-host-port
在使用--dump-replica生成的转储中的“CHANGE REPLICATION SOURCE TO..”中添加“SOURCE_HOST=<host>，SOURCE_PORT=<port>”。
--include-master-host-port
此选项已弃用，将在将来的版本中删除。请改用include-source-host-port。
--insert-ignore
使用INSERT IGNORE插入行。
--lines-terminated-by=name
输出文件中的行以给定字符串终止。
-x, --lock-all-tables
锁定所有数据库中的所有表。通过在整个转储期间获取全局读锁来实现。自动关闭--single-transaction和--lock-tables。
-l, --lock-tables
锁定所有表以供读取。 (默认为开启；使用--skip-lock-tables禁用。)
--log-error=name
将警告和错误追加到给定文件中。
--mysqld-long-query-time=#
为此转储会话设置long_query_time。省略标志意味着使用服务器值。
--source-data[=#]
将二进制日志位置和文件名附加到输出。如果等于1，则输出为CHANGE REPLICATION SOURCE命令；如果等于2，则该命令以注释符号为前缀。该选项会自动打开--lock-all-tables，除非同时指定了--single-transaction（在这种情况下，在转储的开始时会短暂地获取全局读锁）。
--master-data[=#]
此选项已弃用，并将在将来的版本中移除。请改用--source-data。
--max-allowed-packet=#
发送到服务器或从服务器接收的最大数据包长度。
--net-buffer-length=#
TCP/IP和套接字通信的缓冲区大小。
--no-autocommit
在表周围包裹autocommit/commit语句。
-n, --no-create-db
如果给出了--all-databases或--databases，则不输出每个转储数据库的CREATE DATABASE ... IF EXISTS语句。
-t, --no-create-info
不输出表创建信息。
-d, --no-data
不包括行信息。
-N, --no-set-names
等同于--skip-set-charset。
--opt
等同于--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、
--lock-tables、--set-charset和--disable-keys。默认情况下启用，使用--skip-opt禁用。
--order-by-primary
按主键或第一个唯一键排序每个表的行。当转储MyISAM表以加载到InnoDB表时很有用，但会使转储本身需要更长时间。
-p, --password[=name]
连接到服务器时使用的密码。如果未提供密码，则会从tty中询问。
-,, --password1[=name]
第一因子认证插件的密码。
-,, --password2[=name]
第二因子认证插件的密码。
-,, --password3[=name]
第三因子认证插件的密码。
- P, --port=＃
连接时要使用的端口号。
--protocol=name
连接使用的协议（tcp，socket，pipe，memory）。
-q, --quick
不缓冲查询，直接转储到stdout。默认情况下启用，使用--skip-quick禁用。
-Q, --quote-names
使用反引号（`）引用表和列名。默认情况下启用，使用--skip-quote-names禁用。
--replace
使用REPLACE INTO而不是INSERT INTO。
-r, --result-file=name
将输出直接写入给定的文件。此选项应在使用回车换行对（\r\n）分隔文本行的系统（例如DOS、Windows）中使用。此选项确保只使用单个换行符。
-R, --routines
转储存储过程和函数。
--set-charset
将'SET NAMES default_character_set'添加到输出中。默认情况下启用，使用--skip-set-charset禁用。
--set-gtid-purged[=name]
将'SET @@GLOBAL.GTID_PURGED'添加到输出中。此选项的可能值包括ON、COMMENTED、OFF和AUTO。如果使用ON并且服务器上未启用GTIDs，则会生成错误。如果使用COMMENTED，则将'SET @@GLOBAL.GTID_PURGED'作为注释添加。如果使用OFF，则此选项不起作用。如果使用AUTO并且服务器上启用了GTIDs，则会将'SET @@GLOBAL.GTID_PURGED'添加到输出中。如果禁用了GTIDs，则AUTO不起作用。如果未提供任何值，则将考虑默认值（AUTO）。
--single-transaction
通过在单个事务中转储所有表来创建一致的快照。仅适用于支持多版本控制的存储引擎（目前仅有InnoDB）；不能保证对其他存储引擎是一致的。在进行--single-transaction转储时，为确保一个有效的转储文件（正确的表内容和二进制日志位置），不应有其他连接使用以下语句：ALTER TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE，因为一致的快照与这些操作不是隔离的。此选项会自动关闭--lock-tables。
--dump-date
在输出的末尾加上转储日期。默认情况下启用，使用--skip-dump-date禁用。
--skip-opt
禁用--opt。禁用--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-charset和--disable-keys。
-S, --socket=name
要使用的套接字文件。
--server-public-key-path=name
服务器公共RSA密钥的文件路径（PEM格式）。
--get-server-public-key
获取服务器的公共密钥。
--ssl-mode=name
SSL连接模式。
--ssl-ca=name
CA文件的路径（PEM格式）。
--ssl-capath=name
CA目录的路径。
--ssl-cert=name
X509证书的路径（PEM格式）。
--ssl-cipher=name
要使用的SSL密码。
--ssl-key=name
X509密钥的路径（PEM格式）。
--ssl-crl=name
证书吊销列表。
--ssl-crlpath=name
证书吊销列表路径。
--tls-version=name
要使用的TLS版本。允许的值为：TLSv1.2、TLSv1.3。
--ssl-fips-mode=name
SSL FIPS模式（仅适用于OpenSSL）。允许的值为：OFF、ON、STRICT。
--tls-ciphersuites=name
要使用的TLS v1.3密码。
--ssl-session-data=name
要使用的会话数据文件以启用SSL会话重用。
--ssl-session-data-continue-on-failed-reuse
如果设置为ON，则此选项将允许连接在会话数据无法重用时仍然成功。
--tls-sni-servername=name
要传递给服务器的SNI服务器名称。
-T, --tab=name
为每个表创建制表符分隔的文本文件并指定路径。注意：仅当mysqldump在与mysqld服务器相同的机器上运行时才起作用。
--tables
覆盖选项--databases（-B）。
--output-as-version[=name]
定义转储中用于副本和事件命令的术语，允许创建与仅接受过时命令的较低版本兼容的转储。此选项的可能值为SERVER、BEFORE_8_0_23和BEFORE_8_2_0。默认为SERVER，如果设置，则读取服务器版本并输出与该版本兼容的命令。这意味着如果服务器低于8.2.0，则会输出事件的DEPRECATED DISABLE ON SLAVE术语，如果低于8.0.23，则还会使用事件的DEPRECATED SLAVE/CHANGE MASTER术语。如果设置为BEFORE_8_2_0，则SHOW CREATE EVENT命令将始终显示如何在低于8.2.0版本的服务器上创建事件。如果设置为BEFORE_8_0_23，则转储还将包含像START SLAVE或CHANGE MASTER TO等已弃用的副本命令。这会影响--events、--dump-replica、--source-data、--apply-replica-statements和--include-source-host-port的输出。
--triggers
为每个转储的表转储触发器。默认情况下启用，使用--skip-triggers禁用。
--tz-utc
在转储的顶部设置'TIME_ZONE='+00:00'，允许在服务器具有不同时区数据或在具有不同时区的服务器之间移动数据时转储TIMESTAMP数据。默认情况下启用，使用--skip-tz-utc
-u, --user=name
指定用于登录的用户名，如果不是当前用户。
-v, --verbose
打印有关各个阶段的详细信息。
-V, --version
输出版本信息并退出。
-w, --where=name
仅转储所选记录。引号是必需的。
-X, --xml
将数据库转储为格式良好的XML。
--plugin-dir=name
指定客户端插件的目录。
--default-auth=name
指定默认的认证客户端插件。
--enable-cleartext-plugin
启用/禁用明文认证插件。
-M, --network-timeout
允许通过将max_allowed_packet设置为最大值，net_read_timeout/net_write_timeout设置为大值来转储大表。默认情况下启用，使用--skip-network-timeout禁用。
--show-create-table-skip-secondary-engine
控制是否转储SECONDARY_ENGINE CREATE TABLE子句。对不支持服务器端选项的旧服务器没有影响。
--compression-algorithms=name
在服务器/客户端协议中使用的压缩算法。有效值是任何'zstd'、'zlib'、'uncompressed'的组合。
--zstd-compression-level=#
在客户端/服务器协议中使用的此压缩级别，如果--compression-algorithms=zstd。有效范围是1到22（包括）。默认值为3。
--skip-generated-invisible-primary-key
控制是否转储生成的不可见主键和键列。
--init-command=name
连接到MySQL服务器时要执行的单个SQL命令。在重新连接时将自动重新执行。
--init-command-add=name
添加要在连接到MySQL服务器时执行的SQL命令到列表中。在重新连接时将自动重新执行。
--ignore-views
跳过转储表视图。

总结

  MysqlDump是MySQL 数据库管理系统中一个非常有用的工具，适用于各种备份和迁移场景。尽管它在处理大型数据库时可能存在性能和一致性问题，但其简单性和广泛支持使其在许多情况下仍然是首选工具。通过合理配置选项和参数，MysqlDump可以满足大多数 MySQL 数据库的备份和恢复需求。

公众号内直接回复加群也可以,扫码加群也可以，觉得帖子写的不错，点点关注，点点赞，多多转载。

加入数据库技术交流群：

进群福利：

1、知识共享与学习机会

2、问题求助与解决支持

3、技术沉淀和分享平台

4、及时了解行业动态