Mydumper 数据库备份简单教程

发表于 2023-04-02 更新于 2023-03-31 分类于 iTech ， MYSQL 阅读次数： Waline：

0x01 简介

Mydumper 是一个针对 MySQL 和 Drizzle 的高性能多线程备份和恢复工具，其他百度…

项目地址

1	https://github.com/maxbube/mydumper

备份工具比较

工具	区别
mysqldump	属于逻辑备份，会存在锁表
xtrabackup	属于物理备份，不存在锁表
mydumper	属于逻辑备份，锁表的时间很短（40G数据，10分钟之内），同时会记录 `binlog file` 和 `POS`

0x02 安装

本次部署系统环境 Ubuntu20.04，后面操作均在 root 下操作

直接安装
编译安装

直接安装

1
2
3

release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
wget https://github.com/mydumper/mydumper/releases/download/${release}/mydumper_${release:1}.$(lsb_release -cs)_amd64.deb
dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb

Centos

安装完成后生成两个二进制文件

mydumper：导出数据
myloader：导入数据

0x03 语法

mydumper

导出数据

1	mydumper [选项]

选项	注释
-B, –database	要备份的数据库，不指定则备份所有库
-o, –outputdir	备份文件输出的目录
-d, –no-data	不备份表数据
-D, –daemon	守护进程模式
-L, –logfile	备份过程的日志文件
–disk-limits	如果确定没有足够的磁盘空间，则暂停和恢复的限制
-t, –threads	线程数，默认 `4`
-C, –compress-protocol	压缩与mysql通信的数据
-v, –verbose	日志输出级别，默认 `2` `0`：无提示 `1`：错误 `2`：警告 `3`：信息
–defaults-file	使用特定的默认文件
–ssl	使用SSL连接
–ssl-mode	与服务器连接的所需安全状态
–key	SSL私钥
–cert	SSL证书
–ca	SSL CA证书
–capath	包含PEM格式的受信任SSL CA证书的目录的路径名
–cipher	用于SSL加密的允许密码列表
–tls-version	服务器允许加密连接使用哪些协议
–stream	一旦文件被写入，它将通过标准输出流
–no-delete	流完成后，它不会删除文件
-O, –omit-from-file	包含数据库列表的文件。要跳过的表条目，每行一个
-T, –tables-list	需要备份的表，逗号隔开
-h, –host	MySQL 服务端地址
-u, –user	备份所使用的用户
-p, –pasword	连接所用的用户密码
-a, –ask-password	流完成后，它不会删除文件
-P, –port	MySQL 端口号
-S, –socket	使用socket通信时的socket文件
-x, –regex	使用正则表达式匹配 `db.table`
-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	跨时区是使用的选项
–skip-tz-utc	同上
-z, –tidb-snapshot	用于TiDB的快照
-N, –insert-ignore	使用 `INSERT IGNORE` 转储行
-s, –statement-size	生成的 `insert` 语句的字节数，默认 `1000000`
-F, –chunk-filesize	将表按大小分割时，指定分割大小，单位是 MB
-e, –build-empty-files	如果表数据是空，还是产生一个空文件
–where	只导出选择的数据
-i, –ignore-engines	忽略的存储引擎
–load-data
–csv	导出csv格式
-b, –binlogs	导出binlog
-r, –rows	将表按行分割，指定这个选项会关闭 `--chunk-filesize`
-c, –compress	压缩输出文件
–exec	命令使用文件作为参数执行
-l, –long-query-guard	设定阻塞备份的长查询超时时间，默认 `60` 秒
-k, –no-locks	不使用临时共享只读锁，使用这个选项会造成数据不一致
–less-locking	减小对InnoDB表的锁施加时间
-m, –no-schemas	不备份表结构
-K, –kill-long-queries	杀掉长查询 (不退出)
-I, –snapshot-interval	dump快照间隔时间，默认60s，需要在daemon模式下
–use-savepoints	使用 `savepoints` 来减少采集metadata所造成的锁时间

myloader

导入数据

1	myloader [选项]

选项	注释
-d, –directory	数据库备份的目录，从这个目录进行数据恢复
-q, –queries-per-transaction	每次事物执行的查询数量，默认 `1000`
-o, –overwrite-tables	如果表存在，则先清理表
–append-if-not-exist	添加IF NOT EXISTS到建表语句中
-B, –database	须要还原的数据库
-s, –source-db	被还原的数据库（源数据库）
-e, –enable-binlog	启用还原数据的二进制日志
–set-names	设置名称，默认二进制
-L, –logfile	还原备份过程的日志文件
–defaults-file	使用特定的默认文件
-r, –rows	将表按行分割，指定这个选项会关闭 `--chunk-filesize`
–max-threads-per-table	每个表使用的最大线程数，默认 `4`
-t, –threads	还原所使用的线程数，默认 `4`
-C, –compress-protocol	压缩协议
-v, –verbose	日志输出级别，默认 `2`
–ssl	使用SSL连接
–ssl-mode	与服务器连接的所需安全状态
–key	SSL私钥
–cert	SSL证书
–ca	SSL CA证书
–capath	包含PEM格式的受信任SSL CA证书的目录的路径名
–cipher	用于SSL加密的允许密码列表
–tls-version	服务器允许加密连接使用哪些协议
–stream	一旦文件被写入，它将通过标准输出流
–no-delete	不会在流完成后删除文件
-O, –omit-from-file	包含数据库列表的文件。要跳过的表项，每行一个
-T, –tables-list	需要还原的表，逗号隔开
-u, –user	备份所使用的用户
-p, –pasword	连接所用的用户密码
-h, –host	MySQL 服务端地址
-P, –port	MySQL 端口号
-S, –socket	Socket 文件

0x04 使用

备份

以当天日期时间做为名称建立文件夹

1	mkdir `date +%Y%m%d%H%M%S`

常规备份

1	mydumper -h localhost -u root -p passwd -B dbanme -c -t 6 -v 3 -o /data/backup/dbname -L /data/backup/dbname-mydumper.log

选项	注释
-h	地址
-u	用户
-p	密码
-B	数据库
-c	压缩
-t	线程数
-v	日志级别
-o	备份地址
-L	备份日志

备份所有数据库

1	mydumper -h localhost -u root -p passwd -c -t 6 -v 3 -o /data/backup/dbinstance -L /data/backup/dbinstance-mydumper.log

备份指定的表（或者多表，多个表之间逗号分割）

1	mydumper -h localhost -u root -p passwd -B dbanme -T table1,table2 -c -t 6 -v 3 -o /data/backup/dbname -L /data/backup/dbname-mydumper.log

使用正则排除系统库或表

1	mydumper -h localhost -u root -p passwd -G -R -E --regex '^(?!(yiqifa_bs_push\|mysql\|sys))' -o /data/backup/dbname

带存储过程触发器的备份

1	mydumper -h localhost -u root -p passwd -B dbanme -G -R -c -t 6 -v 3 -o /data/backup/dbname -L /data/backup/dbname-mydumper.log

只备份表结构

1	mydumper -h localhost -u root -p passwd -d -B db1 -o /mysql_backup/nodata/

只备份表数据

1	mydumper -h localhost -u root -p passwd -m -B db1 -o /mysql_backup/noschema/

文件结构

使用 mydumper 备份会产生多个文件，比如建库、建表、表数据等都分别在不同文件中。以 db1 库的全备为例，来看下备份出的文件命名规则及作用

$ tree /mysql_backup/db1/
.
├── db1-schema-create.sql
├── db1-schema-post.sql
├── db1.tb1.metadata
├── db1.tb1-schema.sql
├── db1.tb1-schema-triggers.sql
├── db1.tb1.sql
├── db1.tb2.metadata
├── db1.tb2-schema.sql
├── db1.tb2.sql
├── db1.tb3.metadata
├── db1.tb3-schema.sql
├── db1.view1-schema.sql
├── db1.view1-schema-view.sql
└── metadata

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

文件名	注释
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	记录开始及结束备份的时间以及二进制日志位置

恢复

常规恢复

1	myloader -h localhost -u root -p passwd -B dbanme -d /data/backup/dbname -o -v 3

选项	注释
-h	地址
-u	用户
-p	密码
-B	数据库
-d	还原目录
-o	备份地址
-v	日志级别

恢复到指定的数据库，备份的是dbsource，恢复到 dbtarget

1	myloader -h localhost -u root -p passwd -B dbtarget --source-db=dbsource -d /data/backup/dbname -o -v 3