Java定时备份MySql数据库，通过mysql命令备份

1：配置数据库定时备份，删除等参数

backup:
  datasource:
    ip: 127.0.0.1
    db: jeecg_test_local   #定义数据库名
    table: ntcp_data_      #定义数据库表名
    port: 3306
    username: root
    password: root
  backupDay: 2              #备份多少天前的数据库数据
  deleteDay: 2              #删除多少天前数据库备份文件
  backupWPath: D:\backup    #Windows备份路径
  backupLPath: /home/backup  #Linux备份路径

2：先写一个mysql操作工具类

package com.hujy.demo.utils;

import lombok.extern.slf4j.Slf4j;

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

/**
 * @Author xu
 * @create 2022/11/19
 */

@Slf4j
public class DbUtil {

    public static void backup(File file, String ip, String user, String password, String dbTable) {
        try {
            //要配置好mysql的环境变量，如果链接远程需要注意修改ip地址，还有支持访问端口开通
            Runtime rt = Runtime.getRuntime();
            String command = "mysqldump --single-transaction -h" + ip + " -u" + user + " -p" + password + " --set-charset=utf8 " + dbTable;
            log.info("数据库备份执行命令为：{}", command);
            Process child = rt.exec(command);
            InputStream inputStream = child.getInputStream();
            try(BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8);){
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "\r\n");
            }
            outputStreamWriter.flush();}
            inputStream.close();
        } catch (Exception e) {
            log.error("数据库备份错误,错误信息{}", e.getMessage(), e);
            e.printStackTrace();
        }
    }

    public static void load(File file, String ip, String user, String password, String db ) {
        try {
            Runtime rt = Runtime.getRuntime();
            String command = "mysql -h" + ip + " -u" + user + " -p" + password + " --default-character-set=utf8 " + db ;
            log.info("数据库还原执行命令为：{}", command);
            Process child = rt.exec(command);
            OutputStream outputStream = child.getOutputStream();
            try(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();
        } catch (Exception e) {
            log.error("数据库恢复错误,错误信息{}", e.getMessage(), e);
            e.printStackTrace();
        }
    }
}

3：java代码操作数据库定时备份，定时删除

package com.hujy.demo.service.impl;

import com.hujy.demo.utils.DbUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.time.LocalDate;
import java.util.Objects;

/**
 * Description
 *
 * @author xu
 * @date 2022-12-19 10:47
 */
@Slf4j
@Component
@EnableScheduling
public class DbHistoryConfig {


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

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

    /**
     * 数据库地址
     */
    @Value("${backup.datasource.ip}")
    private String ip;

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

    /**
     * 表名
     */
    @Value("${backup.datasource.table}")
    private String table;

    /**
     * 备份多少天前的数据库数据
     */
    @Value("${backup.backupDay}")
    private String backupDay;

    /**
     * 删除多少天前数据库备份
     */
    @Value("${backup.deleteDay}")
    private String deleteDay;

    /**
     * Windows备份路径
     */
    @Value("${backup.backupWPath}")
    private String backupWPath;

    /**
     * Linux备份路径
     */
    @Value("${backup.backupLPath}")
    private String backupLPath;

    @Scheduled(cron = "*/10 * * * * * ")
    private void dbBackup() throws IOException {
        log.info("系统开启定时任务数据库振动器表备份");

        // 当前时间 年月日格式
        LocalDate localDate = LocalDate.now();
        LocalDate backupbefore = localDate.minusDays(Long.parseLong(backupDay));

        // 如果是windows的路径是\\，linux的路径是/ ，例子path是文件路径的前缀，filename是文件的名字
        String pathName;
        if (isWindows()) {
            pathName = backupWPath + "\\";
        } else {
            pathName = backupLPath + "/";
        }


        for (int i = 0; i < 24; i++) {
            // 备份文件名称
            String fileBeforeName = table + backupbefore.toString().replaceAll("-", "");
            File file = circul(pathName, i, fileBeforeName);
            file.getParentFile().mkdirs();
            file.createNewFile();
            // 备份多少天前的数据数据库
            DbUtil.backup(file, ip, user, password, db + " " + file.getName().toString().replaceAll(".sql", ""));
        }

        // 删除多少天前数据库备份文件
        LocalDate before = localDate.minusDays(Long.parseLong(deleteDay));
        for (int i = 0; i < 24; i++) {
            String fileBeforeName = table + before.toString().replaceAll("-", "");
            File file = circul(pathName, i, fileBeforeName);
            if (file.exists()) {
                file.delete();
            }
        }
        log.info("系统结束定时任务数据库备份");

    }

    private File circul(String pathName, int i, String fileBeforeName) {
        String hour;
        if (i < 10) {
            hour = "0" + i;
            fileBeforeName = fileBeforeName + (hour + ".sql");
        } else {
            hour = "" + i;
            fileBeforeName = fileBeforeName + (hour + ".sql");
        }
        return new File(pathName, fileBeforeName);
    }


    /**
     * @param fileName 文件名，根据具体情况修饰
     * 传时间过来 例如 2022111917
     * 返回true恢复成功，false恢复失败
     */

    public Boolean fileRenew(String fileName) {

        String pathName;
        if (isWindows()) {
            pathName = backupWPath + "\\";
        } else {
            pathName = backupLPath + "/";
        }
        File fileRenew = new File(pathName + table + fileName + ".sql");
        return DbUtil.load(fileRenew, ip, user, password, db);
    }

    private static boolean isWindows() {
        return System.getProperty("os.name").toLowerCase().contains("windows");
    }
}

4：数据库恢复，要调用DbHistoryConfig.fileRenew(String arg)方法

dbHistoryConfig.fileRenew("2022111702");

5：最简单的做法

    public static void main(String[] args) {
        String username = "your_username";
        String password = "your_password";
        String database = "your_database";
        String backupPath = "/app/backup/luhe.sql";

        try {
            ProcessBuilder processBuilder = new ProcessBuilder(
                "mysqldump",
                "-u", username,
                "-p" + password,
                database,
                "-r", backupPath
            );

            Process process = processBuilder.start();
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                System.out.println("数据库备份成功！");
            } else {
                System.out.println("数据库备份失败！");
            }
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }

问题：如果备份出来的文件只有0K之类的，通过命令直接备份，可以发现是否有任何问题，可以发现密码不对，数据库不对，缺少允许库，还有使用密码插件等问题

mysqldump --single-transaction -h192.168.11.50 -uroot -proot --default-character-set=utf8 jeecg-boot-luhe > test.sql

问题：如果是idea开发，配置完mysqldump的环境变量后，必须重启idea软件，然后使用cmd窗口命令试一下，是否可以备份成功

问题：如果没有mysqldump命令。在window环境只需要mysql中的bin目录（bin目录中必须要有mysqldump执行文件），然后配置环境变量就行，linux环境，拷贝mysqldump到这个/usr/bin/目录下，如果是docker就需要使用挂载的方法

问题：修改用户密码与"mysql native password"插件设置用户的密码:

这是本地回环localhost连接账号密码

ALTER USER '用户账号'@'localhost’ IDENTIFIED WITH mysql native password By '用户密码',
FLUSH PRIVILEGES:

这是其他机器连接账号密码

ALTER USER '用户账号'@'%’ IDENTIFIED WITH mysql native password By '用户密码',
FLUSH PRIVILEGES: