springboot 人大金仓 kingbase-备份还原，命令中带密码

命令带密码参考

Java代码实现国产人大金仓数据库备份还原需求-CSDN博客文章浏览阅读818次，点赞16次，收藏12次。本人在一次项目中，遇到了需要在系统管理中提供给用户备份还原系统数据的功能，由于项目特殊性，项目底层数据库使用了国产人大金仓数据库(版本V8)。由于本人也是第一次使用金仓数据库，所以在功能实现过程中，踩到了一些坑，特此记录一下，共大家参考，避免踩到和我一样的坑。所以：sys_dump -d 数据库名 -h 127.0.0.1 -p 54321 -U root -W 123456 这个指令是有问题的，于是我又打开了命令窗口，调试了指令发现指令语法错误 -W 123456。于是我get到了，便优化了我的指令。https://blog.csdn.net/weixin_44329740/article/details/135764084

1、工具类

根据驱动类型，选择执行的命令

1）DatabaseUtil 工具类

import cn.hutool.core.util.StrUtil;
import cn.hutool.system.SystemUtil;
import com.fisec.common.enums.DatabaseTypeEnum;

import java.util.HashMap;

public class DatabaseUtil {

    /**
     * 根据数据库驱动类型，获取数据库类型
     *
     * @param dbDriver
     * @return
     */
    public static DatabaseTypeEnum getDbType(String dbDriver) {
        if (dbDriver.startsWith("com.mysql")) {
            return DatabaseTypeEnum.Mysql;
        }

        if (dbDriver.startsWith("com.kingbase")) {
            return DatabaseTypeEnum.KingBase;
        }

        return DatabaseTypeEnum.Unknown;
    }

    /**
     * 获取mysql备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlBackLinuxCmd(String dbHost, String dbName, String username, String pwd) {
        return String.format("mysqldump -h%s -u%s -p%s -R %s", dbHost,
                username, pwd, dbName);
    }

    /**
     * 获取mysql备份命令
     * 切换磁盘需要添加，并奇幻盘符下命令执行
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlBackWindowsCmd(String dbHost, String dbName, String username, String pwd) {
        return String.format("d: && cd D:/Program Files/MySQL/MySQL Server 8.0/bin && .\\mysqldump.exe -h%s -u%s -p%s -R %s", dbHost,
                username, pwd, dbName);
    }

    /**
     * 获取 人大金仓数据备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlBackCmd(String dbHost, String dbName, String username, String pwd) {
        if (SystemUtil.getOsInfo().isWindows()) {
            return mysqlBackWindowsCmd(dbHost, dbName, username, pwd);
        }
        return mysqlBackLinuxCmd(dbHost, dbName, username, pwd);
    }

    /**
     * 获取mysql备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlRecoveryWindowsCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return String.format("d: && cd D:/Program Files/MySQL/MySQL Server 8.0/bin && .\\mysql.exe -h%s -u%s -p%s %s < %s", dbHost,
                username, pwd, dbName, sqlPath);
    }


    /**
     * 获取mysql备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlRecoveryLinuxCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return String.format("mysql -h%s -u%s -p%s %s < %s", dbHost,
                username, pwd, dbName, sqlPath);
    }

    /**
     * 获取 人大金仓数据备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String mysqlRecoveryCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        if (SystemUtil.getOsInfo().isWindows()) {
            return mysqlRecoveryWindowsCmd(dbHost, dbName, username, pwd, sqlPath);
        }
        return mysqlRecoveryLinuxCmd(dbHost, dbName, username, pwd, sqlPath);
    }

    /**
     * 获取人大金仓的备份命令
     * 注：这里路径为 windows 默认路径 ，注版本为 V008R006C008B0014 路径需根据实际情况替换
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseBackWindowsCmd(String dbHost, String dbName, String username, String pwd) {
        return StrUtil.format("cd C:/Program Files/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/ClientTools/bin && sys_dump.exe \"host={dbHost} port=54321 user={username} password={pwd} dbname={dbName}\"",
                new HashMap<>() {{
                    put("dbHost", dbHost);
                    put("dbName", dbName);
                    put("username", username);
                    put("pwd", pwd);
                }});
    }

    /**
     * 获取人大金仓的备份命令 - windows 默认路径 ，注版本为 V008R006C008B0014
     * 路径需根据实际情况替换
     * 所有 java 执行 linux命令需添加前缀 /bin/bash -c
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseBackLinuxCmd(String dbHost, String dbName, String username, String pwd) {
        return StrUtil.format("cd /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/ClientTools/bin && sys_dump \"host={dbHost} port=54321 user={username} password={pwd} dbname={dbName}\"",
                new HashMap<>() {{
                    put("dbHost", dbHost);
                    put("dbName", dbName);
                    put("username", username);
                    put("pwd", pwd);
                }});
    }

    /**
     * 获取人大金仓的恢复数据库命令
     * 注：这里路径为 windows 默认路径 ，注版本为 V008R006C008B0014 路径需根据实际情况替换
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseRecoveryWindowsCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return StrUtil.format("cd C:/Program Files/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/ClientTools/bin && ksql.exe -f {sqlPath} \"host={dbHost} port=54321 user={username} password={pwd} dbname={dbName}\"",
                new HashMap<>() {{
                    put("dbHost", dbHost);
                    put("dbName", dbName);
                    put("username", username);
                    put("pwd", pwd);
                    put("sqlPath", sqlPath);
                }});
    }

    /**
     * 获取人大金仓的备份命令 - windows 默认路径 ，注版本为 V008R006C008B0014
     * 路径需根据实际情况替换
     * 所有 java 执行 linux命令需添加前缀 /bin/bash -c
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseRecoveryLinuxCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return StrUtil.format("cd /opt/Kingbase/ES/V8/KESRealPro/V008R006C008B0014/ClientTools/bin && ksql -f {} \"host={dbHost} port=54321 user={username} password={pwd} dbname={dbName}\"",
                new HashMap<>() {{
                    put("dbHost", dbHost);
                    put("dbName", dbName);
                    put("username", username);
                    put("pwd", pwd);
                    put("sqlPath", sqlPath);
                }});
    }

    /**
     * 获取 人大金仓数据备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseBackCmd(String dbHost, String dbName, String username, String pwd) {
        if (SystemUtil.getOsInfo().isWindows()) {
            return kingBaseBackWindowsCmd(dbHost, dbName, username, pwd);
        }
        return kingBaseBackLinuxCmd(dbHost, dbName, username, pwd);
    }

    /**
     * 获取 人大金仓数据备份命令
     *
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String kingBaseRecoveryCmd(String dbHost, String dbName, String username, String pwd, String sqlPath) {
        if (SystemUtil.getOsInfo().isWindows()) {
            return kingBaseRecoveryWindowsCmd(dbHost, dbName, username, pwd, sqlPath);
        }
        return kingBaseRecoveryLinuxCmd(dbHost, dbName, username, pwd, sqlPath);
    }

    /**
     * 根据数据类型 获取执行的命令
     *
     * @param databaseTypeEnum
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String getDbBackCmd(DatabaseTypeEnum databaseTypeEnum, String dbHost, String dbName, String username, String pwd) {
        return switch (databaseTypeEnum) {
            case Mysql -> mysqlBackCmd(dbHost, dbName, username, pwd);
            case KingBase -> kingBaseBackCmd(dbHost, dbName, username, pwd);
            case Unknown -> null;
        };
    }

    /**
     * 获取数据的的备份命令
     *
     * @param dbDriver
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String getDbBackCmd(String dbDriver, String dbHost, String dbName, String username, String pwd) {
        return getDbBackCmd(getDbType(dbDriver), dbHost, dbName, username, pwd);
    }

    /**
     * 根据数据类型 获取执行的命令
     *
     * @param databaseTypeEnum
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String getDbRecoveryCmd(DatabaseTypeEnum databaseTypeEnum, String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return switch (databaseTypeEnum) {
            case Mysql -> mysqlRecoveryCmd(dbHost, dbName, username, pwd, sqlPath);
            case KingBase -> kingBaseRecoveryCmd(dbHost, dbName, username, pwd, sqlPath);
            case Unknown -> null;
        };
    }

    /**
     * 获取数据的的备份命令
     *
     * @param dbDriver
     * @param dbHost
     * @param dbName
     * @param username
     * @param pwd
     * @return
     */
    public static String getDbRecoveryCmd(String dbDriver, String dbHost, String dbName, String username, String pwd, String sqlPath) {
        return getDbRecoveryCmd(getDbType(dbDriver), dbHost, dbName, username, pwd, sqlPath);
    }

}

依赖枚举 DatabaseTypeEnum

public enum DatabaseTypeEnum {
    /**
     * mysql 数据库
     */
    Mysql("mysql"),
    /**
     * 人大金仓数据
     */
    KingBase("kingBase"),
    /**
     * 未知的数据库类型
     */
    Unknown("unknown");

    private final String value;

    DatabaseTypeEnum(String value) {
        this.value = value;
    }
}

2、备份调用

参数获取

@Value("${spring.datasource.driver-class-name}")
private String dbDriver;
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String dbUsername;
@Value("${spring.datasource.password}")
 private String pwd;

执行内容
String dbHost = dbUrl.split("//")[1].split(":")[0];
        String dbName = dbUrl.split("/")[3];
        // 去除问号及其后面的参数
        if (dbName.contains("?")) {
            dbName = dbName.split("\\?")[0];
        }

        String command = DatabaseUtil.getDbBackCmd(dbDriver, dbHost, dbName, dbUsername, pwd);
        log.info("数据备份命令：{}", command);

        // 创建进程构建器
        ProcessBuilder processBuilder = new ProcessBuilder();
        // window 用 cmd、linux 用 bash
        if (SystemUtil.getOsInfo().isWindows()) {
            processBuilder.command("cmd.exe", "/C", command); // 设置工作目录
        } else {
            processBuilder.command("/bin/bash", "-c", command); // 设置工作目录
        }
        // 启动进程
        Process process = processBuilder.start();

        InputStream errorStream = process.getErrorStream();
        InputStreamReader isr = new InputStreamReader(errorStream, "GBK");

        BufferedReader br = new BufferedReader(isr);
        String line;
        while ((line = br.readLine()) != null) {
            log.info(line);
        }
        br.close();
        isr.close();
        errorStream.close();
        if (process.waitFor() != 0) {
            log.error("数据备份失败");
            throw new Exception("备份失败");
        }

3、数据库恢复调用

恢复失败了，数据sql 脚本被执行，但是恢复失败了

String dbHost = dbUrl.split("//")[1].split(":")[0];
            String dbName = dbUrl.split("/")[3];
            // 去除问号及其后面的参数
            if (dbName.contains("?")) {
                dbName = dbName.split("\\?")[0];
            }

            assert file != null;
            String command = DatabaseUtil.getDbRecoveryCmd(dbDriver, dbHost, dbName, dbUsername, pwd, file.getAbsolutePath());

            // 创建进程构建器
            ProcessBuilder processBuilder = new ProcessBuilder();
            // window 用 cmd、linux 用 bash
            if (SystemUtil.getOsInfo().isWindows()) {
                processBuilder.command("cmd.exe", "/C", command); // 设置工作目录
            } else {
                processBuilder.command("/bin/bash", "-c", command); // 设置工作目录
            }
            // 启动进程
            Process process = processBuilder.start();

            InputStream errorStream = process.getErrorStream();
            InputStreamReader isr = new InputStreamReader(errorStream, StandardCharsets.UTF_8);

            BufferedReader br = new BufferedReader(isr);
            String line;
            while ((line = br.readLine()) != null) {
                log.info(line);
            }
            br.close();
            isr.close();
            errorStream.close();

            if (process.waitFor() != 0) {
                return CommonResult.fail("数据恢复失败");
            }