您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java 實現 MySQL 資料庫備份與還原完整代碼方案

編輯：Java 相關知識

Java 操作 MySQL 備份還原：從 Runtime.exec 到純 Java 實現的演進

資料庫備份還原是系統運維核心功能。Java 實現主要有兩條路徑：呼叫 mysqldump/mysql 客戶端工具 (依賴環境) 與 純 JDBC 程式化實現 (跨平台、可控性強)。本文提供兩套完整方案代碼。

方案一：呼叫 mysqldump / mysql 客戶端 (生產環境推薦)

優點：效能最強、支援完整 MySQL 語法、壓縮、單表/多庫靈活。缺點：依賴客戶端安裝路徑、需處理密碼安全。

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class MySqlBackupRestore {

    // 配置屬性 (實際應從配置檔讀取)
    private static final String MYSQL_DUMP_PATH = "C:/Program Files/MySQL/MySQL Server 8.0/bin/mysqldump.exe";
    private static final String MYSQL_PATH = "C:/Program Files/MySQL/MySQL Server 8.0/bin/mysql.exe";
    private static final String HOST = "localhost";
    private static final String PORT = "3306";
    private static final String USER = "root";
    private static final String PASSWORD = "your_password"; // 注意：生產環境勿硬編碼
    private static final String DATABASE = "my_database";
    private static final String BACKUP_DIR = "D:/db_backups/";

    /**
     * 備份資料庫
     * @param backupFileName 備份文件名 (含 .sql 後綴)
     * @return 是否成功
     */
    public static boolean backup(String backupFileName) {
        File backupDir = new File(BACKUP_DIR);
        if (!backupDir.exists()) backupDir.mkdirs();
        
        File backupFile = new File(BACKUP_DIR + backupFileName);
        List commands = new ArrayList<>();
        commands.add(MYSQL_DUMP_PATH);
        commands.add("--host=" + HOST);
        commands.add("--port=" + PORT);
        commands.add("--user=" + USER);
        commands.add("--password=" + PASSWORD); // 安全風險見下文
        commands.add("--default-character-set=utf8mb4");
        commands.add("--single-transaction"); // InnoDB 熱備份關鍵參數
        commands.add("--routines");
        commands.add("--triggers");
        commands.add("--events");
        commands.add(DATABASE);
        
        ProcessBuilder pb = new ProcessBuilder(commands);
        pb.redirectOutput(backupFile);
        pb.redirectErrorStream(true);
        
        try {
            Process process = pb.start();
            int exitCode = process.waitFor();
            if (exitCode == 0) {
                System.out.println("備份成功：" + backupFile.getAbsolutePath());
                return true;
            } else {
                // 讀取錯誤輸出
                try (BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()))) {
                    String line;
                    while ((line = reader.readLine()) != null) System.err.println(line);
                }
                System.err.println("備份失敗，退出碼：" + exitCode);
                return false;
            }
        } catch (IOException | InterruptedException e) {
            Thread.currentThread().interrupt();
            System.err.println("備份異常：" + e.getMessage());
            return false;
        }
    }

    /**
     * 還原資料庫
     * @param backupFilePath 備份文件完整路徑
     * @return 是否成功
     */
    public static boolean restore(String backupFilePath) {
        File backupFile = new File(backupFilePath);
        if (!backupFile.exists()) {
            System.err.println("備份文件不存在：" + backupFilePath);
            return false;
        }
        
        List commands = new ArrayList<>();
        commands.add(MYSQL_PATH);
        commands.add("--host=" + HOST);
        commands.add("--port=" + PORT);
        commands.add("--user=" + USER);
        commands.add("--password=" + PASSWORD);
        commands.add("--default-character-set=utf8mb4");
        commands.add(DATABASE);
        
        ProcessBuilder pb = new ProcessBuilder(commands);
        pb.redirectInput(backupFile);
        pb.redirectErrorStream(true);
        
        try {
            Process process = pb.start();
            int exitCode = process.waitFor();
            if (exitCode == 0) {
                System.out.println("還原成功：" + backupFilePath);
                return true;
            } else {
                System.err.println("還原失敗，退出碼：" + exitCode);
                return false;
            }
        } catch (IOException | InterruptedException e) {
            Thread.currentThread().interrupt();
            System.err.println("還原異常：" + e.getMessage());
            return false;
        }
    }
    
    // 安全改進：使用 --defaults-extra-file 避免密碼出現在進程列表
    // 1. 建立臨時配置文件 [client] user=root password=xxx
    // 2. 命令加入 --defaults-extra-file=/path/to/temp.cnf
    // 3. 執行完畢立即刪除臨時文件
}

方案二：純 JDBC 程式化實現 (跨平台、無外部依賴)

適用於：嵌入式部署、無法安裝客戶端、需精細控制備份邏輯 (如排除大表、數據脫敏)。

import java.io.*;
import java.sql.*;
import java.util.*;

public class JdbcBackupRestore {
    private static final String URL = "jdbc:mysql://localhost:3306/my_database?useSSL=false&serverTimezone=UTC";
    private static final String USER = "root";
    private static final String PASS = "password";

    /**
     * 完整備份：結構 + 數據
     */
    public static void backupFull(String outputFile) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
                     new FileOutputStream(outputFile), "utf-8"))) {
            
            DatabaseMetaData meta = conn.getMetaData();
            // 1. 備份建表語句
            try (ResultSet tables = meta.getTables(null, null, "%", new String[]{"TABLE"})) {
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    writer.write("-- Table: " + tableName);
                    writer.newLine();
                    writer.write(getCreateTableSQL(conn, tableName));
                    writer.newLine();
                    writer.newLine();
                    
                    // 2. 備份數據 (分批次避免 OOM)
                    backupTableData(conn, writer, tableName, 1000);
                }
            }
            System.out.println("JDBC 備份完成：" + outputFile);
        } catch (Exception e) {
            throw new RuntimeException("備份失敗", e);
        }
    }

    private static String getCreateTableSQL(Connection conn, String tableName) throws SQLException {
        // 實際可用 SHOW CREATE TABLE 或解析 metadata 組裝
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`")) {
            if (rs.next()) return rs.getString(2) + ";";
        }
        return "";
    }

    private static void backupTableData(Connection conn, BufferedWriter writer, String tableName, int batchSize) throws Exception {
        String sql = "SELECT * FROM `" + tableName + "`";
        try (Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
            stmt.setFetchSize(batchSize); // 關鍵：流式讀取，避免全量載入記憶體
            try (ResultSet rs = stmt.executeQuery(sql)) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int colCount = rsmd.getColumnCount();
                
                List columns = new ArrayList<>();
                for (int i = 1; i <= colCount; i++) columns.add("`" + rsmd.getColumnName(i) + "`");
                String colList = String.join(", ", columns);
                
                int count = 0;
                StringBuilder insertPrefix = new StringBuilder("INSERT INTO `").append(tableName).append("` (").append(colList).append(") VALUES ");
                StringBuilder valuesBuilder = new StringBuilder();
                
                while (rs.next()) {
                    valuesBuilder.append("(");
                    for (int i = 1; i <= colCount; i++) {
                        Object val = rs.getObject(i);
                        if (val == null) valuesBuilder.append("NULL");
                        else if (val instanceof String) valuesBuilder.append("'").append(escapeSql((String) val)).append("'");
                        else valuesBuilder.append(val);
                        if (i < colCount) valuesBuilder.append(", ");
                    }
                    valuesBuilder.append("), ");
                    
                    if (++count % batchSize == 0) {
                        writer.write(insertPrefix + valuesBuilder.substring(0, valuesBuilder.length() - 2) + ";");
                        writer.newLine();
                        valuesBuilder.setLength(0);
                    }
                }
                // 寫入剩餘
                if (valuesBuilder.length() > 0) {
                    writer.write(insertPrefix + valuesBuilder.substring(0, valuesBuilder.length() - 2) + ";");
                    writer.newLine();
                }
            }
        }
    }

    private static String escapeSql(String str) {
        return str.replace("\\", "\\\\").replace("'", "\\'").replace("\n", "\\n").replace("\r", "\\r");
    }

    /**
     * 還原：直接執行 SQL 文件 (簡易版，大文件需分批)
     */
    public static void restore(String sqlFile) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(sqlFile), "utf-8"))) {
            
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            StringBuilder sql = new StringBuilder();
            String line;
            int executed = 0;
            
            while ((line = reader.readLine()) != null) {
                line = line.trim();
                if (line.isEmpty() || line.startsWith("--")) continue;
                sql.append(line).append(" ");
                if (line.endsWith(";")) {
                    stmt.addBatch(sql.toString());
                    sql.setLength(0);
                    if (++executed % 100 == 0) {
                        stmt.executeBatch();
                        conn.commit();
                    }
                }
            }
            if (sql.length() > 0) stmt.addBatch(sql.toString());
            stmt.executeBatch();
            conn.commit();
            System.out.println("JDBC 還原完成，執行語句數：" + executed);
        } catch (Exception e) {
            throw new RuntimeException("還原失敗", e);
        }
    }
}

生產環境關鍵最佳實踐

維度	建議
密碼安全	使用 `--defaults-extra-file` 指向臨時加密配置文件，執行後立即銷毀
熱備份一致性	InnoDB 必加 `--single-transaction`；MyISAM 需 `--lock-all-tables`
大數據量優化	JDBC 方案必須 `setFetchSize` 流式讀寫；mysqldump 加 `--quick`
壓縮傳輸	管道輸出到 gzip：`mysqldump ... \| gzip > backup.sql.gz`
定時任務	Spring @Scheduled / Quartz / 系統 cron 配合備份腳本
驗證機制	備份後自動校驗文件大小、表數量、關鍵表行數；定期演練還原