基于 mydumper 实现 MySQL 定期全量备份、恢复方案

已于 2024-03-24 10:20:23 修改
阅读量1k 收藏 29
点赞数 22
分类专栏： MySQL 文章标签： mysql 数据库
于 2024-03-14 17:39:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43692950/article/details/136711943
版权

一、Mydumper 工具介绍

mydumper 是一款社区开源的逻辑备份工具，由 C 语言编写，与 MySQL 官方提供的 mysqldump 相比，它具有更高的性能和更多的功能，例如：

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

  • 支持一致性备份；

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

  • 支持多线程恢复；

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

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

GitHub 地址：https://github.com/maxbube/mydumper

官网地址：https://launchpad.net/mydumper

与其他常见备份工具对比

备份工具安装难度备份类型备份速度备份即时点压缩备份远程备份
mysqldump自带逻辑慢不支持不支持支持
xtrabackup一般物理较快支持不支持不支持
mydumper简单逻辑快支持支持支持

Mydumper 安装

下载安装包：

wget https://github.com/maxbube/mydumper/releases/download/v0.10.7-2/mydumper-0.10.7-2.el7.x86_64.rpm

安装：

rpm -ivh mydumper-0.10.7-2.el7.x86_64.rpm

查看安装版本：

mydumper -V

在这里插入图片描述

核心命令介绍

在 mydumper 中主要使用到两个命令，mydumper 和 myloader ，其中 mydumper 主要用来做数据的备份，myloader 用来进行数据的恢复。

其中 mydumper 所支持的参数如下：

参数缩写解释
–user-u用户名
–pasword-p密码
–host-h地址
–port-P端口
–threads-t备份时的线程数，默认 4
–database-B备份的数据库名称，不指定则备份所有库
–tables-list-T备份的表，名字用逗号隔开，不指定则备份所有表
–outputdir-o备份输出目录
–statement-size-s指定备份文件中每个 SQL 语句的最大大小，默认1000000，与–rows冲突
–rows-r将表按行分割，指定了会关闭 --chunk-filesize
–chunk-filesize-F按大小分割时，指定分割大小，单位是 M
–regex-x使用正则表达式匹配
–compress-c压缩输出文件
–ignore-engines-i忽略的存储引擎
–no-schemas-m不备份表结构
–no-data-d不备份表数据
–triggers-G备份触发器
–events-E备份事件
–routines-R备份存储过程和函数
–no-views-W不备份视图
–no-locks-k不使用临时共享只读锁，使用这个选项会造成数据不一致
–daemon-D启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
–where只导出选择的数据

myloader 所支持的参数如下：

参数名缩写含义
–user-u用户名
–pasword-p密码
–host-h地址
–port-P端口
–threads-t恢复时的线程数，默认4
–directory-d要恢复的备份目录
–queries-per-transaction-q每次事务执行的查询数量，默认是1000
–overwrite-tables-o如果要恢复的表存在，则先drop掉该表
–database-B需要还原到哪个数据库（目标数据库）
–source-db-s选择被还原的数据库（源数据库）
–enable-binlog-e在恢复时开启binlog

下面以一个案例来介绍和使用 Mydumper：

首先创建测试库和测试表：

create database testdb;
use testdb;

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `status` int DEFAULT NULL,
  `delete_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `delete_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `user_role_mapping` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `role_id` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

写入测试数据：

INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (1, '张三', 15, 'zhangsan@test.com', 'zhangsan', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (2, '李四', 16, 'lisi@test.com', 'lisi', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (3, '王五', 15, 'wangwu@test.com', 'wangwu', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (4, '李六', 18, 'liliu@test.com', 'liliu', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (5, '小红', 15, 'xiaohong@test.com', 'xiaohong', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (6, '小明', 19, 'xiaoming@test.com', 'xiaoming', '123', 1, '0');
INSERT INTO `testdb`.`user` (`id`, `name`, `age`, `email`, `username`, `password`, `status`, `delete_flag`) VALUES (7, '小张', 15, 'xiaozhang@test.com', 'xiaozhang', '123', 1, '0');

INSERT INTO `testdb`.`role` (`id`, `role_name`, `desc`, `delete_flag`) VALUES (1, 'admin', '管理员', '0');
INSERT INTO `testdb`.`role` (`id`, `role_name`, `desc`, `delete_flag`) VALUES (2, 'root', '超级管理员', '0');
INSERT INTO `testdb`.`role` (`id`, `role_name`, `desc`, `delete_flag`) VALUES (3, 'common', '普通人', '0');
INSERT INTO `testdb`.`role` (`id`, `role_name`, `desc`, `delete_flag`) VALUES (4, 'leader', '组长', '0');

INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (1, 1, 1);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (2, 2, 1);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (3, 3, 3);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (4, 4, 3);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (5, 5, 4);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (6, 6, 4);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (7, 7, 3);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (8, 1, 2);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (9, 1, 4);
INSERT INTO `testdb`.`user_role_mapping` (`id`, `user_id`, `role_id`) VALUES (10, 2, 4);

二、全量备份和恢复实验

创建备份目录：

mkdir -p /data/backup/bak1

全量备份：

mydumper -h 192.168.40.20 -u root -p root123 -G -R -E -r 100000 -c -o /data/backup/bak1

在这里插入图片描述

从结果中可以看出，里面也包括了 mysql、sys 等系统库。

下卖尝试删除 testdb 数据库：

drop database testdb;

show databases;

在这里插入图片描述

数据恢复：

myloader -h 192.168.40.20 -u root -p root123 -o -d /data/backup/bak1

在这里插入图片描述
查看数据库：

在这里插入图片描述
数据已经成功恢复。

三、单独库表备份和恢复实验

创建备份目录：

mkdir -p /data/backup/bak2

备份 user和role 表

mydumper -h 192.168.40.20 -u root -p root123 -r 100000 -B testdb -T user,role -c -o /data/backup/bak2

在这里插入图片描述

删除 user 表：

在这里插入图片描述

恢复表数据：

myloader -h 192.168.40.20 -u root -p root123 -o -s testdb  -d /data/backup/bak2

在这里插入图片描述

查看数据：

在这里插入图片描述

注意此时 role 表也会被覆盖为原先备份的状态，如果需要单独恢复可以使用自带的 source 命令。

四、定期全量备份数据

定期备份

创建 backup.sh 文件，内容如下：

#!/bin/bash
# MySQL数据库信息
USER='root'
PASSWORD='root123'
HOST='192.168.40.20'
# 备份文件路径和名称
BACKUP_DIR='/data/backup'
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S)"
# 使用 mydumper 备份数据库
mydumper -h $HOST -u $USER -p $PASSWORD -G -R -E -r 100000 -c -x '^(?!(mysql|sys))' -o $BACKUP_FILE
echo $BACKUP_FILE

授予执行权限：

chmod +x backup.sh

添加定时任务：

crontab -e

最后加入下面内容：

*/2 * * * * /data/backup/backup.sh >> /data/backup/backup.log 2>&1

注意：这里为了展示效果，每2分钟执行一次，你用的时候需要修改，例如每天凌晨2点执行：0 2 * * *

等待一会后可看到备份的文件：

在这里插入图片描述

定期删除

备份的文件一直保存下去对磁盘的占用也有一定成本，所以一些旧的备份就可以删除掉了，同样创建 remove.sh 脚本，写入如下内容：

#!/bin/bash
# 备份文件路径
BACKUP_DIR='/data/backup/'
# 查找并删除旧的备份文件
find $BACKUP_DIR -type d -name "backup_*" -mmin +5 -exec rm -rf {} \;
echo "清理备份文件"

注意：这里为了展示效果，每次删除5分钟之前创建的目录，你用的时候需要修改。

授予执行权限：

chmod +x remove.sh

下面同样加入定时任务中执行：

crontab -e

最后加入下面内容：

*/5 * * * * /data/backup/remove.sh >> /data/backup/remove.log 2>&1

每 5 分钟触发一次。

五、其他示例：

备份时使用正则排除系统库：

mydumper -h 192.168.40.20 -u root -p root123-G -R -E  -r 100000 -x '^(?!(mysql|sys))' -o /data/backup/bak3

仅备份表结构

mydumper -h 192.168.40.20 -u root -p root123-d  -r 100000 -B  testdb -o /data/backup/bak4

仅备份表数据

mydumper -h 192.168.40.20 -u root -p root123 -m  -r 100000 -B testdb -o /data/backup/bak5

压缩备份表

mydumper -h 192.168.40.20 -u root -p root123-r 100000 -B testdb -T user -c -o /data/backup/bak6

将某个数据备份还原到另一个数据库中，目标不存在会新建：

myloader -h 192.168.40.20 -u root -p root123-B testdb2 -s testdb -o -d /data/backup/bak3

如果是主从复制，恢复时需要开启binlog：

myloader -h 192.168.40.20 -u root -p root123 -e -o -d /data/backup/bak3
小毕超
关注
  • 22
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
使用mydumper多线程备份MySQL数据库
09-10
MySQL在备份方面包含了自身的mysqldump工具，但其只支持单线程工作，这就使得它无法迅速的备份数据。而 mydumper作为一个实用工具，能够良好支持多线程工作，这使得它在处理速度方面十倍于传统的
mysql全量备份、增量备份实现方法
09-09
主要介绍了mysql全量备份、增量备份实现方法,需要的朋友可以参考下
mysql数据库全量备份脚本
01-23
直接使用
mydumper与mysqldump备份脚本
u010719917的专栏
03-22 227
#!/bin/bash #export LANG=zh_CN.UTF8 script_dir=/root/scripts #如果没有备份文件夹则建立 .
9、Mydumper和Myloader多线程备份恢复
weixin_33912453的博客
06-19 258
<--目录-->1）mydumper介绍2）mydumper特性3）mydumper参数4）mydumper安装方法5) mydumper效率测试6）mydumper导出实战7）mydumper导出的文件介绍8）myloader参数9）myloader导入实战【mydumper介绍】mydumper是一个针对 MySQL 和 Drizzle 的高性能多线程备份和...
使用mydumper进行数据库备份还原
Z_41527的博客
07-21 220
备份数据库school到/backup目录,备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库,直接将MySQL数据库压缩备份,备份MySQL数据库某个(些)表。此例备份student表,同时备份多个MySQL数据库（其他数据库素材自行准备）,仅仅备份数据库结构,备份服务器上所有数据库,还原MySQL数据库,还原压缩的MySQL数据库，使用xtrabackup 备份数据库，在另外的数据库服务器上还原xtrabackup备份，使用mydumper备份数据库，恢复数据库
【MySQL备份】MySQL备份工具-MyDumper
最新发布
DBA之路的博客
12-13 1341
MyDumper是一个MySQL逻辑备份工具。mydumper负责导出 MySQL 数据库的一致备份myloader从 mydumper 读取备份，连接到目标数据库并导入备份。这两个工具都使用多线程功能。MyDumper优势有哪些速度更快 - 多线程并发备份性能更好 - 避免昂贵的字符集转换，整体代码高效易于管理的输出文件 -（比如每个表都能生成单独的备份文件，备份的元数据信息文件metadata记录位点信息等等，便于查看/解析数据）
MySQL备份工具mydumper备份
热门推荐
csdn's blog
07-07 1万+
#!/usr/bin/phthon import os import time import commands import shutil import threading from os.path import join, getsize import MySQLdb as mydb # 备份目录 baseDir = "/data2/backup/backup_data/" ...
mydumper及myloader参数说明
云淡风轻
07-27 4059
mydumper Usage: mydumper [OPTION...] multi-threaded MySQL dumping Help Options: -?, --help Show help options Application Options: -B, --database 需要备份的数据库，一个数据库一条命...
MySQL多线程备份工具mydumper
weixin_44770684的博客
11-25 6582
mysql mydumper myloader mysqldump mysql备份
mysql全量备份和快速恢复的方法整理
09-08
在本篇文章里小编给各位整理的是关于mysql全量备份和快速恢复的方法整理内容，需要的朋友们可以参考下。
Mysql全量备份与配套恢复脚本，可使用crontab定时自动全量备份
03-18
Mysql全量备份与配套恢复脚本，可使用crontab定时自动全量备份
mysqldumper安装及使用
舍得斋
04-01 9234
1、综述 mysqldumper工具是可以支持多线程的逻辑备份工具，比mysqldump的性能更好，能够支持大数据量的逻辑表备份，测试的结果是可以支持一库多表或全表，不支持多库同时备份，在centons6.5及mysql5.6版本上是可以使用的 为了进行大数据量的数据同步，但是mysql的两个版本有相差较大，一个是5.6的，一个是5.6的不能进行物理备份恢复（innobackupex 工具）就研...
mydumper及myloader用法
carcoon的专栏
11-19 2769
mydumper及myloader用法 mydumper -h 192.168.182.128 -u source -p 123456 -B source -t 8 -r 10000 -c --less-locking -v 3 -D -L /test/log/mydumper.log -o /test/data/ myloader -h 192.168.182.128 -u target -p 123456 -B target -e -t 8 -d /test/data/0/ --overw
MySQL - MySQL 8.0(七)备份：使用mydumper进行备份
写虫师的博客
08-13 4181
文章目录 安装 1. Apt 2. Yum 完全备份 一致的备份 备份单独表 使用正则表达式来备份特定的数据库 采用mydumper备份大表 无阻塞备份 压缩备份 仅备份数据
29.3 基于mydumper备份并恢复数据
weixin_41275260的博客
06-01 329
29.3　基于mydumper备份并恢复数据 mydumper也是一个数据备份工具，但不是MySQL自带的工具，需要单独安装，比mysqlpump的并行度和性能要高。本节将简单介绍如何基于mydumper备份并恢复数据。 29.3.1　安装mydumper mydumper的安装比较简单，直接在服务器的命令行中输入如下命令即可。 [root@binghe150 ~]...
mydumper备份数据库
T_testView的博客
12-03 1612
mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具，备份方式为逻辑备份。它支持多线程，备份速度远高于原生态的mysqldump以及众多优异特性。因此该工具是DBA们的不二选择。本文主要描述该工具的使用方法并给出示例。 有关mydumper安装参考：mydumper安装及安装故障汇总 1、mydumper的特点 a、多线程逻辑备份,生产的多个备份文件 b、与mysqldump相...
MySQL备份教程（3）mydumper备份与还原教程
weixin_42073629的博客
05-25 718
mydumper是一款基于mysqldump进行过优化的逻辑备份工具，它能多线程并发备份和还原，效率比mysqldump更高。在同一服务器进行80G数据的备份还原测试，使用mydumper进行备份时间为5分钟左右，恢复时间为80分钟，相比mysqldump的备份还原，提升了2倍以上的性能。 1、安装mydumper yum install https://github.com/maxbube/mydumper/releases/download/v0.10.1/mydumper-0.10.1-2.el
mysql数据库全量备份
09-15
全量备份MySQL数据库可以使用mysqldump命令。您可以按照以下步骤执行全量备份： 1. 打开终端并登录到MySQL服务器。 2. 运行以下命令来执行全量备份： ``` mysqldump -u<用户名> -p<密码> <数据库名> > <输出路径及sql文件名> ``` 请将<用户名>替换为您的MySQL用户名，<密码>替换为您的MySQL密码，<数据库名>替换为您要备份的数据库名称，<输出路径及sql文件名>替换为您要保存备份的路径和文件名。 以下是一个示例命令： ``` mysqldump -uroot -padmin test >/home/test.sql ``` 执行完毕后，您将在指定的输出路径中找到全量备份的SQL文件。您可以使用该文件来还原或导入数据库。请确保在执行备份操作时，MySQL服务是运行的且具有足够的权限进行备份操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小毕超

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值