MySQL mydumper工具

#Install development tools
yum install -y cmake gcc gcc-c++ git make

#Install development versions of GLib, ZLib, PCRE and ZSTD
yum install -y glib2-devel openssl-devel pcre-devel zlib-devel libzstd-devel

添加环境变量(mysql客户端库文件)

export LD_LIBRARY_PATH=/usr/local/mysql/lib

安装mydumper

cd /soft/mydumper-master
cmake .
make
sudo make install

安装完成后生成两个二进制文件mydumper和myloader位于/usr/local/bin目录下

2. mydumper参数解释

mydumper --help

参数	简写	描述
连接选项
--host	-h	连接的MySQL数据库服务器
--user	-u	连接数据库的用户
--password	-p	连接数据库用户的密码
--ask-password	-a	提示输入用户的密码
--port	-P	MySQL数据库端口
--socket	-S	用于连接的 UNIX 域套接字文件
--protocol		用于连接的协议（tcp、socket）
--compress-protocol	-C	对 MySQL 连接使用压缩
--ssl		使用 SSL 连接
--ssl-mode		连接到服务器所需的安全状态：DISABLED、PREFERRED、REQUIRED、VERIFY_CA、VERIFY_IDENTITY
--key		密钥文件的路径名
--cert		证书文件的路径名
--ca		证书颁发机构文件的路径名
--capath		包含 PEM 格式的受信任 SSL CA 证书的目录的路径名
--cipher		用于 SSL 加密的允许密码列表
--tls-version		服务器允许哪些协议进行加密连接
过滤选项
--regex	-X	'db.table' 匹配的正则表达式
--database	-B	需要备份的数据库
--ignore-engines	-i	要忽略的存储引擎的列表，逗号分隔
--updated-since	-U	使用 Update_time 仅转储最近 U 天内更新的表
--partition-regex		按分区名称过滤的正则表达式
--omit-from-file	-O	包含要跳过的数据库.表条目列表的文件，每行一个（在应用正则表达式选项之前跳过）
--tables-list	-T	要备份的表列表，以逗号分隔（不排除正则表达式选项）。表名必须包含数据库名。例如：test.t1，test.t2
锁选项
--tidb-snapshot	-z	使用 TiDB 快照
--no-locks	-k	不执行临时共享读锁，警告：这将会导致非一致性备份
--use-savepoints		使用 savepoints 来减少元数据锁的产生，需要 SUPER 权限
--no-backup-locks		不使用 Percona 的 backup locks （备份锁）
--lock-all-tables		对所有的表 LOCK TABLE，而不是使用FTWRL
--less-locking		对于 InnoDB 存储引擎的表最少的锁时间
--trx-consistency-only		事务一致性
--skip-ddl-locks		跳过DDL锁
PMM选项
--pmm-path		默认值为 /usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
--pmm-resolution		默认值较高
执行选项
--exec-threads		与 --exec 一起使用的线程数量
--exec		使用文件作为参数来执行命令
--exec-per-thread		设置将由 STDIN 接收并将 STDOUT 写入输出文件的命令
--exec-per-thread-extension		使用 --exec-per-thread 时设置 STDOUT 文件的扩展名
长查询选项
--long-query-retries		重新检测长查询，默认0（不检测）
--long-query-retry-interval		长查询检测的间隔时间，默认60秒
--long-query-guard	-l	设置长查询的时间阈值，默认60秒
--kill-long-queries	-k	kill 长查询(替代aborting)
Job选项
--max-threads-per-table		每张表的最大线程数
--char-deep		定义当主键是字符串时要使用的字符数
--char-chunk		定义应将表拆分为多少部分。默认情况下我们使用线程数量
--rows	-r	将表拆分为这么多行的块。它可以是 MIN:START_AT:MAX。 MAX 可以为 0，表示没有限制。如果查询时间少于 1 秒，则块大小将加倍；如果查询时间超过 2 秒，则块大小减半
--split-partitions		将分区转储到单独的文件中。此选项会覆盖分区表的 --rows 选项
校验选项
--checksum-all	-M	转储所有对象的校验和
--data-checksums		转储表与数据的校验和
--schema-checksums		转储模式表和视图创建校验和
--routine-checksums		转储触发器、函数和例程的校验和
对象选项
--no-schemas	-m	不备份表结构，(数据和触发器)
--all-tablespaces	-Y	备份所有表空间
--no-data	-d	不备份表的数据
--triggers	-G	备份触发器，默认情况下不备份
--events	-E	备份事件，默认情况下不备份
--routines	-R	备份存储过程和函数，默认情况下不备份
--skip-constraints		从CREATE TABLE语句中删除约束。默认情况下，不修改该语句
--skip-indexes		从CREATE TABLE语句中删除索引。默认情况下，不修改该语句
--views-as-tables		将视图作为表导出
--no-views	-W	不备份视图
Statement选项
--load-data		不创建 INSERT INTO 语句，而是创建 LOAD DATA 语句和 .dat 文件
--csv		自动启用--load-data 并设置变量以 CSV 格式导出
--include-header		添加列名到头部
--fields-terminated-by		定义分割字段的字符
--fields-enclosed-by		定义包围字段的字符。默认： ”
--fields-escaped-by		转义字符，默认: '\'
--lines-starting-by		在每行的开头添加字符串。使用 --load-data 时，它会添加到 LOAD DATA 语句中。当使用它时，它也会影响 INSERT INTO 语句。
--lines-terminated-by		在每行末尾添加字符串。当使用 --load-data 时，它被添加到 LOAD DATA 语句中。当使用它时，它也会影响 INSERT INTO 语句。
--statement-terminated-by		这可能永远不会被使用，除非你知道你在做什么
--insert-ignore	-N	使用 INSERT IGNORE 备份行
--replace		用 REPLACE 替换备份行
--complete-insert		使用包含列名的完整 INSERT 语句
--hex-blob		使用十六进制表示法转储二进制列
--skip-definer		从 CREATE 语句中删除 DEFINER。默认情况下，语句不被修改
--statement-size	-s	尝试的 INSERT 语句大小（以字节为单位），默认 1000000
--tz-utc		SET TIME_ZONE='+00:00' 在转储顶部，允许在服务器具有不同时区的数据或数据在不同时区的服务器之间移动时转储 TIMESTAMP 数据，默认为使用 - -skip-tz-utc 禁用。
--skip-tz-utc		禁用--tz-utc
--set-names		设置名称，使用它需要您自担风险，默认二进制文件
Extra选项
--chunk-filesize	-F	将数据文件分割成以 MB 为单位的多个块
--exit-if-broken-table-found		如果发现损坏的表则退出
--success-on-1146		如果表不存在，则不增加错误计数并发出警告而不是错误
--build-empty-files	-e	即使表中没有可用数据也构建转储文件
--no-check-generated-fields		与生成字段相关的查询不会被执行。如果您生成了列，则会导致恢复问题
--order-by-primary		如果不存在主键，则按主键或唯一键对数据进行排序
--compact		提供较少的详细输出。禁用页眉/页脚构造
--compress	-c	使用 /usr/bin/gzip 和 /usr/bin/zstd 压缩输出文件。选项：GZIP 和 ZSTD。默认值：GZIP
--use-defer		使用延迟整数分片，直到处理完所有非整数主键表（为大量表保存RSS）
--check-row-count		如果导出的行数与select count(*)不一致，则导出失败
Daemon选项
--daemon	-D	后台运行
--snapshot-interval	-I	每个转储快照之间的间隔（以分钟为单位），需要 --daemon，默认 60
--snapshot-count	-X	快照数量，默认2
Application选项
--help	-?	显示帮助选项
--outputdir	-o	输出文件的目录
--clear		导出前清理输出目录
--dirty		覆盖输出目录而不清除（注意左下块）
--stream		一旦文件被写入，它将通过 STDOUT 流式传输。自 v0.12.7-1 起，接受 NO_DELETE、NO_STREAM_AND_NO_DELETE 和 TRADITIONAL（默认值），如果未给出参数则使用
--logfile	-L	要使用的日志文件名，默认情况下使用 stdout
--disk-limits		如果确定没有足够的磁盘空间，则设置暂停和恢复的限制。接受类似以下值：'<resume>:<pause>'（以 MB 为单位）。例如：100:500 将在只有 100MB剩余时暂停，如果有 500MB可用则恢复
--threads	-t	要使用的线程数，默认 4
--version	-V	显示程序版本并退出
--verbose	-v	输出的详细程度，0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
--debug		打开调试输出（自动将verbosity设置为3）
--defaults-file		使用特定的默认文件。默认值：/etc/mydumper.cnf
--defaults-extra-file		使用额外的默认文件。这是在 --defaults-file 之后加载的，替换以前定义的值
--fifodir		需要时将在其中创建 FIFO 文件的目录。默认值：与备份相同

3. 备份例子

3.1 备份全库(未包含其他)

mydumper -u root -p 123456 -o /backup

3.2 备份全库(包含其他)

包含触发器(-G)、事件(-E)、存储过程和函数(-R)

mydumper -u root -p 123456 -G -R -E -o /backup

3.3 备份指定数据库(-B或--database)

mydumper -u root -p 123456 -G -R -E -B zs,python -o /backup

3.4 导出指定表(-T或--tables-list)

mydumper -u root -p 123456  -T zs.aa,python.y -o /backup

3.5 只导出表结构，不导出表数据(-d或--no-data)

mydumper -u root -p 123456 -B zs -d -o /backup

3.6 只导出表数据，不导出表结构(-m或者--no-schemas)

mydumper -u root -p 123456 -m -B zs -o /backup

3.7 压缩备份(-c或--compress)

mydumper -u root -p 123456 -B zs,python -c --clear -o /backup

3.8 综合备份

事务一致性、压缩、4线程、备份前清理备份目录、备份包含触发器、事件、存储过程和函数，备份前清理目录

mydumper -u root -p 123456 -G -E -R --trx-consistency-only -t 4 -c --clear -o /backup

3.9 使用正则表达式

排除某些表或者数据库,使用--regex参数

# 可以使用--regex功能，例如不备份zs和mysql数据库： 
mydumper --regex '^(?!(zs\.|mysql\.))'
 
# 仅备份zs和python数据库：
mydumper --regex '^(zs\.|python\.)'
 
# 不备份以zs开头的所有数据库：
mydumper --regex '^(?!(zs))'
 
# 转储不同数据库中的指定表
mydumper --regex '^(db1\.table1$|db2\.table2$)'
 
# 备份几个数据库但丢弃一些表：
mydumper --regex '^(?=(?:(db1\.|db2\.)))(?!(?:(db1\.table1$|db2\.table2$)))'

4. 备份文件命名

如果使用了 --rows(-r)，每个表的备份文件都会被分割成多个文件,文件后会有5个数字填充(该表总数据)，5个数字后再跟5个数字(分割后的数据)，格式如下

database.table.chunk.sql(.gz|.zst)

sbtest.sbtest2.00000.00001.sql
sbtest.sbtest2.00000.00003.sql
sbtest.sbtest2.00000.00004.sql
sbtest.sbtest2.00000.00006.sql
sbtest.sbtest2.00000.00007.sql
sbtest.sbtest2.00000.00009.sql
sbtest.sbtest2.00000.00010.sql
sbtest.sbtest2.00000.sql

每个表的文件都会写到单独的文件中(没有使用--no-schemas才会有表结构文件)

#建库文件
database-schema-create.sql(.gz|.zst)

#表结构文件
database.table-schema.sql(.gz|.zst)

#表数据文件
database.table.sql(.gz|.zst)

可能各个版本备份出来的文件命名稍有不同，从文件命名可以较为明显的看出该文件的内容，大致总结下文件命名规则如下：

metadata：记录开始及结束备份的时间，二进制日志位置和表名以及行数
dbname-schema-create.sql：建库语句。
dbname.tbname-schema.sql：此表的表结构创建语句。
dbname.tbname.sql：该表的插入数据语句（若该表为空则不存在此文件）。
dbname.viewname-schema.sql：创建视图的表（列举出视图字段，然后创建表）。
dbname.viewname-schema-view.sql：创建视图的真正语句。
dbname.tbname-schema-triggers.sql：创建触发器语句。
dbname-schema-post.sql：包含事件、存储过程及函数创建语句（若存在则有该文件）。

5. 备份原理

备份流程

主要步骤概括

主线程 FLUSH TABLES WITH READ LOCK , 施加全局只读锁，以阻止 DML 语句写入，保证数据的一致性；
读取当前时间点的二进制日志文件名和日志写入的位置并记录在 metadata 文件中，以供即时点恢复使用；
N 个（线程数可以指定，默认是 4个）dump 线程 START TRANSACTION WITH CONSISTENT SNAPSHOT ; 开启读一致的事物；
dump non-InnoDB tables , 首先导出非事物引擎的表；
主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁；
dump InnoDB tables , 基于事物导出 InnoDB 表；
事物结束。

6. myloader参数

参数	简写	描述
连接参数
--host	-h	连接的MySQL数据库服务器
--user	-u	连接数据库的用户
--password	-p	连接数据库用户的密码
--ask-password	-a	提示输入用户的密码
--port	-P	MySQL数据库端口
--socket	-S	用于连接的 UNIX 域套接字文件
--protocol		用于连接的协议（tcp、socket）
--compress-protocol	-C	对 MySQL 连接使用压缩
--ssl		使用 SSL 连接
--ssl-mode		连接到服务器所需的安全状态：DISABLED、PREFERRED、REQUIRED、VERIFY_CA、VERIFY_IDENTITY
--key		密钥文件的路径名
--cert		证书文件的路径名
--ca		证书颁发机构文件的路径名
--capath		包含 PEM 格式的受信任 SSL CA 证书的目录的路径名
--cipher		用于 SSL 加密的允许密码列表
--tls-version		服务器允许哪些协议进行加密连接
过滤选项
--regex	-x	'db.table' 匹配的正则表达式
--source-db	-s	要还原的数据库，-s db1 -B db2，表示源库中的db1数据库，导入到db2数据库中。
--skip-triggers		不导入触发器，默认导入
--skip-post		不导入事件、存储过程和函数，默认都导入
--skip-constraints		不导入约束，默认导入
--skip-indexes		不导入innodb表的二级索引，默认导入
--no-data		不导入表数据
--omit-from-file	-O	包含要跳过的数据库.表条目列表的文件，每行一个（在应用正则表达式选项之前跳过）
--tables-list	-T	要备份的表列表，以逗号分隔（不排除正则表达式选项）。表名必须包含数据库名。例如：test.t1，test.t2
PMM选项
--pmm-path		默认值为 /usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
--pmm-resolution		默认值较高
Execution选项
--enable-binlog	-e	启用恢复数据的二进制日志记录
--innodb-optimize-keys		创建不包含索引的表，并在末尾添加索引。选项：AFTER_IMPORT_PER_TABLE和AFTER_IMPORT_ALL_TABLES。默认值：AFTER_IMPORT_PER_TABLE
--no-schema		不导入表结构和触发器
--purge-mode		这指定了截断模式，可以是：FAIL、NONE、DROP、truncate和DELETE。未设置默认值：FAIL
--disable-redo-log		禁用REDO_LOG并在之后启用它，不检查初始状态
--checksum		校验和：skip, fail, warn(默认)。
--overwrite-tables	-o	如果表存在则删除表
--overwrite-unsafe		与--overwrite-tables相同，但尽量快速数据加载。可能导致外键的InnoDB死锁
--retry-count		锁等待超时超过重试计数，默认为10（当前仅适用于DROP TABLE）
--serialized-table-creation		表的重新创建将以串行方式执行，每次执行一个线程。这意味着架构创建的最大线程数=1。此选项将在以后的版本中删除
--stream		它将从STDIN接收流，并在开始处理之前在磁盘中创建文件。自v0.12.7-1起，接受NO_DELETE、NO_STREAM_AND_NO_DELETE和TRADICTIONAL(这是默认值，在未给定参数的情况下使用)
Threads选项
--max-threads-per-table		每张表要使用的最大线程数，默认为--threads
--max-threads-for-index-creation		用于创建索引的最大线程数，默认为4
--max-threads-for-post-actions		操作后的最大线程数，如：约束、过程、视图和触发器，默认为1
--max-threads-for-schema-creation		用于创建schema的最大线程数。当它设置为1时，与序列化表创建相同，默认为4
--exec-per-thread		设置STDIN将从输入文件接收的命令，并写入STDOUT
--exec-per-thread-extension		当使用--exec-per-thread时，设置输入文件扩展名。否则将被忽略
Statement选项
--rows	-r	将INSERT语句拆分成多行
--queries-per-transaction	-q	每个事务的查询数，默认为1000
--append-if-not-exist		将IF NOT EXISTS追加到创建表语句中。当https://bugs.mysql.com/bug.php?id=103791已实施
--set-names		设置名称，使用风险自负，默认二进制
--skip-definer		从CREATE语句中删除DEFINER。默认情况下，不修改语句
Application选项
--help	-?	帮助选项
--directory	-d	导入的目录
--logfile	-L	要使用的日志文件名，默认情况下使用stdout
--database	-B	要恢复到的备用数据库
--quote-character	-Q	INSERT语句中使用的标识符引号字符，可以是`或者"，默认从导出的文件中读取
--resume		期望在备份目录中找到恢复文件，并且只处理这些文件
--threads	-t	要使用的线程数，默认 4
--version	-V	显示程序版本并退出
--verbose	-v	输出的详细程度，0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
--debug		打开调试输出（自动将verbosity设置为3）
--defaults-file		使用特定的默认文件。默认值：/etc/mydumper.cnf
--defaults-extra-file		使用额外的默认文件。这是在 --defaults-file 之后加载的，替换以前定义的值
--fifodir		需要时将在其中创建 FIFO 文件的目录。默认值：与备份相同

7. 恢复例子

7.1 恢复指定库

myloader -u root -p 123456 -s zs,python -d /backup

7.2 将源库的zs库导入备库的zs01库

myloader -u root -p 123456 -B zs01 -s zs -d /backup

7.3 导入特定的表

在导出的目录中选择metadata文件和需要单独导入的表的结构文件和数据文件移到一个新目录下，最好提前建好库表，否则需要把建库表的文件一起移动到新目录，移动到新目录后可以使用myloader指定新目录进行恢复数据。

1. mydumper介绍