mysqldump-数据库备份程序

mysqldump-数据库备份程序

mysqldump 客户端程序执行逻辑备份,生成一组 SQL 语句,可以执行这些语句以重现原始数据库对象定义和表数据。

选项列表

mysqldump 的选项可以在命令行中指定,也可以在配置文件的 [mysqldump] 和 [client] 组中指定。

当指定选项时,顺序很重要,因为选项是从前往后处理的,如果有重复功能,前面的选项会被覆盖。

连接选项

  • –bind-address=ip_address
    • 当有多个网络接口时,可以用这个选项指定要连接的服务端
  • –compress, -C

    已弃用。

    • 尽可能压缩客户端和服务器之间发送的所有信息
  • –compression-algorithms=value
    • 连接时的压缩算法,默认值是 uncompressed
  • –default-auth=plugin
    • 指定身份验证插件
  • –enable-cleartext-plugin
    • 启用 mysql_clear_password 明文身份验证插件
  • –get-server-public-key
    • 从服务器请求基于 RSA 密钥对的密码交换所需的公共密钥。此选项适用于使用 caching_sha2_password 身份验证插件进行身份验证的客户端。
  • –host=host_name, -h host_name
    • 主机名
  • –login-path=name
    • 指定登录路径文件 .mylogin.cnf
  • –password[=password], -p[password]
    • 密码
  • –pipe, -W
    • 在 Windows 上,使用命名管道连接到服务器。
  • –plugin-dir=dir_name
    • 指定插件目录
  • –port=port_num, -P port_num
    • 连接端口
  • –protocol={TCP|SOCKET|PIPE|MEMORY}
    • 连接协议
  • –server-public-key-path=file_name
    • PEM 格式的文​​件的路径名,其中包含服务器用于基于 RSA 密钥对的密码交换所需的公用密钥的客户端副本。此选项适用于使用 sha256_password或caching_sha2_password 身份验证插件进行身份验证的客户端。
  • –socket=path, -S path
    • 连接使用的 socket 文件路径
  • –ssl*
    • 以 –ssl 开头的选项指定是否使用 SSL 连接到服务器,并指示在何处查找 SSL 密钥和证书。
  • –ssl-fips-mode={OFF|ON|STRICT}
    • 是否在客户端上启用 FIPS 模式
  • –tls-ciphersuites=ciphersuite_list
    • 使用 TLSv1.3 的加密连接的允许密码列表
  • –tls-version=protocol_list
    • 加密连接允许的 TLS 协议
  • –user=user_name, -u user_name
    • 用户
  • –zstd-compression-level=level
    • zstd 算法的压缩级别,允许的压缩级别是1-22,默认是3

配置文件选项

  • –defaults-extra-file=file_name
    • 额外的配置文件。在全局配置文件之后,用户配置文件之前会读取此文件。
  • –defaults-file=file_name
    • 仅读取给定的配置文件 (.mylogin.cnf 例外,这个配置文件总是被读取)
  • –defaults-group-suffix=str
    • 选项组名称的后缀

      如果 –defaults-group-suffix=_other`,mysql 也会读取 [client_other] 和 [mysql_other]

  • –no-defaults

    • 不读取任何配置文件 ( .mylogin.cnf 例外)
  • –print-defaults
    • 打印输出程序名称和所有读取的配置文件内容

DDL 选项

  • –add-drop-database
    • 建库前先删库。在每个 CREATE DATABASE 语句之前增加一个 DROP DATABASE 语句
    • mysql 库是用户无法删除的,如果指定了此选项同时需要备份 mysql 库,则会产生错误。所以,此选项不能和 –all-databases 一起使用
  • –add-drop-table
    • 在每个 CREATE TABLE 语句之前增加一个 DROP TABLE 语句
  • –add-drop-trigger
    • 在每个 CREATE TRIGGER 语句之前增加一个 DROP TRIGGER 语句
  • –all-tablespaces, -Y
    • ~~~向表备份文件中添加创建 NDB 表使用的任何表空间所需的所有 SQL 语句。此选项仅与 NDB 集群表相关。

      ~~~

  • –no-create-db, -n

    • 如果指定了 –databases 或 –all-databases 选项,则禁止在输出中包含 CREATE DATABASE 语句
  • –no-create-info, -t
    • 备份文件中不包含 CREATE TABLE 语句
  • –no-tablespaces, -y
    • 备份文件中不包含 CREATE LOGFILE GROUP 和 CREATE TABLESPACE 语句
  • –replace
    • 使用 REPLACE 语句,而不是 INSERT 语句

调试选项

  • –allow-keywords
    • 允许作为列名的关键字
  • –comments, -i
    • 在备份文件中记录注释,如程序版本、服务器版本、主机,默认已启用。使用 –skip-comments 禁用此功能
  • –debug[=debug_options], -# [debug_options]

    MySQL 的二进制发行版不是以此选项构建的。

    • 编写调试日志。典型的 debug_options 字符串是 d:t:o,file_name。默认值为 d:t:o,/tmp/mysql.trace。仅当 MySQL 是使用 WITH_DEBUG 构建的时,此选项才可用
  • –debug-check

    MySQL 的二进制发行版不是以此选项构建的。

    • 程序退出时,打印一些调试信息。仅当 MySQL 是使用 WITH_DEBUG 构建的时,此选项才可用。
  • –debug-info

    MySQL 的二进制发行版不是以此选项构建的。

    • 程序退出时,打印调试信息以及内存和 CPU 使用情况统计信息。仅当 MySQL 是使用 WITH_DEBUG 构建的时,此选项才可用。
  • –dump-date
    • 如果已启用 –comments,此选项会在备份完成时记录一个时间。默认已启用。可以使用 –skip-dump-date 禁用此功能。
      -- Dump completed on DATE
      
  • –force, -f
    • 强制执行 (即使报错也执行)
  • –log-error=file_name
    • 将错误和警告记录到指定的文件中,默认不记录。
  • –skip-comments
    • 禁用注释
  • –verbose, -v
    • 详细模式,输出更多信息。

帮助选项

  • –help, -?
    • 帮助
  • –version, -V
    • 显示版本

国际化选项

  • –character-sets-dir=dir_name
    • 字符集的安装路径
  • –default-character-set=charset_name
    • 默认字符集
  • –no-set-names, -N
    • 关闭 –set-charset 设置,与指定 –skip-set-charset 相同
  • –set-charset
    • SET NAMES default_character_set 记录到输出中。默认已开启。使用 –skip-set-charset 禁用此功能。

主从复制的选项

  • –apply-slave-statements
    • 如果备份时使用了 –dump-slave 选项,将会在二进制日志语句之前增加 STOP REPLICA | SLAVE,之后增加 START REPLICA | SLAVE
  • –delete-master-logs
    • 对于主节点,备份后通过 PURGE BINARY LOGS 删除二进制日志
    • 此选项自动启用 –master-data
  • –dump-slave[=value]

    现在的复制一般都会开启 GTID,所以这个选项基本用不到。

    • 与 –master-data 相似,不同之处在于,此选项用于主从复制,且会覆盖 –master-data。输出信息中包含一个 CHANGE REPLICATION SOURCE TO 语句,表明了二进制日志的坐标。
    • 如果服务端开启了 GTID 模式的复制,则不能应用此选项
  • –include-master-host-port
    • 如果备份时使用了 –dump-slave 选项,将增加 SOURCE_HOST | MASTER_HOST 和 SOURCE_PORT | MASTER_PORT,用于记录主节点的主机名和端口号
  • –master-data[=value]
    • 使输出信息中包含一个 CHANGE REPLICATION SOURCE TO 语句,表明了二进制日志的坐标
    • 如果要避免锁表,还需要指定 –single-transaction
    • 选项值
      • 1:(默认) CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO 不加注释
      • 2:CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO 语句以注释形式添加
  • –set-gtid-purged=value
    • 在基于 GTID 模式的复制结构中,控制 SET @@GLOBAL.gtid_purged 语句和 SET @@ SESSION.sql_log_bin = 0 语句。如果启用了 GTID 模式,则默认启用此选项。
    • 对于 5.6、5.7 版本,如果 dump 文件中包含系统表,则不建议在启用 GTID 时加载 dump 文件
    • 选项值
      • AUTO
        • (默认) 在启用 GTID 的服务器上自动启用此选项
      • OFF
        • 禁用
      • ON
        • 开启。如果设置了此选项,但是服务器未启用 GTID ,则报错。
      • COMMENTED
        • 以注释的形式记录 SET @@GLOBAL.gtid_purged 语句,正常记录 SET @@ SESSION.sql_log_bin = 0 语句。

格式选项

  • –compact
    • 产生紧凑输出。此选项启用 –skip-add-drop-table, –skip-add-locks, –skip-comments, –skip-disable-keys 和 –skip-set-charset 选项。
  • –compatible=name
    • 兼容模式输出。唯一的选项值是 ansi 。
  • –complete-insert, -c
    • 使用包含列名的完整 INSERT 语句
  • –create-options
    • 在 CREATE TABLE 语句中包括所有特定于 MySQL 的表的选项
  • –fields-terminated-by=…, –fields-enclosed-by=…, –fields-optionally-enclosed-by=…, –fields-escaped-by=…
    • 分隔符。这些选项与 –tab 选项一起使用,并且与 LOAD DATA 的相应 FIELDS 子句具有相同的含义。
  • –hex-blob
    • 使用十六进制表示法转储二进制列
  • –lines-terminated-by=…
    • 该选项与 –tab 选项一起使用,其含义与 LOAD DATA 的相应 LINES 子句相同。
  • –quote-names, -Q
    • 标识符,将数据库名、表名、列名使用反引号 “`” 引起来。默认已启用。可以使用 –skip-quote-names 禁用此功能。
  • –result-file=file_name, -r file_name
    • 输出到指定文件。适合 Windows 端应用。
  • –show-create-skip-secondary-engine=value
    • 在 CREATE TABLE 语句中排除 SECONDARY ENGINE 子句
  • –tab=dir_name, -T dir_name
    • 生成以 tab 作为分隔符的 txt 格式的数据文件。
    • 对于每个表,都会生成一个 tbl_name.sql 文件(包含表结构)和一个 tbl_name.txt 文件(包含数据)
  • –tz-utc
    • 此选项使 TIMESTAMP 列可以在不同时区的服务器之间转储和重新加载,还可以防止由于夏时制而导致的更改。
    • 默认已启用,可以使用 –skip-tz-utc 禁用此功能。
  • –xml, -X
    • 以 XML 格式输出

筛选选项

  • –all-databases, -A
    • 备份所有库

      在 8.0 版本之前,–routines 和 –events 包含的存储过程、函数、事件都保存在 mysql.proc 和 mysql.event 表中,所以备份所有库时,不需要单独指定这两个选项。

      但是,8.0 版本删除了这两个 mysql 表,相应的对象存储在数据字典中,所以需要单独指定这两个选项。

    • 导出文件中包含 create database 和 use 语句

  • –databases, -B

    • 备份某些库,此选项必须在命令行使用

    • 导出文件中包含 create database 和 use 语句

    • 备份单库时,可忽略此选项

      shell> mysqldump test > dump.sql
      shell> mysqldump --databases test > dump.sql
      
      • 如果没有 –databases,导出文件中不包含 create database 或 use 语句
      • 重新加载导出文件时,必须指定一个默认库名
      • 重新加载时,可以指定与原库不同的库名
      • 如果要加载的库不存在,必须创建
      • –add-drop-database 选项不起作用,因为导出文件中不包含 create database 语句
  • –events, -E
    • 输出事件。包含 CREATE EVENT 语句。
  • –ignore-error=error[,error]…
    • 忽略指定的错误。多个选项值之间用逗号分隔
    • –force 是忽略所有错误,优先级更高。
  • –ignore-table=db_name.tbl_name
    • 备份时忽略的表或视图。必须同时指定库名和表名。如果指定多个表,要多次使用此选项。
  • –no-data, -d
    • 不备份表数据,只记录表结构
  • –routines, -R
    • 输出存储例程 (存储过程和函数) ,输出中包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句
  • –tables
    mysqldump -uroot -p -S /tmp/mysql.sock -B test --tables t1 t2 > /tmp/test_t1_t2.sql
    
    • 指定要备份的表,此选项必须在命令行使用。选项后面的所有名称都将视为表名称,会覆盖 –databases 或 -B 选项
  • –triggers
    • 输出触发器。默认已启用,可以使用 –skip-triggers 禁用此功能。
  • –where=’where_condition’, -w ‘where_condition’
    • 指定 WHERE 条件

      “`shell
      –where="user='jimf'"
      -w"userid>1"
      -w"userid mysqldump db_name > backup-file.sql
      </p></li>
      </ul>

      <h1>重新加载</h1>

      <p>shell> mysql db_name mysql -e "source /path-to-backup/backup-file.sql" db_name

      “`

    • 备份所有库

      shell> mysqldump -uroot -p -S /tmp/mysql.sock -A > /tmp/all_databases.sql
      
    • 备份多个库:mysql 和 test
      shell> mysqldump -uroot -p -S /tmp/mysql.sock -B mysql test > /tmp/mysql_test.sql
      
    • 备份指定的库:mysql
      shell> mysqldump -uroot -p -S /tmp/mysql.sock -B mysql > /tmp/mysql.sql
      
    • 对于 InnoDB 表,可以在线备份
      shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql
      
    • 备份指定的表:test.t1
      shell> mysqldump -uroot -p -S /tmp/mysql.sock --master-data=2 --single-transaction test t1 > /tmp/test_t1.sql
      

      Note: 恢复时需要先进入这个库,因为备份中没有 use test 语句。

      mysql> use test;
      mysql> set sql_log_bin=0;
      mysql> source /tmp/test_t1.sql;
      mysql> set sql_log_bin=1;
      
    • 记录二进制坐标,有利于时间点恢复 (前滚)
      shell> mysqldump --all-databases --master-data=2 > all_databases.sql
      or
      shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql
      
    • 从一个服务器加载到另一个服务器
      shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
      

    限制

    • 默认情况下,mysqldump 不会备份 Performance_schema 或 sys 库。要备份这两个库,需要明确指定。
    • mysqldump 不会备份 InnoDB 的 CREATE TABLESPACE 语句
    • mysqldump 不会备份 NDB 集群的 ndbinfo 信息库
    • mysqldump 会包含重建常规日志和慢日志表的语句,但是日志内容不会备份。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注