📦 Java 实现 MySQL 同机 & 异机自动备份到 MinIO（附完整代码）

📦 Java 实现 MySQL 同机 & 异机自动备份到 MinIO（附完整代码）

一句话总结：通过 mysqldump + .my.cnf 安全凭据 + SSH/SFTP + MinIO，实现一套通用、安全、自动化的 MySQL 备份方案，支持本地和远程数据库。

---

🎯 背景与目标

• 需求：定期备份指定 MySQL 数据库（如 nacos），无论 MySQL 是否与应用同机。
• 要求：
  • 密码不暴露在命令行（防 ps 泄露）；
  • 支持远程数据库（通过 SSH 执行 mysqldump）；
  • 备份文件压缩后上传至 MinIO；
  • 自动清理旧备份（保留最近 7 份）；
  • 全流程临时文件自动清理。

---

🔧 核心思路

1. 凭据安全：使用临时 .my.cnf 文件（权限 600）传递 MySQL 用户密码，避免命令行明文。
2. 同机 vs 异机判断：通过 IP/主机名比对，自动选择本地执行或 SSH 远程执行。
3. 远程执行：
   • 通过 JSch 上传 .my.cnf 到远程 /tmp；
   • 在远程执行 mysqldump 并立即删除凭据；
   • 通过 SFTP 拉取 .sql 文件回本地。
4. 存储：.sql → .tar.gz → 上传 MinIO；
5. 清理：保留最近 7 份，自动删除更老的备份。

---

📄 完整代码（关键部分）

依赖：jsch（SSH/SFTP）、commons-compress（tar.gz）、minio（对象存储）

@Service
public class MySqlBackupService {

    protected final static String TEMP_DIR = System.getProperty("java.io.tmpdir") + File.separator;

    @Value("${mysql.host}") private String mysqlHost;
    @Value("${mysql.port}") private int mysqlPort;
    @Value("${mysql.user}") private String mysqlUser;
    @Value("${mysql.password}") private String mysqlPassword;
    @Value("${mysql.databases}") private String databases;
    @Value("${mysql.ssh.user}") private String sshUser;
    @Value("${mysql.ssh.password}") private String sshPassword;

    @Autowired private MinioService minioService;
    private static final String bucketName = "backup";

    public Long backupToMinio() throws Exception {
        boolean isLocal = isSameMachine();
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String backupFileName = "mysql_backup_" + timestamp + ".sql";
        String localBackupPath = TEMP_DIR + "tmp" + File.separator + backupFileName;
        String localCnfPath = TEMP_DIR + "tmp" + File.separator + ".my_" + timestamp + ".cnf";

        try {
            createTempMyCnf(localCnfPath, mysqlUser, mysqlPassword, mysqlHost, mysqlPort);

            if (isLocal) {
                executeMysqldumpLocally(localCnfPath, localBackupPath);
            } else {
                executeMysqldumpRemotely(localCnfPath, localBackupPath);
            }

            String tarGzPath = localBackupPath + ".tar.gz";
            createTarGzSingleFile(localBackupPath, tarGzPath);

            String objectName = "mysql/mysql_backup_" + timestamp + ".tar.gz";
            minioService.uploadObject(bucketName, objectName, tarGzPath);
            deleteOldBackups();

            StatObjectResponse stat = minioService.statObject(bucketName, objectName);
            System.out.println("✅ 备份完成: " + objectName + " (" + FileUtil.convertFileSize(stat.size()) + ")");
            return stat.size() / (1024 * 1024); // MB
        } finally {
            // 清理所有临时文件
            deleteFileQuietly(localBackupPath);
            deleteFileQuietly(localCnfPath);
            deleteFileQuietly(localBackupPath + ".tar.gz");
        }
    }
}

🔐 安全生成 .my.cnf

private void createTempMyCnf(String cnfPath, String user, String password, String host, int port) throws IOException {
    String content = String.format(
        "[client]\nuser=%s\npassword=%s\nhost=%s\nport=%d\n",
        user, password, host, port
    );
    File cnfFile = new File(cnfPath);
    cnfFile.getParentFile().mkdirs();
    try (FileWriter writer = new FileWriter(cnfFile)) {
        writer.write(content);
    }

    // 设置权限为 600（仅所有者可读写）
    if (!System.getProperty("os.name").toLowerCase().contains("win")) {
        Set<PosixFilePermission> perms = new HashSet<>();
        perms.add(PosixFilePermission.OWNER_READ);
        perms.add(PosixFilePermission.OWNER_WRITE);
        Files.setPosixFilePermissions(cnfFile.toPath(), perms);
    }
}

🌐 远程执行 mysqldump（关键步骤）

private void executeMysqldumpRemotely(String localCnfPath, String localOutputFile) throws Exception {
    String remoteCnf = "/tmp/.my_remote.cnf";
    String remoteSql = "/tmp/mysql_backup_remote.sql";

    uploadFileViaSsh(localCnfPath, remoteCnf); // 上传凭据

    String dbList = String.join(" ", databases.split(","));
    String remoteCommand = String.format(
        "chmod 600 %s && " +
        "mysqldump --defaults-file=%s --databases %s --single-transaction --routines --triggers --set-gtid-purged=OFF > %s && " +
        "rm -f %s", // 立即删除凭据！
        remoteCnf, remoteCnf, dbList, remoteSql, remoteCnf
    );
    executeRemoteCommand(remoteCommand);

    downloadFileViaSsh(remoteSql, localOutputFile); // 拉取备份
    executeRemoteCommand("rm -f " + remoteSql);     // 清理远程 SQL
}

✅ 安全要点：远程 .my.cnf 在使用后立即删除，避免残留。

🛠️ 配置示例（application.yml）

mysql:
  host: 192.168.1.111
  port: 3306
  user: root
  password: 1234
  databases: nacos
  ssh:
    user: root
    password: 1234   # 仅当 host 非 localhost 时需要

💡 若 host 是 localhost 或本机 IP，则跳过 SSH，直接本地执行。

✅ 优势总结

特性	说明
密码安全	通过 .my.cnf 传参，避免 ps 泄露
自动适配	自动判断同机/异机，无需手动切换逻辑
远程安全	凭据上传 → 使用 → 立删，不留痕迹
存储可靠	压缩后上传 MinIO，节省空间
自动清理	保留最近 7 份，防存储爆炸

---

📌 使用建议

• 定时任务：配合 @Scheduled 每天凌晨执行；
• 日志监控：建议将 System.out 替换为 Logger；
• 密钥替代密码：生产环境建议用 SSH 密钥代替密码（JSch 支持）；
• MinIO 权限：确保 backup bucket 可写。

---

📝 本文仅为个人技术记录，代码已在线上稳定运行。欢迎收藏，方便日后查阅。

完整代码

import com.jcraft.jsch.*;
import io.minio.StatObjectResponse;
import org.apache.commons.compress.archivers.tar.TarArchiveEntry;
import org.apache.commons.compress.archivers.tar.TarArchiveOutputStream;
import org.apache.commons.compress.compressors.gzip.GzipCompressorOutputStream;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.net.InetAddress;
import java.net.NetworkInterface;
import java.nio.file.Files;
import java.nio.file.attribute.PosixFilePermission;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
public class MySqlBackupService {

    protected final static String TEMP_DIR = System.getProperty("java.io.tmpdir") + File.separator;

    @Value("${mysql.host}")
    private String mysqlHost;

    @Value("${mysql.port}")
    private int mysqlPort;

    @Value("${mysql.user}")
    private String mysqlUser;

    @Value("${mysql.password}")
    private String mysqlPassword;

    @Value("${mysql.databases}")
    private String databases;

    @Value("${mysql.ssh.user}")
    private String sshUser;

    @Value("${mysql.ssh.password}")
    private String sshPassword;

    @Autowired
    private MinioService minioService;

    private static final String bucketName = "backup";

    // ==============================
    // 主入口
    // ==============================
    public Long backupToMinio() throws Exception {
        boolean isLocal = isSameMachine();
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss"));
        String backupFileName = "mysql_backup_" + timestamp + ".sql";
        String localBackupPath = TEMP_DIR + "tmp" + File.separator + backupFileName;

        // 临时凭据文件路径
        String localCnfPath = TEMP_DIR + "tmp" + File.separator + ".my_" + timestamp + ".cnf";

        try {
            // 1. 生成本地临时 .my.cnf
            createTempMyCnf(localCnfPath, mysqlUser, mysqlPassword, mysqlHost, mysqlPort);

            if (isLocal) {
                System.out.println("📁 MySQL 在本地，使用安全凭据执行 mysqldump...");
                executeMysqldumpLocally(localCnfPath, localBackupPath);
            } else {
                System.out.println("🌐 MySQL 在远程，通过 SSH 安全执行 mysqldump...");
                executeMysqldumpRemotely(localCnfPath, localBackupPath);
            }

            // 2. 压缩上传
            String tarGzPath = localBackupPath + ".tar.gz";
            createTarGzSingleFile(localBackupPath, tarGzPath);

            String objectName = "mysql/mysql_backup_" + timestamp + ".tar.gz";
            minioService.uploadObject(bucketName, objectName, tarGzPath);
            deleteOldBackups();

            StatObjectResponse stat = minioService.statObject(bucketName, objectName);
            System.out.println("✅ MySQL 备份已上传到 MinIO: " + objectName + " (" + FileUtil.convertFileSize(stat.size())+")");
            return stat.size() / (1024 * 1024);
        } finally {
            // 3. 清理所有临时文件（即使异常）
            deleteFileQuietly(localBackupPath);
            deleteFileQuietly(localCnfPath); // 修正：原代码误删了 .tar.gz
            deleteFileQuietly(localBackupPath + ".tar.gz");
        }
    }

    // ==============================
    // 生成临时 .my.cnf 文件（权限 600）
    // ==============================
    private void createTempMyCnf(String cnfPath, String user, String password, String host, int port) throws IOException {
        String content = String.format(
                "[client]\n" +
                        "user=%s\n" +
                        "password=%s\n" +
                        "host=%s\n" +
                        "port=%d\n",
                user, password, host, port
        );

        File cnfFile = new File(cnfPath);
        // 确保父目录存在（跨平台安全）
        cnfFile.getParentFile().mkdirs();

        try (FileWriter writer = new FileWriter(cnfFile)) {
            writer.write(content);
        }

        // 设置权限为 600（仅所有者可读写）
        if (!System.getProperty("os.name").toLowerCase().contains("win")) {
            Set<PosixFilePermission> perms = new HashSet<>();
            perms.add(PosixFilePermission.OWNER_READ);
            perms.add(PosixFilePermission.OWNER_WRITE);
            Files.setPosixFilePermissions(cnfFile.toPath(), perms);
        }
    }

    // ==============================
    // 本地执行 mysqldump（使用 --defaults-file）
    // ==============================
    private void executeMysqldumpLocally(String cnfPath, String outputFile) throws IOException, InterruptedException {
        File outputFileObj = new File(outputFile);
        outputFileObj.getParentFile().mkdirs(); // 确保目录存在

        String dbList = String.join(" ", databases.split(","));
        String command = String.format(
                "mysqldump --defaults-file=%s --databases %s --single-transaction --routines --triggers --set-gtid-purged=OFF > %s",
                cnfPath, dbList, outputFile
        );

        Process process = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", command});
        int exitCode = process.waitFor();

        if (exitCode != 0) {
            try (BufferedReader err = new BufferedReader(new InputStreamReader(process.getErrorStream()))) {
                String line;
                StringBuilder errorMsg = new StringBuilder();
                while ((line = err.readLine()) != null) errorMsg.append(line).append("\n");
                throw new RuntimeException("mysqldump 失败: " + errorMsg);
            }
        }

        if (!outputFileObj.exists() || outputFileObj.length() == 0) {
            throw new RuntimeException("mysqldump 未生成有效备份文件");
        }
    }

    // ==============================
    // 远程执行 mysqldump（安全方式）
    // ==============================
    private void executeMysqldumpRemotely(String localCnfPath, String localOutputFile) throws Exception {
        String remoteCnf = "/tmp/.my_remote.cnf";
        String remoteSql = "/tmp/mysql_backup_remote.sql";

        try {
            // 1. 上传 .my.cnf 到远程（SFTP）
            uploadFileViaSsh(localCnfPath, remoteCnf);

            // 2. 在远程执行 mysqldump（使用 --defaults-file）
            String dbList = String.join(" ", databases.split(","));
            String remoteCommand = String.format(
                    "chmod 600 %s && " +
                            "mysqldump --defaults-file=%s --databases %s --single-transaction --routines --triggers --set-gtid-purged=OFF > %s && " +
                            "rm -f %s", // 执行完立即删除远程 .my.cnf
                    remoteCnf, remoteCnf, dbList, remoteSql, remoteCnf
            );

            executeRemoteCommand(remoteCommand);

            // 3. 拉取 .sql 文件
            downloadFileViaSsh(remoteSql, localOutputFile);

            // 4. 清理远程 .sql
            executeRemoteCommand("rm -f " + remoteSql);
        } finally {
            // 确保远程凭据被清理（即使失败）
            try {
                executeRemoteCommand("rm -f " + remoteCnf);
            } catch (Exception ignored) {}
        }
    }

    // ==============================
    // SFTP 工具方法
    // ==============================
    private void uploadFileViaSsh(String localFile, String remoteFile) throws JSchException, SftpException {
        JSch jsch = new JSch();
        Session session = jsch.getSession(sshUser, mysqlHost, 22);
        if (!sshPassword.isEmpty()) {
            session.setPassword(sshPassword);
        }
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();

        ChannelSftp sftp = (ChannelSftp) session.openChannel("sftp");
        sftp.connect();
        sftp.put(localFile, remoteFile);
        sftp.disconnect();
        session.disconnect();
    }

    private void downloadFileViaSsh(String remoteFile, String localFile) throws JSchException, SftpException {
        // 确保本地目录存在
        new File(localFile).getParentFile().mkdirs();

        JSch jsch = new JSch();
        Session session = jsch.getSession(sshUser, mysqlHost, 22);
        if (!sshPassword.isEmpty()) {
            session.setPassword(sshPassword);
        }
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();

        ChannelSftp sftp = (ChannelSftp) session.openChannel("sftp");
        sftp.connect();
        sftp.get(remoteFile, localFile);
        sftp.disconnect();
        session.disconnect();
    }

    private void executeRemoteCommand(String command) throws JSchException, IOException {
        JSch jsch = new JSch();
        Session session = jsch.getSession(sshUser, mysqlHost, 22);
        if (!sshPassword.isEmpty()) {
            session.setPassword(sshPassword);
        }
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();

        ChannelExec channel = (ChannelExec) session.openChannel("exec");
        channel.setCommand(command);
        channel.setInputStream(null);
        channel.setErrStream(System.err);

        InputStream in = channel.getInputStream();
        channel.connect();

        byte[] tmp = new byte[1024];
        while (in.read(tmp, 0, tmp.length) != -1) {
            // 可选日志
        }

        channel.disconnect();
        session.disconnect();
    }

    // ==============================
    // 其他工具方法（保持不变）
    // ==============================
    private boolean isSameMachine() {
        return isLocalhost(mysqlHost);
    }

    private boolean isLocalhost(String host) {
        if (host == null) return false;
        if ("localhost".equalsIgnoreCase(host) || "127.0.0.1".equals(host) || "::1".equals(host)) {
            return true;
        }
        try {
            Set<String> localIps = getLocalIpAddresses();
            return localIps.contains(host);
        } catch (Exception e) {
            return false;
        }
    }

    private Set<String> getLocalIpAddresses() throws Exception {
        return Collections.list(NetworkInterface.getNetworkInterfaces()).stream()
                .flatMap(ni -> {
                    try {
                        return Collections.list(ni.getInetAddresses()).stream();
                    } catch (Exception e) {
                        return Stream.of();
                    }
                })
                .filter(ia -> ia instanceof java.net.Inet4Address)
                .map(InetAddress::getHostAddress)
                .collect(Collectors.toSet());
    }

    private void deleteOldBackups() {
        List<BackupObject> backupObjects = minioService.listObjectsRecursive(bucketName, "mysql/", true);
        int excessCount = backupObjects.size() - 7;
        if (excessCount > 0) {
            backupObjects.sort(Comparator.comparing(BackupObject::getModifiedTime));
            for (int i = 0; i < excessCount; i++) {
                minioService.deleteObject(bucketName, backupObjects.get(i).getObjectName());
            }
        }
    }

    private void createTarGzSingleFile(String sqlFilePath, String tarGzPath) throws IOException {
        File sqlFile = new File(sqlFilePath);
        if (!sqlFile.exists()) {
            throw new FileNotFoundException("SQL 文件不存在: " + sqlFilePath);
        }

        new File(tarGzPath).getParentFile().mkdirs();

        try (FileOutputStream fOut = new FileOutputStream(tarGzPath);
             BufferedOutputStream bOut = new BufferedOutputStream(fOut);
             GzipCompressorOutputStream gzOut = new GzipCompressorOutputStream(bOut);
             TarArchiveOutputStream tOut = new TarArchiveOutputStream(gzOut)) {

            tOut.setLongFileMode(TarArchiveOutputStream.LONGFILE_POSIX);
            TarArchiveEntry entry = new TarArchiveEntry(sqlFile, sqlFile.getName());
            tOut.putArchiveEntry(entry);
            try (FileInputStream fis = new FileInputStream(sqlFile)) {
                byte[] buffer = new byte[8192];
                int bytesRead;
                while ((bytesRead = fis.read(buffer)) != -1) {
                    tOut.write(buffer, 0, bytesRead);
                }
            }
            tOut.closeArchiveEntry();
        }
    }

    private void deleteFileQuietly(String path) {
        try {
            new File(path).delete();
        } catch (Exception ignored) {}
    }
}