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

此处含有隐藏内容，需要正确输入密码后可见！
- 密码
~~–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
- 打印输出程序名称和所有读取的配置文件内容

–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 会包含重建常规日志和慢日志表的语句，但是日志内容不会备份。