场景
在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成功");
}
}
运行效果
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/136442.html