java使用command-exec对mysql进行备份与恢复

使用java的Runtime.getRuntime().exec来进行mysql的备份与恢复

该方法百度有很多实现方法。但是process.waitFor()阻塞卡死问题我没有找到很好的解决方法。

使用Apache的command-exec来进行mysql的备份与恢复。(本人使用的该方法)

需要导入command-exec的jar包 command-exec.jar
maven导入配置

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-exec -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-exec</artifactId>
    <version>1.3</version>
</dependency>

command-exec是对Runtime.getRuntime().exec进行了封装。

备份

/**
     * 导出sql文件
     * 采用Apache的command-exec来执行命令
     * @param host ip地址
     * @param port 端口
     * @param userName 账号
     * @param password 密码
     * @param savePath sql文件保存路径
     * @param fileName 文件名
     * @param databaseName 要备份的数据库名
     * @return
     */
    public static void exportDbSql(String host,String port, String userName, String password, String savePath, String fileName, String databaseName) throws ApiException {

        logger.debug("mysqlDumpApi.exportDbSql,param:host={},port={},userName={},password={},savePath={},fileName={},databaseName={}",
                host,port, userName, password, savePath, fileName, databaseName);

        File saveFile = new File(savePath);
        if (!saveFile.exists()) {// 如果目录不存在
            saveFile.mkdirs();// 创建文件夹
        }
        if(!savePath.endsWith(File.separator)){
            savePath = savePath + File.separator;
        }

        FileOutputStream fos = null;
        ByteArrayOutputStream errorStream = null;
        try {
            // 拼接命令
            CommandLine command = new CommandLine("mysqldump");
            command.addArgument("-h"+host);
            command.addArgument("-P"+port);
            command.addArgument("-u"+userName);
            command.addArgument("-p"+password);
            command.addArgument(databaseName);

            logger.debug("mysqlDumpApi.exportDbSql,command:{}",command.toString());

            DefaultExecutor executor=new DefaultExecutor();
            //导出到文件
            fos=new FileOutputStream(savePath+fileName);
            //命令执行的错误信息
            errorStream = new ByteArrayOutputStream();
            //fos为标准输出流，errorStream为错误输出流，这里一定要传这两个参数，否则最终生成的文件中会包含警告信息，导致恢复时无法恢复
            executor.setStreamHandler(new PumpStreamHandler(fos, errorStream));

            DefaultExecuteResultHandler resultHandler = new DefaultExecuteResultHandler();
            executor.execute(command,resultHandler);

            //等待子线程完成
            resultHandler.waitFor();
            //获取执行的返回值
            int exitValue=resultHandler.getExitValue();
            String errorStreamStr=errorStream.toString();
            /*
                判断命令执行是否成功。
                exitValue==0表示成功，如果错误信息中存在ERROR也认为失败
             */
            if (exitValue!=0 || errorStreamStr.contains("ERROR")) {
                logger.error("mysqlDumpApi.exportDbSql executor fail.errorMsg:{}",errorStreamStr);
                throw new ApiException("mysqlDumpApi.exportDbSql executor fail errorMsg:"+errorStreamStr);
            }

        }catch (Exception e) {
            logger.error("mysqlDumpApi.exportDbSql fail.",e);
            throw new ApiException("mysqlDumpApi.exportDbSql fail",e);
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
                if (errorStream != null) {
                    errorStream.close();
                }
            } catch (IOException e) {
                logger.error("mysqlDumpApi.exportDbSql fail.",e);
                throw new ApiException("mysqlDumpApi.exportDbSql fail",e);
            }
        }
    }

恢复

/**
     * 恢复数据
     * @param host
     * @param port
     * @param userName
     * @param password
     * @param sqlPath
     * @param databaseName
     * @return
     */
    public static void restoreDbBySql(String host,String port,String userName,String password,String sqlPath,String databaseName) throws ApiException {

        logger.debug("mysqlDumpApi.restoreDbBySql,param:host={},port={},userName={},password={},sqlPath={},databaseName={}",
                host,port, userName, password, sqlPath, databaseName);


        ByteArrayOutputStream outputStream = null;
        ByteArrayOutputStream errorStream = null;
        InputStream inputStream = null;

        try {

            CommandLine command = new CommandLine("mysql");
            command.addArgument("-h"+host);
            command.addArgument("-P"+port);
            command.addArgument("-u"+userName);
            command.addArgument("-p"+password);
            command.addArgument(databaseName);

            logger.debug("mysqlDumpApi.restoreDbBySql,command:{}",command.toString());

            DefaultExecutor executor=new DefaultExecutor();
            outputStream = new ByteArrayOutputStream();
            errorStream = new ByteArrayOutputStream();
            //sql文件路径
            inputStream = new FileInputStream(sqlPath);

            PumpStreamHandler psHandler=new PumpStreamHandler(outputStream,errorStream,inputStream);
            executor.setStreamHandler(psHandler);

            DefaultExecuteResultHandler resultHandler = new DefaultExecuteResultHandler();
            executor.execute(command,resultHandler);

            resultHandler.waitFor();
            int exitValue = resultHandler.getExitValue();
            String errorStreamStr=errorStream.toString();
            /*
                判断命令执行是否成功。
                exitValue==0表示成功，如果错误信息中存在ERROR也认为失败
             */
            if (exitValue!=0 || errorStreamStr.contains("ERROR")) {
                logger.error("mysqlDumpApi.restoreDbBySql executor fail.errorMsg:{}",errorStreamStr);
                throw new ApiException("mysqlDumpApi.restoreDbBySql executor fail errorMsg:"+errorStreamStr);
            }
        } catch (Exception e) {
            logger.error("mysqlDumpApi.restoreDbBySql fail.",e);
            throw new ApiException("mysqlDumpApi.restoreDbBySql fail",e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (errorStream != null) {
                    errorStream.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                logger.error("mysqlDumpApi.restoreDbBySql fail.",e);
                throw new ApiException("mysqlDumpApi.restoreDbBySql fail",e);
            }
        }
    }

如果有其他更好的方法，欢迎评论，供大家参考