spring boot定时备份数据库

一、spring boot备份数据库基本知识

1、获取当前的操作系统名称以及操作一些命令

• 获取当前操作系统名字
• 通过cmd命令进行操作

@Test
void contextLoads() {
    String sysName = System.getProperty("os.name").toLowerCase();
    System.out.println(sysName);

    String cmd = "java -version";
    Process exec = Runtime.getRuntime().exec(cmd);

    if (exec.waitFor() == 0){
        log.info("操作操作命令成功！！！" + exec);
    }

    
}

2、数据库备份操作

数据库备份命令如下：

mysqldump备份简单操作：
mysqldump -h[ip] -P[端口号] -u[用户名] -p[密码] 数据库名 表名 >导出的文件名.sql

mysqldump -h[ip] -P[端口号] -u[用户名] -p[密码] 数据库名 表名1 表名2 表名3 | gzip >导出的文件名.sql.gz

二、定时备份方式一

properties配置文件

spring:
  mvc:
    static-path-pattern: /**
  web:
    resources:
      static-locations: [classpath:/static/,classpath:/html]


  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://124.223.15.21:3306/teachingmanagement
    driver-class-name: com.mysql.cj.jdbc.Driver
    win-path: C://file//sql//
    linux-path: /root/Teaching-Management-System/sql
    linux_ip: 124.223.15.21
    win_ip: 39.210.151.186
    dbName: teachingmanagement
mybatis:
  mapper-locations: classpath:mapper/*.xml

创建QuartzJobBean对象

package com.example.springboot12_day.Config;

import cn.hutool.core.date.DateUtil;
import com.example.springboot12_day.utils.Constants;
import lombok.extern.slf4j.Slf4j;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.quartz.QuartzJobBean;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.IOException;
import java.util.Date;

/**
 * @author 程世玉
 * @create 2022/3/23 8:52
 * @PROJECT_NAME Second-SpringBootTest
 * @Description
 */
@Slf4j
@Component
public class TestQuartz extends QuartzJobBean {
    @Value("${spring.datasource.win-path}")
    private String win_path;

    @Value("${spring.datasource.linux-path}")
    private String linux_path;

    @Value("${spring.datasource.linux_ip}")
    private String linux_ip;

    @Value("${spring.datasource.username}")
    private String dbUserName;

    @Value("${spring.datasource.password}")
    private String dbpassword;

    @Value("${spring.datasource.dbName}")
    private String dbName;

    @Override
    protected void executeInternal(JobExecutionContext context) throws JobExecutionException {



        /*2021-01-02*/
        log.info("定时任务开始执行：" + DateUtil.format(new Date(), "yyyy-MM-dd hh:mm:ss"));

        /*设置sql语句保存地址，测试的时候本机Windows系统，数据库则是在Linux系统中，这里路径写Windows保存路径*/
        String win = win_path;
        /*设置用户名*/
        String username = dbUserName;
        /*设置密码*/
        String password = dbpassword;
        /*设置数据库ip*/
        String ip = linux_ip;

        /*判断要保存的路径是否正确*/
        File file = new File(win_path);
        if (!file.exists()) {
            /*如果不存在则进行创建*/
            file.mkdirs();
        }
        StringBuilder mysqlFileName = new StringBuilder()
                .append(dbName)
                .append("_")
                .append(DateUtil.format(new Date(), "yyyy-MM-dd-HH-mm-ss"))
                .append(Constants.FILE_SUFFIX);
        /*拼接cmd命令*/
        StringBuffer cmd = new StringBuffer()
                .append("mysqldump ")
                .append("--no-tablespaces ")
                .append("-h")
                .append(ip)
                .append(" -u")
                .append(username)
                .append(" -p")
                .append(password)
                .append(" --ignore-table ")
                .append(dbName)
                .append(".mysql_backups ")
                .append(dbName)
                .append(" > ")
                .append(win_path)
                .append(mysqlFileName);

        String[] command = new String[0];
        /*我在这里就不区分是Linux还是Windows系统了，默认是Windows系统了*/
        command = new String[]{"cmd", "/c", String.valueOf(cmd)};
        /*开始进行备份*/
        try {
            Runtime.getRuntime().exec(command);
            log.info("数据库备份成功！！！");
        } catch (IOException e) {
            log.info("数据库备份失败！！！");
            e.printStackTrace();
        }
    }
}

配置信息

withIntervalInSeconds(10) //设置时间周期单位秒

这个可以更换

package com.example.springboot12_day.Config;

import org.quartz.*;
import org.quartz.impl.StdSchedulerFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author 程世玉
 * @create 2022/3/23 8:51
 * @PROJECT_NAME Second-SpringBootTest
 * @Description
 */
@Configuration
public class QuartzConfig {
    @Bean
    public JobDetail getJobDetail(){
        return JobBuilder.newJob(TestQuartz.class).withIdentity("testQuartz").storeDurably().build();
    }


    @Bean
    public Trigger testQuartzTrigger() throws SchedulerException {
        SimpleScheduleBuilder scheduleBuilder = SimpleScheduleBuilder.simpleSchedule()
                .withIntervalInSeconds(10)  //设置时间周期单位秒
                .repeatForever();
        return TriggerBuilder.newTrigger().forJob(getJobDetail())
                .withIdentity("testQuartz")
                .withSchedule(scheduleBuilder)
                .build();
    }

}

三、定时备份方式二

提供一个接口，通过前端页面，调用该接口进行可视化操作备份数据库

这种更加好！利用了接口！！！

数据库层：

CREATE TABLE IF NOT EXISTS `mysql_backups` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `mysql_ip` VARCHAR ( 15 ) DEFAULT NULL COMMENT '数据库IP',
    `mysql_port` VARCHAR ( 5 ) DEFAULT NULL COMMENT '数据库端口',
    `mysql_cmd` VARCHAR ( 230 ) DEFAULT NULL COMMENT '备份命令',
    `mysql_back_cmd` VARCHAR ( 230 ) DEFAULT NULL COMMENT '恢复命令',
    `database_name` VARCHAR ( 20 ) DEFAULT NULL COMMENT '数据库名称',
    `backups_path` VARCHAR ( 50 ) DEFAULT NULL COMMENT '备份数据地址',
    `backups_name` VARCHAR ( 50 ) DEFAULT NULL COMMENT '备份文件名称',
    `operation` INT ( 11 ) DEFAULT NULL COMMENT '操作次数',
    `status` INT ( 1 ) DEFAULT NULL COMMENT '数据状态（1正常，-1删除）',
    `recovery_time` DATETIME DEFAULT NULL COMMENT '恢复时间',
    `create_time` DATETIME DEFAULT NULL COMMENT '备份时间',
    PRIMARY KEY ( `id` ),
INDEX baskups_index ( mysql_ip, mysql_port, backups_path, database_name,backups_name) USING BTREE COMMENT '索引'
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARSET = UTF8 ROW_FORMAT = COMPACT COMMENT = 'MySQL数据备份表';

实体类：

package com.example.springboot12_day.entity;

import java.util.Date;
import java.io.Serializable;

/**
 * MySQL数据备份表(MysqlBackups)实体类
 *
 * @author makejava
 * @since 2022-03-22 22:54:24
 */
public class MysqlBackups implements Serializable {
    private static final long serialVersionUID = 289003440696670408L;
    /**
     * 主键id
     */
    private Integer id;
    /**
     * 数据库IP
     */
    private String mysqlIp;
    /**
     * 数据库端口
     */
    private String mysqlPort;
    /**
     * 备份命令
     */
    private String mysqlCmd;
    /**
     * 恢复命令
     */
    private String mysqlBackCmd;
    /**
     * 数据库名称
     */
    private String databaseName;
    /**
     * 备份数据地址
     */
    private String backupsPath;
    /**
     * 备份文件名称
     */
    private String backupsName;
    /**
     * 操作次数
     */
    private Integer operation;
    /**
     * 数据状态（1正常，-1删除）
     */
    private Integer status;
    /**
     * 恢复时间
     */
    private Date recoveryTime;
    /**
     * 备份时间
     */
    private Date createTime;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getMysqlIp() {
        return mysqlIp;
    }

    public void setMysqlIp(String mysqlIp) {
        this.mysqlIp = mysqlIp;
    }

    public String getMysqlPort() {
        return mysqlPort;
    }

    public void setMysqlPort(String mysqlPort) {
        this.mysqlPort = mysqlPort;
    }

    public String getMysqlCmd() {
        return mysqlCmd;
    }

    public void setMysqlCmd(String mysqlCmd) {
        this.mysqlCmd = mysqlCmd;
    }

    public String getMysqlBackCmd() {
        return mysqlBackCmd;
    }

    public void setMysqlBackCmd(String mysqlBackCmd) {
        this.mysqlBackCmd = mysqlBackCmd;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public String getBackupsPath() {
        return backupsPath;
    }

    public void setBackupsPath(String backupsPath) {
        this.backupsPath = backupsPath;
    }

    public String getBackupsName() {
        return backupsName;
    }

    public void setBackupsName(String backupsName) {
        this.backupsName = backupsName;
    }

    public Integer getOperation() {
        return operation;
    }

    public void setOperation(Integer operation) {
        this.operation = operation;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    public Date getRecoveryTime() {
        return recoveryTime;
    }

    public void setRecoveryTime(Date recoveryTime) {
        this.recoveryTime = recoveryTime;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

}

Dao层

Mapper文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.springboot12_day.dao.MysqlBackupsDao">

    <resultMap type="com.example.springboot12_day.entity.MysqlBackups" id="MysqlBackupsMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="mysqlIp" column="mysql_ip" jdbcType="VARCHAR"/>
        <result property="mysqlPort" column="mysql_port" jdbcType="VARCHAR"/>
        <result property="mysqlCmd" column="mysql_cmd" jdbcType="VARCHAR"/>
        <result property="mysqlBackCmd" column="mysql_back_cmd" jdbcType="VARCHAR"/>
        <result property="databaseName" column="database_name" jdbcType="VARCHAR"/>
        <result property="backupsPath" column="backups_path" jdbcType="VARCHAR"/>
        <result property="backupsName" column="backups_name" jdbcType="VARCHAR"/>
        <result property="operation" column="operation" jdbcType="INTEGER"/>
        <result property="status" column="status" jdbcType="INTEGER"/>
        <result property="recoveryTime" column="recovery_time" jdbcType="TIMESTAMP"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
    </resultMap>

<!--    <sql id="Base_Column_List">-->
<!--        id-->
<!--        as id,-->
<!--        `mysql_ip` as mysqlIp,-->
<!--        `mysql_port` as mysqlPort,-->
<!--        `mysql_cmd` as mysqlCmd,-->
<!--        `mysql_back_cmd` as mysqlBackCmd,-->
<!--        `database_name` as databaseName,-->
<!--        `backups_path` as backupsPath,-->
<!--        `backups_name` as backupsName,-->
<!--        `operation` as operation,-->
<!--        `status` as status,-->
<!--        `recovery_time` as recoveryTime,-->
<!--        `create_time` as createTime-->
<!--    </sql>-->

    <select id="selectListId" resultMap="MysqlBackupsMap">
        SELECT *
        FROM `mysql_backups`
        WHERE `status` != 0
          AND id = #{id}
    </select>

    <select id="selectBackupsList" resultMap="MysqlBackupsMap">
        SELECT *
        FROM `mysql_backups`
        WHERE `status` != 0
        ORDER BY create_time DESC
    </select>



    <!--查询单个-->
    <select id="queryById" resultMap="MysqlBackupsMap">
        select id,
               mysql_ip,
               mysql_port,
               mysql_cmd,
               mysql_back_cmd,
               database_name,
               backups_path,
               backups_name,
               operation,
               status,
               recovery_time,
               create_time
        from mysql_backups
        where id = #{id}
    </select>

    <!--查询指定行数据-->
    <select id="queryAllByLimit" resultMap="MysqlBackupsMap">
        select
        id, mysql_ip, mysql_port, mysql_cmd, mysql_back_cmd, database_name, backups_path, backups_name, operation,
        status, recovery_time, create_time
        from mysql_backups
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="mysqlIp != null and mysqlIp != ''">
                and mysql_ip = #{mysqlIp}
            </if>
            <if test="mysqlPort != null and mysqlPort != ''">
                and mysql_port = #{mysqlPort}
            </if>
            <if test="mysqlCmd != null and mysqlCmd != ''">
                and mysql_cmd = #{mysqlCmd}
            </if>
            <if test="mysqlBackCmd != null and mysqlBackCmd != ''">
                and mysql_back_cmd = #{mysqlBackCmd}
            </if>
            <if test="databaseName != null and databaseName != ''">
                and database_name = #{databaseName}
            </if>
            <if test="backupsPath != null and backupsPath != ''">
                and backups_path = #{backupsPath}
            </if>
            <if test="backupsName != null and backupsName != ''">
                and backups_name = #{backupsName}
            </if>
            <if test="operation != null">
                and operation = #{operation}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="recoveryTime != null">
                and recovery_time = #{recoveryTime}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
        </where>
        limit #{pageable.offset}, #{pageable.pageSize}
    </select>

    <!--统计总行数-->
    <select id="count" resultType="java.lang.Long">
        select count(1)
        from mysql_backups
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="mysqlIp != null and mysqlIp != ''">
                and mysql_ip = #{mysqlIp}
            </if>
            <if test="mysqlPort != null and mysqlPort != ''">
                and mysql_port = #{mysqlPort}
            </if>
            <if test="mysqlCmd != null and mysqlCmd != ''">
                and mysql_cmd = #{mysqlCmd}
            </if>
            <if test="mysqlBackCmd != null and mysqlBackCmd != ''">
                and mysql_back_cmd = #{mysqlBackCmd}
            </if>
            <if test="databaseName != null and databaseName != ''">
                and database_name = #{databaseName}
            </if>
            <if test="backupsPath != null and backupsPath != ''">
                and backups_path = #{backupsPath}
            </if>
            <if test="backupsName != null and backupsName != ''">
                and backups_name = #{backupsName}
            </if>
            <if test="operation != null">
                and operation = #{operation}
            </if>
            <if test="status != null">
                and status = #{status}
            </if>
            <if test="recoveryTime != null">
                and recovery_time = #{recoveryTime}
            </if>
            <if test="createTime != null">
                and create_time = #{createTime}
            </if>
        </where>
    </select>

    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into mysql_backups(mysql_ip, mysql_port, mysql_cmd, mysql_back_cmd, database_name, backups_path,
                                  backups_name, operation, status, recovery_time, create_time)
        values (#{mysqlIp}, #{mysqlPort}, #{mysqlCmd}, #{mysqlBackCmd}, #{databaseName}, #{backupsPath}, #{backupsName},
                #{operation}, #{status}, #{recoveryTime}, #{createTime})
    </insert>

    <insert id="insertBatch" keyProperty="id" useGeneratedKeys="true">
        insert into mysql_backups(mysql_ip, mysql_port, mysql_cmd, mysql_back_cmd, database_name, backups_path,
        backups_name, operation, status, recovery_time, create_time)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.mysqlIp}, #{entity.mysqlPort}, #{entity.mysqlCmd}, #{entity.mysqlBackCmd}, #{entity.databaseName},
            #{entity.backupsPath}, #{entity.backupsName}, #{entity.operation}, #{entity.status}, #{entity.recoveryTime},
            #{entity.createTime})
        </foreach>
    </insert>

    <insert id="insertOrUpdateBatch" keyProperty="id" useGeneratedKeys="true">
        insert into mysql_backups(mysql_ip, mysql_port, mysql_cmd, mysql_back_cmd, database_name, backups_path,
        backups_name, operation, status, recovery_time, create_time)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.mysqlIp}, #{entity.mysqlPort}, #{entity.mysqlCmd}, #{entity.mysqlBackCmd}, #{entity.databaseName},
            #{entity.backupsPath}, #{entity.backupsName}, #{entity.operation}, #{entity.status}, #{entity.recoveryTime},
            #{entity.createTime})
        </foreach>
        on duplicate key update
        mysql_ip = values(mysql_ip),
        mysql_port = values(mysql_port),
        mysql_cmd = values(mysql_cmd),
        mysql_back_cmd = values(mysql_back_cmd),
        database_name = values(database_name),
        backups_path = values(backups_path),
        backups_name = values(backups_name),
        operation = values(operation),
        status = values(status),
        recovery_time = values(recovery_time),
        create_time = values(create_time)
    </insert>

    <!--通过主键修改数据-->
    <update id="update">
        update mysql_backups
        <set>
            <if test="mysqlIp != null and mysqlIp != ''">
                mysql_ip = #{mysqlIp},
            </if>
            <if test="mysqlPort != null and mysqlPort != ''">
                mysql_port = #{mysqlPort},
            </if>
            <if test="mysqlCmd != null and mysqlCmd != ''">
                mysql_cmd = #{mysqlCmd},
            </if>
            <if test="mysqlBackCmd != null and mysqlBackCmd != ''">
                mysql_back_cmd = #{mysqlBackCmd},
            </if>
            <if test="databaseName != null and databaseName != ''">
                database_name = #{databaseName},
            </if>
            <if test="backupsPath != null and backupsPath != ''">
                backups_path = #{backupsPath},
            </if>
            <if test="backupsName != null and backupsName != ''">
                backups_name = #{backupsName},
            </if>
            <if test="operation != null">
                operation = #{operation},
            </if>
            <if test="status != null">
                status = #{status},
            </if>
            <if test="recoveryTime != null">
                recovery_time = #{recoveryTime},
            </if>
            <if test="createTime != null">
                create_time = #{createTime},
            </if>
        </set>
        where id = #{id}
    </update>

    <!--通过主键删除-->
    <delete id="deleteById">
        delete
        from mysql_backups
        where id = #{id}
    </delete>

</mapper>

Dao层接口

package com.example.springboot12_day.dao;

import com.example.springboot12_day.entity.MysqlBackups;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Pageable;

import java.util.List;

/**
 * MySQL数据备份表(MysqlBackups)表数据库访问层
 *
 * @author makejava
 * @since 2022-03-22 22:54:24
 */
public interface MysqlBackupsDao {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    MysqlBackups queryById(Integer id);

    /**
     * 查询指定行数据
     *
     * @param mysqlBackups 查询条件
     * @param pageable     分页对象
     * @return 对象列表
     */
    List<MysqlBackups> queryAllByLimit(MysqlBackups mysqlBackups, @Param("pageable") Pageable pageable);

    /**
     * 统计总行数
     *
     * @param mysqlBackups 查询条件
     * @return 总行数
     */
    long count(MysqlBackups mysqlBackups);

    /**
     * 新增数据
     *
     * @param mysqlBackups 实例对象
     * @return 影响行数
     */
    int insert(MysqlBackups mysqlBackups);

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<MysqlBackups> 实例对象列表
     * @return 影响行数
     */
    int insertBatch(@Param("entities") List<MysqlBackups> entities);

    /**
     * 批量新增或按主键更新数据（MyBatis原生foreach方法）
     *
     * @param entities List<MysqlBackups> 实例对象列表
     * @return 影响行数
     * @throws org.springframework.jdbc.BadSqlGrammarException 入参是空List的时候会抛SQL语句错误的异常，请自行校验入参
     */
    int insertOrUpdateBatch(@Param("entities") List<MysqlBackups> entities);

    /**
     * 修改数据
     *
     * @param mysqlBackups 实例对象
     * @return 影响行数
     */
    int update(MysqlBackups mysqlBackups);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    int deleteById(Integer id);



    /**
     * 查询所有备份数据
     */
    List<MysqlBackups> selectBackupsList();

    /**
     * 根据ID查询
     */
    MysqlBackups selectListId(@Param("id") Long id);
}

Service层

package com.example.springboot12_day.service;

import com.example.springboot12_day.entity.MysqlBackups;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;

import java.util.List;

/**
 * MySQL数据备份表(MysqlBackups)表服务接口
 *
 * @author makejava
 * @since 2022-03-22 22:54:24
 */
public interface MysqlBackupsService {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    MysqlBackups queryById(Integer id);

    /**
     * 分页查询
     *
     * @param mysqlBackups 筛选条件
     * @param pageRequest  分页对象
     * @return 查询结果
     */
    Page<MysqlBackups> queryByPage(MysqlBackups mysqlBackups, PageRequest pageRequest);

    /**
     * 新增数据
     *
     * @param mysqlBackups 实例对象
     * @return 实例对象
     */
    MysqlBackups insert(MysqlBackups mysqlBackups);

    /**
     * 修改数据
     *
     * @param mysqlBackups 实例对象
     * @return 实例对象
     */
    MysqlBackups update(MysqlBackups mysqlBackups);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 是否成功
     */
    boolean deleteById(Integer id);


    /**
     * 查询所有备份数据
     */
    List<MysqlBackups> selectBackupsList();

    /**
     * mysql备份接口
     */
    Object mysqlBackups(String filePath, String url, String userName, String password);

    /**
     * 根据ID查询
     */
    MysqlBackups selectListId(Long id);

    /**
     * 恢复数据库
     *
     * @param smb      恢复对象
     * @param userName 数据库用户名
     * @param password 数据库密码
     * @return
     */
    Object rollback(MysqlBackups smb, String userName, String password);
}

package com.example.springboot12_day.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import com.example.springboot12_day.entity.MysqlBackups;
import com.example.springboot12_day.dao.MysqlBackupsDao;
import com.example.springboot12_day.service.MysqlBackupsService;
import com.example.springboot12_day.utils.Constants;
import com.example.springboot12_day.utils.ErrorTip;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;

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

/**
 * MySQL数据备份表(MysqlBackups)表服务实现类
 *
 * @author makejava
 * @since 2022-03-22 22:54:24
 */
@Service("mysqlBackupsService")
@Slf4j
public class MysqlBackupsServiceImpl implements MysqlBackupsService {
    @Resource
    private MysqlBackupsDao mysqlBackupsDao;

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    @Override
    public MysqlBackups queryById(Integer id) {
        return this.mysqlBackupsDao.queryById(id);
    }

    /**
     * 分页查询
     *
     * @param mysqlBackups 筛选条件
     * @param pageRequest  分页对象
     * @return 查询结果
     */
    @Override
    public Page<MysqlBackups> queryByPage(MysqlBackups mysqlBackups, PageRequest pageRequest) {
        long total = this.mysqlBackupsDao.count(mysqlBackups);
        return new PageImpl<>(this.mysqlBackupsDao.queryAllByLimit(mysqlBackups, pageRequest), pageRequest, total);
    }

    /**
     * 新增数据
     *
     * @param mysqlBackups 实例对象
     * @return 实例对象
     */
    @Override
    public MysqlBackups insert(MysqlBackups mysqlBackups) {
        this.mysqlBackupsDao.insert(mysqlBackups);
        return mysqlBackups;
    }

    /**
     * 修改数据
     *
     * @param mysqlBackups 实例对象
     * @return 实例对象
     */
    @Override
    public MysqlBackups update(MysqlBackups mysqlBackups) {
        this.mysqlBackupsDao.update(mysqlBackups);
        return this.queryById(mysqlBackups.getId());
    }

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 是否成功
     */
    @Override
    public boolean deleteById(Integer id) {
        return this.mysqlBackupsDao.deleteById(id) > 0;
    }

    @Override
    public List<MysqlBackups> selectBackupsList() {
        return mysqlBackupsDao.selectBackupsList();
    }

    @Override
    public Object mysqlBackups(String filePath, String url, String userName, String password) {
        // 获取ip
        final String ip = url.substring(13, 26);
        System.out.println(ip);
        // 获取端口号
        final String port = url.substring(27, 31);
        System.out.println(port);
        // 获取数据库名称
        final String database_name = url.substring(32, 50);
        System.out.println(database_name);
        // 数据库文件名称
        StringBuilder mysqlFileName = new StringBuilder()
                .append(Constants.DATA_BASE_NAME)
                .append("_")
                .append(DateUtil.format(new Date(), "yyyy-MM-dd-HH-mm-ss"))
                .append(Constants.FILE_SUFFIX);
        // 备份命令
        StringBuilder cmd = new StringBuilder()
                .append("mysqldump ")
                .append("--no-tablespaces ")
                .append("-h")
                .append(ip)
                .append(" -u")
                .append(userName)
                .append(" -p")
                .append(password)
                // 排除MySQL备份表
                .append(" --ignore-table ")
                .append(database_name)
                .append(".mysql_backups ")
                .append(database_name)
                .append(" > ")
                .append(filePath)
                .append(mysqlFileName);


        log.info("执行cmd命令：" + cmd);
        // 判断文件是否保存成功
        if (!FileUtil.exist(filePath)) {
            FileUtil.mkdir(filePath);
            return new ErrorTip(HttpStatus.REQUEST_HEADER_FIELDS_TOO_LARGE.value(), "备份失败，文件保存异常，请查看文件内容后重新尝试！");
        }
        // 获取操作系统名称
        String osName = System.getProperty("os.name").toLowerCase();
        String[] command = new String[0];
        if (Constants.isSystem(osName)) {
            // Windows
            command = new String[]{"cmd", "/c", String.valueOf(cmd)};
        } else {
            // Linux
            command = new String[]{"/bin/sh", "-c", String.valueOf(cmd)};
        }
        MysqlBackups smb = new MysqlBackups();
        // 备份信息存放到数据库
        smb.setMysqlIp(ip);
        smb.setMysqlPort(port);
        smb.setBackupsName(String.valueOf(mysqlFileName));
        smb.setDatabaseName(database_name);
        smb.setMysqlCmd(String.valueOf(cmd));
        smb.setBackupsPath(filePath);
        smb.setCreateTime(DateTime.now());
        smb.setStatus(1);
        smb.setOperation(0);
        mysqlBackupsDao.insert(smb);
        log.error("数据库备份命令为：{}", cmd);
        // 获取Runtime实例
        Process process = null;
        try {
            process = Runtime.getRuntime().exec(command);
            if (process.waitFor() == 0) {
                log.info("Mysql 数据库备份成功,备份文件名：{}", mysqlFileName);
            } else {
                return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "网络异常，数据库备份失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "网络异常，数据库备份失败");
        }
        return smb;
    }

    @Override
    public MysqlBackups selectListId(Long id) {
        return mysqlBackupsDao.selectListId(id);
    }

    @Override
    public Object rollback(MysqlBackups smb, String userName, String password) {
        // 备份路径和文件名
        StringBuilder realFilePath = new StringBuilder().append(smb.getBackupsPath()).append(smb.getBackupsName());
        if (!FileUtil.exist(String.valueOf(realFilePath))) {
            return new ErrorTip(HttpStatus.NOT_FOUND.value(), "文件不存在，恢复失败，请查看目录内文件是否存在后重新尝试！");
        }
        StringBuilder cmd = new StringBuilder()
                .append("mysql -h")
                .append(smb.getMysqlIp())
                .append(" -u")
                .append(userName)
                .append(" -p")
                .append(password)
                .append(" ")
                .append(smb.getDatabaseName())
                .append(" < ")
                .append(realFilePath);
        String[] command = new String[0];
        log.error("数据库恢复命令为：{}", cmd);
        // 获取操作系统名称
        String osName = System.getProperty("os.name").toLowerCase();
        if (Constants.isSystem(osName)) {
            // Windows
            command = new String[]{"cmd", "/c", String.valueOf(cmd)};
        } else {
            // Linux
            command = new String[]{"/bin/sh", "-c", String.valueOf(cmd)};
        }
        // 恢复指令写入到数据库
        smb.setMysqlBackCmd(String.valueOf(cmd));
        // 更新操作次数
        smb.setRecoveryTime(DateTime.now());
        smb.setOperation(smb.getOperation() + 1);
        // 获取Runtime实例
        Process process = null;
        try {
            process = Runtime.getRuntime().exec(command);
            if (process.waitFor() == 0) {
                log.error("Mysql 数据库恢复成功,恢复文件名：{}", realFilePath);
            } else {
                return new ErrorTip(HttpStatus.GATEWAY_TIMEOUT.value(), "网络异常，恢复失败，请稍后重新尝试！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return new ErrorTip(HttpStatus.GATEWAY_TIMEOUT.value(), "网络异常，恢复失败，请稍后重新尝试！");
        }
        return smb;
    }
}

工具类：

package com.example.springboot12_day.utils;

/**
 * @author 程世玉
 * @create 2022/3/22 23:06
 * @PROJECT_NAME Second-SpringBootTest
 * @Description
 */
public class Constants {
    /**
     * 文件后缀
     */
    public static final String FILE_SUFFIX = ".sql";
    /*数据库的名字*/
    public static final String DATA_BASE_NAME = "teachingmanagement";

    /**
     * 判断操作系统类型、Linux|Windows
     */
    public static boolean isSystem(String osName) {
        Boolean flag = null;
        if (osName.startsWith("windows")) {
            flag = true;
        } else if (osName.startsWith("linux")) {
            flag = false;
        }
        return flag;
    }
}

package com.example.springboot12_day.utils;

import lombok.Data;

/**
 * @author 程世玉
 * @create 2022/3/22 23:09
 * @PROJECT_NAME Second-SpringBootTest
 * @Description
 */
@Data
public class ErrorTip {
    private int code;
    private String msg;
    private Object data;

    public ErrorTip(int code, String msg) {
        this.code = code;
        this.msg = msg;
    }
}

package com.example.springboot12_day.utils;

import lombok.Data;

/**
 * @author 程世玉
 * @create 2022/3/22 23:37
 * @PROJECT_NAME Second-SpringBootTest
 * @Description
 */
@Data
public class SuccessTip {
    private Object data;

    public SuccessTip(Object data) {
        this.data = data;
    }
}

Controller层

package com.example.springboot12_day.controller;

import com.example.springboot12_day.entity.MysqlBackups;
import com.example.springboot12_day.service.MysqlBackupsService;
import com.example.springboot12_day.utils.Constants;
import com.example.springboot12_day.utils.ErrorTip;
import com.example.springboot12_day.utils.SuccessTip;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * MySQL数据备份表(MysqlBackups)表控制层
 *
 * @author makejava
 * @since 2022-03-22 22:54:24
 */
@RestController
@RequestMapping("mysqlBackups")
public class MysqlBackupsController {

    /**
     * 数据库用户名
     */
    @Value("${spring.datasource.username}")
    private String userName;

    /**
     * 数据库密码
     */
    @Value("${spring.datasource.password}")
    private String password;

    /**
     * 数据库url
     */
    @Value("${spring.datasource.url}")
    private String url;

    /**
     * Windows数据库备份地址
     */
    @Value("${spring.datasource.win-path}")
    private String windowsPath;

    /**
     * Linux数据库备份地址
     */
    @Value("${spring.datasource.linux-path}")
    private String linuxPath;

    /**
     * 服务对象
     */
    @Resource
    private MysqlBackupsService mysqlBackupsService;


    /**
     * 获取所有备份数据列表
     * @return
     */
    @GetMapping("/backupsList")
    public Object backupsList() {
        List<MysqlBackups> systemMysqlBackups = mysqlBackupsService.selectBackupsList();
        return new SuccessTip(systemMysqlBackups);
    }

    /**
     * MySQL备份
     * @return
     */
    @PostMapping("/mysqlBackups")
    public Object mysqlBackups() {
        String path = null;
        // 获取操作系统名称
        String osName = System.getProperty("os.name").toLowerCase();
        if (Constants.isSystem(osName)) {
            // Windows
            path = this.windowsPath;
        } else {
            // Linux
            path = this.linuxPath;
        }
        // 数据库用户名
        String userName = this.userName;
        // 数据库密码
        String password = this.password;
        // 数据库地址
        String url = this.url;
        // 调用备份
        Object systemMysqlBackups = mysqlBackupsService.mysqlBackups(path, url, userName, password);
        return new SuccessTip(systemMysqlBackups);
    }

    /**
     * 恢复数据库
     * @param map
     * @return
     */
    @PutMapping("/rollback")
//    @ApiJsonObject(name = "恢复数据库", value = {
//            @ApiJsonProperty(name = "id", example = "1", value = "数据id", dataType = "long", required = true)})
//                           @ApiParam(value = "恢复数据库")
    public Object rollback(@RequestBody Map<String, Object> map) {
        Long id = Long.valueOf(map.get("id").toString());
        if (id == null) {
            return new ErrorTip(HttpStatus.INTERNAL_SERVER_ERROR.value(), "id不能为null，请重新尝试！");
        }
        // 数据库用户名
        String userName = this.userName;
        // 数据库密码
        String password = this.password;
        // 根据id查询查询已有的信息
        MysqlBackups smb = mysqlBackupsService.selectListId(id);
        // 恢复数据库
        Object rollback = mysqlBackupsService.rollback(smb, userName, password);
        // 更新操作次数
        mysqlBackupsService.update(smb);
        return new SuccessTip(rollback);
    }
}