如何用Java代码实现带WHERE条件的多表MySQL备份至同一文件

我需要通过Java代码对多张表执行带WHERE条件的指定表备份，且所有备份内容需写入同一个backup.sql文件。编写了如下代码，但运行时仅第一个表的备份成功写入文件，第二个表的备份未生效。

原代码 #

try {
                String dbName = "gcc_db";
                String dbTable = "course";
                String dbTable_1 = "course_grade";
                String whereStatement = "\"" + 3 + "\"";
                String dbUser = "root";
                String dbPass = "admin";

                /***********************************************************/
                // Execute Shell Command
                /***********************************************************/
                String executeCmd1 = "";
                String executeCmd2 = "";                
                
                executeCmd1 = "C:/wamp64/bin/mysql/mysql5.7.14/bin/mysqldump -u "+dbUser+" -p"+dbPass+" "+dbName+" "+dbTable+" --where=id="+whereStatement+" --single-transaction --skip-lock-tables --no-create-info -r D:/backup.sql";
                executeCmd2 = "C:/wamp64/bin/mysql/mysql5.7.14/bin/mysqldump -u "+dbUser+" -p"+dbPass+" "+dbName+" "+dbTable_1+" --where=course_id="+whereStatement+" --single-transaction --skip-lock-tables --no-create-info >> D:/backup.sql";
                System.out.println(executeCmd1);
                System.out.println(executeCmd2);
                Process runtimeProcess =Runtime.getRuntime().exec(executeCmd1);
                int processComplete = runtimeProcess.waitFor();
                if(processComplete == 0){

                System.out.println("Backup taken successfully");
                Process runtimeProcess1 =Runtime.getRuntime().exec(executeCmd2);
                int processComplete1 = runtimeProcess.waitFor();
                if(processComplete1 == 0){
                    System.out.println("Backup taken successfully");
                } else {
                    System.out.println("Could not take mysql backup:::::::::::2");
                }

                } else {

                    System.out.println("Could not take mysql backup:::::::::::1");

                }
                } catch (IOException | InterruptedException ex) {
                    ex.printStackTrace();                   
                }

运行现象 #

仅第一个表的备份内容写入backup.sql，第二个表的备份无输出：

-- MySQL dump 10.13  Distrib 5.7.14, for Win64 (x86_64)
--
-- Host: localhost    Database: db
-- ------------------------------------------------------
-- Server version   5.7.14

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping data for table `course`
--
-- WHERE:  id=3

LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (3,'C0003','test-sample',2,'Test Sample');
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-03-06 11:17:55

问题原因 #

1. 等待逻辑错误：第二个进程等待时误用了runtimeProcess.waitFor()，而非对应进程runtimeProcess1.waitFor()，导致程序一直等待已结束的第一个进程，忽略第二个进程的执行状态。
2. Windows重定向不生效：Runtime.exec()不会自动解析shell的>>追加符号，直接执行会把>>当作参数传给mysqldump，无法实现追加写入。

修复方案 #

方案一：修正等待逻辑+通过CMD执行重定向命令 #

try {
    String dbName = "gcc_db";
    String dbTable = "course";
    String dbTable_1 = "course_grade";
    String whereStatement = "\"" + 3 + "\"";
    String dbUser = "root";
    String dbPass = "admin";

    String executeCmd1 = "";
    String executeCmd2 = "";                
    
    // 第一个命令用-r直接写入文件
    executeCmd1 = "C:/wamp64/bin/mysql/mysql5.7.14/bin/mysqldump -u " + dbUser + " -p" + dbPass + " " + dbName + " " + dbTable + " --where=id=" + whereStatement + " --single-transaction --skip-lock-tables --no-create-info -r D:/backup.sql";
    // 第二个命令通过cmd /c执行，解析>>符号实现追加
    executeCmd2 = "cmd /c \"C:/wamp64/bin/mysql/mysql5.7.14/bin/mysqldump -u " + dbUser + " -p" + dbPass + " " + dbName + " " + dbTable_1 + " --where=course_id=" + whereStatement + " --single-transaction --skip-lock-tables --no-create-info >> D:/backup.sql\"";
    
    System.out.println(executeCmd1);
    System.out.println(executeCmd2);
    
    Process runtimeProcess = Runtime.getRuntime().exec(executeCmd1);
    int processComplete = runtimeProcess.waitFor();
    if (processComplete == 0) {
        System.out.println("第一张表备份成功");
        Process runtimeProcess1 = Runtime.getRuntime().exec(executeCmd2);
        // 等待第二个进程完成
        int processComplete1 = runtimeProcess1.waitFor();
        if (processComplete1 == 0) {
            System.out.println("第二张表备份成功");
        } else {
            System.out.println("第二张表备份失败:::::::::::2");
        }
    } else {
        System.out.println("第一张表备份失败:::::::::::1");
    }
} catch (IOException | InterruptedException ex) {
    ex.printStackTrace();                   
}

方案二：通过Java IO流统一写入（跨平台更友好） #

不依赖shell重定向，直接读取mysqldump的输出流，写入到同一个文件：

import java.io.*;
import java.nio.charset.StandardCharsets;

public class MysqlBackup {
    public static void main(String[] args) {
        String dbName = "gcc_db";
        String dbTable = "course";
        String dbTable_1 = "course_grade";
        String whereStatement = "\"" + 3 + "\"";
        String dbUser = "root";
        String dbPass = "admin";
        String dumpPath = "C:/wamp64/bin/mysql/mysql5.7.14/bin/mysqldump";
        String backupFile = "D:/backup.sql";

        // 构建备份命令（移除输出相关参数）
        String[] cmd1 = {dumpPath, "-u", dbUser, "-p" + dbPass, dbName, dbTable,
                "--where=id=" + whereStatement, "--single-transaction", "--skip-lock-tables", "--no-create-info"};
        String[] cmd2 = {dumpPath, "-u", dbUser, "-p" + dbPass, dbName, dbTable_1,
                "--where=course_id=" + whereStatement, "--single-transaction", "--skip-lock-tables", "--no-create-info"};

        try (FileOutputStream fos = new FileOutputStream(backupFile);
             BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(fos, StandardCharsets.UTF_8))) {

            // 执行第一个备份并写入
            executeDump(cmd1, writer);
            // 执行第二个备份并追加
            executeDump(cmd2, writer);

            System.out.println("所有表备份完成");
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }

    private static void executeDump(String[] cmd, BufferedWriter writer) throws IOException, InterruptedException {
        Process process = Runtime.getRuntime().exec(cmd);
        // 读取命令输出并写入文件
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream(), StandardCharsets.UTF_8))) {
            String line;
            while ((line = reader.readLine()) != null) {
                writer.write(line);
                writer.newLine();
            }
        }
        // 读取错误输出用于排查问题
        try (BufferedReader errReader = new BufferedReader(new InputStreamReader(process.getErrorStream(), StandardCharsets.UTF_8))) {
            String line;
            while ((line = errReader.readLine()) != null) {
                System.err.println(line);
            }
        }
        int exitCode = process.waitFor();
        if (exitCode != 0) {
            throw new RuntimeException("备份命令执行失败，退出码：" + exitCode);
        }
    }
}

内容的提问来源于stack exchange，提问作者Java_Prog_Ideas