mysqlpump-数据库备份程序

没有评论

mysqlpump-数据库备份程序

mysqlpump 相当于 mysqldump 的增强版，支持多线程备份，速度有很大的提升。

mysqlpump 的特点

  • 并行处理数据库以及数据库中的对象，以加快转储过程
  • 更好地控制要转储的数据库和数据库对象
  • 将用户帐户作为帐户管理语句 (CREATE USER，GRANT) 转储，而不是直接插入到 mysql 系统数据库
  • 创建压缩输出的能力
  • 进度指示器 (值是估计值)
  • 对于转储文件重新加载，通过在插入行后添加索引，可以更快地为 InnoDB 表创建辅助索引

调用语法

默认备份所有库

mysqlpump --all-databases

备份单个库/表

mysqlpump db_name
mysqlpump db_name tbl_name1 tbl_name2 ...

备份多个库

mysqlpump --databases db_name1 db_name2 ...

备份授权表

默认情况下，mysqlpump 不会备份用户账户定义，即使备份中包含 mysql 库 (其中包括授权表)。

要将授权表内容以 CREATE USER 和 GRANT 语句的形式作为逻辑定义转储，请使用 –users 选项并禁止所有数据库转储。

mysqlpump --exclude-databases=% --users

重新加载转储文件

mysqlpump [options] > dump.sql
mysql < dump.sql

选项

mysqlpump 的选项可以在配置文件的 [mysqlpump]、[client] 标签组中指定。

帮助选项

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

DDL 选项

  • –add-drop-database
    • 建库前先删库。在每个 CREATE DATABASE 语句之前增加一个 DROP DATABASE 语句
    • mysql 库是用户无法删除的，如果指定了此选项同时需要备份 mysql 库，则会产生错误。所以，此选项不能和 –all-databases 一起使用
  • –add-drop-table
    • 在每个 CREATE TABLE 语句之前增加一个 DROP TABLE 语句
  • –add-drop-user
    • 在每个 CREATE USER 语句之前编写 DROP USER 语句
  • –no-create-db, -n
    • 如果指定了 –databases 或 –all-databases 选项，则禁止在输出中包含 CREATE DATABASE 语句
  • –no-create-info, -t
    • 备份文件中不包含 CREATE TABLE 语句
  • –replace
    • 使用 REPLACE 语句，而不是 INSERT 语句

事务选项

  • –add-locks
    • 在每个表的前后增加 LOCK TABLES 和 UNLOCK TABLES 语句。
    • 可以加快重载时的插入速度。
    • –add-locks 和 –single-transaction 是互斥的
  • –single-transaction
    • 此选项将隔离模式设置为 REPEATABLE READ，并在备份前向服务端发送一个 START TRANSACTION 语句。这样可以保持 InnoDB 表的一致性，而不会阻塞任何应用程序。
    • 为保证一致性，任何会话连接都不可以执行以下语句：ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE
    • –single-transaction 选项和 –lock-tables 选项是互斥的，因为 LOCK TABLES 导致任何未决事务隐式提交。
    • 对于大表的备份，应该将此选项和 –quick 一起使用。

筛选选项

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

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

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

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

    • –all-databases 和 –databases 是互斥的

  • –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 语句。
    • 默认已开启，可以使用 –skip-events 禁用此功能。
  • –routines, -R
    • 输出存储例程 (存储过程和函数) ，输出中包含 CREATE PROCEDURE 和 CREATE FUNCTION 语句
  • –skip-dump-rows, -d
    • 不备份表的数据
  • –triggers
    • 输出触发器。默认已启用，可以使用 –skip-triggers 禁用此功能。
  • –users
    • 以 CREATE USER 和 GRANT 语句的形式将用户帐户作为逻辑定义转储

    • 默认情况下，mysqlpump 不会备份 mysql 库 (包括授权表)。如果想要备份授权表的内容，可以使用此选项，同时抑制所有库的转储。

      mysqlpump --exclude-databases=% --users

包含和排除选项

  • –exclude-databases=db_list
    • 不备份 db_list (逗号分隔的数据库名称列表) 中的库
  • –exclude-events=event_list
    • 不备份 event_list (逗号分隔的事件名称列表) 中的事件
  • –exclude-routines=routine_list
    • 不备份 routine_list (逗号分隔的存储例程名称列表) 中的存储例程
  • –exclude-tables=table_list
    • 不备份 table_list (逗号分隔的表名称列表) 中的表
  • –exclude-triggers=trigger_list
    • 不备份 trigger_list (逗号分隔的触发器名称列表) 中的触发器
  • –exclude-users=user_list
    • 不备份 user_list (逗号分隔的用户名称列表) 中的用户
  • –include-databases=db_list
    • 备份 db_list 列表中的数据库
  • –include-events=event_list
    • 备份 event_list 列表中的事件
  • –include-routines=routine_list
    • 备份 routine_list 列表中的存储例程
  • –include-tables=table_list
    • 备份 table_list 列表中的表
  • –include-triggers=trigger_list
    • 备份 trigger_list 列表中的触发器
  • –include-users=user_list
    • 备份 user_list 列表中的用户

连接选项

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

    已弃用。

    • 尽可能压缩客户端和服务器之间发送的所有信息
  • –compress-output=algorithm
    • 指定输出的压缩算法。默认不压缩。
    • 允许的算法是 LZ4 和 ZLIB
    • 解压缩时要具有适当的实用程序
      • 如果系统命令 lz4 和 openssl zlib 不可用，可以使用 MySQL 发行版自带的 lz4_decompress 和 zlib_decompress 程序来解压缩
  • –compression-algorithms=value
    • 连接时的压缩算法，默认值是 uncompressed
  • –default-auth=plugin
    • 指定身份验证插件
  • –get-server-public-key
    • 从服务器请求基于 RSA 密钥对的密码交换所需的公共密钥。此选项适用于使用 caching_sha2_password 身份验证插件进行身份验证的客户端。
    • 如果使用了 –server-public-key-path=file_name，它的优先级高于 –get-server-public-key
  • –host=host_name, -h host_name
    • 主机名
  • –login-path=name
    • 指定登录路径文件 .mylogin.cnf
  • –password[=password], -p
     此处含有隐藏内容，需要正确输入密码后可见！

    • 密码
  • –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

国际化选项

  • –character-sets-dir=path
    • 字符集的安装路径
  • –default-character-set=charset_name
    • 默认字符集。如果没有指定，默认使用 utf8。
  • –set-charset
    • 将 SET NAMES default_character_set 记录到输出中。默认已开启。使用 –skip-set-charset 禁用此功能。

性能选项

  • –column-statistics
    • dump 文件中增加 ANALYZE TABLE 语句，以便重新加载 dump 文件时可以收集统计信息。
    • 默认已开启，但是大型表收集统计信息要耗费很多时间。
  • –extended-insert=N, -e
    • INSERT 语句使用多行插入语法 (包含多个 VALUES 列表)。这样可以生成较小的 dump 文件，并且重新加载时会加快插入速度。
    • N 指示每个 INSERT 语句中要包括的行数。默认值为250。值为1时，每个表行产生一个 INSERT 语句。
  • –insert-ignore
    • 使用 INSERT IGNORE 语句替换 INSERT 语句
  • –max-allowed-packet=N
    • 客户端/服务器端通信缓冲区的最大大小。默认值为 24MB，最大为 1GB。
  • –net-buffer-length=N
    • 用于客户端/服务器端通信 (TCP/IP 或 socket) 的缓冲区的初始大小。
    • 当创建多行 INSERT 语句时 (与 –extended-insert 或 –opt 选项一样)， mysqldump 会创建长达 –net-buffer-length 个字节的行。
    • 此选项的值不能超过系统变量 net_buffer_length
  • –default-parallelism=N
    • 每个并行处理队列的默认线程数。预设值为2
    • –parallel-schemas 选项也会影响并行性，可用于覆盖默认线程数
    • 如果要运行单线程进程，并且不创建队列
      • –default-parallelism=0
      • –parallel-schemas
  • –parallel-schemas=[N:]db_list
    • 创建一个序列用来处理 db_list 中的数据库
    • 如果未指定 N，则使用 –default-parallelism 选项指定的值作为序列的进程数
    • 如果 –default-parallelism 未指定，则会创建一个默认队列用于转储用户定义
  • –defer-table-indexes
    • 在转储输出时，将每个表的创建索引操作推迟到这个表的数据加载完毕之后
    • 这适用于所有存储引擎，但 InnoDB 仅适用于辅助引
    • 默认已开启，可以使用 –skip-defer-table-indexes 禁用此功能

格式选项

  • –complete-insert, -c
    • 使用包含列名的完整 INSERT 语句
  • –hex-blob
    • 使用十六进制表示法转储二进制列
  • –tz-utc
    • 此选项使 TIMESTAMP 列可以在不同时区的服务器之间转储和重新加载，还可以防止由于夏时制而导致的更改。
    • 默认已启用，可以使用 –skip-tz-utc 禁用此功能。
  • –log-error-file=file_name
    • 指定错误日志存放的位置。如果不指定，警告和错误会使用标准输出。
  • –skip-definer
    • 在视图和存储程序中，忽略 CREATE 语句的 DEFINER 和 SQL SECURITY 子句。
    • 重新加载转储文件时，该转储文件将创建使用默认 DEFINER 和 SQL SECURITY 值的对象
  • –watch-progress
    • 定期显示进度指示器，该指示器提供有关表，行和其他对象的已完成和总数的信息。

调试选项

  • –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 构建的时，此选项才可用。

配置文件选项

  • –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
    • 打印输出程序名称和所有读取的配置文件内容

主从复制的选项

  • –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 语句。

对象选择

包含和排除选项

mysqlpump 具有一组包含和排除选项。任何包含或排除选项都可以多次给出。效果是累加的。这些选项的顺序无关紧要。

mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
  • –include-databases, –exclude-databases
    • 适用于数据库及其中的所有对象
  • –include-tables, –exclude-tables
    • 适用于表。除非给出特定于触发器的选项，否则这些选项还会影响与表关联的触发器
  • –include-triggers, –exclude-triggers
    • 适用于触发器
  • –include-routines, –exclude-routines
    • 适用于存储过程和函数。如果例程选项与存储过程名称匹配，则它也与相同名称的函数匹配
  • –include-events, –exclude-events
    • 适用于事件计划程序事件
  • –include-users, –exclude-users
    • 适用于用户帐户

包含和排除列表

  • 每个包含和排除选项的值是相应对象类型的逗号分隔名称的列表 
    --exclude-databases=test,world
--include-tables=customer,invoice

对象名称

  • 对象名称可以使用通配符 
    --include-tables=t%,__tmp
    • %：匹配多个字符
    • _：匹配单个字符
  • 对于用户，指定的不带主机部分的名称将使用隐含的主机 ％ 进行解释。
    • 例如，u1 和 u1@％ 是等效的

包含和排除选项的相互作用

  • (默认) 无 include 和 exclude 选项
    • 转储所有数据库
  • 无 exclude 选项，有 include 选项
    • 仅转储 include 对象
  • 有 exclude 选项，无 include 选项
    • 转储所有对象，除了 exclude 的对象
  • 有 exclude 选项，有 include 选项
    • 转储所有对象，除了 exclude 和未 include 的对象

指定备份对象的其他方式

  • –all-databases
    • –include-databases=％ 与 –all-databases 类似，但是会选择所有数据库进行转储，即使是 –all-databases 例外的数据库
  • –databases
    • 等效于 –include-databases

mysqlpump 并行处理

可以选择数据库之间 (同时转储多个数据库) 和数据库内 (同时转储给定数据库的多个对象) 的并发性。

并行处理选项

默认情况下，mysqlpump 设置一个带有两个线程的队列。也可以创建其他队列并控制分配给每个队列的线程数，包括默认队列。

  • –default-parallelism=N
    • 指定用于每个队列的默认线程数。默认2。
    • 默认队列始终使用默认线程数。除非另行指定，否则其他队列也将使用默认线程数。
  • –parallel-schemas=[N:]db_list
    • 为转储数据库设置一个处理队列 db_list，并指定队列使用 N 个进程
    • db_list 中允许使用通配符 % 和 _
    • 如果没有指定 N，则使用 –default-parallelism 选项的值作为队列的进程数
  • –parallel-schemas
    • 多实例时，用于指定多个队列
    • 如果此选项未指定队列，则 mysqlpump 使用默认队列

并行处理的粒度

  • 并行处理的粒度是单独的数据库
    • 例如，一个单独的表不能使用多个进程并行处理
  • 示例
    • 一个队列处理 db1、db2，一个队列处理 db3，默认队列处理其他数据库 
      mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
    • 所有队列使用4个进程处理 
      mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
          --default-parallelism=4
    • 一个队列处理 db1、db2，使用5个进程；一个队列处理 db3，使用3个进程；默认队列处理其他数据库，使用2个进程 
      mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
    • 如果要运行单线程进程，并且不创建队列
      • –default-parallelism=0
      • –parallel-schemas

mysqlpump 的限制

某些对象不会转储

  • 默认不转储 performance_schema, ndbinfo 和 sys 库
    • 如果要转储这些库，需要明确指定
      • –databases
      • –include-databases
  • 不会转储 INFORMATION_SCHEMA 库

  • 不会转储 InnoDB 的 CREATE TABLESPACE 语句

  • 可以以逻辑形式转储用户账户的 CREATE USER 和 GRANT 语句

    • 选项
      • –include-users
      • –users
    • mysql 数据库的转储默认不包含用户定义

      user, db, tables_priv, columns_priv, procs_priv 或 proxies_priv。

      如果要备份这些表，需要明确指定。

      mysqlpump mysql user db ...

权限

如果未使用 –single-transaction 选项，对于转储的对象，至少满足以下条件。

  • 对于转储表，需要 SELECT 权限
  • 对于转储图，需要 SHOW VIEW 权限
  • 对于转储触发器，需要 TRIGGER、LOCK TABLES 权限
  • 如果要转储用户定义，需要对 mysql 库的 SELECT 权限

编码

  • 在 Windows 上使用 PowerShell 通过输出重定向创建的转储将创建具有 UTF-16 编码的文件。但是，不允许将 UTF-16 作为连接字符集，因此无法正确加载转储文件。

  • 要解决此问题，请使用 –result-file 选项，该选项以 ASCII 格式创建输出

    mysqlpump [options] --result-file=dump.sql
发表于：MySQL
2021-04-20
# MySQL# mysqlpump# Programs# 客户端程序
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-04-20发表，共计8843字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
MySQL 配置文件
字符集和排序规则
mysql-命令行客户端
安装 MySQL
评论（没有评论）