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

一、Mydumper 工具介绍

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

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

• • 支持一致性备份；

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

• • 支持多线程恢复；

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

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

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

官网地址：https://launchpad.net/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 所支持的参数如下：

myloader 所支持的参数如下：

下面以一个案例来介绍和使用 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

原文始发于微信公众号（狂热JAVA小毕超）：基于 mydumper 实现 MySQL 定期全量备份、恢复方案