一、命令格式详解

1.1 语法格式

mysqldump [选项] 数据库[表]

1.2 常用参数

–all-databases, -A：备份所有数据库。 –databases, -B：用于备份多个数据库。如果不使用此选项，mysqldump 将使用第一个名称参数作为数据库名称，使用下一个参数作为数据库名称。表名。使用此选项，mysqldum 将每个名称视为数据库名称。 –force, -f: 如果发现SQL错误，忽略它们并继续备份–host=hostname, -h hostname: 备份的主机名，默认为localhost –no-data , -d: 只导出表结构–password[=password], -p[password]: 密码–port=port_num, -P port_num: 指定TCP/IP 连接的端口号–quick , -q: 快速导出–tables : 覆盖–数据库或-B 选项。以下参数被视为表名称–user=user_name, -u user_name: 用户名–xml, -X: 导出到XML 文件

1.3 完整参数

–所有数据库， -A

导出所有数据库。

mysqldump -uroot -p –所有数据库

–所有表空间，-Y

导出所有表空间。

mysqldump -uroot -p –所有数据库–所有表空间

–无表空间，-y

不导出表空间信息。

mysqldump -uroot -p –所有数据库–无表空间

–添加-删除-数据库

在创建每个数据库之前添加删除数据库语句。

mysqldump -uroot -p –all-databases –add-drop-database

–添加-删除-表

在创建每个数据表之前添加删除数据表语句。 （默认打开。使用–skip-add-drop-table 取消选项）

mysqldump -uroot -p –all-databases （默认添加drop 语句）

mysqldump -uroot -p –all-databases –skip-add-drop-table （取消drop 语句）

–添加锁

在导出每个表之前增加LOCK TABLES，然后再UNLOCK TABLE。 （默认打开；使用–skip-add-locks 取消选项）

mysqldump -uroot -p –all-databases （默认添加LOCK 语句）

mysqldump -uroot -p –all-databases –skip-add-locks （取消LOCK 语句）

–允许关键字

允许创建作为关键字的列名称。这是通过在每个列名称前加上表名称前缀来完成的。

mysqldump -uroot -p –all-databases –allow-keywords

–应用从属语句

在导出末尾的“CHANGE MASTER”之前添加“STOP SLAVE”，并在导出末尾添加“START SLAVE”。

mysqldump -uroot -p –all-databases –apply-slave-statements

–字符集目录

字符集文件目录

mysqldump -uroot -p –all-databases –character-sets-dir=/usr/local/mysql/share/mysql/charsets

– 评论

附加注释信息。默认情况下此功能处于启用状态，可以使用–skip-comments 取消。

mysqldump -uroot -p –all-databases （默认日志注释）

mysqldump -uroot -p –all-databases –skip-comments （取消注释）

– 兼容的

导出的数据与其他数据库或旧版本的MySQL 兼容。值可以包括：ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options。

要使用多个值，请用逗号分隔值。尽管我们不保证完全兼容，但我们会尽力保持兼容性。

mysqldump -uroot -p –all-databases –compatibility=ansi

– 袖珍的

导出较少的输出信息（用于调试目的）。删除注释、标题和尾随结构。可用选项： –skip-add-drop-table –skip-add-locks –skip-comments –skip-disable-keys

mysqldump -uroot -p –all-databases –compact

–完整插入，-c

使用完整的插入语句（包括列名）。这样做可以提高插入效率，但会受到max_allowed_packet 参数的影响，可能会导致插入错误。

mysqldump -uroot -p –all-databases –complete-insert

–压缩，-C

启用压缩以在客户端和服务器之间传递所有信息。

mysqldump -uroot -p –所有数据库–compress

–创建选项，-a

在CREATE TABLE 语句中包含所有MySQL 功能选项。 （默认打开）

mysqldump -uroot -p –所有数据库

–数据库，-B

导出多个数据库。该参数后面的所有名称参数都被视为数据库名称。

mysqldump -uroot -p –数据库测试mysql

– 调试

输出调试信息以进行调试。默认值为：d:t:o,/tmp/mysqldump.trace

mysqldump -uroot -p –所有数据库–debug

mysqldump -uroot -p –all-databases –debug=”d:t:o,/tmp/debug.trace”

–调试检查

检查内存和打开文件的使用情况并退出。

mysqldump -uroot -p –all-databases –debug-check

– 调试信息

输出调试信息并退出

mysqldump -uroot -p –all-databases –debug-info

–默认字符集

设置默认字符集。默认值为utf8。

mysqldump -uroot -p –all-databases –default-character-set=latin1

–延迟插入

使用INSERT DELAYED 方法导出数据

mysqldump -uroot -p –all-databases –layed-insert

–删除主日志

备份master后删除日志。该参数自动启用–master-data。

mysqldump -uroot -p –all-databases –delete-master-logs

–禁用键

对于每个表，使用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */; 和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */; 语句引用INSERT 语句。这使得导入转储文件的速度更快，因为索引是在插入所有行之后创建的。该选项仅适用于MyISAM 表，并且默认情况下处于选中状态。

mysqldump -uroot -p –所有数据库

–转储奴隶

选择此选项会将主二进制日志位置和文件名添加到要将数据导出到的文件中。如果设置为1，则使用CHANGE MASTER 命令输出数据文件。将其设置为2 会在命令前添加描述性信息。除非指定了–single-transaction，否则此选项将打开–lock-all-tables。此选项会自动关闭–lock-tables 选项。默认值为0。

mysqldump -uroot -p –all-databases –dump-slave=1

mysqldump -uroot -p –all-databases –dump-slave=2

–事件，-E

导出事件。

mysqldump -uroot -p –all-databases –events

–扩展插入，-e

对多个VALUES 列使用INSERT 语法。这将导致导出文件更小并且导入速度更快。默认开启。要取消该选项，请使用–skip-extended-insert。

mysqldump -uroot -p –所有数据库

mysqldump -uroot -p –all-databases–skip-extended-insert （取消选项）

——球场终结者

导出文件中将忽略指定字段。与–tab 选项一起使用，而不是与–databases 和–all-databases 选项一起使用。

mysqldump -uroot -p test test –tab=”/home/mysql” –fields-termminate-by=”#”

——周围都是田野

输出文件中的每个字段都将用指定的字符包裹。与–tab 选项一起使用，而不是与–databases 和–all-databases 选项一起使用。

mysqldump -uroot -p test test –tab=”/home/mysql” –fields-enheld-by=”#”

–fields-可选-封闭-by

输出文件中的各个字段可以选择用指定字符包裹。与–tab 选项一起使用，而不是与–databases 和–all-databases 选项一起使用。

mysqldump -uroot -p test test –tab=”/home/mysql” –fields-enheld-by=”#” –fields-optionally-enheld-by=”#”

–字段转义者

输出文件中的各个字段会忽略指定的字符。与–tab 选项一起使用，而不是与–databases 和–all-databases 选项一起使用。

mysqldump -uroot -p mysql 用户–tab=”/home/mysql” –fields-escaped-by=”#”

–刷新日志

请在开始导出之前刷新日志。

注意：如果您一次导出多个数据库（使用–databases 或–all-databases 选项），则每个数据库的日志都会更新。除非您使用–lock-all-tables 或–master-data。在这种情况下，日志被刷新一次，并且所有表同时被锁定。因此，如果要同时导出和刷新日志，应该使用–lock-all-tables或–master-data和–flush-logs。

mysqldump -uroot -p –all-databases –flush-logs

–闪存权限

导出mysql 数据库后，发出FLUSH PRIVILEGES 语句。为了正确恢复，每当导出mysql 数据库并依赖mysql 数据库数据时都必须使用此选项。

mysqldump -uroot -p –all-databases –flush-privileges

– 力量

忽略导出过程中发生的SQL 错误。

mysqldump -uroot -p –all-databases –force

– 帮助

显示帮助消息并退出。

mysqldump –帮助

–十六进制斑点

使用十六进制格式导出二进制字符串字段。如果您有二进制数据，则应该使用此选项。受影响的字段类型为BINARY、VARBINARY 和BLOB。

mysqldump -uroot -p –all-databases –hex-blob

–主机，-h

需要导出的主机信息

mysqldump -uroot -p –host=localhost –all-databases

–忽略表

指定的表将不会被导出。如果要指定要忽略的多个表，则必须重复多次，一次一个表。每个表都必须有数据库和表名。示例： –ignore-table=database.table1 –ignore-table=database.table2 ……

mysqldump -uroot -p –host=localhost –all-databases –ignore-table=mysql.user

–include-master 主机端口

将“MASTER_HOST=, MASTER_PORT=”添加到–dump-slave 生成的“CHANGE MASTER TO.”语句中。

mysqldump -uroot -p –host=localhost –all-databases –include-master-host-port

–插入-忽略

要插入行，请使用INSERT IGNORE 语句。

mysqldump -uroot -p –host=localhost –all-databases –insert-ignore

— 行终止符

输出文件的每一行都由指定的字符串分隔。与–tab 选项一起使用，而不是与–databases 和–all-databases 选项一起使用。

mysqldump -uroot -p –host=localhost test test –tab=”/tmp/mysql” –lines-termminate-by=”##”

–锁定所有表，-x

发送请求锁定所有数据库中的所有表，以保证数据一致性。这是一个全局读锁，可以使用–single-transaction 和–lock-tables 选项自动关闭。

mysqldump -uroot -p –host=localhost –all-databases –lock-all-tables

–锁表，-l

在开始导出之前锁定所有表。使用READ LOCAL 锁定表以允许并行插入到MyISAM 表中。对于启用事务的表（例如InnoDB 或BDB），–single-transaction 是更好的选择，因为根本不需要锁定表。

请注意，导出多个数据库时，–lock-tables 会为每个数据库单独锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

mysqldump -uroot -p –host=localhost –all-databases –lock-tables

–记录错误

将警告和错误消息添加到指定文件

mysqldump -uroot -p –host=localhost –all-databases –log-error=/tmp/mysqldump_error_log.err

– 主要的数据

此选项将二进制日志位置和文件名添加到输出文件中。如果为1，则输出CHANGE MASTER命令，如果为2，则在输出CHANGE MASTER命令之前添加注释信息。该选项打开–lock-all-tables 选项，除非还指定了–single-transaction （在这种情况下，在导出开始时会获取一个短全局读锁；否则，请参阅-transaction 下的–single）选项）。此选项会自动关闭–lock-tables 选项。

mysqldump -uroot -p –host=localhost –all-databases –master-data=1;

mysqldump -uroot -p –host=localhost –all-databases –master-data=2;

–最大允许数据包

服务器将发送和接收的最大数据包长度。

mysqldump -uroot -p –host=localhost –all-databases –max_allowed_packet=10240

–net_buffer_length

TCP/IP 和套接字连接的缓存大小。

mysqldump -uroot -p –host=localhost –all-databases –net_buffer_length=1024

–没有自动提交

使用自动提交/提交语句包装表。

mysqldump -uroot -p –host=localhost –all-databases –no-autocommit

–不创建数据库，-n

仅导出数据而不添加任何CREATE DATABASE 语句。

mysqldump -uroot -p –host=localhost –all-databases –no-create-db

–无创建信息，-t

仅导出数据而不添加任何CREATE TABLE 语句。

mysqldump -uroot -p –host=localhost –all-databases –no-create-info

–无数据，-d

不导出任何数据，仅导出数据库表结构。

mysqldump -uroot -p –host=localhost –all-databases –no-data

–无设置名称，-N

相当于–skip-set-charset

mysqldump -uroot -p –host=localhost –all-databases –no-set-names

– 选择

相当于–add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, –disable-keys 。该选项默认启用，可以使用–skip-opt 禁用。

mysqldump -uroot -p –host=localhost –all-databases –opt

— 按主要排序

按主键或第一个唯一键（如果存在）对每个表中的记录进行排序。对于将MyISAM 表导出到InnoDB 表很有用，但导出过程需要更长的时间。

mysqldump -uroot -p –host=localhost –all-databases –order-by-primary

–密码，-p

连接数据库的密码

–pipe（在Windows 系统上可用）

使用命名管道连接到mysql

mysqldump -uroot -p –host=localhost –all-databases –pipe

–端口，-P

连接数据库端口号

– 协议

使用的连接协议包括TCP、套接字、管道和内存。

mysqldump -uroot -p –host=localhost –all-databases –protocol=tcp

–快速，-q

查询不会被缓冲并直接导出到标准输出。默认开启。使用–skip-quick 取消此选项。

mysqldump -uroot -p –host=localhost –all-databases

mysqldump -uroot -p –host=localhost –all-databases –skip-quick

–引用名称，-Q

使用(`) 引用表名和列名。默认开启。要取消此选项，请使用–skip-quote-names。

mysqldump -uroot -p –host=localhost –all-databases

mysqldump -uroot -p –host=localhost –all-databases –skip-quote-names

– 交换

使用REPLACE INTO 而不是INSERT INTO。

mysqldump -uroot -p –host=localhost –all-databases –replace

–结果文件，-r

直接输出到指定文件。此选项应用于使用回车/换行对(\\r\\n) 进行换行的系统（DOS、Windows 等）。此选项可确保仅使用一行。

mysqldump -uroot -p –host=localhost –all-databases –result-file=/tmp/mysqldump_result_file.txt

–例行程序，-R

导出存储过程和自定义函数。

mysqldump -uroot -p –host=localhost –all-databases –routines

–设置字符集

将“SET NAMESdefault_character_set”添加到输出文件中。默认开启。使用–skip-set-charset 选项将其关闭。

mysqldump -uroot -p –host=localhost –all-databases

mysqldump -uroot -p –host=localhost –all-databases –skip-set-charset

–单笔交易

此选项在导出数据之前发送BEGIN SQL 语句。 BEGIN 不会阻塞应用程序并确保导出期间数据库的状态一致。仅适用于多版本存储引擎，并且仅适用于InnoDB。 LOCK TABLES 隐式提交待处理事务，因此该选项和–lock-tables 选项是互斥的。如果要导出大型表，还必须使用–qu。

ick 选项。
mysqldump -uroot -p –host=localhost –all-databases –single-transaction
–dump-date
将导出时间添加到输出文件中。默认为打开状态，使用–skip-dump-date关闭选项。
mysqldump -uroot -p –host=localhost –all-databases
mysqldump -uroot -p –host=localhost –all-databases –skip-dump-date
–skip-opt
禁用–opt选项.
mysqldump -uroot -p –host=localhost –all-databases –skip-opt
–socket,-S
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
mysqldump -uroot -p –host=localhost –all-databases –socket=/tmp/mysqld.sock
–tab,-T
为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。
mysqldump -uroot -p –host=localhost test test –tab=”/home/mysql”
–tables
覆盖–databases (-B)参数，指定需要导出的表名。
mysqldump -uroot -p –host=localhost –databases test –tables test
–triggers
导出触发器。该选项默认启用，用–skip-triggers禁用它。
mysqldump -uroot -p –host=localhost –all-databases –triggers
–tz-utc
在导出顶部设置时区TIME_ZONE=’+00:00′ ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
mysqldump -uroot -p –host=localhost –all-databases –tz-utc
–user, -u
指定连接的用户名。
–verbose, –v
输出多种平台信息。
–version, -V
输出mysqldump版本信息并退出
–where, -w
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
mysqldump -uroot -p –host=localhost –all-databases –where=” user=’root’”
–xml, -X
导出XML格式.
mysqldump -uroot -p –host=localhost –all-databases –xml
–plugin_dir
客户端插件的目录，用于兼容不同的插件版本。
mysqldump -uroot -p –host=localhost –all-databases –plugin_dir=”/usr/local/lib/plugin”
–default_auth
客户端插件默认使用权限。
mysqldump -uroot -p –host=localhost –all-databases –default-auth=”/usr/local/lib/plugin/”

二、mysqldump常见的几个错误

2.1、提示命令未找到 -bash: mysqldump: 未找到命令

执行如下命令
mysqldump –opt -uroot -p12345678 –databases test> /data/db_bak/test223.zip可能会出现如下错误

解决方法
需要导入mysql的环境变量
查询导入的环境变量是否包含mysql
echo $PATH 在/etc/profile配置mysql系统环境变量,一般都是/usr/local/mysql/bin这个文件夹
export PATH=$PATH:/usr/local/mysql/bin#刷新环境变量后生效source /etc/profile

2.2、 the command line interface can be insecure

错误详情为：mysqldump: [Warning] Using a password on the command line interface can be insecure。

原因：mysql版本大于5.6的话，增加的安全提醒机制，则会出现警告：Warning: Using a password on the command line interface can be insecure. 使用 mysqldump 命令备份 MySQL 数据库时，我们使用了 -p 参数带上了密码，这样虽然可以快速备份，但是密码会在命令行中明文可见，容易被窃取。为了更安全地备份数据库， MySQL5.6之后的版本会提醒使用 MySQL 配置文件中的加密方式来指定密码，这样可以避免密码泄露的风险。当然只是提醒，不影响具体的执行。
解决方法：
修改mysql配置文件，增加用户名和密码
vim /etc/my.cnf内容参考如下
[mysqldump]…user=rootpassword=”你的mysql密码”然后重启mysql数据库，执行mysqldump命令的时候不需要输入用户名密码即可
mysqldump –opt –databases test> /data/db_bak/test223.zip

2.3、Gotpacket bigger than ‘max_allowed_packet‘ bytes

大家进行MySQL数据库备份mysqldump报错Error 2020: Got packet bigger than ‘max_allowed_packet‘ bytes
比如执行如下命令：
mysqldump –opt -uroot -p12345678 –databases test> /data/db_bak/test223.zip可能会出现下图错误

解决方法：增加max_allowed_packet参数
mysqldump –opt -uroot -p12345678 –max_allowed_packet=512M –databases test> /data/db_bak/test223.zip

三、 常用的几个备份命令

#备份服务器所有的数据库包含数据mysqldump -uroot -p12345678 -A >/data/db_bak/2023_mysql_all_bak.sql#备份服务器所有的数据库仅包含表结构mysqldump -uroot -p123456 -A-d>/data/db_bak/2023bak.sql#备份服务器所有的数据库仅包含表数据mysqldump -uroot -p123456 -A-t>/data/db_bak/2023DataBak.sql#备份单个数据库的数据和结构mysqldump -uroot-p123456 test>/data/db_bak/test_bak.sql#备份单个数据库多个表的数据和结构mysqldump -uroot -p123456 test tb1 tb2>f:\test_table.sql