首先,想一下备份与还原功能是怎么做的?很简单,就是把备份成一个sql文件,然后还原这份sql文件。
MySql的备份命令:
mysqldump -h地址ip -u用户名 -p密码 数据库 (表名称) > xxx.sql
MySql的还原命令:
mysql -h地址ip -u用户名 -p密码 数据库 < xxx.sql
在java中,怎么实现是一个问题?其实也很简单,就是创建一个String,写入命令。然后执行。
第一种方法:
package com.thinkgem.jeesite.common.utils;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import com.thinkgem.jeesite.common.config.Global;
/**
* @Author: huxm
* 数据库备份与还原
* @Date: 2019/2/20 11:30
*/
public class MySqlDBUtil {
/**
* 备份数据库db
* @param root
* @param pwd
* @param dbName
* @param backPath
* @param backName
*/
public static void dbBackUp(String pathSql) throws Exception {
File fileSql = new File(pathSql);
//创建备份sql文件
if (!fileSql.exists()){
fileSql.createNewFile();
}
String os = System.getProperty("os.name"); // 系统名称
//mysqldump -hlocalhost -uroot -p123456 db > /home/back.sql
StringBuffer sb = new StringBuffer();
if (os.toLowerCase().startsWith("win")) {
sb.append("mysqldump");
} else if (os.toLowerCase().startsWith("linux")) {
sb.append("/usr/local/mysql/bin/mysqldump");
}
sb.append(" -h"+Global.getConfig("jdbc.ip"));
sb.append(" -u"+Global.getConfig("jdbc.username"));
sb.append(" -p"+Global.getConfig("jdbc.password"));
sb.append(" "+Global.getConfig("jdbc.dbName")+" "+Global.getConfig("jdbc.table")+" >");
sb.append(pathSql);
System.out.println("cmd命令为:"+sb.toString());
Runtime runtime = Runtime.getRuntime();
System.out.println("开始备份:");
if (os.toLowerCase().startsWith("win")) {
Process process = runtime.exec("cmd /c"+sb.toString()); // windows
process.waitFor();
} else if (os.toLowerCase().startsWith("linux")) {
Process process = runtime.exec("/bin/sh -c "+sb.toString()); // linux
process.waitFor();
}
System.out.println("备份成功!");
}
/**
* 恢复数据库
* @param root
* @param pwd
* @param dbName
* @param filePath
* mysql -hlocalhost -uroot -p123456 db < /home/back.sql
*/
public static void dbRestore(String filePath){
StringBuilder sb = new StringBuilder();
String os = System.getProperty("os.name"); // 系统名称
if (os.toLowerCase().startsWith("win")) {
sb.append("mysql");
} else if (os.toLowerCase().startsWith("linux")) {
sb.append("/usr/bin/mysql");
}
sb.append(" -h"+Global.getConfig("jdbc.ip"));
sb.append(" -u"+Global.getConfig("jdbc.username"));
sb.append(" -p"+Global.getConfig("jdbc.password"));
sb.append(" "+Global.getConfig("jdbc.dbName")+" <");
sb.append(filePath);
System.out.println("cmd命令为:"+sb.toString());
Runtime runtime = Runtime.getRuntime();
System.out.println("开始还原数据");
try {
Process process = null;
if (os.toLowerCase().startsWith("win")) {
process = runtime.exec("cmd /c"+sb.toString()); // windows
} else if (os.toLowerCase().startsWith("linux")) {
process = runtime.exec("/bin/sh -c "+sb.toString()); // linux
}
InputStream is = process.getInputStream();
BufferedReader bf = new BufferedReader(new InputStreamReader(is,"utf8"));
String line = null;
while ((line=bf.readLine())!=null){
System.out.println(line);
}
is.close();
bf.close();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("还原成功!");
}
public static void main(String[] args) throws Exception {
String backName = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".sql";
MySqlDBUtil.dbBackUp("d:/"+backName);
dbRestore("d:/"+backName);
}
}
注意:Linux环境下,如果Mysql不是安装在/use/bin下面的话,需要做一个软连接
1.找到mysqldump所在的路径,linux命令:find / -name mysqldump
2.将msqldump超链接至:/usr/bin目录下,linux命令:ln -s /usr/local/mysql/bin/mysqldump /usr/bin即可
第二种方法:
/**
* mysql的备份方法
*
* @param command 命令行
* @param savePath 备份路径
* @return
*/
public boolean backup(String command, String savePath) {
boolean flag;
// 获得与当前应用程序关联的Runtime对象
Runtime r = Runtime.getRuntime();
BufferedReader br = null;
BufferedWriter bw = null;
try {
// 在单独的进程中执行指定的字符串命令
Process p = r.exec(command);
// 获得连接到进程正常输出的输入流,该输入流从该Process对象表示的进程的标准输出中获取数据
InputStream is = p.getInputStream();
// InputStreamReader是从字节流到字符流的桥梁:它读取字节,并使用指定的charset将其解码为字符
InputStreamReader isr = new InputStreamReader(is, "utf-8");
//BufferedReader从字符输入流读取文本,缓冲字符,提供字符,数组和行的高效读取
br = new BufferedReader(isr);
String s;
StringBuffer sb = new StringBuffer("");
// 组装字符串
while ((s = br.readLine()) != null) {
sb.append(s + "\r\n");
}
s = sb.toString();
// 创建文件输出流
FileOutputStream fos = new FileOutputStream(savePath);
// OutputStreamWriter是从字符流到字节流的桥梁,它使用指定的charset将写入的字符编码为字节
OutputStreamWriter osw = new OutputStreamWriter(fos, "utf-8");
// BufferedWriter将文本写入字符输出流,缓冲字符,以提供单个字符,数组和字符串的高效写入
bw = new BufferedWriter(osw);
bw.write(s);
bw.flush();
flag = true;
} catch (IOException e) {
flag = false;
e.printStackTrace();
} finally {
//由于输入输出流使用的是装饰器模式,所以在关闭流时只需要调用外层装饰类的close()方法即可,
//它会自动调用内层流的close()方法
try {
if (null != bw) {
bw.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (null != br) {
br.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return flag;
}
/**
* mysql的还原方法
*
* @param command 命令行
* @param savePath 还原路径
* @return
*/
public boolean recover(String command, String savePath) {
boolean flag;
Runtime r = Runtime.getRuntime();
BufferedReader br = null;
BufferedWriter bw = null;
try {
Process p = r.exec(command);
OutputStream os = p.getOutputStream();
FileInputStream fis = new FileInputStream(savePath);
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 + "\r\n");
}
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;
}