JAVA实现数据库备份与还原

JAVA实现数据库备份与还原[MYSQL为例]数据库数据的备份与还原最好是使用数据库自带的客户端软件来实现,JAVA可以通过调用mysqldump、mysql客户端软件分别进行mysql数据库的备份还原,当然前提是JAVA服务器必须安装mysql客户端软件,但是也可以通过java telnet登陆数据库服务器实现备份,关于telnet的相关资料请Google.MYSQL支持的备份方式：1.使用SELECT INTO ...OUTFILE，例如SQL代码1SELECT * INTO OUTFILE "D:\\test.sql" fields terminatedby ',' lines terminatedby '' from category ;生成的文件会存放在数据库服务器上面SELECT INTO只备份了数据库表中的数据:如下为category备份后的文件内容XML/HTML代码21,ELECTRONICS,\N32,TELEVISIONS,143,TUBE,254,LCD,265,PLASMA,276,PORTABLE ELECTRONICS,187,MP3 PLAYERS,698,FLASH,7109,CD PLAYERS,61110,2 WAY RADIOS,62.使用BACKUP TABLE，例如SQL代码12BACKUPTABLE 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代码13mysqldump -hhostname -uusername -ppassword databasename > ‘backupfile’JAVA代码如下：Java代码14public final String BACKUP_COMMAND ="mysqldump";15public final String ENCODING ="utf8";1617public boolean backup(String file) {18 boolean isSuccess =true;19 try {20 Runtime rt = Runtime.getRuntime();21 String backupStr =this.getBackupStr();22 T(backupStr);23 Process process = rt.exec(backupStr);2425 BufferedReader br =new BufferedReader(newInputStreamReader(process.getInputStream(), ENCODING));2627 String inStr ="";28 StringBuffer sb =new StringBuffer("");29 while ((inStr = br.readLine()) !=null) {30 sb.append(inStr).append("");31 }32 String outStr = sb.toString();3334 OutputStreamWriter writer =new OutputStreamWriter(newFileOutputStream(file), ENCODING);35 writer.write(outStr);36 writer.flush();3738 br.close();39 writer.close();40 }catch (Exception e) {41 e.printStackTrace();42 isSuccess =false;43 }44 return isSuccess;45}4647private String getBackupStr() {48 String backupStr = BACKUP_COMMAND +" -u" + db.getUserName() +" -p" +db.getPassword() +" -h" + db.getHost() +" --set-charset=" + db.getEncoding() +"" + db.getName();49 return backupStr;50}51MYSQL还原命令行：SQL代码52mysql -hhostname -uusername -ppassword databasename < ‘backupfile’JAVA代码如下：Java代码53public final String REVERT_COMMAND ="mysql";5455public boolean revert(String file) {56 try {57 Runtime rt = Runtime.getRuntime();58 String revertStr =this.getRevertStr();59 Process process = rt.exec(revertStr);6061 String inStr;62 StringBuffer sb =new StringBuffer("");63 BufferedReader br =new BufferedReader(new InputStreamReader(64 new FileInputStream(file), ENCODING));65 while ((inStr = br.readLine()) !=null) {66 sb.append(inStr).append("");67 }68 String outStr = sb.toString();6970 OutputStreamWriter writer =newOutputStreamWriter(process.getOutputStream(), ENCODING);71 writer.write(outStr);72 writer.flush();73 br.close();74 writer.close();75 }catch (Exception e) {76 e.printStackTrace();77 return false;78 }79 return true;80}8182private String getRevertStr() {83 String backupStr = REVERT_COMMAND +" -u" + db.getUserName() +" -p" +db.getPassword() +" -h" + db.getHost() +" " + db.getName();84 return backupStr;85}mysqlhotcopy程序,MYSQL 5.1的参考手册的说明："如果你在服务器上进行备份，并且表均为MyISAM表,它运行在Unix和NetWare中"实现原理和mysqldump一样，这里暂不详细说明附上mysqldump备份后的文件的内容：DROP TABXML/HTML代码86CREATE TABLE `category` (87 `category_id` int(11) NOT NULL auto_increment,88 `name` varchar(20) NOT NULL,89 `parent` int(11) default NULL,90 PRIMARY KEY (`category_id`)91)ENGINE=MyISAM DEFAULTCHARSET=latin1 ROW_FORMAT=REDUNDANT;9293--94-- Dumping data for table `category`95--969798/*!40000 ALTER TABLE `category` DISABLE KEYS */;99LOCK TABLES `category` WRITE;100INSERT INTO `category` VALUES (1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),(4,'LCD',2),(5,'PLAS MA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);101UNLOCK TABLES;102/*!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();}。