Merge branch 'refs/heads/dev_business' into dev_新疆_绿能

云

21 小时以前 7e69a1f3aef86c535ecbb781128b04950f235076

 yudao-module-system/src/main/java/cn/iocoder/yudao/module/system/service/database/DatabaseBackupServiceImpl.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,460 @@
package cn.iocoder.yudao.module.system.service.database;

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;
import java.io.IOException;
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;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
@Slf4j
public class DatabaseBackupServiceImpl implements DatabaseBackupService {

    /** å¤‡ä»½è¶…时时间（分钟） */
    private static final long BACKUP_TIMEOUT_MINUTES = 10;
    /** MySQL å®¹å™¨å†…部端口 */
    private static final String DOCKER_MYSQL_PORT = "3306";

    @Value("${spring.datasource.dynamic.datasource.master.url:}")
    private String dbUrl;

    @Value("${spring.datasource.dynamic.datasource.master.username:root}")
    private String dbUsername;

    @Value("${spring.datasource.dynamic.datasource.master.password:}")
    private String dbPassword;

    /** å¤‡ä»½å‘½ä»¤ï¼Œå¯æ˜¾å¼æŒ‡å®šï¼ˆå¦‚ docker exec mysql8 mysqldump）；留空则自动检测本地 mysqldump æˆ– Docker MySQL å®¹å™¨ */
    @Value("${yudao.database-backup.command:}")
    private String backupCommand;

    /** å¤‡ä»½ç›®æ ‡æ•°æ®åº“ host，留空则从数据源 JDBC URL è§£æž */
    @Value("${yudao.database-backup.host:}")
    private String backupHost;

    /** å¤‡ä»½ç›®æ ‡æ•°æ®åº“ port，留空则从数据源 JDBC URL è§£æž */
    @Value("${yudao.database-backup.port:}")
    private String backupPort;

    /** å¤‡ä»½ç›®æ ‡æ•°æ®åº“名，留空则从数据源 JDBC URL è§£æž */
    @Value("${yudao.database-backup.database:}")
    private String backupDatabase;

    /** å¤‡ä»½è´¦å·ï¼Œç•™ç©ºåˆ™ä½¿ç”¨æ•°æ®æºè´¦å· */
    @Value("${yudao.database-backup.username:}")
    private String backupUsername;

    /** å¤‡ä»½å¯†ç ï¼Œç•™ç©ºåˆ™ä½¿ç”¨æ•°æ®æºå¯†ç  */
    @Value("${yudao.database-backup.password:}")
    private String backupPassword;

    /** ä¸»æ•°æ®æºï¼Œç”¨äºŽ JDBC å¯¼å‡ºå…œåº• */
    @Resource
    private DataSource dataSource;

    @Override
    public void backupDatabase(HttpServletResponse response) throws Exception {
        if (StrUtil.isEmpty(dbUrl)) {
            throw new ServiceException(500, "未找到数据库连接配置");
        }

        // å¤‡ä»½è¿žæŽ¥çš„数据库信息：优先使用 yudao.database-backup é…ç½®ï¼Œæœªé…ç½®çš„部分从数据源 JDBC URL è§£æžå…œåº•
        String dbHost = backupHost;
        String dbPort = backupPort;
        String dbName = backupDatabase;
        if (StrUtil.isEmpty(dbHost) || StrUtil.isEmpty(dbPort) || StrUtil.isEmpty(dbName)) {
            Matcher matcher = Pattern.compile("jdbc:mysql://([^:]+):(\\d+)/([^?]+)").matcher(dbUrl);
            if (matcher.find()) {
                if (StrUtil.isEmpty(dbHost)) {
                    dbHost = matcher.group(1);
                }
                if (StrUtil.isEmpty(dbPort)) {
                    dbPort = matcher.group(2);
                }
                if (StrUtil.isEmpty(dbName)) {
                    dbName = matcher.group(3);
                }
            }
        }
        if (StrUtil.isEmpty(dbHost) || StrUtil.isEmpty(dbPort) || StrUtil.isEmpty(dbName)) {
            log.warn("无法从配置或 JDBC URL è§£æžå®Œæ•´çš„数据库信息: host={}, port={}, db={}", dbHost, dbPort, dbName);
            throw new ServiceException(500, "无法解析数据库备份连接信息，请在 yudao.database-backup ä¸­é…ç½® host/port/database");
        }
        // localhost ç»Ÿä¸€èµ° TCP 127.0.0.1：mysqldump å¯¹ localhost å¯èƒ½èµ° socket/loopback，
        // è€Œä¸åŒçŽ¯å¢ƒ root@localhost çš„认证要求差异大，强制 TCP æ›´ç¨³å®š
        if ("localhost".equalsIgnoreCase(dbHost)) {
            dbHost = "127.0.0.1";
        }
        String username = StrUtil.isEmpty(backupUsername) ? dbUsername : backupUsername;
        String password = StrUtil.isEmpty(backupPassword) ? dbPassword : backupPassword;

        // ç»„装候选备份命令，按优先级依次尝试：手动配置 > æœ¬æœº mysqldump > Docker å®¹å™¨ mysqldump
        List<List<String>> candidates = new ArrayList<>();
        List<String> labels = new ArrayList<>();
        if (StrUtil.isNotEmpty(backupCommand)) {
            candidates.add(splitCommand(backupCommand));
            labels.add("手动配置命令[" + backupCommand + "]");
        }
        if (isLocalMysqldumpAvailable()) {
            candidates.add(buildLocalMysqldumpCommand(dbHost, dbPort, username, password, dbName));
            labels.add("本机 mysqldump");
        }
        String container = findMysqlDockerContainer(dbPort);
        if (StrUtil.isNotEmpty(container)) {
            candidates.add(buildDockerExecCommand(container, username, password, dbName));
            labels.add("Docker å®¹å™¨[" + container + "] mysqldump");
        }
        log.info("数据库备份候选方式：{}", labels);

        // ä¾æ¬¡å°è¯•æ‰€æœ‰å¤–部命令方式
        List<String> failures = new ArrayList<>();
        String timestamp = DateUtil.format(new Date(), "yyyyMMdd_HHmmss");
        String fileName = "backup_" + timestamp + ".sql";
        for (int i = 0; i < candidates.size(); i++) {
            File tempFile = File.createTempFile("backup_", ".sql");
            try {
                String error = runMysqldump(candidates.get(i), password, tempFile);
                if (error == null) {
                    log.info("数据库备份成功，使用方式：{}", labels.get(i));
                    downloadAttachment(response, tempFile, fileName);
                    return;
                }
                log.warn("备份方式 [{}] å¤±è´¥ï¼š{}", labels.get(i), error);
                failures.add(labels.get(i) + "：" + error);
            } finally {
                FileUtil.del(tempFile);
            }
        }

        // å¤–部命令全部失败或不可用时，使用 JDBC å¯¼å‡ºå…œåº•ï¼Œä¿è¯ä»»ä½•éƒ¨ç½²çŽ¯å¢ƒï¼ˆå¦‚应用容器内无 mysqldump/docker）都能备份
        File tempFile = File.createTempFile("backup_", ".sql");
        try {
            String error = exportByJdbc(tempFile);
            if (error == null) {
                log.info("数据库备份成功，使用方式：JDBC å¯¼å‡ºå…œåº•");
                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");
    }

    /**
     * æž„建本机 mysqldump å¤‡ä»½å‘½ä»¤
     */
    private List<String> buildLocalMysqldumpCommand(String host, String port, String username, String password, String dbName) {
        List<String> command = new ArrayList<>();
        command.add("mysqldump");
        // MySQL 8 caching_sha2_password è®¤è¯é»˜è®¤ä¸è¯·æ±‚服务端公钥，会报 Access denied (using password: YES)
        command.add("--get-server-public-key");
        command.add("-h" + host);
        command.add("-P" + port);
        command.add("-u" + username);
        if (StrUtil.isEmpty(password)) {
            command.add("--skip-password");
        }
        command.add(dbName);
        return command;
    }

    /**
     * æž„建通过 docker exec è°ƒç”¨å®¹å™¨å†… mysqldump çš„备份命令
     */
    private List<String> buildDockerExecCommand(String container, String username, String password, String dbName) {
        List<String> command = new ArrayList<>();
        command.add("docker");
        command.add("exec");
        // docker exec ä¸ä¼šè½¬å‘宿主机环境变量，密码需通过 -e æ˜¾å¼ä¼ å…¥å®¹å™¨
        if (StrUtil.isNotEmpty(password)) {
            command.add("-e");
            command.add("MYSQL_PWD=" + password);
        }
        command.add(container);
        command.add("mysqldump");
        command.add("--get-server-public-key");
        // å®¹å™¨å†…固定走 127.0.0.1:3306 çš„ TCP è¿žæŽ¥è‡ªèº«ï¼Œç»•å¼€ socket/loopback è®¤è¯å·®å¼‚
        command.add("-h127.0.0.1");
        command.add("-P" + DOCKER_MYSQL_PORT);
        command.add("-u" + username);
        if (StrUtil.isEmpty(password)) {
            command.add("--skip-password");
        }
        command.add(dbName);
        return command;
    }

    /**
     * æ‰§è¡Œ mysqldump å‘½ä»¤ï¼Œå°† stdout å†™å…¥ä¸´æ—¶æ–‡ä»¶
     *
     * @return æ‰§è¡ŒæˆåŠŸè¿”回 null；失败返回错误描述
     */
    private String runMysqldump(List<String> command, String password, File outputFile) {
        try {
            ProcessBuilder pb = new ProcessBuilder(command);
            // å¯†ç é€šè¿‡çŽ¯å¢ƒå˜é‡ä¼ é€’，避免出现在命令行及进程列表
            if (StrUtil.isNotEmpty(password)) {
                pb.environment().put("MYSQL_PWD", password);
            }
            Process process = pb.start();

            // åŽå°è¯»å– stderr，防止管道缓冲区写满导致进程阻塞
            StringBuilder stderr = new StringBuilder();
            Thread stderrThread = new Thread(() -> {
                try (BufferedReader reader = new BufferedReader(
                        new InputStreamReader(process.getErrorStream(), StandardCharsets.UTF_8))) {
                    String line;
                    while ((line = reader.readLine()) != null) {
                        stderr.append(line).append(System.lineSeparator());
                    }
                } catch (IOException ignored) {
                }
            });
            stderrThread.start();

            // mysqldump è¾“出写入本地临时文件（docker exec åœºæ™¯è¾“出不会落在容器内文件系统）
            try (InputStream in = process.getInputStream();
                 OutputStream out = new FileOutputStream(outputFile)) {
                IoUtil.copy(in, out);
            }

            boolean finished = process.waitFor(BACKUP_TIMEOUT_MINUTES, TimeUnit.MINUTES);
            stderrThread.join();
            if (!finished) {
                process.destroyForcibly();
                return "执行超时（超过 " + BACKUP_TIMEOUT_MINUTES + " åˆ†é’Ÿï¼‰";
            }
            if (process.exitValue() != 0) {
                return "exitCode=" + process.exitValue() + "，" + stderr.toString().trim();
            }
            return null;
        } catch (IOException e) {
            return "启动命令失败: " + e.getMessage();
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            return "执行被中断: " + e.getMessage();
        }
    }

    /**
     * å°†å¤‡ä»½æ–‡ä»¶ä½œä¸ºé™„件输出到响应
     */
    private void downloadAttachment(HttpServletResponse response, File file, String fileName) throws IOException {
        response.setContentType("application/octet-stream; charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        response.setContentLengthLong(file.length());
        try (FileInputStream fis = new FileInputStream(file);
             OutputStream os = response.getOutputStream()) {
            IoUtil.copy(fis, os);
        }
    }

    /**
     * å°†é…ç½®çš„命令字符串按空白切分为参数列表
     */
    private List<String> splitCommand(String command) {
        List<String> parts = new ArrayList<>();
        for (String token : command.trim().split("\\s+")) {
            parts.add(token);
        }
        return parts;
    }

    /**
     * æ£€æµ‹æœ¬æœº mysqldump æ˜¯å¦å¯ç”¨
     */
    private boolean isLocalMysqldumpAvailable() {
        try {
            Process process = new ProcessBuilder("mysqldump", "--version").redirectErrorStream(true).start();
            boolean finished = process.waitFor(5, TimeUnit.SECONDS);
            if (!finished) {
                process.destroyForcibly();
                return false;
            }
            return process.exitValue() == 0;
        } catch (IOException e) {
            return false;
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            return false;
        }
    }

    /**
     * æŸ¥æ‰¾è¿è¡Œä¸­çš„ MySQL Docker å®¹å™¨ï¼Œä¼˜å…ˆé€‰æ‹©ç«¯å£æ˜ å°„与数据源端口一致的容器
     *
     * @return å®¹å™¨åï¼›æœªæ‰¾åˆ°è¿”回 null
     */
    private String findMysqlDockerContainer(String dbPort) {
        try {
            Process ps = new ProcessBuilder("docker", "ps", "--format", "{{.ID}}|{{.Names}}|{{.Image}}")
                    .redirectErrorStream(true).start();
            String output = IoUtil.read(ps.getInputStream(), StandardCharsets.UTF_8);
            if (!ps.waitFor(10, TimeUnit.SECONDS) || ps.exitValue() != 0) {
                return null;
            }
            List<String[]> containers = new ArrayList<>();
            for (String line : output.split("\n")) {
                String[] parts = line.trim().split("\\|");
                if (parts.length == 3 && parts[2].startsWith("mysql")) {
                    containers.add(parts);
                }
            }
            if (containers.isEmpty()) {
                return null;
            }
            // ä¼˜å…ˆé€‰æ‹©ç«¯å£æ˜ å°„与数据源端口一致的容器
            for (String[] container : containers) {
                Process portProcess = new ProcessBuilder("docker", "port", container[0], "3306/tcp")
                        .redirectErrorStream(true).start();
                String portOutput = IoUtil.read(portProcess.getInputStream(), StandardCharsets.UTF_8);
                if (portProcess.waitFor(10, TimeUnit.SECONDS) && portProcess.exitValue() == 0
                        && portOutput.contains(dbPort)) {
                    return container[1];
                }
            }
            log.warn("未匹配到端口映射的 MySQL å®¹å™¨ï¼Œä½¿ç”¨ç¬¬ä¸€ä¸ª: {}", containers.get(0)[1]);
            return containers.get(0)[1];
        } catch (IOException e) {
            log.warn("检测 Docker çŽ¯å¢ƒå¤±è´¥: {}", e.getMessage());
            return null;
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
            return null;
        }
    }
}