• 运维特工,战胜心魔!!
  • 有些事情其实没有那么难,只是我们感觉难,走出第一步!
  • 你所浪费的今天,是昨天死去的人奢望的明天!!!
  • 欢迎访问 unixfbi.com 运维特工社区http://unixfbi.com/
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧
  • 运维特工:http://www.unixfbi.com
  • 人生没有白走的路,每一步都算数!!
  • 空杯心态,沉淀自己!

mysqldump 参数和使用方法介绍

MySQL unixfbi 2年前 (2018-01-11) 3774次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

mysqldump 参数和使用方法介绍

mysqldump 是 MySQL 数据库自带的一款命令行工具,mysqldump 属于单线程,功能是非常强大的,不仅常被用于执行数据备份任务,甚至还可以用于数据迁移。

备份粒度相当灵活,既可以针对整个 MySQL 服务,也可以只备份某个或者某几个 DB,或者还可以指定只备份某个或者某几个表对象,甚至可以实现只备份表中某些符合条件的记录。

mysqldump 命令创建的是逻辑备份,它输出的结果集有两种格式:一种是将数据转换成标准 SQL 语句(一堆 CREATE , DROP ,INSERT 等语句);另一种是将数据按照指定的分隔符,输出成定界格式的平面文件。

mysqldump 使用参数很多,但是实际上经常用到的并没有多少。下面我们来介绍一下这些参数:

mysqldump 具体有多少参数,我们可以使用

$ mysqldump  --help

命令查看

1.常用参数

  • -?, --help: 显示帮助信息,英文的;
  • -u, --user: 指定连接的用户名;
  • -p, --password: 指定用户的密码,可以交互输入密码;
  • -S , --socket: 指定 socket 文件连接,本地登录才会使用。
  • -h, --host: 指定连接的服务器名称或者 IP。
  • -P, --port=: 连接数据库监听的端口。
  • --default-character-set: 设置字符集,默认是 UTF8。
  • -A, --all-databases: 导出所有数据库。不过默认情况下是不会导出 information_schema 库。
  • -B, --databases: 导出指定的某个/或者某几个数据库,参数后面所有名字参量都被看作数据库名,包含 CREATE DATABASE 创建库的语句。
  • --tables: 导出指定表对象,参数格式为“库名 表名”,默认该参数将覆盖-B/--databases参数。
  • -w, --where: 只导出符合条件的记录。
  • -l, --lock-tables: 默认参数,锁定读取的表对象,想导出一致性备份的话最后使用该参数,会导致无法对表执行写入操作。
  • --single-transaction:
    该选项在导出数据之前提交一个 BEGIN SQL 语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储 引擎,仅 InnoDB。本选项和--lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交,使用参数--single-transaction会自动关闭该选项。
    在 InnoDB 导出时会建立一致性快照,在保证导出数据的一致性前提下,又不会堵塞其他会话的读写操作,相比--lock-tables 参数来说锁定粒度要低,造成的影响也要小很多。指定这个参数后,其他连接不能执行 ALTER TABLE、DROP TABLE 、RENAME TABLE、TRUNCATE TABLE 这类语句,事务的隔离级别无法控制 DDL 语句。
  • -d, --no-data: 只导出表结构,不导出表数据。
  • -t, --no-create-info: 只导出数据,而不添加 CREATE TABLE 语句。
  • -f, --force: 即使遇到 SQL 错误,也继续执行,功能类似 Oracle exp 命令中的 ignore 参数。
  • -F, ---flush-logs: 在执行导出前先刷新日志文件,视操作场景,有可能会触发多次刷新日志文件。一般来说,如果是全库导出,建议先刷新日志文件,否则就不用了。
  • --master-data[=#]: 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有 RELOAD 权限,并且必须启用二进制日志。如果该选项值等于 1,位置和文件名被写入 CHANGE MASTER 语句形式的转储输出,如果你使用该 SQL 转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始。如果选项值等于 2,CHANGE MASTER 语句被写成 SQL 注释。如果 value 被省略,这是默认动作。
    --master-data选项会启用--lock-all-tables,除非还指定--single-transaction(在这种情况下,只在刚开始转储时短时间获得全局读锁定。又见--single-transaction。在任何一种情况下,日志相关动作发生在转储时。该选项自动关闭--lock-tables。
    所以,我在 INNODB 引擎的数据库备份时,我会同时使用--master-data=2 和 --single-transaction 两个选项。
  • -x, --lock-all-tables: 在导出任务执行期间锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局锁定,并且自动关闭--single-transaction--lock-tables 选项。这个参数副作用比较大,这是全库锁定,备份执行过程中,该库无法进行读写操作,不是所有业务场景都能接受的。请慎用。
  • -n, --no-create-db: 不生成建库的语句 CREATE DATABASE ... IF EXISTS,即使指定--all-databases 或--databases 这类参数。
  • --triggers: 导出表的触发器脚本,默认就是启用状态。使用--skip-triggers 禁用它。
  • -R, --routines: 导出存储过程以及自定义函数。
    在转储的数据库中转储存储程序(函数和程序)。
  • -E, --events: 输出 event。
  • --ignore-table: 指定的表对象不做导出,参数值的格式为[db_name,tblname],注意每次只能指定一个值,如果有多个表对象都不进行导出操作的话,那就需要指定多个--ignore-table 参数,并为每个参数指定不同的参数值。
  • --add-drop-database: 在任何创建库语句前,附加 DROP DATABASE 语句。
  • --add-drop-table: 在任何创建表语句前,附加 DROP TABLE 语句。这个参数是默认启用状态,可以使用-- skip-add-drop-table参数禁用该参数。
  • --add-drop-trigger: 创建任何触发器前,附加 DROP TRIGGER 语句。
  • --add-locks: 在生成的 INSERT 语句前附加 LOCK 语句,该参数默认是启用状态。使用--skip-add-locks 参数禁用。
  • -K, --disable-keys: 在导出的文件中输出 '/!40000 ALTER TABLE tb_name DISABLE KEYS */; 以及
    '/
    !40000 ALTER TABLE tb_name ENABLE KEYS */; ' 等信息。这两段信息会分别放在 INSERT 语句的前后,也就是说,在插入数据前先禁用索引,等完成数据插入后再启用索引,目的是为了加快导入的速度。该参数默认就是启用状态。可以通过--skip-disable-keys 参数来禁用。
  • --opt: 功能等同于同时指定了 --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, 以及 --disable-keys 这些参数。默认就是启用状态。使用--skip-opt 来禁用该参数。
  • --skip-opt: 禁用--opt 选项,相当于同时禁用 --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, 及 --disable-keys 这些参数。
  • -q, --quick: 导出时不会将数据加载到缓存,而是直接输出。默认就是启用状态。可以使用--skip-quick 来禁用该参数。

2.mysqldump 默认参数

mysqldjmp 默认使用的参数由以下几个:
--opt
--add-drop-table
--add-locks
-i,--comments
-a,--create-options
-e, --extended-insert
-l, --lock-tables
-q, --quick
-K, --disable-keys
-Q, --quote-names
--dump-date
--ssl
--triggers
--tz-utc

上面这些参数,执行 mysqldump 命令的时候默认就会带上的。

3.mysqldump 常用方法

(1) 获取一个完整备份
不锁库备份

$ mysqldump -uusername -p --triggers --routines --events -A -B --single-transaction --master-data=2 > backup.$(date +%F).sql

(2) 导出指定库

$ mysqldump -uusername -p -B dbname > backup.$(date +%F).sql

如果是导出单库也可以不使用-B 参数,无非就是没有创建库的语句。
如果是多个库可以使用如下命令,但是必须使用-B 参数

$ mysqldump -uusername -p -B DB1 DB2 DB3 > backup.$(date +%F).sql

(3) 导出指定表的数据和结构

$ mysqldump -uusername -p DBNAME table1 table2 > tablename.sql    

或者使用 --tables 参数

$ mysqldump -uusername -p --tables DBNAME table1 table2 > backup.$(date +%F).sql  

或者

$ mysqldump  -uusername -p DBNAME  --tables table1 table2 table3 > tablename.sql

(4) 导出指定表的结构
不包含数据

$ mysqldump -ubackup -p --no-data  DBNAME table1 table2 > backup.$(date +%F).sql

或者使用--tables 参数

$ mysqldump -ubackup -p --no-data DBNAME  --tables table1 table2 > backup.$(date +%F).sql

或者

$ mysqldump -ubackup -p --no-data  --tables DBNAME table1 table2 > backup.$(date +%F).sql

(5) 导出指定表的数据
不包含表结构

$ mysqldump  -uusername -p --no-create-info DBNAME  table1 table2 table3 > backup.$(date +%F).sql

或者使用--tables 参数

$ mysqldump  -uusername -p --no-create-info DBNAME --tables table1 table2 table3 > backup.$(date +%F).sql

或者

$ mysqldump  -uusername -p --no-create-info  --tables DBNAME table1 table2 table3 > backup.$(date +%F).sql

(6) 导出整个数据库结构 (包括表结构)
不包含数据

$ mysqldump -uusername -p --no-data DBNAME > backup.$(date +%F).sql

(7) 导出数据库表结构和数据时排除某些表
使用 --ignore-table 参数

$ mysqldump -uusername -p  --single-transaction --master-data=2 --add-drop-database  -B DBNAME --ignore-table=DBNAME.table1 --ignore-table=DBNAME.table2 > backup.$(date +%F).sql

(8) 导出数据直接压缩

$ mysqldump -uusername -p -B DBNAME | gzip > backup.sql.gz

解压命令:

$ gunzip backup.sql.gz

本文出自 “运维特工” 博客,转载请务必保留原文链接 和 http://www.unixfbi.com


UnixFBI 运维特工 , 版权所有丨如未注明 , 均为原创丨 转载请务必注明原文链接http://www.unixfbi.com/336.html
喜欢 (0)
[支付宝]
分享 (0)
unixfbi
关于作者:
运维工程师一枚,做有价值的事情!
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址