mySql每天自动备份数据库

package cn.stylefeng.guns.modular.task;


import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.*;
import java.nio.charset.StandardCharsets;

/**
 * @author shiye
 * @create 2022-06-22 16:27
 */
public class DbUtil {

    private static final Log logger = LogFactory.getLog(DbUtil.class);

    public static void backup(File file, String user, String password, String db, String ip) throws IOException {
        InputStream inputStream = null;
        BufferedReader bufferedReader = null;
        OutputStreamWriter outputStreamWriter = null;
        try {
            Runtime rt = Runtime.getRuntime();
            /**
             * 这是在windows下使用得数据备份命令：C:/Program Files/MySQL/MySQL Server 5.7/bin/mysqldump
             * 在liunx下需要尝试：mysqldump
             */
            String command = "C:/Program Files/MySQL/MySQL Server 5.7/bin/mysqldump -u" + user + " -p" + password + " -h " + ip + " " + " --databases " + db;
            logger.info("备份数据得命令：" + command);

            Process child = rt.exec(command);
            inputStream = child.getInputStream();
            bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
            outputStreamWriter = new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8);
            String str;

            //flag 用来标识是否有数据备份，默认没有false
            boolean flag = false;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "\r\n");
                flag = true;
            }
            if (!flag) {
                throw new IOException("没有数据需要备份...");
            }
            outputStreamWriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("导出excel报错：", e.getCause());
            throw e;
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
            if (bufferedReader != null) {
                bufferedReader.close();
            }
            if (outputStreamWriter != null) {
                outputStreamWriter.close();
            }
        }
    }

    public static void load(File file, String user, String password, String db) {
        try {
            Runtime rt = Runtime.getRuntime();
            String command = "mysql -u" + user + " -p" + password + " --default-character-set=utf8 " + db;
            Process child = rt.exec(command);
            OutputStream outputStream = child.getOutputStream();
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), StandardCharsets.UTF_8));
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(outputStream, StandardCharsets.UTF_8);
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "\r\n");
            }
            outputStreamWriter.flush();
            outputStream.close();
            bufferedReader.close();
            outputStreamWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

package cn.stylefeng.guns.modular.task;

import cn.stylefeng.guns.modular.system.model.Notice;
import cn.stylefeng.guns.modular.system.service.INoticeService;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;

/**
 * @author shiye
 * @create 2022-06-22 16:26
 */
@Component
public class DbJob {
    private final Log logger = LogFactory.getLog(DbJob.class);

    // Environment  environment 是用于获取spring配置文件值的接口
    @Autowired
    private Environment environment;

    //数据备份异常得时候 写入通知表进行页面通知
    @Autowired
    private INoticeService noticeService;

    /*
     * 定时时间是每天凌晨2点。
     */
    @Scheduled(cron = "0 0 2 * * ?")
    public void backup() throws IOException {
        logger.info("系统开启定时任务数据库备份");
        try {
            // 数据库用户名
            String user = environment.getProperty("spring.datasource.username");
            // 数据库密码
            String password = environment.getProperty("spring.datasource.password");
            // 数据库地址
            String url = environment.getProperty("spring.datasource.url");

            if (StringUtils.isEmpty(url)) {
                logger.error("没有数据需要导入 推出....");
                throw new IOException("没有查询到需要备份得url");
            }

            // 动态截取数据库url中的数据库名称
            int index1 = url.indexOf("3306/");
            int index2 = url.indexOf("?");
            String db = url.substring(index1 + 5, index2);

            int index3 = url.indexOf("jdbc:mysql://");
            int index4 = url.indexOf(":3306");
            String ip = url.substring(index3 + 13, index4);

            // 当前时间 年月日格式
            LocalDate localDate = LocalDate.now();
            // 备份文件名称
            String fileName = localDate.toString() + ".sql";
            // 输出
            File file = new File("backup", fileName);
            file.getParentFile().mkdirs();
            file.createNewFile();

            // 备份今天数据库
            DbUtil.backup(file, user, password, db, ip);
            // 删除15天前数据库备份文件
            LocalDate before = localDate.minusDays(15);
            String fileBeforeName = before.toString() + ".sql";
            File fileBefore = new File("backup", fileBeforeName);
            if (fileBefore.exists()) {
                logger.info("历史数据直接删除掉 ：" + fileBeforeName);
                fileBefore.delete();
            }
        } catch (Exception e) {
            logger.error("备份数据库异常", e);
            Notice notice = new Notice();
            notice.setTitle("数据备份异常通知");
            notice.setCreatetime(new Date());
            notice.setType(1);
            notice.setContent("数据库在备份中出现错误，请联系管理员进行处理。数据备份时间：" + LocalDateTime.now());
            notice.setCreater(1);
            noticeService.insert(notice);
        }
        logger.info("系统结束定时任务数据库备份");
    }

}

@EnableScheduling