windows系统下使用java语言，在mysql数据库中做定时数据备份、删除

有这样一个业务需求，需要将数据归档的表每月定时备份，并且删除之前表中的数据，话不多说，直接上代码！

注意：这种方法适合数据量小，业务要求不高的场景！

项目采用SpringBoot  + MyBatis  + MySql实现。

以下是详细的代码步骤说明：

第一步：在springboot下新建task定时任务类,并且定义方法。

 

package com.nb.nbbl.utils.task;

import com.nb.nbbl.mapper.TaskMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;

@Component
@Slf4j
public class Task {
    private final static String  ip="127.0.0.1";
    private final static int  port= 3306;
    private final static String  user="root";
    private final static String  password="root";
    private  final static String databasenameame ="nbbl.2#";


    @Autowired
    private  TaskMapper taskMapper;

    @Scheduled(cron = "0 55 23 1 * ?") // 每月1号的23点59分执行
//    @Scheduled(cron = "*/10 * * * * ?")
    public void backMysql() throws IOException, InterruptedException {
        log.info("备份数据库");
        String backDate = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss").format(new Date());
        //判断是windows系统还是linux系统
        String os = System.getProperty("os.name");
        if(os.toLowerCase().startsWith("win")) {
            System.out.println(os + "执行备份");
            dataBaseDump(ip, port, user, password, backDate);
        }
    }
    public  void dataBaseDump(String ip,int port,String user,String passWord,String backDate) throws IOException, InterruptedException {
        File file = new File("D:\\MysqlBackFile");
        if(!file.exists()){
            file.mkdir();
        }
         // 注意这句命令一定要加上\\很容易出错
        String command =" D:\\mysqldump\\mysqldump -h" +ip+ " -P" +port+ " -u" +user+ " -p" +passWord+ " " +databasenameame+ ">" +file+"\\"+backDate+".sql";
        System.out.println(command);
        StringBuilder msg = new StringBuilder();
        BufferedReader bufferedReader = null;
        Process proc = null;
        //命令之间不能有空格
        ProcessBuilder pb = new ProcessBuilder("cmd", "/c", command);
//        pb.directory(new File(dir));
        try {
            proc = pb.start();
            System.out.println( proc.getOutputStream());

            bufferedReader = new BufferedReader(new InputStreamReader(proc.getErrorStream(), "gbk"));
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                msg.append(line);
            }
            System.out.println(msg);
            proc.waitFor();
            if(  proc.waitFor() ==0){
                System.out.println("数据备份成功");
                //数据备份成功后，执行删除操作，删除7天以前的数据
                taskMapper.deleteData2();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (bufferedReader != null) {
                try {
                    bufferedReader.close();
                } catch (Exception ex) {
                }
            }
            if(proc!=null){
                proc.destroy();
            }
        }

    }
}
第二步:定义删除数据接口

package com.nb.nbbl.mapper;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TaskMapper {

   void deleteData2();

}
第三步：编写task.xml

<?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.nb.nbbl.mapper.TaskMapper">

<!--    <insert id="insert" parameterType="com.nb.nbbl.entity.DeviceTable">-->
<!--        insert into device_table(device_code,age,birthday,status) values(#{name},#{age},#{birthday},#{status})-->
<!--    </insert>-->
    <delete id="deleteData2" >
        {call deleteData()}
    </delete>
</mapper>


第四步：编写存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `deleteData`()
BEGIN
#Routine body goes here...
delete from bar_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from btm where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from cavitytemp where DATE_SUB(CURDATE(), INTERVAL 7 DAY) >date(systime);
delete from device where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_area_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_whole_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from heater_inn_out_control where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from pressure where DATE_SUB(CURDATE(), INTERVAL 7 DAY) >date(systime);
delete from product_type where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from robot_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from safety_bolt_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);
delete from warnning_record where DATE_SUB(CURDATE(), INTERVAL 1 DAY) >=date(systime);

END

 

第五步：查看结果

 

 

至此，简单的数据备份这个功能点已经实现了！