JAVA实现数据库备份与还原

JAVA实现数据库备份与还原[MYSQL为例]

数据库数据的备份与还原最好是使用数据库自带的客户端软件来实现,JAVA可以通过调用mysqldump、mysql客户端软件分别进行mysql数据库的备份还原,当然前提是JAVA服务器必须安装mysql客户端软件,但是也可以通过java telnet登陆数据库服务器实现备份,关于telnet的相关资料请Google.

MYSQL支持的备份方式：

1.使用SELECT INTO ...OUTFILE，例如

SQL代码

SELECT * INTO OUTFILE "D:\\test.sql" fields terminatedby ',' lines terminatedby '' from category ;

生成的文件会存放在数据库服务器上面

SELECT INTO只备份了数据库表中的数据:如下为category备份后的文件内容

XML/HTML代码

1,ELECTRONICS,\N

2,TELEVISIONS,1

3,TUBE,2

4,LCD,2

5,PLASMA,2

6,PORTABLE ELECTRONICS,1

7,MP3 PLAYERS,6

8,FLASH,7

9,CD PLAYERS,6

10,2 WAY RADIOS,6

2.使用BACKUP TABLE，例如

SQL代码

BACKUPTABLE test_tableto 'D:\backup';

BACKUP只针对MyISAM表格，且在MYSQL 5.1的参考手册中“注释：本语句不理想”; BACKUP语句效果为拷贝数据库表中的.frm文件到数据库服务器目标目录

3.使用mysqldump程序或mysqlhotcopy脚本

MYSQL 5.1的参考手册中关于mysqldump的说明：“可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。“

JAVA可以运用Process类的exec(String str)方法调用系统命令，所以需要在服务器上安装mysqldump程序已完成备份，可以对远程的数据库服务器进行备份(配置hostname参数),备份语句具体参数参考MYSQL参考手册

MYSQL备份命令行：

SQL代码

mysqldump -hhostname -uusername -ppassword databasename > ‘backupfile’

JAVA代码如下：

Java代码

public final String BACKUP_COMMAND ="mysqldump";

public final String ENCODING ="utf8";

public boolean backup(String file) {

boolean isSuccess =true;

try {

Runtime rt = Runtime.getRuntime();

String backupStr =this.getBackupStr();

T(backupStr);

Process process = rt.exec(backupStr);

BufferedReader br =new BufferedReader(new InputStreamReader(process.getInputStream(), ENCODING));

String inStr ="";

StringBuffer sb =new StringBuffer("");

while ((inStr = br.readLine()) !=null) {

sb.append(inStr).append("");

}

String outStr = sb.toString();

OutputStreamWriter writer

FileOutputStream(file), ENCODING);

writer.write(outStr);

writer.flush();

br.close(); writer.close(); }catch (Exception e) { =new OutputStreamWriter(new

e.printStackTrace();

isSuccess =false;

}

return isSuccess;

}

private String getBackupStr() {

String backupStr = BACKUP_COMMAND +" -u" + db.getUserName() +" -p" + db.getPassword() +" -h" + db.getHost() +" --set-charset=" + db.getEncoding() +"

" + db.getName();

return backupStr;

}

MYSQL还原命令行：

SQL代码

mysql -hhostname -uusername -ppassword databasename < ‘backupfile’ JAVA代码如下：

Java代码

public final String REVERT_COMMAND ="mysql";

public boolean revert(String file) {

try {

Runtime rt = Runtime.getRuntime();

String revertStr =this.getRevertStr();

Process process = rt.exec(revertStr);

String inStr;

StringBuffer sb =new StringBuffer("");

BufferedReader br =new BufferedReader(new InputStreamReader( new FileInputStream(file), ENCODING));

while ((inStr = br.readLine()) !=null) {

sb.append(inStr).append("");

}

String outStr = sb.toString();

OutputStreamWriter writer

OutputStreamWriter(process.getOutputStream(), ENCODING);

writer.write(outStr);

writer.flush();

br.close();

writer.close();

}catch (Exception e) {

e.printStackTrace();

return false;

}

return true;

}

private String getRevertStr() {

String backupStr = REVERT_COMMAND +" -u" + db.getUserName() +" -p" + db.getPassword() +" -h" + db.getHost() +" " + db.getName(); =new

return backupStr;

}

mysqlhotcopy程序,MYSQL 5.1的参考手册的说明："如果你在服务器上进行备份，并且表均为MyISAM表,它运行在Unix和NetWare中"

实现原理和mysqldump一样，这里暂不详细说明

附上mysqldump备份后的文件的内容：DROP TAB

XML/HTML代码

CREATE TABLE `category` (

`category_id` int(11) NOT NULL auto_increment,

`name` varchar(20) NOT NULL,

`parent` int(11) default NULL,

PRIMARY KEY (`category_id`)

)ENGINE=MyISAM DEFAULTCHARSET=latin1 ROW_FORMAT=REDUNDANT;

--

-- Dumping data for table `category`

--

/*!40000 ALTER TABLE `category` DISABLE KEYS */;

LOCK TABLES `category` WRITE;

INSERT INTO `category` VALUES (1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

UNLOCK TABLES;

/*!40000 ALTER TABLE `category` ENABLE KEYS */;

数据库的备份import java.io.IOException;

import javax.swing.JOptionPane;

public class JavaOutputOracle {

public static void main(String[] args) {

Runtime rt = Runtime.getRuntime();

Process processexp = null;// 创建一个本机进程

String exp = "exp scott/tiger@oracle file=d:/gengen.dmp";

try {

processexp = rt.exec(exp);

if (processexp.waitFor() != 0) {

JOptionPane.showMessageDialog(null, "数据库备份失败");

} else {

JOptionPane.showMessageDialog(null, "数据库备份成功");

}

} catch (IOException e) {

e.printStackTrace();

} catch (InterruptedException e) {

e.printStackTrace();

}

}

}

数据的恢复:

import java.io.IOException;

import javax.swing.JOptionPane;

public class OracleInput{

public static void main(String[] args) {

Runtime rt = Runtime.getRuntime();

Process processexp = null;

String imp = "imp scott/tiger@oracle file=d:/gengen.dmp";

try {

processexp = rt.exec(imp);

if (processexp.waitFor() != 0) {

JOptionPane.showMessageDialog(null, "没有备份的数据库信息文件");

}

} catch (IOException e) {

e.printStackTrace();

} catch (InterruptedException e) {

e.printStackTrace();

}

}

}

做一个项目中的数据库备份与恢复(MSSQL)

数据库备份

String webtruepath=getServletContext().getRealPath(request.getServletPath());//取Servlet的真实路径 java.io.File file=new java.io.File(webtruepath);

file=file.getParentFile();

String path=file.getPath()+"\\..\\WEB-INF\\lib\\"+name+".dbbak";//name文件名

String bakSQL="backup database school to disk=? with init";//SQL语句

java.sql.PreparedStatement bak=DBS.getConnection().prepareStatement(bakSQL);

bak.setString(1,path);//path必须是绝对路径

if(!bak.execute())over="备份成功";

else over="备份失败";

bak.close();

数据库恢复

if(!DBS.close()){

over="关闭所有链接失败";

}else{

String webtruepath=getServletContext().getRealPath(request.getServletPath()); java.io.File file=new java.io.File(webtruepath);

file=file.getParentFile();

String path=file.getPath()+"\\..\\WEB-INF\\lib\\"+name;

String reSQL="restore database school from disk=? with replace";

Class.forName(DBInf.getDriverClassName());

java.sql.Connection con=DriverManager.getConnection(DBInf.getMester());

java.sql.PreparedStatement reStmt=con.prepareStatement(reSQL);

reStmt.setString(1,path);//path必须是绝对路径

if(!reStmt.execute())over="恢复成功";

else over="恢复失败";

reStmt.close();

con.close();

}

本文来源：https://www.bwwdw.com/article/khpe.html