问java 实现mySQL数据备份、还原的问题,大家帮帮忙,感激不尽
原理利用java执行exec方法调用mySQLdump命令实现备份,调用mySQL命令实现还原。
出现错误和结果如下:
1)备份时,备份文件为空文件;
2)还原时,出现异常如下:java.io.IOException: 管道正在被关闭。
at java.io.FileOutputStream.writeBytes(Native Method)
at java.io.FileOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.write(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.writeBytes(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.implWrite(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.Writer.write(Unknown Source)
at file.test.DataImport.main(DataImport.java:53)
备份代码如下:
/*注:要将mysql的bin目录加入到环境变量Path中
*将MySql中的数据库导出到文件中 备份
*/
import java.io.*;
import java.lang.*;
public class DataExport {
public static void main(String[] args) {
// 数据库导出
String user = "root"; // 数据库帐号
String password = "root"; // 登陆密码
String database = "eshop"; // 需要备份的数据库名
String filepath = "e:\\EShop.sql"; // 备份的路径地址
//注意mysqldump是调用mysql数据库的一个组件,在未在系统变量中声明的话,要在这里写mysqldump的完整路径
String stmt1 = "mysqldump -u" + user + " -p" + password +" "+database +" > "+ filepath;
//" --result-file=" + filepath;
/*
* String mysql="mysqldump test -u root -proot
* --result-file=d:\\test.sql";
*/
try {
//Process Proexec=Runtime.getRuntime().exec("cmd /c mysqldump -h localhost -u root -p root -b eshop > E:\\eshop.sql");
Process Proexec=Runtime.getRuntime().exec("cmd /c"+stmt1); // 设置导出编码为utf8。这里必须是utf8
// 把进程执行中的控制台输出信息写入.sql文件,即生成了备份文件。注:如果不对控制台信息进行读出,则会导致进程堵塞无法运行
InputStream in = Proexec.getInputStream();// 控制台的输出信息作为输入流
InputStreamReader inputStr= new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8,否则从流中读入的是乱码
String inStr;
StringBuffer sb = new StringBuffer("");
String outStr;
// 组合控制台输出信息字符串
BufferedReader br = new BufferedReader(inputStr);
while ((inStr = br.readLine()) != null) {
sb.append(inStr + "\r\n");
}
outStr = sb.toString();
System.out.println("inStr0"+outStr);
// 要用来做导入用的sql目标文件:
FileOutputStream fout = new FileOutputStream(filepath);
OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");
writer.write(outStr);
// 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免
writer.flush();
// 别忘记关闭输入输出流
in.close();
inputStr.close();
br.close();
writer.close();
fout.close();
System.out.println("数据已导出到文件" + filepath + "中");
}
catch (IOException e) {
e.printStackTrace();
}
}
}
结果是:outStr 为空,即备份文件Eshop.sql为空,出问题在Process Proexec=Runtime.getRuntime().exec("cmd /c"+stmt1); stmt1命令有问题
还原代码如下:
/*将数据从磁盘上的文本文件还原到MySql中的数据库
*/
import java.io.*;
import java.lang.*;
/* u
* 还原MySql数据库
* */
public class DataImport {
public static void main(String[] args) {
String filepath = "d:\\EShop.sql";
// 备份的路径地址
String stmt1 = "mysqladmin -uroot -padmin create EShop";
String stmt2 = "mysqladmin -uroot -padmin eshop < " + filepath;
//String[] cmd = { "cmd", "/c", stmt2 };
try {
Process proexec=Runtime.getRuntime().exec("cmd /c "+stmt2);
//Runtime.getRuntime().exec(cmd);
OutputStream out = proexec.getOutputStream();//控制台的输入信息作为输出流
String inStr;
StringBuffer sb = new StringBuffer("");
String outStr;
BufferedReader br = new BufferedReader(new InputStreamReader(
new FileInputStream(filepath), "utf8"));
while ((inStr = br.readLine()) != null) {
sb.append(inStr + "\r\n");
}
outStr = sb.toString();
OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
System.out.println("outStr");
writer.write(outStr);
// 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免
writer.flush();
// 别忘记关闭输入输出流
out.close();
br.close();
writer.close();
System.out.println("数据已从 " + filepath + " 导入到数据库中");
} catch (IOException e) {
e.printStackTrace();
}
}
}
结果是:出现异常java.io.IOException: 管道正在被关闭。
at java.io.FileOutputStream.writeBytes(Native Method)
at java.io.FileOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.write(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.writeBytes(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.implWrite(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.Writer.write(Unknown Source)
at file.test.DataImport.main(DataImport.java:53)
原理利用java执行exec方法调用mySQLdump命令实现备份,调用mySQL命令实现还原。
出现错误和结果如下:
1)备份时,备份文件为空文件;
2)还原时,出现异常如下:java.io.IOException: 管道正在被关闭。
at java.io.FileOutputStream.writeBytes(Native Method)
at java.io.FileOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.write(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.writeBytes(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.implWrite(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.Writer.write(Unknown Source)
at file.test.DataImport.main(DataImport.java:53)
备份代码如下:
/*注:要将mysql的bin目录加入到环境变量Path中
*将MySql中的数据库导出到文件中 备份
*/
import java.io.*;
import java.lang.*;
public class DataExport {
public static void main(String[] args) {
// 数据库导出
String user = "root"; // 数据库帐号
String password = "root"; // 登陆密码
String database = "eshop"; // 需要备份的数据库名
String filepath = "e:\\EShop.sql"; // 备份的路径地址
//注意mysqldump是调用mysql数据库的一个组件,在未在系统变量中声明的话,要在这里写mysqldump的完整路径
String stmt1 = "mysqldump -u" + user + " -p" + password +" "+database +" > "+ filepath;
//" --result-file=" + filepath;
/*
* String mysql="mysqldump test -u root -proot
* --result-file=d:\\test.sql";
*/
try {
//Process Proexec=Runtime.getRuntime().exec("cmd /c mysqldump -h localhost -u root -p root -b eshop > E:\\eshop.sql");
Process Proexec=Runtime.getRuntime().exec("cmd /c"+stmt1); // 设置导出编码为utf8。这里必须是utf8
// 把进程执行中的控制台输出信息写入.sql文件,即生成了备份文件。注:如果不对控制台信息进行读出,则会导致进程堵塞无法运行
InputStream in = Proexec.getInputStream();// 控制台的输出信息作为输入流
InputStreamReader inputStr= new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8,否则从流中读入的是乱码
String inStr;
StringBuffer sb = new StringBuffer("");
String outStr;
// 组合控制台输出信息字符串
BufferedReader br = new BufferedReader(inputStr);
while ((inStr = br.readLine()) != null) {
sb.append(inStr + "\r\n");
}
outStr = sb.toString();
System.out.println("inStr0"+outStr);
// 要用来做导入用的sql目标文件:
FileOutputStream fout = new FileOutputStream(filepath);
OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");
writer.write(outStr);
// 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免
writer.flush();
// 别忘记关闭输入输出流
in.close();
inputStr.close();
br.close();
writer.close();
fout.close();
System.out.println("数据已导出到文件" + filepath + "中");
}
catch (IOException e) {
e.printStackTrace();
}
}
}
结果是:outStr 为空,即备份文件Eshop.sql为空,出问题在Process Proexec=Runtime.getRuntime().exec("cmd /c"+stmt1); stmt1命令有问题
还原代码如下:
/*将数据从磁盘上的文本文件还原到MySql中的数据库
*/
import java.io.*;
import java.lang.*;
/* u
* 还原MySql数据库
* */
public class DataImport {
public static void main(String[] args) {
String filepath = "d:\\EShop.sql";
// 备份的路径地址
String stmt1 = "mysqladmin -uroot -padmin create EShop";
String stmt2 = "mysqladmin -uroot -padmin eshop < " + filepath;
//String[] cmd = { "cmd", "/c", stmt2 };
try {
Process proexec=Runtime.getRuntime().exec("cmd /c "+stmt2);
//Runtime.getRuntime().exec(cmd);
OutputStream out = proexec.getOutputStream();//控制台的输入信息作为输出流
String inStr;
StringBuffer sb = new StringBuffer("");
String outStr;
BufferedReader br = new BufferedReader(new InputStreamReader(
new FileInputStream(filepath), "utf8"));
while ((inStr = br.readLine()) != null) {
sb.append(inStr + "\r\n");
}
outStr = sb.toString();
OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
System.out.println("outStr");
writer.write(outStr);
// 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免
writer.flush();
// 别忘记关闭输入输出流
out.close();
br.close();
writer.close();
System.out.println("数据已从 " + filepath + " 导入到数据库中");
} catch (IOException e) {
e.printStackTrace();
}
}
}
结果是:出现异常java.io.IOException: 管道正在被关闭。
at java.io.FileOutputStream.writeBytes(Native Method)
at java.io.FileOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.write(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.writeBytes(Unknown Source)
at sun.nio.cs.StreamEncoder$CharsetSE.implWrite(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at sun.nio.cs.StreamEncoder.write(Unknown Source)
at java.io.OutputStreamWriter.write(Unknown Source)
at java.io.Writer.write(Unknown Source)
at file.test.DataImport.main(DataImport.java:53)