Java中使用mysqldump实现mysql数据库备份并将sql文件打成zip压缩包

Love The Way You Lie 2023-10-05 16:21 7阅读 0赞

## 场景 ##

在Java代码中调用mysqldump命令实现对指定的mysql数据库和指定的表导出为sql文件。

并将sql文件进行压缩成zip存储备份。

### mysqldump 简介 ###

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

要想使用我们需要找到mysql安装目录下的bin下的mysqldump.exe

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

因为没有将其添加到环境变量中，所以需要找到其所在的全路径。

注：

博客：  
[https://blog.csdn.net/badao\_liumang\_qizhi][https_blog.csdn.net_badao_liumang_qizhi]  
关注公众号  
霸道的程序猿  
获取编程相关电子书、教程推送与免费下载。

## 实现 ##

首先需要声明一些执行mysqldump的变量

private static String hostIP = "127.0.0.1";
        private static String userName = "root";
        private static String password = "123456";
        //sql文件存储的路径
        private static String savePath = "D:/bak";
        //sql文件存储名
        private static String fileName = "badaoBak"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".sql";
        //数据库名
        private static String databaseName = "test";
        private static final int BUFFER = 8192;
        //zip压缩包存储路径
        private static String zipPath = "D:/bak/badao.zip";

然后新建方法用语执行sql的导出

/**
         * 执行数据备份
         * @return
         */
        public static String dataBakExec()
        {
            String sqlFilePath = "";
            File saveFile = new File(savePath);
            // 如果目录不存在
            if (!saveFile.exists()) {
                // 创建文件夹
                saveFile.mkdirs();
            }
            if(!savePath.endsWith(File.separator)){
                savePath = savePath + File.separator;
            }
    
            PrintWriter printWriter = null;
            BufferedReader bufferedReader = null;
            try {
                printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));
                sqlFilePath= savePath + fileName;
                //导出指定数据库指定表的结构和数据
                Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book ");
                //导出指定数据库指定表的结构
                //Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName + " book -d");
                //导出指定数据库指定表符合条件的结构和数据
                //Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book "+" --where=\" price> 100" + "\" ");
                InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
                bufferedReader = new BufferedReader(inputStreamReader);
                String line;
                while((line = bufferedReader.readLine())!= null){
                    printWriter.println(line);
                }
                printWriter.flush();
                //0 表示线程正常终止。
                if(process.waitFor() == 0){
                    System.out.println("备份数据成功");
    
                }
            }catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (bufferedReader != null) {
                        bufferedReader.close();
                    }
                    if (printWriter != null) {
                        printWriter.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return  sqlFilePath;
        }

注意把这里的mysqldump的路径改为自己的路径。

执行的命令如果不加具体的数据库则导出所有的表，数据库后面加表明则是导出具体的表。

并且还可以选择导出表的结构和数据以及符合要求的表数据。

具体自行搜索musqldump命令。

备份sql效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

sql备份成功后将其路径返回，然后再新建一个生成zip压缩包的方法

/**
         * 压缩sql文件为zip
         * @param filePath sql文件路径
         * @param zipPath  要生成的zip压缩包路径
         */
        public static void zipFile(String filePath,String zipPath) {
            ZipOutputStream out = null;
            try {
                out = new ZipOutputStream(new FileOutputStream(zipPath));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            //得到文件列表信息
            File file = new File(filePath);
            // 压缩zip包
            try {
                if (!file.exists()) {
                    return;
                }
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
                try {
    
                    ZipEntry entry = new ZipEntry(file.getName());
                    out.putNextEntry(entry);
                    int count;
                    byte data[] = new byte[BUFFER];
                    while ((count = bis.read(data, 0, BUFFER)) != -1) {
                        out.write(data, 0, count);
                    }
    
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }finally {
                    out.closeEntry();
                    bis.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("生成zip成功");
        }

然后完整的main方法示例代码

package com.badao.mysqlbak;
    
    import java.io.*;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipOutputStream;
    
    public class MysqlBakMain {
    
        private static String hostIP = "127.0.0.1";
        private static String userName = "root";
        private static String password = "123456";
        //sql文件存储的路径
        private static String savePath = "D:/bak";
        //sql文件存储名
        private static String fileName = "badaoBak"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".sql";
        //数据库名
        private static String databaseName = "test";
        private static final int BUFFER = 8192;
        //zip压缩包存储路径
        private static String zipPath = "D:/bak/badao.zip";
    
    
        public static void main(String[] args) {
            String sqlFilePath = dataBakExec();
            System.out.println("备份的sql文件保存路径为:"+sqlFilePath);
            zipFile(sqlFilePath,zipPath);
        }
    
        /**
         * 执行数据备份
         * @return
         */
        public static String dataBakExec()
        {
            String sqlFilePath = "";
            File saveFile = new File(savePath);
            // 如果目录不存在
            if (!saveFile.exists()) {
                // 创建文件夹
                saveFile.mkdirs();
            }
            if(!savePath.endsWith(File.separator)){
                savePath = savePath + File.separator;
            }
    
            PrintWriter printWriter = null;
            BufferedReader bufferedReader = null;
            try {
                printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));
                sqlFilePath= savePath + fileName;
                //导出指定数据库指定表的结构和数据
                Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book ");
                //导出指定数据库指定表的结构
                //Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName + " book -d");
                //导出指定数据库指定表符合条件的结构和数据
                //Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book "+" --where=\" price> 100" + "\" ");
                InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
                bufferedReader = new BufferedReader(inputStreamReader);
                String line;
                while((line = bufferedReader.readLine())!= null){
                    printWriter.println(line);
                }
                printWriter.flush();
                //0 表示线程正常终止。
                if(process.waitFor() == 0){
                    System.out.println("备份数据成功");
    
                }
            }catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (bufferedReader != null) {
                        bufferedReader.close();
                    }
                    if (printWriter != null) {
                        printWriter.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return  sqlFilePath;
        }
    
        /**
         * 压缩sql文件为zip
         * @param filePath sql文件路径
         * @param zipPath  要生成的zip压缩包路径
         */
        public static void zipFile(String filePath,String zipPath) {
            ZipOutputStream out = null;
            try {
                out = new ZipOutputStream(new FileOutputStream(zipPath));
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            //得到文件列表信息
            File file = new File(filePath);
            // 压缩zip包
            try {
                if (!file.exists()) {
                    return;
                }
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
                try {
    
                    ZipEntry entry = new ZipEntry(file.getName());
                    out.putNextEntry(entry);
                    int count;
                    byte data[] = new byte[BUFFER];
                    while ((count = bis.read(data, 0, BUFFER)) != -1) {
                        out.write(data, 0, count);
                    }
    
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }finally {
                    out.closeEntry();
                    bis.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("生成zip成功");
        }
    
    }

运行效果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201224154029952.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201224154055759.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201224154128150.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70