SpringBoot实现MySQL数据库自动备份管理系统

原创

Java秦师兄

发布于 2022-08-16 14:26:55

3.4K0

发布于 2022-08-16 14:26:55

最近写了一个 MySQL 数据库自动、手动备份管理系统开源项目，想跟大家分享一下，项目地址：

https://gitee.com/asurplus/db-backup

1、界面献上

登录界面

首页

实例管理

执行备份

任务管理

备份记录

2、引入依赖

<!--用于动态创建数据库连接-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>4.0.3</version>
</dependency>
<!-- mysql连接驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 定时任务 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-quartz</artifactId>
</dependency>
<!-- Sa-Token-Quick-Login 插件 -->
<dependency>
    <groupId>cn.dev33</groupId>
    <artifactId>sa-token-quick-login</artifactId>
    <version>1.30.0</version>
</dependency>

3、Sa-Token-Quick-Login 快速登录插件

如果你开发了一个小系统，并不需要多用户登录，但是必须得有登录，你又不想写登录，那么用 Sa-Token-Quick-Login 快速登录插件是你的不二选择，具体用法参考：【SpringBoot】59、SpringBoot使用Sa-Token-Quick-Login插件快速登录认证

https://lizhou.blog.csdn.net/article/details/123571910

4、动态创建数据库连接

1、拼接连接地址

/**
 * 拼接url
 *
 * @param host
 * @param port
 * @return
 */
public static String getUrl(String host, String port, String database) {
    if (StringUtils.isBlank(database)) {
        database = "mysql";
    }
    return "jdbc:mysql://" + host + ":" + port + "/" + database + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&useSSL=true&characterEncoding=UTF-8";
}

我们通过 ip，端口就能动态创建数据库连接，因为每个数据库实例中都有 “mysql” 数据库，我们默认使用 “mysql” 数据库来创建连接

2、获取数据库连接配置

/**
 * 数据库连接配置
 *
 * @param properties 数据库连接信息
 * @return
 */
public static HikariConfig getHikariConfig(DbProperties properties) {
    HikariConfig hikariConfig = new HikariConfig();
    hikariConfig.setDriverClassName(properties.getClassName());
    hikariConfig.setJdbcUrl(getUrl(properties.getHost(), properties.getPort(), properties.getDatabase()));

    hikariConfig.setUsername(properties.getUsername());
    hikariConfig.setPassword(properties.getPassword());

    hikariConfig.setMaximumPoolSize(2);
    hikariConfig.setMinimumIdle(1);
    hikariConfig.setAutoCommit(true);
    hikariConfig.setConnectionTestQuery("SELECT 1 FROM DUAL");
    hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
    hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
    hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    return hikariConfig;
}

3、创建数据源

/**
 * 创建数据源
 *
 * @param hikariConfig
 * @return
 */
public static HikariDataSource createDataSource(HikariConfig hikariConfig) {
    if (null == hikariConfig) {
        return null;
    }
    return new HikariDataSource(hikariConfig);
}

这样我们就能得到 HikariDataSource 数据源了，可以用来执行 SQL 语句，例如：查询实例中的数据库，查询数据库中的数据表，对数据表中的数据实现 “增删改查” 操作

4、获取实例中的所有数据库

/**
 * 获取数据库信息
 */
public static List<String> listDataBases(DataSource dataSource) {
    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<String> databases = jdbcTemplate.query("SHOW DATABASES", new RowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        }, null);
        return DbUtil.filterDatabase(databases);
    } catch (Exception e) {
        log.error("获取数据库信息失败：{}", e.getMessage());
    }
    return null;
}

使用 “show databases” SQL 语句，查询实例中的所有数据库信息，因为实例中包含 MySQL 的默认数据库，我们需要将其过滤掉，如下：

private static final List<String> FILTER_DATABASE = Arrays.asList("information_schema", "mysql", "performance_schema", "sys");

public static List<String> filterDatabase(List<String> list) {
    if (CollectionUtil.isEmpty(list)) {
        return null;
    }
    List<String> resList = new ArrayList<>();
    for (String item : list) {
        if (!FILTER_DATABASE.contains(item)) {
            resList.add(item);
        }
    }
    return resList;
}

5、获取数据库中的所有数据表

/**
 * 获取数据表信息
 */
public static List<String> listTables(DataSource dataSource) {
    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<String> databases = jdbcTemplate.query("SHOW TABLES;", new RowMapper<String>() {
            public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString(1);
            }
        }, null);
        return databases;
    } catch (Exception e) {
        log.error("获取数据表信息失败：{}", e.getMessage());
    }
    return null;
}

使用 “show tables” SQL 语句，查询数据库中的所有数据表信息

5、数据库备份

1、公共参数

/**
 * 项目路径
 */
public static final String PROJECT_PATH = System.getProperty("user.dir");

/**
 * 当前系统类型
 */
public static final String OS_NAME = System.getProperty("os.name");

2、拼接备份数据库命令

/**
 * 拼接备份sql命令
 *
 * @param host     主机地址
 * @param port     端口
 * @param dbName   数据库名称
 * @param tables   表格名称
 * @param dataType 备份参数【0-表结构1-表数据 else 表结构+表数据】
 * @param username 用户名
 * @param password 密码
 * @param path     备份文件目录
 * @param fileName 备份文件名
 * @return
 */
public static String[] createBackupCommand(String host, String port, String dbName, String[] tables, Integer dataType, String username, String password, String path, String fileName) {
    String[] commands = new String[3];
    if (OS_NAME.startsWith("Win")) {
        commands[0] = "cmd.exe";
        commands[1] = "/c";
    } else {
        commands[0] = "/bin/sh";
        commands[1] = "-c";
    }
    // 拼接命令
    StringBuilder mysqldump = new StringBuilder();
    mysqldump.append("mysqldump");
    mysqldump.append(" --opt");

    // 用户，密码
    mysqldump.append(" --user=").append(username);
    mysqldump.append(" --password=").append(password);

    // ip，端口
    mysqldump.append(" --host=").append(host);
    mysqldump.append(" --port=").append(port);

    // 使用的连接协议，包括：tcp, socket, pipe, memory
    mysqldump.append(" --protocol=tcp");

    // 设置默认字符集，默认值为utf8
    mysqldump.append(" --default-character-set=utf8");
    // 在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态
    mysqldump.append(" --single-transaction=TRUE");

    // 导出存储过程以及自定义函数
    mysqldump.append(" --routines");
    // 导出事件
    mysqldump.append(" --events");

    // 只备份表结构
    if (null != dataType) {
        if (0 == dataType) {
            mysqldump.append(" --no-data");
        }
        // 只备份表数据
        else if (1 == dataType) {
            mysqldump.append(" --no-create-info");
        }
    }

    // 数据库名
    mysqldump.append(" ").append(dbName);

    // 数据表名
    if (null != tables && 0 < tables.length) {
        for (String item : tables) {
            mysqldump.append(" ").append(item);
        }
    }

    // 保存文件路径
    mysqldump.append(" > ").append(path).append(fileName);

    commands[2] = mysqldump.toString();
    return commands;
}

3、执行备份数据库命令

/**
 * @param host     主机地址
 * @param port     端口
 * @param dbName   数据库名称
 * @param tables   表格名称
 * @param dataType 备份参数【0-表结构1-表数据 else 表结构+表数据】
 * @param username 用户名
 * @param password 密码
 * @return
 */
public BackRespVO backup(String host, String port, String dbName, String[] tables, Integer dataType, String username, String password) {
    // 返回对象
    BackRespVO respVO = new BackRespVO();
    try {
        // 当前年月日
        String ymd = MysqlTool.getDate();
        // 文件目录
        String path = PROJECT_PATH + File.separator + "static" + File.separator + ymd + File.separator;
        // 文件名
        String fileName = IdUtil.fastSimpleUUID() + ".sql";
        // 创建文件
        File file = new File(path, fileName);
        // 路径不存在，则新建
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        // shell 命令脚本
        String[] commands = createBackupCommand(host, port, dbName, tables, dataType, username, password, path, fileName);

        Runtime runtime = Runtime.getRuntime();
        Process process = runtime.exec(commands);
        // 备份成功
        if (process.waitFor() == 0) {
            respVO.setFile(file);
            return respVO;
        }
        // 备份失败
        else {
            InputStream is = process.getErrorStream();
            if (is != null) {
                BufferedReader in = new BufferedReader(new InputStreamReader(is, OS_NAME.startsWith("Win") ? "GBK" : "UTF-8"));
                String line;
                StringBuilder sb = new StringBuilder();
                while ((line = in.readLine()) != null) {
                    sb.append(line);
                }
                respVO.setMsg("【" + dbName + "】备份失败，原因：" + sb);
                respVO.setFile(file);
            }
        }
    } catch (Exception e) {
        respVO.setMsg("【" + dbName + "】备份失败，原因：" + e.getMessage());
    }
    return respVO;
}

4、执行备份任务，并保存备份记录

/**
 * 异步执行备份任务，保存备份记录
 *
 * @param dbId         实例id
 * @param host         主机地址
 * @param port         端口
 * @param dbName       数据库名
 * @param tables       数据表名
 * @param dataType     备份参数【0-表结构1-表数据 else 表结构+表数据】
 * @param username     用户名
 * @param password     密码
 * @param startTime    开始时间
 * @param categoryEnum 备份类型（手动备份，自动备份）
 */
@Async
public void saveBackUp(Integer dbId, String host, String port, String dbName, String[] tables, Integer dataType, String username, String password, Date startTime, BackupCategoryEnum categoryEnum) {
    // 执行备份
    BackRespVO respVO = backup(host, port, dbName, tables, dataType, username, password);
    // 备份失败
    if (!respVO.isSuccess()) {
        if (null != respVO.getFile()) {
            respVO.getFile().delete();
        }
    }
    // 保存备份记录
    BackupLog backupLog = new BackupLog();
    backupLog.setDbId(dbId);
    backupLog.setCategory(categoryEnum.getMsg());
    backupLog.setDatabaseName(dbName);
    backupLog.setTablesName(StringUtils.join(tables, ","));
    backupLog.setDataType(dataType);
    backupLog.setStatus(respVO.isSuccess());
    backupLog.setMsg(respVO.getMsg());
    // 备份成功
    if (respVO.isSuccess()) {
        // 文件相对路径
        backupLog.setFilePath(respVO.getFile().getPath().replace(MysqlTool.PROJECT_PATH + File.separator, ""));
        backupLog.setFileSize(respVO.getFile().length());
    }
    // 开始时间
    backupLog.setStartTime(startTime);
    backupLog.setEndTime(new Date());
    backupLog.setSpendTime(backupLog.getEndTime().getTime() - backupLog.getStartTime().getTime());
    backupLogMapper.insert(backupLog);
}

我们采用异步执行的方式，因为备份过程可能会很漫长，执行备份了之后，得到备份的文件路径，保存在备份记录中，可以实时进行查看

6、添加自动备份定时任务

定时任务主要依赖 quartz，可以动态管理定时任务，非常方便

1、定时任务管理工具类

import cn.hutool.core.collection.CollectionUtil;
import com.asurplus.entity.BackupTask;
import com.asurplus.mapper.BackupTaskMapper;
import lombok.extern.slf4j.Slf4j;
import org.quartz.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.util.List;

/**
 * quartz工具类
 *
 * @Author Asurplus
 */
@Slf4j
@Component
public class QuartzManager {

    /**
     * 参数传递key
     */
    public static final String PARAM_KEY = "params";

    /**
     * 执行任务类名
     */
    public static final String CLASS_NAME = "com.asurplus.job.DbBackupJob";

    /**
     * 程序调度器
     */
    @Autowired
    private Scheduler scheduler;
    @Resource
    private BackupTaskMapper backupTaskMapper;

    /**
     * 系统启动执行
     */
    @PostConstruct
    public void init() {
        List<BackupTask> list = backupTaskMapper.selectList(null);
        if (CollectionUtil.isNotEmpty(list)) {
            for (BackupTask item : list) {
                try {
                    add(item.getId(), item.getCron(), item.getParam(), item.getStatus());
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
        }
    }

    /**
     * 添加定时任务
     */
    public void add(Integer id, String cronExpression, String param, Boolean status) {
        try {
            // 构建job信息
            JobDetail jobDetail = JobBuilder.newJob(getClass(CLASS_NAME).getClass()).withIdentity(getKey(id)).usingJobData(PARAM_KEY, param).build();
            // 表达式调度构建器(即任务执行的时间)
            CronScheduleBuilder scheduleBuilder = CronScheduleBuilder.cronSchedule(cronExpression);
            // 按新的cronExpression表达式构建一个新的trigger
            CronTrigger trigger = TriggerBuilder.newTrigger().withIdentity(getKey(id)).withSchedule(scheduleBuilder).build();
            // 创建定时任务
            scheduler.scheduleJob(jobDetail, trigger);
            // 停止
            if (!status) {
                stop(id);
            }
        } catch (Exception e) {
            log.error("添加定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 编辑定时任务
     */
    public void update(Integer id, String cronExpression, String param, Boolean status) {
        try {
            // 判断是否存在，存在先删除
            if (scheduler.checkExists(JobKey.jobKey(getKey(id)))) {
                scheduler.deleteJob(JobKey.jobKey(getKey(id)));
            }
            // 再创建
            add(id, cronExpression, param, status);
        } catch (Exception e) {
            log.error("修改定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 暂停任务
     */
    public void stop(Integer id) {
        try {
            scheduler.pauseJob(JobKey.jobKey(getKey(id)));
        } catch (SchedulerException e) {
            // 暂停定时任务失败
            log.error("暂停定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 恢复任务
     */
    public void start(Integer id) {
        try {
            scheduler.resumeJob(JobKey.jobKey(getKey(id)));
        } catch (SchedulerException e) {
            // 暂停定时任务失败
            log.error("启动定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 立即执行一次
     */
    public void run(Integer id) {
        try {
            scheduler.triggerJob(JobKey.jobKey(getKey(id)));
        } catch (SchedulerException e) {
            // 暂停定时任务失败
            log.error("执行定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 删除定时任务
     */
    public void delete(Integer id) {
        try {
            // 停止触发器
            scheduler.pauseTrigger(TriggerKey.triggerKey(getKey(id)));
            // 移除触发器
            scheduler.unscheduleJob(TriggerKey.triggerKey(getKey(id)));
            // 删除任务
            scheduler.deleteJob(JobKey.jobKey(getKey(id)));
        } catch (Exception e) {
            log.error("删除定时任务失败：{}", e.getMessage());
        }
    }

    /**
     * 根据类名获取类
     */
    private Job getClass(String className) throws Exception {
        Class<?> class1 = Class.forName(className);
        return (Job) class1.newInstance();
    }

    /**
     * 拼接key
     *
     * @return
     */
    public String getKey(Integer id) {
        return "dbBackUp-" + id;
    }
}

包含对定时任务的 “增删改查” 操作，默认执行 job 为：com.asurplus.job.DbBackupJob

2、备份任务

import com.alibaba.fastjson.JSONObject;
import com.asurplus.config.quartz.QuartzManager;
import com.asurplus.enums.BackupCategoryEnum;
import com.asurplus.utils.MysqlTool;
import com.asurplus.vo.BackupJobVO;
import lombok.extern.slf4j.Slf4j;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.Date;

/**
 * 备份定时任务
 */
@Slf4j
public class DbBackupJob implements Job {

    @Autowired
    private MysqlTool mysqlTool;

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {
        // 解析参数
        BackupJobVO vo = JSONObject.parseObject(context.getJobDetail().getJobDataMap().getString(QuartzManager.PARAM_KEY), BackupJobVO.class);
        // 开始备份
        mysqlTool.saveBackUp(vo.getDbId(), vo.getHost(), vo.getPort(), vo.getDbName(), vo.getTables(), vo.getDataType(), vo.getUsername(), vo.getPassword(), new Date(), BackupCategoryEnum.AUTO);
    }
}

获取到备份参数，包括：主机地址，端口，用户名，密码，数据库名等，就能执行备份任务了

好了，我们的数据库备份管理系统大致就介绍完了，如您在阅读中发现不足，欢迎留言！！！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

java