MySQL备份迁移之mydumper

Max的技术札记 2023-03-28 原文

本文笔者示例中的 mydumper 版本为 mydumper 0.9.1。

简介

mydumper 是一款开源的 MySQL 逻辑备份工具，主要由 C 语言编写。与 MySQL 自带的 mysqldump 类似，但是 mydumper 更快更高效。
mydumper 的一些优点特性：

轻量级C语言开发
支持多线程备份数据，备份后按表生成多个备份文件
支持事务性和非事务性表一致性备份
支持将导出的文件压缩，节约空间
支持多线程恢复
支持已守护进程模式工作，定时快照和连续二进制日志
支持按指定大小将备份文件切割
数据与建表语句分离

下载安装

安装方式非常多，以下介绍几种常见的方式。

Ubuntu 中自带了 myloader

sudo apt-get install mydumper

使用 deb 包安装，以 Ubuntu 为例

apt-get install libatomic1
wget https://github.com/mydumper/mydumper/releases/download/v0.11.5/mydumper_0.11.5-1.$(lsb_release -cs)_amd64.deb dpkg -i mydumper_0.11.5-1.$(lsb_release -cs)_amd64.deb

编译安装
docker 安装

根据实际平台情况，可选择不同的安装方式，官方也提供了一些常见的安装文档，https://github.com/mydumper/mydumper

参数说明

mydumper 参数说明

-B, --database              要备份的数据库，不指定则备份所有库，一般建议备份的时候一个库一条命令
-T, --tables-list           需要备份的表，名字用逗号隔开
-o, --outputdir             备份文件输出的目录
-s, --statement-size        生成的insert语句的字节数，默认1000000
-r, --rows                  将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize        将表按大小分块时，指定的块大小，单位是 MB
-c, --compress              压缩输出文件
-e, --build-empty-files     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
-x, --regex                 是同正则表达式匹配 'db.table'
-i, --ignore-engines        忽略的存储引擎，用逗号分割
-m, --no-schemas            不备份表结构
-d, --no-data               不备份表数据
-G, --triggers              备份触发器
-E, --events                备份事件
-R, --routines              备份存储过程和函数
-W, --no-views              不备份视图
--where                     只导出符合条件的数据
-k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致
--less-locking              减少对InnoDB表的锁施加时间（这种模式的机制下文详解）
-l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
--kill-long-queries         杀掉长查询 (不退出)
-b, --binlogs               导出binlog
-D, --daemon                启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下
-L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc                    跨时区时使用的选项。允许备份timestamp,这样会导致不同时区的备份还原出问题，默认关闭。
--skip-tz-utc               同上，默认值。
--use-savepoints            使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
--success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host                  连接的主机名
-u, --user                  备份所使用的用户
-p, --password              密码
-P, --port                  端口
-S, --socket                使用socket通信时的socket文件
-t, --threads               开启的备份线程数，默认是4
-C, --compress-protocol     压缩与mysql通信的数据
-V, --version               显示版本号
-v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

myloader 参数说明

-d, --directory                   备份文件的文件夹
-q, --queries-per-transaction     每次事务执行的查询数量，默认是1000
-o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
-B, --database                    还原到的数据库（目标库）
-s, --source-db                   被还原的数据库（源数据库），-s db1 -B db2，表示源库中的db1数据库，导入到db2数据库中。
-e, --enable-binlog               启用还原数据的二进制日志，具体见下面关于 -e 的提示说明
-h, --host                        主机
-u, --user                        还原的用户
-p, --password                    密码
-P, --port                        端口
-S, --socket                      socket文件
-t, --threads                     还原所使用的线程数，默认是4
-C, --compress-protocol           压缩协议
-V, --version                     显示版本
-v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

常用案例

mydumper 导出示例

# 个人实际中最常用的备份语句
mydumper -B test -o /home/mydumper/data/test -e -G -R -E -D -u root -p 123456 -h 192.168.0.191 -P 3306 -v 3 --long-query-guard 288000 --skip-tz-utc --no-locks --logfile /home/mydumper/log/test

# 备份全部数据库 
mydumper -u root -p 123456 -o /home/mydumper/data/all/

# 备份全部数据库，排除系统库，
mydumper -u root -p 123456 --regex '^(?!(mysql|sys|performance_schema|information_schema))' -o /home/mydumper/data/all/

# 备份全部数据库，包含触发器、事件、存储过程及函数
mydumper -u root -p 123456 -G -R -E -o /home/mydumper/data/all/

# 备份指定库
mydumper -u root -p 123456 -G -R -E -B db1 -o /home/mydumper/data/db1

# 备份指定表
mydumper -u root -p 123456 -B db1 -T tb1,tb2 -o /home/mydumper/data/db1

# 只备份表结构
mydumper -u root -p 123456 -B db1 -d -o /home/mydumper/data/db1

# 只备份表数据
mydumper -u root -p 123456 -B db1 -m -o /home/mydumper/data/db1

myloader 导入案例

# 个人实际中最常用的导入语句
myloader -h 192.168.0.192 -P 33306 -u root -p 123456 -t 1 -v 3 -d /home/mydumper/data/test/0/ -B test

# 从备份中恢复指定库
myloader -u root -p 123456 -s db1 -o -d /home/mydumper/data/all/0/

# 导入时开启 binlog
myloader -u root -p 123456 -e -o -d /home/mydumper/data/db1/0/

# 将源库的 db1 导入到备库的 db1_bak 库中
myloader -u root -p 123456 -B db1_bak -s db1 -o -d /home/mydumper/data/db1/0/

# 导入特定的某几张表
## 先将 metadata 文件和需要单独导入的表的结构文件和数据文件导入到单独的文件夹中。此处默认库已建好，否则还需要复制建库相关语句。
cp /home/mydumper/data/db1/0/metadata /backup/db1/0/
cp /home/mydumper/data/db1/0/d1.t1-schema.sql /backup/db1/0/
cp /home/mydumper/data/db1/0/d1.t1.sql /backup/db1/0/
## 从新文件夹中导入数据
myloader -u root -p 123456 -B db1 -d /backup/db1/0/
## 以上就可以单独导入 db1.t1 表

关于 -e 参数，需要稍微注意下。默认情况下，myloader 是不开启 binlog 的，这样可以提高导入速度。如果导入实例有从库，且需要导入的结果同步到从库上，则需要使用 -e 打开 binlog 记录。

导出之后的目录如下，以数据库 d1 ，其中有表 t1 为例：

-d1
 -0
   metadata             记录备份时间点的Binlog信息，日志文件名和写入位置
   d1-schema-create.sql 建库语句
   d1-schema-post.sql   存储过程，函数，事件创建语句
   d1.t1-schema.sql     表结构文件
   d1.t1.sql            表数据文件，若使用了分块参数，大表的数据文件会出现多个，以数字分开。
 -1

以上为比较常见的导出后的目录结构，根据实际情况不同，可能还有会含有触发器的文件，含有视图的文件等。

常见问题与实践经验

Error switching to database whilst restoring table

使用 myloader 导入时会出现这类报错，可以尝试的解决方法如下：调大 wait_timeout 参数；调大 max_packet_size 参数；使用一个线程导入， -t 1。

(myloader:35671): CRITICAL **: Error restoring test.email_logger from file test.email_logger.sql: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

MySQL 的一个 Bug，可以尝试手动修改对应的备份文件，将

/*!40101 SET NAMES binary*/;

修改为：

/*!40101 SET NAMES utf8mb4*/;

(myloader:34726): CRITICAL **: Error restoring test.(null) from file test-schema-post.sql: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

在导入 AWS RDS 时部分存储过程创建失败，有比较严格的权限限制，需要导入用户有 SUPER 权限，但是 AWS RDS 用户无法授予 SUPER 权限。针对这部分存储过程，可以考虑手动在备份库上创建。

大表导出优化

使用 -r或-F参数，对导出的数据文件进行分片。

备份机器配置尽可能高

备份前先预估大小，避免机器磁盘不足。尽可能选用配置较高的机器，加快备份速度。

非必要数据不备份

备份前对于不用备份的数据可以提前进行一次删除，也可在导出数据时添加正则参数等过滤部分表

备份尽量不跨网络

备份数据时尽量在内网中进行，若需要将数据迁移到外网，可以备份完之后，将备份文件拷贝到外网服务器上，尽量减少导出时网络不稳定的干扰。导入时同理。

加快导入速度的一些方法

选择合适的线程数，根据实际情况和机器配置，选择合适的线程参数，并非线程数越多越快。
导入时关闭 MySQL 的 binlog 写入，待导入完成后再开启。
在内网或较稳定的环境中进行导入。

原理与架构

mydumper 工作流程

主要步骤概括

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

myloader 工作原理

更多技术文章，请关注我的个人博客 www.immaxfang.com 和小公众号 Max的学习札记。

有关MySQL备份迁移之mydumper的更多相关文章

ruby-on-rails - Ruby on Rails 迁移，将表更改为 MyISAM - 2
如何正确创建Rails迁移，以便将表更改为MySQL中的MyISAM？目前是InnoDB。运行原始执行语句会更改表，但它不会更新db/schema.rb，因此当在测试环境中重新创建表时，它会返回到InnoDB并且我的全文搜索失败。我如何着手更改/添加迁移，以便将现有表修改为MyISAM并更新schema.rb，以便我的数据库和相应的测试数据库得到相应更新？最佳答案我没有找到执行此操作的好方法。您可以像有人建议的那样更改您的schema.rb，然后运行:rakedb:schema:load，但是，这将覆盖您的数据。我的做法是(假设
ruby - 使用 Vim Rails，您可以创建一个新的迁移文件并一次性打开它吗？ - 2
使用带有Rails插件的vim，您可以创建一个迁移文件，然后一次性打开该文件吗？textmate也可以这样吗？最佳答案你可以使用rails.vim然后做类似的事情::Rgeneratemigratonadd_foo_to_bar插件将打开迁移生成的文件，这正是您想要的。我不能代表textmate。关于ruby-使用VimRails，您可以创建一个新的迁移文件并一次性打开它吗？，我们在StackOverflow上找到一个类似的问题： https://sta
使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
ruby-on-rails - 在现有数据库上进行 Rails 迁移 - 2
我正在创建一个新的Rails3.1应用程序。我希望这个新应用程序重用现有数据库(由以前的Rails2应用程序创建)。我创建了新的应用程序定义模型，它重用了数据库中的一些现有数据。在开发和测试阶段，一切正常，因为它在干净的表数据库上运行，但是当尝试部署到生产环境时，我收到如下消息:PGError:ERROR:column"email"ofrelation"users"alreadyexists***[err::localhost]:ALTERTABLE"users"ADDCOLUMN"email"charactervarying(255)DEFAULT''NOTNULL但是我在迁移中有这
ruby - 如何使用 ruby mysql2 执行事务 - 2
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
ruby-on-rails - Rails 迁移中的 PostgreSQL 点类型 - 2
我想使用PostgreSQL中的point类型。我已经完成了:railsgmodelTestpoint:point最终的迁移是:classCreateTests当我运行时:rakedb:migrate结果是:==CreateTests:migrating====================================================--create_table(:tests)rakeaborted!Anerrorhasoccurred,thisandalllatermigrationscanceled:undefinedmethod`point'for#/hom
ruby-on-rails - Rails 3 : belongs_to, has_one 和迁移 - 2
我是Rails的新手，我是从Django背景开始接触它的。我已经接受了这样一个事实，即模型和数据库模式在Rails和在线Django中是分开的。但是，我仍在努力处理迁移。我的问题很简单-如何使用迁移向模型添加关系？例如，我现在有Artist和Song作为ActiveRecord::Base子类的空模型，没有任何关系。我需要开始做这件事:classArtist但是我如何使用railsgmigrate更改架构以反射(reflect)这一点？我正在使用Rails3.1.3。最佳答案现在，在Rails4中，您可以:classAddPro
ruby-on-rails - Rails 4 从迁移索引中删除迁移 ID - 2
如何在rakedb:migrate:status中删除带有“**NOFILE**”的迁移ID列表？例如:StatusMigrationIDMigrationName--------------------------------------------------up20131017204224Createusersup20131218005823**********NOFILE**********up20131218011334**********NOFILE**********我不明白为什么当我自己手动删除它时它仍然保留旧的迁移文件，因为我正在研究迁移的工作原理。这是为了记录吗？但
ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2
我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。