📦 Java 实现 MySQL 同机 & 异机自动备份到 MinIO(附完整代码)
一句话总结:通过
mysqldump
+.my.cnf
安全凭据 + SSH/SFTP + MinIO,实现一套通用、安全、自动化的 MySQL 备份方案,支持本地和远程数据库。
🎯 背景与目标
- 需求:定期备份指定 MySQL 数据库(如
nacos
),无论 MySQL 是否与应用同机。 - 要求:
- 密码不暴露在命令行(防
ps
泄露); - 支持远程数据库(通过 SSH 执行
mysqldump
); - 备份文件压缩后上传至 MinIO;
- 自动清理旧备份(保留最近 7 份);
- 全流程临时文件自动清理。
- 密码不暴露在命令行(防
🔧 核心思路
- 凭据安全:使用临时
.my.cnf
文件(权限 600)传递 MySQL 用户密码,避免命令行明文。 - 同机 vs 异机判断:通过 IP/主机名比对,自动选择本地执行或 SSH 远程执行。
- 远程执行:
- 通过 JSch 上传
.my.cnf
到远程/tmp
; - 在远程执行
mysqldump
并立即删除凭据; - 通过 SFTP 拉取
.sql
文件回本地。
- 通过 JSch 上传
- 存储:
.sql
→.tar.gz
→ 上传 MinIO; - 清理:保留最近 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.111port: 3306user: rootpassword: 1234databases: nacosssh:user: rootpassword: 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;@Autowiredprivate 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.cnfcreateTempMyCnf(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.gzdeleteFileQuietly(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.cnfremoteCnf, remoteCnf, dbList, remoteSql, remoteCnf);executeRemoteCommand(remoteCommand);// 3. 拉取 .sql 文件downloadFileViaSsh(remoteSql, localOutputFile);// 4. 清理远程 .sqlexecuteRemoteCommand("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) {}}
}