MySQL 数据库备份命令之 mysqldump 详解

mysqldump 是 MySQL 自带的逻辑备份工具，备份原理是连接到需要备份的 MySQL 服务导出 SQL insert 语句。

实验平台：CentOS 7.4

mysqldump version: 10.13

• 一、mysqldump 命令详解
• 二、使用案例

一、mysqldump 命令详解

$ mysqldump --help

--print-defaults            #显示默认参数项
--no-defaults               #除了 login file 不从任何选项文件中读取默认选项
--defaults-file=#           #从指定文件读取默认选项
--login-path=#              #指定 login file 路径
  -A, --all-databases       #备份所有库
  -Y, --all-tablespaces     #备份全部表空间
  -y, --no-tablespaces      #不导出任何表空间
  --add-drop-database       #每个库创建之前添加 drop 数据库语句
  --add-drop-table          #每个表创建之前添加 drop 数据表语句(默认为打开状态，使用 --skip-add-drop-table 取消选项)
  --add-drop-trigger  Add a DROP TRIGGER before each create.
  --add-locks               #在每个表导出之前增加 LOCK TABLES 并且之后 UNLOCK TABLE。(默认为打开状态，使用 --skip-add-locks 取消选项)
  --allow-keywords          #允许创建是关键词的列名字。这由表名前缀于每个列名做到
  --apply-slave-statements  #在 CHANGE MASTER 前添加 STOP SLAVE，并且在导出的最后添加 START SLAVE
  --bind-address=name IP address to bind to.
  --character-sets-dir=name #字符集文件的目录
  -i, --comments            #附加注释信息。默认为打开，(可以用 --skip-comments 取消)
  --compatible=name         #导出的数据将和其它数据库或旧版本的 MySQL 相兼容；可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，要使用几个值，用逗号将它们隔开。并不保证能完全兼容，而是尽量兼容。
  --compact                 #导出更少的输出信息(用于调试). 去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset.
  -c, --complete-insert     #使用完整的 insert 语句(包含列名称)。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败
  -C, --compress            #在客户端和服务器之间启用压缩传递所有信息
  -a, --create-options      #在 CREATE TABLE 语句中包括所有 MySQL 特性选项。(默认为打开状态，使用 --skip-create-options 关闭)
  -B, --databases           #指定需要备份的库名，跟库名
  -E, --events              #导出转储事件
  -e, --extended-insert     #使用具有多个 VALUES 列的 INSERT 语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用 --skip-extended-insert 关闭
  --fields-terminated-by=name #导出文件中忽略给定字段。与 --tab 选项一起使用，不能用于 --databases 和 --all-databases 选项
  --fields-enclosed-by=name #输出文件中的各个字段用给定字符包裹。与 --tab 选项一起使用，不能用于 --databases 和 --all-databases 选项          
  --fields-optionally-enclosed-by=name #输出文件中的各个字段用给定字符选择性包裹。与 --tab 选项一起使用，不能用于 --databases 和 --all-databases 选项
  --fields-escaped-by=name  #输出文件中的各个字段忽略给定字符。与 --tab 选项一起使用，不能用于 --databases 和 --all-databases 选项
  -F, --flush-logs          #开始导出之前刷新日志;假如一次导出多个数据库(使用选项 --databases 或者 --all-databases)，将会逐个数据库刷新日志。除使用 --lock-all-tables 或者 --master-data 外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用 --lock-all-tables 或者 --master-data 和 --flush-logs
  --flush-privileges        #在导出 mysql 数据库之后，发出一条FLUSH  PRIVILEGES 语句。为了正确恢复，该选项应该用于导出 mysql 数据库和依赖 mysql 数据库数据的任何时候
  -f, --force               #在导出过程中忽略出现的 SQL 报错，失败也会继续导出。
  -?, --help                #打印 help 信息
  --hex-blob                #使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有 BINARY、VARBINARY、BLOB
  -h, --host=name           #指定 MySQL 服务的 IP 地址
  --ignore-table=name       #不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名
  --include-master-host-port #在 --dump-slave 产生的 'CHANGE  MASTER TO..' 语句中增加 'MASTER_HOST=<host>，MASTER_PORT=<port>'
  --insert-ignore           #在插入行时使用 INSERT IGNORE 语句
  --lines-terminated-by=name #输出文件的每行用给定字符串划分。与 --tab 选项一起使用，不能用于 --databases 和 --all-databases选项
  -x, --lock-all-tables     #提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项
  -l, --lock-tables         #开始导出前，锁定所有表
  --log-error=name          #附加警告和错误信息到给定文件
  --master-data[=#]         选项有3个可用值，0、 1 、2，这三个值分别表示不同的含义
为0：表示在使用 mysqldump 进行备份时，不记录对应二进制日志文件位置，将此值显式的设置为 0 与不使用此选项的效果相同。
为1：表示在使用 mysqldump 进行备份时，记录对应二进制日志文件位置，此值为默认值，也就是说，使用 --master-data 与使用 --master-data=1 的效果相同，如果将此选项的值设置为1，则会在备份文件中生成对应的 "CHANGE MASTER TO" 语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的 position(位置)，生成此语句的目的是，在主从复制结构中的"从服务器"中通过备份sql还原数据以后，告诉"从库"，从"主库"的二进制日志文件中的哪个位置开始"同步"。如果我们没有使用主从复制结构，同时又想要在备份时记录二进制日志文件的 position，则可以将此选项的值设置为 2。
为2：表示在使用 mysqldump 进行备份时，记录对应二进制日值文件的位置，如果将此选项的值设置为 2，则会在备份文件中生成对应的 "CHANGE MASTER TO" 语句，此语句中标明了备份开始时二进制日志的前缀名以及其所处的 position(位置)，但是 "CHANGE MASTER TO" 语句将会被注释，与此值为1时不同，此选项值为1时，"CHANGE MASTER TO"语句不会被注释，此选项值为2时，"CHANGE MASTER TO"语句会被注释，所以，如果只是单纯的为了记录备份时的二进制日志文件位置，那么将此选项值设置为2即可。
  --max-allowed-packet=#    #服务器发送和接受的最大包长度
  --net-buffer-length=#     #TCP/IP 和 socket 连接的缓存大小
  --no-autocommit           #使用 autocommit/commit 语句包裹表
  -n, --no-create-db        #只导出数据，而不添加 CREATE DATABASE 语句
  -t, --no-create-info      #只导出数据，而不添加 CREATE TABLE 语句
  -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            #指定 MySQL 服务的密码
  -P, --port=#              #指定 MySQL 服务的端口号
  --protocol=name           #使用的连接协议，包括：tcp, socket, pipe, memory
  -q, --quick               #不缓冲查询，直接导出到标准输出。默认为打开状态，使用 --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 禁用
  --dump-date               #将导出时间添加到输出文件中。默认为打开状态，使用 --skip-dump-date 禁用
  --skip-opt                #禁用 –opt
  -S, --socket=name         #指定连接 mysql 的 socket 文件位置，默认路径 /tmp/mysql.sock
  --secure-auth             #使用旧版本 (pre-4.1.1) 以下的协议拒绝连接到 mysql 服务 (默认打开，使用 --skip-secure-auth 禁用)
  --ssl                     #启用 SSL 连接
  --ssl-ca=name             #指定 PEM 格式的 CA 文件，与 -ssl 连用
  --ssl-capath=name         #CA 目录，与 -ssl 连用
  --ssl-cert=name           #PEM 格式的 X509 证书，与 -ssl 连用
  --ssl-cipher=name         #使用 SSL 加密，与 -ssl 连用
  --ssl-key=name            #PEM 格式的 X509 密钥，与 -ssl 连用
  --ssl-crl=name            #证书吊销列表，与 -ssl 连用
  --ssl-crlpath=name        #证书吊销列表路径，与 -ssl 连用
  --ssl-verify-server-cert  #验证服务器在其证书中的“公共名称”连接时使用的主机名
  --ssl-mode=name           #SSL 连接模式
  -T, --tab=name      #为每个表在给定路径创建 tab 分割的文本文件。注意：仅仅用于 mysqldump 和 mysqld 服务器运行在相同机器上。注意使用 --tab 不能指定 --databases 参数
  --tables            #覆盖 --databases (-B) 参数，指定需要导出的表名，在后面的版本会使用 table 取代 tables
  --triggers          #导出触发器。该选项默认启用，用 --skip-triggers 禁用
  --tz-utc            #在导出顶部设置时区 TIME_ZONE='+00:00' ，以保证在不同时区导出的 TIMESTAMP 数据或者数据被移动其他时区时的正确性 (默认打开，使用 --skip-tz-utc 禁用)
  -u, --user=name     #指定 MySQL 服务的用户
  -v, --verbose       #输出多种平台信息
  -V, --version       #打印版本信息
  -w, --where=name    #只转储给定的 WHERE 条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来
  -X, --xml           #导出 XML 格式
  --plugin-dir=name   #客户端插件的目录，用于兼容不同的插件版本
  --default-auth=name #客户端插件默认使用权限
  --enable-cleartext-plugin #启用/禁用明文验证插件
  --single-transaction  #保证导出的一致性状态

二、使用案例

1.备份所有库

$ mysqldump -uroot -p --all-databases > /data/backup/all-databases.sql

2.指定 blog 库，可添加多个 database name 来备份多个库

$ mysqldump -uroot -p blog > /data/backup/blog.sql

3.只导出表结构

$ mysqldump -uroot -p -d blog > /data/backup/blog.sql

4.只导出数据

$ mysqldump -uroot -p -t blog > /data/backup/blog.sql

5.排除库里指定的表 table1,table2

$ mysqldump -uroot -p blog --ignore-table=blog.table1 --ignore-table=blog.table2 > /data/backup/blog.sql

6.恢复(导入)库

$ mysql -uroot -p blog < /data/backup/blog.sql
或
> use blog;
> source /data/backup/blog.sql;

Over~

689 0 2020-11-08

打赏一下，各位朋友请随意打赏！

×

如果觉得我的文章对你有用，请随意赞赏

取消