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: 
@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 {



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

        
        String win = win_path;
        
        String username = dbUserName;
        
        String password = dbpassword;
        
        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);
        
        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];
        
        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;


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


public class MysqlBackups implements Serializable {
    private static final long serialVersionUID = 289003440696670408L;
    
    private Integer id;
    
    private String mysqlIp;
    
    private String mysqlPort;
    
    private String mysqlCmd;
    
    private String mysqlBackCmd;
    
    private String databaseName;
    
    private String backupsPath;
    
    private String backupsName;
    
    private Integer operation;
    
    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文件





    
        
        
        
        
        
        
        
        
        
        
        
        
    

















    
        SELECT *
        FROM `mysql_backups`
        WHERe `status` != 0
          AND id = #{id}
    

    
        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 count(1)
        from mysql_backups
        
            
                and id = #{id}
            
            
                and mysql_ip = #{mysqlIp}
            
            
                and mysql_port = #{mysqlPort}
            
            
                and mysql_cmd = #{mysqlCmd}
            
            
                and mysql_back_cmd = #{mysqlBackCmd}
            
            
                and database_name = #{databaseName}
            
            
                and backups_path = #{backupsPath}
            
            
                and backups_name = #{backupsName}
            
            
                and operation = #{operation}
            
            
                and status = #{status}
            
            
                and recovery_time = #{recoveryTime}
            
            
                and create_time = #{createTime}
            
        
    

    
    
        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 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
        
            (#{entity.mysqlIp}, #{entity.mysqlPort}, #{entity.mysqlCmd}, #{entity.mysqlBackCmd}, #{entity.databaseName},
            #{entity.backupsPath}, #{entity.backupsName}, #{entity.operation}, #{entity.status}, #{entity.recoveryTime},
            #{entity.createTime})
        
    

    
        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
        
            (#{entity.mysqlIp}, #{entity.mysqlPort}, #{entity.mysqlCmd}, #{entity.mysqlBackCmd}, #{entity.databaseName},
            #{entity.backupsPath}, #{entity.backupsName}, #{entity.operation}, #{entity.status}, #{entity.recoveryTime},
            #{entity.createTime})
        
        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)
    

    
    
        update mysql_backups
        
            
                mysql_ip = #{mysqlIp},
            
            
                mysql_port = #{mysqlPort},
            
            
                mysql_cmd = #{mysqlCmd},
            
            
                mysql_back_cmd = #{mysqlBackCmd},
            
            
                database_name = #{databaseName},
            
            
                backups_path = #{backupsPath},
            
            
                backups_name = #{backupsName},
            
            
                operation = #{operation},
            
            
                status = #{status},
            
            
                recovery_time = #{recoveryTime},
            
            
                create_time = #{createTime},
            
        
        where id = #{id}
    

    
    
        delete
        from mysql_backups
        where id = #{id}

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;


public interface MysqlBackupsDao {

    
    MysqlBackups queryById(Integer id);

    
    List queryAllByLimit(MysqlBackups mysqlBackups, @Param("pageable") Pageable pageable);

    
    long count(MysqlBackups mysqlBackups);

    
    int insert(MysqlBackups mysqlBackups);

    
    int insertBatch(@Param("entities") List entities);

    
    int insertOrUpdateBatch(@Param("entities") List entities);

    
    int update(MysqlBackups mysqlBackups);

    
    int deleteById(Integer id);



    
    List selectBackupsList();

    
    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;


public interface MysqlBackupsService {

    
    MysqlBackups queryById(Integer id);

    
    Page queryByPage(MysqlBackups mysqlBackups, PageRequest pageRequest);

    
    MysqlBackups insert(MysqlBackups mysqlBackups);

    
    MysqlBackups update(MysqlBackups mysqlBackups);

    
    boolean deleteById(Integer id);


    
    List selectBackupsList();

    
    Object mysqlBackups(String filePath, String url, String userName, String password);

    
    MysqlBackups selectListId(Long id);

    
    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;


@Service("mysqlBackupsService")
@Slf4j
public class MysqlBackupsServiceImpl implements MysqlBackupsService {
    @Resource
    private MysqlBackupsDao mysqlBackupsDao;

    
    @Override
    public MysqlBackups queryById(Integer id) {
        return this.mysqlBackupsDao.queryById(id);
    }

    
    @Override
    public Page queryByPage(MysqlBackups mysqlBackups, PageRequest pageRequest) {
        long total = this.mysqlBackupsDao.count(mysqlBackups);
        return new PageImpl<>(this.mysqlBackupsDao.queryAllByLimit(mysqlBackups, pageRequest), pageRequest, total);
    }

    
    @Override
    public MysqlBackups insert(MysqlBackups mysqlBackups) {
        this.mysqlBackupsDao.insert(mysqlBackups);
        return mysqlBackups;
    }

    
    @Override
    public MysqlBackups update(MysqlBackups mysqlBackups) {
        this.mysqlBackupsDao.update(mysqlBackups);
        return this.queryById(mysqlBackups.getId());
    }

    
    @Override
    public boolean deleteById(Integer id) {
        return this.mysqlBackupsDao.deleteById(id) > 0;
    }

    @Override
    public List 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;


public class Constants {
    
    public static final String FILE_SUFFIX = ".sql";
    
    public static final String DATA_base_NAME = "teachingmanagement";

    
    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;


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


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


@RestController
@RequestMapping("mysqlBackups")
public class MysqlBackupsController {

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

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

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

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

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

    
    @Resource
    private MysqlBackupsService mysqlBackupsService;


    
    @GetMapping("/backupsList")
    public Object backupsList() {
        List systemMysqlBackups = mysqlBackupsService.selectBackupsList();
        return new SuccessTip(systemMysqlBackups);
    }

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

    
    @PutMapping("/rollback")
//    @ApiJsonObject(name = "恢复数据库", value = {
//            @ApiJsonProperty(name = "id", example = "1", value = "数据id", dataType = "long", required = true)})
//                           @ApiParam(value = "恢复数据库")
    public Object rollback(@RequestBody Map 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);
    }
}

spring boot定时备份数据库

Java相关栏目本月热门文章