MyDumper介绍及使用

MyDumper工具

一、mydumper介绍

mydumper负责导出 MySQL 数据库的一致备份

myloader从 mydumper 读取备份，连接到目标数据库并导入备份。

mydumper的github地址：https://github.com/mydumper/mydumper

mydumper 主要有以下几点特性：

支持多线程导出数据，速度更快。

支持一致性备份。

支持将导出文件压缩，节约空间。

支持多线程恢复。

支持以守护进程模式工作，定时快照和连续二进制日志。

支持按照指定大小将备份文件切割。

数据与建表语句分离。

二、mydumper安装

第一种方法: 在github上直接下载rpm包进行安装

[root@localhost ~]# wget https://github.com/mydumper/mydumper/releases/download/v0.13.1-2/mydumper-0.13.1-2.el7.x86_64.rpm

[root@localhost ~]# rpm -ivh mydumper-0.13.1-2.el7.x86_64.rpm

第二种方法：编译安装安装：

[root@localhost ~]# yum -y  install glib2-devel mysql-devel zlib-devel pcre-devel zlib gcc-c++ gcc cmake

[root@localhost ~]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz

[root@localhost ~]# echo "/u01/mysql3308/mysql/lib" >> /etc/ld.so.conf

[root@localhost ~]# tar -zxvf mydumper-0.9.1.tar.gz

[root@localhost ~]# cd mydumper-0.9.1/

[root@localhost mydumper-0.9.1]# cmake .

[root@localhost mydumper-0.9.1]# make

[root@localhost mydumper-0.9.1]# make install

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

[root@localhost bin]# ls /usr/local/bin/

mydumper  myloader

三、mydumper使用

1、运行mydumper --help查看选项参数

-B, --database                # 指定导出的数据库

-o, --outputdir               # 要将文件输出到的目录

-d, --no-data                 # 不导出表数据

-D, --daemon                  # 启用守护程序模式

-L, --logfile                 # 要使用的日志文件名，默认情况下使用标准输出

--disk-limits                 # 如果确定没有足够的磁盘空间，则设置暂停和恢复  的限制。接受以下值：'<resume>:<pause>'，以MB为单位。例如：100:500 当只有100MB可用时将暂停，如果有500MB可用时将恢复

-t, --threads                 # 要使用的线程数，默认为4

-C, --compress-protocol       # 对MySQL连接使用压缩

-V, --version                 # 显示程序版本并退出

-v, --verbose                 # 输出的详细性，0=无提示，1=错误，2=警告，3=信息，默认值2

--defaults-file               # 使用特定的默认文件

--ssl                         # 使用SSL连接

--ssl-mode                    # 与服务器连接的所需安全状态：DISABLED,PREFERRED,REQUIRED,VERIFY_CA,VERIFY_IDENTITY

--key                         # 密钥文件的路径名

--cert                        # 证书文件的路径名

--ca                          # 证书颁发机构文件的路径名

--capath                      # 包含PEM格式的受信任SSL CA证书的目录的路径名

--cipher                      # 用于SSL加密的允许密码列表

--tls-version                 # 服务器允许加密连接使用哪些协议

--stream                      # 一旦文件被写入，它将通过标准输出流

--no-delete                   # 流完成后，它不会删除文件

-O, --omit-from-file          # 包含数据库列表的文件。要跳过的表条目，每行一个（在应用regex选项之前跳过）

-T, --tables-list             # 要备份的表，以逗号隔开（不排除regex选项）

-h, --host                    # 连接的主机地址

-u, --user                    # 用户名

-p, --password                # 密码

-a, --ask-password            # 提示输入用户密码

-P, --port                    # 端口

-S, --socket                  # 用于连接的UNIX域套接字文件

-x, --regex                   # 正则表达式

-G, --triggers                # 导出触发器。默认情况下，不会转储触发器

--split-partitions            # 将分区转储到单独的文件中。此选项覆盖分区表的--rows选项。

--max-rows                    # 在估计表之后，限制每个块的行数，默认为1000000

--no-check-generated-fields   # 与生成的字段相关的查询将不会被执行。如果生成了列，则会导致恢复问题

--order-by-primary            # 如果不存在主键，则按主键或唯一键对数据排序

-E, --events                  # 转储事件。缺省情况下，不转储事件

-R, --routines                # 转储存储过程和函数。默认情况下，它不转储存储过程或函数

-W, --no-views                # 不转储视图

-M, --checksum-all            # 转储所有元素的校验和

--data-checksums              # 用数据转储表校验和

--schema-checksums            # 转储模式表和视图创建校验和

--routine-checksums           # 转储触发器、函数和例程校验和

--tz-utc                      # 在转储的顶部设置TIME_ZONE='+00:00'，以允许在服务器具有不同时区的数据或数据

                               在不同时区的服务器之间移动时转储时间戳数据，默认情况下使用——skip-tz-utc禁用。

--complete-insert             # 使用包含列名的完整INSERT语句

-z, --tidb-snapshot           # 用于TiDB的快照

-N, --insert-ignore           # 使用INSERT IGNORE转储行

--replace                     # 使用REPLACE转储行

--exit-if-broken-table-found  # 如果遇到损坏的表则退出

--success-on-1146             # 如果表不存在，则不增加错误计数和警告而不是关键

--use-savepoints              # 使用保存点减少元数据锁定问题，需要SUPER特权

-s, --statement-size          # 尝试INSERT语句的大小(以字节为单位)，默认为1000000

-F, --chunk-filesize          # 将表拆分为输出文件大小的块。该值的单位是MB

-e, --build-empty-files       # 即使表中没有可用的数据，也要构建转储文件

--where                       # 只转储选定的记录

-i, --ignore-engines          # 逗号分隔的要忽略的存储引擎列表

--csv                         # 自动启用--load-data并设置变量以CSV格式导出。

-r, --rows                    # 尝试将表分成这样多行的块。这个选项关闭--chunk-filesize

-c, --compress                # 压缩输出文件

--exec                        # 命令使用文件作为参数执行

--long-query-retries          # 重试检查长查询，默认为0(不重试)

--long-query-retry-interval   # 重新尝试长查询检查之前的等待时间(以秒为单位)，默认为60

-l, --long-query-guard        # 以秒为单位设置长查询定时器，默认为60秒

--tidb-snapshot               # 用于TiDB的快照

-U, --updated-since           # 使用Update_time只转储最近U天更新的表

-k, --no-locks                # 不执行临时共享读锁。警告:这将导致不一致的备份

-Y, --all-tablespaces         # 转储所有表空间。

--no-backup-locks             # 不使用Percona备份锁

--lock-all-tables             # 使用LOCK TABLE代替FTWRL

--less-locking                # 最小化InnoDB表的锁时间。

-m, --no-schemas              # 不备份表数据

-K, --kill-long-queries       # 终止长时间运行的查询(而不是中止)

--set-names                   # 设置名称，使用风险自负，默认二进制

2、导出后文件命名规则

dbname-schema-create.sql：建库语句。

dbname-schema-post.sql：包含事件、存储过程及函数创建语句（若存在则有该文件）。

dbname.tbname.metadata：记录这个表的行数。

dbname.tbname-schema.sql：此表的创建语句。

dbname.tbname-schema-triggers.sql：创建触发器语句（若该表存在触发器 则有此文件）。

dbname.tbname.sql：该表的插入数据语句（若该表为空 则不存在此文件）。

dbname.viewname-schema.sql：创建视图语句（只列举出视图字段）。

dbname.viewname-schema-view.sql：创建视图的真正语句。

metadata：记录开始及结束备份的时间以及二进制日志位置。

3、备份原理

1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性  

2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用  

3、N个（线程数可以指定，默认是4）dump线程把事务隔离级别改为可重复读 并开启读一致的事物  

4、dump non-InnoDB tables, 首先导出非事物引擎的表  

5、主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁  

6、dump InnoDB tables, 基于事物导出InnoDB表  

7、事物结束

4、常用组合

备份全部数据库：

$ mydumper -u root -p root -o /u01/mysql/backup

备份全部数据库包，包含触发器、事件、存储过程及函数

$ mydumper -u root -p root -G -R -E -o /u01/mysql/backup

备份指定数据库

$ mydumper -u root -p root -G -R -E -B test01  -o /u01/mysql/backup

备份指定表

$ mydumper -u root -p root  -B test01 -T user,dept -o /u01/mysql/backup

备份表结构

$ mydumper -u root -p root -B test01 -d -o /u01/mysql/backup

备份表数据：

$ mydumper -u root -p root -m -B test01 -o /u01/mysql/backup

压缩备份某个表

$ mydumper -u root -p root  -B test01 -T test01 -c -o /u01/mysql/backup

# 备份多个库下的大部分表（排除不需要备份的表）

mydumper -u root -p root  --regex '^(?!test01\.t1|test02\.t2|test03\.t3|test04\.t4)' -o /u01/mysql/backup

#只备份多个库下的几张表（要备份的表数量较少，\b表示精确匹配）

mydumper -u root -p  --regex '^(test.t0)\b|test01.t01)\b' --daemon -o /u01/mysql/backup

# 使用正则备份指定库,不能通过像 mysqldump 那样以逗号分隔备份多个库，而是通过正则表达式来达到备份多个库的目的。

备份test01、test02 库

mydumper -u root -p root  --regex '^(test01\.|test02\.)'  -o /u01/mysql/backup/test01_test02

备份包含test01、test02 字符的库或者表

mydumper -u root -p root  --regex 'test01|test02'  -o /u01/mysql/backup/db3_db2_3306

# 使用正则排除系统库

mydumper -u root -p root --regex '^(?!(mysql|sys))' -o /u01/mysql/backup  

四、myloader使用

使用myloader --help查看选项参数

-d, --directory                              # 要导入的转储文件的目录

-q, --queries-per-transaction                # 每个事务的查询数，默认为1000

-o, --overwrite-tables                       # 如果存在表则删除

--append-if-not-exist                        # 添加IF NOT EXISTS到建表语句中

-B, --database                               # 还原到的数据库（目标库）

-s, --source-db                              # 被还原的数据库（源数据库），-s db1 -B db2，表示源库中的db1数据库，导入到db2数据库中。

-e, --enable-binlog                          # 启用还原数据的二进制日志记录

--innodb-optimize-keys                       # 创建没有索引的表，并在最后添加索引

--set-names                                  # 设置名称，使用风险自负，默认二进制

-L, --logfile                                # 要使用的日志文件名称，默认使用标准输出

--purge-mode                                 # 指定截断模式，可以是：NONE, DROP, TRUNCATE and DELETE

--disable-redo-log                           # 禁用REDO_LOG，然后启用它，不检查初始状态

-r, --rows                                   # 将INSERT语句拆分为这么多行。

--max-threads-per-table                      # 每个表使用的最大线程数，默认为4

--skip-triggers                              # 跳过触发器。默认情况下，它导入触发器

--skip-post                                  # 跳过事件、存储过程和函数。默认情况下，它导入事件、存储过程或函数

--no-data                              # 不导入表数据

--serialized-table-creation                  # 重新创建表将以每次一个线程的方式执行

--resume                                     # 期望在备份目录中找到恢复文件，并只处理那些文件

--pmm-path                                   # 默认值将是/usr/local/percona/pmm2/collectors/textfile-collector/high-resolution

-t, --threads                                # 要使用的线程数，默认为4

-C, --compress-protocol                      # 在MySQL连接上使用压缩

-V, --version                                # 显示程序版本并退出

-v, --verbose                                # 输出的详细性，0=无提示，1=错误，2=警告，3=信息，默认值2

--no-delete                                  # 不会在流完成后删除文件

-O, --omit-from-file                         # 包含数据库列表的文件。要跳过的表项，每行一个(在应用regex选项之前跳过)

-T, --tables-list                            # 要备份的表，以逗号隔开（不排除regex选项）

-h, --host                                   # 连接的主机地址

-u, --user                                   # 用户名

-p, --password                               # 密码

-a, --ask-password                           # 提示输入用户密码

-P, --port                                   # 端口

-S, --socket                                 # 用于连接的UNIX域套接字文件

-x, --regex                                  # 正则表达式

--skip-definer                               # 从CREATE语句中移除DEFINER。缺省情况下，不修改语句

常用命令：

从备份中恢复指定的库：

$ myloader -u root -p root -s test01 -o -d /u01/mysql/backup

导入时开启binlog：

$ myloader -u root -p root -e -s test01 -o -d /u01/mysql/backup

将源库的testdb导入到备库的test01库：

$ myloader -u root -p root -B test02 -s test01 -o -d /u01/mysql/backup

导入特定的表（备份文件里包含表的结构和数据文件）

myloader -u root -p root  -B test -o -d /u01/mysql/backup

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