MySQL备份迁移之mydumper

简介

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

  • 轻量级C语言开发
  • 支持数据多线程备份,备份后按表生成多个备份文件
    [En]

    support multi-thread backup of data, and generate multiple backup files according to table after backup*

  • 支持事务性和非事务性表一致备份
    [En]

    support transactional and non-transactional table consistent backups*

  • 支持压缩导出文件以节省空间
    [En]

    support to compress exported files to save space*

  • 支持多线程恢复
  • 支持守护程序模式、计划快照和连续二进制日志
    [En]

    support daemon mode, scheduled snapshots and continuous binary logs*

  • 支持按指定大小对备份文件进行切片
    [En]

    support slicing backup files according to specified size*

  • 数据与建表语句分离

下载安装

安装的方法有很多种。以下是一些常见的问题。

[En]

There are many ways to install. Here are some common ones.

  • 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               启用还原数据的二进制日志
-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 导出示例

个人实践中最常用的BACKUP语句<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>The most commonly used backup statements in personal practice</font>*</details>
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/

备份所有数据库并排除系统库<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Back up all databases and exclude system libraries</font>*</details>
mydumper -u root -p 123456 --regex '^(?!(mysql|sys|performance_schema|information_schema))' -o /home/mydumper/data/all/

备份所有数据库,包括触发器、事件、存储过程和函数<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>Back up all databases, including triggers, events, stored procedures, and functions</font>*</details>
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 导入案例

个人实践中最常用的导入语句<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>The most commonly used import statements in personal practice</font>*</details>
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             &#x8BB0;&#x5F55;&#x5907;&#x4EFD;&#x65F6;&#x95F4;&#x70B9;&#x7684;Binlog&#x4FE1;&#x606F;&#xFF0C;&#x65E5;&#x5FD7;&#x6587;&#x4EF6;&#x540D;&#x548C;&#x5199;&#x5165;&#x4F4D;&#x7F6E;
   d1-schema-create.sql &#x5EFA;&#x5E93;&#x8BED;&#x53E5;
   d1-schema-post.sql   &#x5B58;&#x50A8;&#x8FC7;&#x7A0B;&#xFF0C;&#x51FD;&#x6570;&#xFF0C;&#x4E8B;&#x4EF6;&#x521B;&#x5EFA;&#x8BED;&#x53E5;
   d1.t1-schema.sql     &#x8868;&#x7ED3;&#x6784;&#x6587;&#x4EF6;
   d1.t1.sql            &#x8868;&#x6570;&#x636E;&#x6587;&#x4EF6;&#xFF0C;&#x82E5;&#x4F7F;&#x7528;&#x4E86;&#x5206;&#x5757;&#x53C2;&#x6570;&#xFF0C;&#x5927;&#x8868;&#x7684;&#x6570;&#x636E;&#x6587;&#x4EF6;&#x4F1A;&#x51FA;&#x73B0;&#x591A;&#x4E2A;&#xFF0C;&#x4EE5;&#x6570;&#x5B57;&#x5206;&#x5F00;&#x3002;
 -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 写入,待导入完成后再开启。
    在内部网或更稳定的环境中导入。
    [En]

    Import in an intranet or in a more stable environment.

原理与架构

mydumper 工作流程

MySQL备份迁移之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 工作原理

MySQL备份迁移之mydumper

更多技术文章,请关注我的个人博客 www.immaxfang.com 和小公众号 Max&#x7684;&#x5B66;&#x4E60;&#x672D;&#x8BB0;

Original: https://www.cnblogs.com/immaxfang/p/16188555.html
Author: immaxfang
Title: MySQL备份迁移之mydumper

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505452/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 在浏览器中Django项目的静态文件打不开的一个原因

    2022-09-27 问题描述: 编写Django代码时,设置了一个”static”文件夹,在里面放置了一张图片。在”setting&#8221…

    数据库 2023年6月14日
    0162
  • 主从复制架构直接转换MGR(manual)

    IP port role info 192.168.188.81 3316 node1 master 192.168.188.82 3316 node2 slave1 192.16…

    数据库 2023年6月16日
    0270
  • 日月既往,不可复追,暑期实习结束!

    在从上海回南京的高铁上码下了这篇文章,心中感慨万千, 两个月弹指一挥间,初来时还略有不适,突然要走了竟然还生出一些留念,所谓 “天可补,海可填,南山可移,日月既往,不可…

    数据库 2023年6月6日
    0210
  • jmeter使用base64验证码登录

    依赖环境/工具:java环境、jmeter、python3、pycharm(ide工具) 此次主要操作步骤: 使用jmeter工具把验证码base64加密信息保存至本地。 编写py…

    数据库 2023年6月14日
    0137
  • 数据库_多表查询_内连接&外连接

    首先明确一下定义: 内连接:在多表查询的时候,只检索出表之间相互匹配的行(满足匹配条件的行),结果中不包括两个表之间不匹配的行。 一个不太准确但易懂的例子:比如有两个表,表1存储了…

    数据库 2023年6月16日
    0203
  • 在windows上用docker desktop安装StoneDB

    自6月底开源以来,许多热心的社区用户都对StoneDB进行了编译和测试,也有一些用户询问StoneDB是否会支持Windows。虽然适配Windows版本的StoneDB尚未进入研…

    数据库 2023年5月24日
    0145
  • Java面试题(七)–Spring Boot

    1、Spring Boot提供了哪些核心功能?(高频) 1、jar包方式运行 通过引入spring-boot-maven-plugin插件可以将springboot项目打包成一个可…

    数据库 2023年6月16日
    0153
  • MySQL常用数据类型及细节

    类型 类型举例 整数类型 TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT 浮点类型 FLOAT、DOUBLE 定点数类型 DECIM…

    数据库 2023年5月24日
    0136
  • MySQL使用步骤

    出现mysqld: Can’t create directory ‘D:\Environment\mysql-5.7.37 \data’ (Er…

    数据库 2023年5月24日
    0191
  • MYSQL–>视图

    视图就是一种 虚拟存在的表。因为视图的数据不在数据库中实际存在。 视图的行和列的数据都来自于 我们定义视图所使用的表 其中,定义视图所使用的表叫 基表 视图的行和列的数据是在使用视…

    数据库 2023年6月14日
    0125
  • 图片防盗链

    如何避免别的网站直接通过本网站的url访问本网站资源 简单的防盗 我可以做到请求来的时候先看看当前请求是从哪个网站过来的如果是本网站那么正常访问如果是其他网站直接拒绝请求头里面有一…

    数据库 2023年6月14日
    0123
  • MySQL高可用安装

    MySQL HA部署 环境准备 创建本地yum源 确认关闭 SELinux 防火墙设置 MySQL安装 使用 root 用户操作创建相关的用户组和用户 上传/解压介质 设置自启动 …

    数据库 2023年6月16日
    0105
  • http状态码总结

    表示临时响应并需要请求者继续执行操作的状态代码。 100 (继续) 请求者应当继续提出请求。 服务器返回此代码表示已收到请求的第一部分,正在等待其余部分。101 (切换协议) 请求…

    数据库 2023年6月6日
    0119
  • leetcode 513. Find Bottom Left Tree Value 找树左下角的值 (简单)

    给定一个二叉树的 根节点 root,请找出该二叉树的 最底层 最左边 节点的值。 假设二叉树中至少有一个节点。 示例 1: 输入: root = [2,1,3]输出: 1 示例 2…

    数据库 2023年6月16日
    0149
  • Linux–>定时任务调度

    指定系统在某个时间执行特点的命令或程序。 任务调度分类: crontab 选项 常用选项 选项 说明 -e 编辑crontab定时任务 -l 查询crontab任务 -r 删除当前…

    数据库 2023年6月14日
    0114
  • nexus3搭建私有Docker仓库

    Nexus3: version: "3" services: nexus: image: sonatype/nexus3:3.33.0 volumes: – /…

    数据库 2023年6月9日
    0134
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球