From 6c983ceba714f2f2120fd49dfecda8b01d961634 Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期一, 31 八月 2026 18:07:30 +0800
Subject: [PATCH] feat(database): 添加数据库备份的JDBC兜底方案
---
yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java | 137 +++++++++++++++++++++++++++++++++++++++++++--
1 files changed, 131 insertions(+), 6 deletions(-)
diff --git a/yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java b/yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java
index 69903fa..0ff72a9 100644
--- a/yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java
+++ b/yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java
@@ -3,14 +3,18 @@
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
+import cn.hutool.core.util.HexUtil;
import cn.hutool.core.util.StrUtil;
import cn.iocoder.yudao.framework.common.exception.ServiceException;
+import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
+import javax.sql.DataSource;
import java.io.BufferedReader;
+import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
@@ -18,8 +22,14 @@
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
+import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Statement;
+import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@@ -68,6 +78,10 @@
/** 澶囦唤瀵嗙爜锛岀暀绌哄垯浣跨敤鏁版嵁婧愬瘑鐮� */
@Value("${yudao.database-backup.password:}")
private String backupPassword;
+
+ /** 涓绘暟鎹簮锛岀敤浜� JDBC 瀵煎嚭鍏滃簳 */
+ @Resource
+ private DataSource dataSource;
@Override
public void backupDatabase(HttpServletResponse response) throws Exception {
@@ -121,13 +135,9 @@
candidates.add(buildDockerExecCommand(container, username, password, dbName));
labels.add("Docker 瀹瑰櫒[" + container + "] mysqldump");
}
- if (candidates.isEmpty()) {
- throw new ServiceException(500, "鏈娴嬪埌鍙敤鐨� mysqldump 鐜锛氭湰鏈烘湭瀹夎 MySQL 瀹㈡埛绔紝涔熸病鏈夎繍琛屼腑鐨� MySQL Docker 瀹瑰櫒銆�"
- + "璇峰畨瑁� MySQL 瀹㈡埛绔紝鎴栭�氳繃 yudao.database-backup.command 鎵嬪姩鎸囧畾澶囦唤鍛戒护锛堝 docker exec mysql8 mysqldump锛�");
- }
log.info("鏁版嵁搴撳浠藉�欓�夋柟寮忥細{}", labels);
- // 渚濇灏濊瘯鎵�鏈夋柟寮忥紝鍏ㄩ儴澶辫触鎵嶆姤閿�
+ // 渚濇灏濊瘯鎵�鏈夊閮ㄥ懡浠ゆ柟寮�
List<String> failures = new ArrayList<>();
String timestamp = DateUtil.format(new Date(), "yyyyMMdd_HHmmss");
String fileName = "backup_" + timestamp + ".sql";
@@ -146,7 +156,122 @@
FileUtil.del(tempFile);
}
}
- throw new ServiceException(500, "鏁版嵁搴撳浠藉け璐ワ紙宸插皾璇� " + candidates.size() + " 绉嶆柟寮忥級锛�" + String.join(" 锝� ", failures));
+
+ // 澶栭儴鍛戒护鍏ㄩ儴澶辫触鎴栦笉鍙敤鏃讹紝浣跨敤 JDBC 瀵煎嚭鍏滃簳锛屼繚璇佷换浣曢儴缃茬幆澧冿紙濡傚簲鐢ㄥ鍣ㄥ唴鏃� mysqldump/docker锛夐兘鑳藉浠�
+ File tempFile = File.createTempFile("backup_", ".sql");
+ try {
+ String error = exportByJdbc(tempFile);
+ if (error == null) {
+ log.info("鏁版嵁搴撳浠芥垚鍔燂紝浣跨敤鏂瑰紡锛欽DBC 瀵煎嚭鍏滃簳");
+ downloadAttachment(response, tempFile, fileName);
+ return;
+ }
+ log.warn("JDBC 瀵煎嚭鍏滃簳澶辫触锛歿}", error);
+ failures.add("JDBC 瀵煎嚭鍏滃簳锛�" + error);
+ } finally {
+ FileUtil.del(tempFile);
+ }
+ throw new ServiceException(500, "鏁版嵁搴撳浠藉け璐ワ細" + String.join(" 锝� ", failures));
+ }
+
+ /**
+ * 閫氳繃 JDBC 鐩磋繛鏁版嵁搴撳鍑哄缓琛ㄨ鍙ヤ笌鍏ㄩ噺鏁版嵁锛屼綔涓哄閮ㄥ懡浠や笉鍙敤鏃剁殑鍏滃簳澶囦唤鏂瑰紡
+ *
+ * @return 鎵ц鎴愬姛杩斿洖 null锛涘け璐ヨ繑鍥為敊璇弿杩�
+ */
+ private String exportByJdbc(File outputFile) {
+ try (Connection conn = dataSource.getConnection();
+ BufferedWriter writer = new BufferedWriter(
+ new OutputStreamWriter(new FileOutputStream(outputFile), StandardCharsets.UTF_8))) {
+ writer.write("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n");
+ writer.write("/*!40101 SET NAMES utf8mb4 */;\n");
+ writer.write("SET TIME_ZONE='+00:00';\n");
+ writer.write("SET FOREIGN_KEY_CHECKS=0;\n");
+ writer.write("SET UNIQUE_CHECKS=0;\n");
+ writer.write("SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n\n");
+ try (Statement st = conn.createStatement();
+ ResultSet rs = st.executeQuery("SHOW TABLES")) {
+ List<String> tables = new ArrayList<>();
+ while (rs.next()) {
+ tables.add(rs.getString(1));
+ }
+ for (String table : tables) {
+ exportTable(conn, writer, table);
+ }
+ }
+ writer.write("\nSET FOREIGN_KEY_CHECKS=1;\n");
+ writer.write("SET UNIQUE_CHECKS=1;\n");
+ writer.write("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n");
+ return null;
+ } catch (Exception e) {
+ log.error("JDBC 瀵煎嚭鍏滃簳澶辫触", e);
+ return "JDBC 瀵煎嚭寮傚父: " + e.getMessage();
+ }
+ }
+
+ /**
+ * 瀵煎嚭鍗曞紶琛ㄧ殑寤鸿〃璇彞涓庡叏閮ㄦ暟鎹�
+ */
+ private void exportTable(Connection conn, BufferedWriter writer, String table) throws Exception {
+ String quoted = "`" + table.replace("`", "``") + "`";
+ // 寤鸿〃璇彞
+ try (Statement st = conn.createStatement();
+ ResultSet rs = st.executeQuery("SHOW CREATE TABLE " + quoted)) {
+ if (rs.next()) {
+ writer.write(rs.getString(2));
+ writer.write(";\n\n");
+ }
+ }
+ // 鍏ㄩ噺鏁版嵁锛屾祦寮忚鍙栭伩鍏嶅ぇ琛ㄥ崰鐢ㄨ繃澶氬唴瀛�
+ try (Statement st = conn.createStatement()) {
+ st.setFetchSize(Integer.MIN_VALUE);
+ try (ResultSet rs = st.executeQuery("SELECT * FROM " + quoted)) {
+ ResultSetMetaData meta = rs.getMetaData();
+ int columnCount = meta.getColumnCount();
+ int[] types = new int[columnCount];
+ for (int i = 0; i < columnCount; i++) {
+ types[i] = meta.getColumnType(i + 1);
+ }
+ while (rs.next()) {
+ StringBuilder sb = new StringBuilder("INSERT INTO ").append(quoted).append(" VALUES (");
+ for (int i = 0; i < columnCount; i++) {
+ if (i > 0) {
+ sb.append(',');
+ }
+ if (rs.getObject(i + 1) == null) {
+ sb.append("NULL");
+ continue;
+ }
+ int type = types[i];
+ if (type == Types.BIT) {
+ sb.append(rs.getBoolean(i + 1) ? '1' : '0');
+ } else if (isBinaryType(type)) {
+ sb.append("X'").append(HexUtil.encodeHexStr(rs.getBytes(i + 1))).append("'");
+ } else {
+ sb.append('\'').append(escapeSql(rs.getString(i + 1))).append('\'');
+ }
+ }
+ sb.append(");\n");
+ writer.write(sb.toString());
+ }
+ }
+ }
+ writer.write('\n');
+ }
+
+ /**
+ * 鏄惁浜岃繘鍒跺垪绫诲瀷锛屼簩杩涘埗鏁版嵁浠ュ崄鍏繘鍒跺瓧闈㈤噺瀵煎嚭閬垮厤涔辩爜
+ */
+ private boolean isBinaryType(int type) {
+ return type == Types.BINARY || type == Types.VARBINARY
+ || type == Types.LONGVARBINARY || type == Types.BLOB;
+ }
+
+ /**
+ * SQL 瀛楃涓插瓧闈㈤噺杞箟
+ */
+ private String escapeSql(String value) {
+ return value.replace("\\", "\\\\").replace("'", "''").replace("\0", "\\0");
}
/**
--
Gitblit v1.9.3