Springboot实现通过mysqldump备份数据库到指定位置

可通过读取配置文件动态获取数据库连接等信息 

mysqldump.exe文件一般位于MySQL安装目录bin文件夹下

package com.yms.service;


import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;


@Component
public class MysqlDumpConfig {

  //备份文件目录
  @Value("${mysql.backup.path}")
  private String filePath;

  //mysqldump的可执行文件路径（一般在mysql安装目录的bin目录下）
  @Value("${mysql.mysqlDump.path}")
  private String mysqlDumpPath;

  //要备份的数据库表名，空格隔开
  @Value("${mysql.backup.tables:yms_user yms_role}")
  private String tables;

  @Value("${yms.database.url}")
  private String dataUrl;

  @Value("${yms.database.username}")
  private String dataUserName;

  @Value("${yms.database.password}")
  private String dataPwd;

  public String backUpSystemLogs() {
    String backPath = "";
    try {
      File saveFile = new File(filePath);
      // 如果目录不存在则创建文件夹
      if (!saveFile.exists()) {
        saveFile.mkdirs();
      }
      String fileName =
          "backup_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(System.currentTimeMillis()) + "_sql.sql";
      backPath = filePath + fileName;
      //开始拼接命令
      StringBuilder command = appendCmd(backPath);
      // 调用外部执行exe文件的javaAPI
      System.out.println("执行mysql指令：" + command);
      String os = System.getProperty("os.name");
      Process process = null;
      if (os.toLowerCase().startsWith("win")) {
        process = Runtime.getRuntime().exec(command.toString());
      } else {//Linux系统执行命令
        process = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", command.toString()});
      }
      // 0 表示线程正常终止。
      Integer waitFor = process.waitFor();
      Integer exitValue = process.exitValue();
      logger.info("process.waitFor()-process.exitValue()===>{},{}", waitFor,exitValue);
      try{
          InputStream errorStream = process.getErrorStream();
          writeToLocal(filePath + "error.txt",errorStream);
      }catch (Exception e){
          System.out.println("未产生异常信息，备份正常");
      }
      if (waitFor != 0) {
          System.out.println("===========backup failure================");
      } else {
          System.out.println("===========backup successful================");
          process.destroy();
      }
    } catch (UnsupportedEncodingException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } catch (InterruptedException e) {
      e.printStackTrace();
    }
    return backPath;
  }

  /**
   * 拼接执行命令
   */
  private StringBuilder appendCmd(String backPath) {
    StringBuilder command = new StringBuilder();
    String mysqlIp = "";
    String mysqlPort = "";
    String mysqlData = "";

    if (StringUtils.isNotBlank(dataUrl)) {
      //从配置中解析出数据库ip+名+端口
      String[] split = dataUrl.split("\\?")[0].split("/");
      mysqlData = split[split.length - 1];
      String allIp = split[split.length - 2];
      String[] ips = allIp.split(":");
      mysqlIp = ips[0];
      mysqlPort = ips[1];
    }
    // 拼接命令行的命令
    //windows: D:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump  --opt -h127.0.0.1 --user=root -P3306 --password=123456 --databases test --tables test_user --result-file=D:\back\backup_20211118_030951_sql.sql --default-character-set=utf8
    //linux: /usr/local/mysql/bin/mysqldump  --opt -h10.11.177.118 --user=root --password='123456' -P3306 --databases test --tables test_user test_role --result-file=/back/backup_20211118_030951_sql.sql
    //如果加了-t 是只导出数据不导出表结构
    command.append(mysqlDumpPath + "mysqldump");
    command.append("  --opt -h" + mysqlIp);
    command.append(" --user=" + dataUserName);
    // 区分系统
    String os = System.getProperty("os.name");
    if (os.toLowerCase().startsWith("win")) {
      command.append(" --password=" + dataPwd);
    } else {
      //linux需要引号密码，否则会执行失败
      command.append(" --password=");
      command.append("'" + dataPwd + "'");
    }
    //端口用大写的P指向
    command.append(" -P" + mysqlPort);
    command.append(" --databases " + mysqlData);
    // 如果表名不为空则表示备份指定表
    if (!StringUtils.isEmpty(tables)) {
      command.append(" --tables " + tables);
    }
    command.append(" --result-file=" + backPath);
    return command;
  }

    private void writeToLocal(String destination, InputStream input)
        throws IOException {
        int index;
        byte[] bytes = new byte[1024];
        FileOutputStream downloadFile = new FileOutputStream(destination);
        while ((index = input.read(bytes)) != -1) {
            downloadFile.write(bytes, 0, index);
            downloadFile.flush();
        }
        input.close();
        downloadFile.close();

    }

    public static void main(String[] args) {
        MysqlDumpConfig config = new MysqlDumpConfig();
        String backPath = config.backUpSystemLogs();
        System.out.println("----------->备份文件路径：" + backPath);
    }

}