• 【SpringBoot】70、SpringBoot实现MySQL数据库自动备份管理系统

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

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

    • 1

    1、界面献上

    登录界面

    在这里插入图片描述
    首页

    在这里插入图片描述
    实例管理

    在这里插入图片描述
    执行备份

    在这里插入图片描述
    任务管理

    在这里插入图片描述
    备份记录

    在这里插入图片描述

    2、引入依赖

    

    com.zaxxer
    HikariCP
    4.0.3



    mysql
    mysql-connector-java



    org.springframework.boot
    spring-boot-starter-quartz



    cn.dev33
    sa-token-quick-login
    1.30.0


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

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

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

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

    • 1

    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";
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    我们通过 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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 3、创建数据源
    /**
 * 创建数据源
 *
 * @param hikariConfig
 * @return
 */
public static HikariDataSource createDataSource(HikariConfig hikariConfig) {
    if (null == hikariConfig) {
        return null;
    }
    return new HikariDataSource(hikariConfig);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

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

    • 4、获取实例中的所有数据库
    /**
 * 获取数据库信息
 */
public static List listDataBases(DataSource dataSource) {
    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List databases = jdbcTemplate.query("SHOW DATABASES", new RowMapper() {
            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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

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

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 5、获取数据库中的所有数据表
    /**
 * 获取数据表信息
 */
public static List listTables(DataSource dataSource) {
    try {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List databases = jdbcTemplate.query("SHOW TABLES;", new RowMapper() {
            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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    使用 “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");

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 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;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 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);
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

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

    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 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;
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163

    包含对定时任务的 “增删改查” 操作，默认执行 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);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

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

    好了，我们的数据库备份管理系统大致就介绍完了，有感兴趣的朋友可以下载我的开源项目：

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

    • 1

    如您在阅读中发现不足，欢迎留言！！！

  • 相关阅读:
    js的new Function()常用方法
    2024蓝桥杯每日一题（BFS）
    SCS【3】单细胞转录组数据 GEO 下载及读取
    Python中的eval() & exec()
    （附源码）ssm招聘网站 毕业设计 250858
    jenkins使用docker安装保姆级教程（面向小白教程，最新最全，全图文）2022-8-1，不会docker也没关系
    软考高级 | 系统架构设计师笔记（一）
    为何90%以上的企业都在实施MES系统？到底有何好处？
    CI/CD 持续集成与持续交付（1）
    六面阿里天猫，已拿offer，我的面经复盘总结，原来进大厂没那么难了
  • 原文地址：https://blog.csdn.net/qq_40065776/article/details/126298228