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;
|
}
|
}
|
}
|
