借助 mydumper 实现定期 MySQL 全量备份、恢复方案

一、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>&

注意：这里为了展示效果，每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>&

每 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

借助 mydumper 实现定期 MySQL 全量备份、恢复方案

一、Mydumper 工具介绍

二、全量备份和恢复实验

全量备份：

数据恢复：

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

备份 user和role 表

四、定期全量备份数据

定期备份

定期删除

五、其他示例：

相关推荐

最近更新

热门阅读