Java中使用mysqldump实现mysql数据库备份并将sql文件打成zip压缩包 |
您所在的位置：网站首页 › java定义包要使用什么语句执行命令 › Java中使用mysqldump实现mysql数据库备份并将sql文件打成zip压缩包 |
Java中使用mysqldump实现mysql数据库备份并将sql文件打成zip压缩包
|
场景
在Java代码中调用mysqldump命令实现对指定的mysql数据库和指定的表导出为sql文件。 并将sql文件进行压缩成zip存储备份。 mysqldump 简介mysqldump 是 MySQL 自带的逻辑备份工具。 它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。 要想使用我们需要找到mysql安装目录下的bin下的mysqldump.exe
因为没有将其添加到环境变量中，所以需要找到其所在的全路径。 注： 博客：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效果
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成功"); } }运行效果
|
【本文地址】
今日新闻 |
推荐新闻 |