Java实现MySQL备份与恢复_java txt恢复mysql数据库-CSDN博客

本文链接：https://blog.csdn.net/qq_42695907/article/details/104451475

本文介绍了一种使用Java代码实现MySQL数据库备份与恢复的方法，包括如何通过Java调用mysqldump和mysql命令进行数据库导出和导入，同时讨论了在5.6及以上版本中遇到的GTID相关问题及解决策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.io.*;

public class MySQLBackup {

    private String ip;    //MySQL数据库所在服务器地址IP
    private String userName;    //数据库连接用户名
    private String password;    //数据库连接密码
    private String databaseName;    //导出数据库名称

    /**
     * 备份
     *
     * @param filePath 数据库备份路径
     * @param fileName 导出文件名
     * @return true 成功 false 失败
     */
    public Boolean backup(String filePath, String fileName) {
        File file = new File(filePath);
        if (!file.exists())
            file.mkdirs();
        PrintWriter printWriter = null;
        BufferedReader bufferedReader = null;
        try {
            printWriter = new PrintWriter(new OutputStreamWriter(
                    new FileOutputStream(filePath + fileName), "utf8"));
            Process process = Runtime.getRuntime().exec("mysqldump -h" +
                    ip + " -u" + userName + " -p" + password + " --default-character-set=utf8 " + databaseName);
            InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
            bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                printWriter.println(line);
            }
            printWriter.flush();
            if (process.waitFor() == 0) {//0 表示线程正常终止。
                return true;
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            try {
                if (bufferedReader != null) {
                    bufferedReader.close();
                }
                if (printWriter != null) {
                    printWriter.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    /**
     * 数据库恢复
     *
     * @param filePath 备份文件位置
     * @return
     */
    public Boolean recover(String filePath) {
        boolean flag;
        BufferedReader br = null;
        BufferedWriter bw = null;
        try {
            Process p = Runtime.getRuntime().exec("mysql -h" + ip + " -u" + userName + " -p" + password + " " + databaseName);
            OutputStream os = p.getOutputStream();
            FileInputStream fis = new FileInputStream(filePath);
            InputStreamReader isr = new InputStreamReader(fis, "utf-8");
            br = new BufferedReader(isr);
            String s;
            StringBuffer sb = new StringBuffer("");
            while ((s = br.readLine()) != null) {
                sb.append(s + System.lineSeparator());
            }
            s = sb.toString();
            OutputStreamWriter osw = new OutputStreamWriter(os, "utf-8");
            bw = new BufferedWriter(osw);
            bw.write(s);
            bw.flush();
            flag = true;
        } catch (IOException e) {
            flag = false;
            e.printStackTrace();
        } finally {
            try {
                if (null != bw) {
                    bw.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                if (null != br) {
                    br.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }
}


import java.io.*;

public class MySQLBackup {

    private String ip;    //MySQL数据库所在服务器地址IP
    private String userName;    //数据库连接用户名
    private String password;    //数据库连接密码
    private String databaseName;    //导出数据库名称

    /**
     * Java代码实现MySQL数据库导出
     *
     * @param filePath 数据库导出文件文件路径
     * @param fileName 导出文件名
     * @return true 成功 false 失败
     */
    public Boolean exportDatabase(String filePath, String fileName) {
        try {
            File file = new File(filePath);
            if (!file.exists())
                file.mkdirs();
            Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c",
                    "mysqldump -h" + ip + " -u" + userName + " -p" + password + " --set-gtid-purged=off" +
                            " --no-autocommit --default-character-set=utf8 " +
                            databaseName + ">" + filePath + fileName});
        } catch (IOException e) {
        }

        return false;
    }

    /**
     * 数据库恢复
     *
     * @param filePath 备份文件位置
     * @return
     */
    public Boolean recover(String filePath) {
        boolean flag;
        try {
            Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c",
                    "mysql -h" + ip + " -u" + userName + " -p" + password + " " + databaseName + "<" + filePath});
            flag = true;
        } catch (IOException e) {
            flag = false;
        }

        return flag;
    }
}

备份恢复中遇到的问题

备份中出现

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs
of all transactions, even those that changed suppressed parts of the database. If you
don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump,
pass --all-databases --triggers --routines --events.

是5.6以后，加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。
官方给的：A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).
所以可能是因为在一个数据库里面唯一，但是当导入其他的库就有可能重复。所有会有一个提醒。

可以通过添加--set-gtid-purged=off 或者–gtid-mode=OFF这两个参数设置。