java mysql 数据库备份和还原操作

港控/mmm° 2022-10-19 04:19 225阅读 0赞

package com.itenp.gen.action;
    
    import java.io.BufferedReader;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.io.OutputStreamWriter;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.apache.log4j.Logger;
    import org.springframework.context.annotation.Scope;
    import org.springframework.stereotype.Controller;
    
    import com.itenp.gen.common.Constants;
    import com.itenp.gen.common.ParameterUtil;
    import com.itenp.gen.service.i.BackUpServiceI;
    import com.itenp.gen.system.PropertiesCache;
    import com.itenp.gen.vo.BackUpVO;
    import com.itenp.gen.vo.UserVO;
    
    /** 说明:数据备份还原
     *  作者:fhadmin
     *  from www.fhadmin.cn
    */
    @SuppressWarnings("serial")
    @Scope("prototype")
    @Controller("backupAction")
    public class BackUpAction extends BaseAction {
    	
    	  private static Logger log = Logger.getLogger(BackUpAction.class);
    	
    	  private String databaseDirectory = PropertiesCache.getInstance().getDatabaseDirectory(); //数据库bin路径
    	  private String databaseName = PropertiesCache.getInstance().getDatabaseName();//数据库名
    	  private String userName = PropertiesCache.getInstance().getUserName();
    	  private String userPwd = PropertiesCache.getInstance().getUserPwd();
    	  private String copyDbCmd = PropertiesCache.getInstance().getCopyDbCmd();//数据库备份命令
    	  private String copyprocCmd = PropertiesCache.getInstance().getCopyprocCmd();//存储工程备份命令
    	  private String copyedFilePath = PropertiesCache.getInstance().getCopyedFilePath(); //备份后的文件存放位置
    	  private String restoreCmd = PropertiesCache.getInstance().getRestoreCmd();		//数据库还原命令
    	  
    	  private String msg = "";
    	  private String msg1 = "";
    	
    	
    	
    	
    	private BackUpServiceI service;
    	
    	@Resource(name="backupService")
    	public void setBackupService(BackUpServiceI service){
    		
    		this.service=service;
    	}
    	
    	@SuppressWarnings("unchecked")
    	public String list() throws Exception
    	{
    		//System.out.println("copyedFilePath"+copyedFilePath);
    		HashMap map = new HashMap();
    		
    		//得到符合条件的记录List
    		List<BackUpVO> list = (List)service.findList(map);
    	
    		request.setAttribute("list", list);
    
    		log.debug("####"+PropertiesCache.getInstance().getTemplateDirectory());
    	//	System.out.println(PropertiesCache.getInstance().getTemplateDirectory());
    		return "success";
    	}
    	
    	@SuppressWarnings("unchecked")
    	public String backup() throws Exception
    	{
    					Runtime rt = Runtime.getRuntime();
    		 
    		            // 调用 mysql 的 cmd:
    	           
    					
    					
    					/*System.out.println("数据库bin路径"+databaseDirectory);
    					System.out.println("数据库名"+databaseName);
    					System.out.println("用户名"+userName);
    					System.out.println("密码"+userPwd);
    					System.out.println("数据库备份命令"+copyDbCmd);
    					System.out.println("存储工程备份命令"+copyprocCmd);*/
    					
    					String str = databaseDirectory+"/"+copyDbCmd+" --no-defaults "+"-u"+userName+" "+"-p"+userPwd+" "+copyprocCmd+" "+"--set-charset=utf-8"+" "+databaseName;
    					
    					//str = "D:\SOFT\MySQL\MySQL Server 5.5\bin/mysqldump -uroot -proot -R --set-charset=utf-8 nlnk";
    					
    					//System.out.println(str);
    					
    		            // Process child = rt.exec(databaseDirectory+"/"+copyDbCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+copyprocCmd+" "+"--set-charset=utf-8"+" "+databaseName);// 设置导出编码为utf8。这里必须是utf8
    					Process child = rt.exec(str);// 设置导出编码为utf8。这里必须是utf8
    					
    		             //调用mysql的cmd:备份某个表
    		            // Process childtable = rt
                        // .exec("D:/db/mysql/mysql-5.0.45-win32/bin/mysqldump -uroot -pmysql  --set-charset=utf8 ibtts t_a_dbbak");// 设置导出编码为utf8。这里必须是utf8
    					//  Process childtable = rt
                        // .exec(databaseDirectory+"/"+copyDbCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+"--set-charset=utf8"+" "+databaseName+" "+"t_a_dbbak");// 设置导出编码为utf8。这里必须是utf8
    		            
    		             // 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行
    		             InputStream in = child.getInputStream();// 控制台的输出信息作为输入流
    		          
    		             InputStreamReader xx = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码
    		             
    		             String inStr;
    		             StringBuffer sb = new StringBuffer("");
    		             String outStr;
    		             // 组合控制台输出信息字符串
    		             BufferedReader br = new BufferedReader(xx);
    		             
    		            // System.out.println("------------"+br.readLine());
    		             
    		            while ((inStr = br.readLine()) != null) {
    		                 sb.append(inStr + "\r\n");
    		             }
    		             outStr = sb.toString();
    		             
    		             //判断是否保存成功
    		             if(outStr != null && !outStr.trim().equals("")){
    		            	 msg="ok";
    		             }
    		             
    		             
    		             //System.out.println("-----"+outStr);
    		             	//创建文件名称
    		             SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
    		             String today=format.format(new Date());
    		             
    		         	//String targetDirectory = scontext.getRealPath("/WEB-INF/DBbackup");
    		         		//	System.out.println(targetDirectory);
    		             // 要用来做导入用的sql目标文件：D:/ibtts.sql
    		           
    		             FileOutputStream fout = new FileOutputStream(
    		            		 copyedFilePath+"/"+today+"ibtts.sql" );
    		             OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");
    		             writer.write(outStr);
    		            // 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
    		             writer.flush();
    		 
    		             // 别忘记关闭输入输出流
    		             in.close();
    		             xx.close();
    		             br.close();
    		             writer.close();
    		             fout.close();
    		 
    		            // System.out.println("/* Output OK! */");
    
    
    		             //数据添加到数据库
    		             HashMap map = new HashMap();
    		             
    		             map.put("db_nm", "ibtts");
    		             map.put("bk_nm", today+"ibtts.sql");
    		             map.put("bk_user",((UserVO)session.get(Constants.S_LOGIN_USER)).getUser_id());
    		             
    		             service.create(map);
    		             
    		           //  copytable();
    		             
    		             return list();
    		     }
    	
    	/**
    	 * 数据的恢复
    	 */
    	@SuppressWarnings("unchecked")
    	public String load() throws Exception
    	{		
    		copytable();
    		
    		//得到页面所有参数
    		Map<String, String> map = ParameterUtil.getStringMap(request,session);
    		//String targetDirectory = scontext.getRealPath("/WEB-INF/DBbackup");
    		
    		//获取id后进行查询
    		BackUpVO vo=(BackUpVO) service.findById(map);
    		
    		String fPath=copyedFilePath+"/"+vo.getBk_nm();
    		
    		System.out.println(fPath);
    		
    		//map.put("Id", vo.getId());
    
    		//String fPath = "D:/ibtts.sql";
    		Runtime rt = Runtime.getRuntime();
    
    		// 调用 mysql 的 cmd:
    		//Process child = rt.exec("D:/db/mysql/mysql-5.0.45-win32/bin/mysql -uroot -pmysql ibtts");
    		Process child = rt.exec(databaseDirectory+"/"+restoreCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+databaseName);
    		
    		//System.out.println(databaseDirectory+"/"+restoreCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+databaseName);
    		
    		java.io.OutputStream out =child.getOutputStream();//控制台的输入信息作为输出流
    		String inStr;
    		StringBuffer sb = new StringBuffer("");
    		String outStr;
    		BufferedReader br = new BufferedReader(new InputStreamReader(
    		new FileInputStream(fPath), "utf8"));
    		while ((inStr = br.readLine()) != null) {
    		sb.append(inStr + "\r\n");
    		}
    		outStr = sb.toString();
    
    		OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
    		writer.write(outStr);
    		// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
    		writer.flush();
    		// 别忘记关闭输入输出流
    		out.close();
    		br.close();
    		writer.close();
    		
    		//获取还原用户
    		map.put("restore_user", ((UserVO)session.get(Constants.S_LOGIN_USER)).getUser_id());
    		
    		//service.modify(map);
    		
    		restoretable(map);			//---------------
    		
    		//System.out.println(map);
    
    		//System.out.println("恢复成功");
    		
    		request.getSession().setAttribute("dbmap", map);
    		
    		msg1 = "tiaozhuan";
    		
    		return "success";
    		}
    	
    	
    	//对特定表的处理
    	public void copytable() throws Exception
    	{
    		Runtime rt = Runtime.getRuntime();
    		 
            // 调用 mysql 的 cmd:
           
             //调用mysql的cmd:备份某个表
             //Process child = rt
            // .exec("D:/db/mysql/mysql-5.0.45-win32/bin/mysqldump -uroot -pmysql  --set-charset=utf8 ibtts t_a_dbbak");// 设置导出编码为utf8。这里必须是utf8
             
             Process child = rt.exec(databaseDirectory+"/"+copyDbCmd+" --no-defaults "+"-u"+userName+" "+"-p"+userPwd+" "+"--set-charset=utf8"+" "+databaseName+" "+"t_a_dbbak");// 设置导出编码为utf8。这里必须是utf8
             // 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行
             InputStream in = child.getInputStream();// 控制台的输出信息作为输入流
          
            // System.out.println(databaseDirectory+"/"+copyDbCmd+" --no-defaults "+"-u"+userName+" "+"-p"+userPwd+" "+"--set-charset=utf8"+" "+databaseName+" "+"t_a_dbbak");
             
             InputStreamReader xx = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码
            
             
             String inStr;
             StringBuffer sb = new StringBuffer("");
             String outStr;
             // 组合控制台输出信息字符串
             BufferedReader br = new BufferedReader(xx);
            while ((inStr = br.readLine()) != null) {
                 sb.append(inStr + "\r\n");
             }
             outStr = sb.toString();
             
    
             
         	//String targetDirectory = scontext.getRealPath("/WEB-INF/DBbackup");
      
             // 要用来做导入用的sql目标文件：D:/ibtts.sql
           
             FileOutputStream fout = new FileOutputStream(
            		 copyedFilePath+"/"+"ibtts_talbe.sql" );
             OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");
             writer.write(outStr);
            // 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
             writer.flush();
    
             // 别忘记关闭输入输出流
             in.close();
             xx.close();
             br.close();
             writer.close();
             fout.close();
     
    	}
    	
    	public void restoretable(Map map) throws Exception
    	{
    		
    		String fPath=copyedFilePath+"/"+"ibtts_talbe.sql";
    		
    
    		//String fPath = "D:/ibtts.sql";
    		Runtime rt = Runtime.getRuntime();
    
    		// 调用 mysql 的 cmd:
    		//Process child = rt.exec("D:/db/mysql/mysql-5.0.45-win32/bin/mysql -uroot -pmysql ibtts");
    		Process child = rt.exec(databaseDirectory+"/"+restoreCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+databaseName);
    		
    		System.out.println(databaseDirectory+"/"+restoreCmd+" "+"-u"+userName+" "+"-p"+userPwd+" "+databaseName);
    		
    		java.io.OutputStream out =child.getOutputStream();//控制台的输入信息作为输出流
    		String inStr;
    		StringBuffer sb = new StringBuffer("");
    		String outStr;
    		BufferedReader br = new BufferedReader(new InputStreamReader(
    		new FileInputStream(fPath), "utf8"));
    		while ((inStr = br.readLine()) != null) {
    		sb.append(inStr + "\r\n");
    		}
    		outStr = sb.toString();
    
    		OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");
    		writer.write(outStr);
    		// 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免
    		writer.flush();
    		// 别忘记关闭输入输出流
    		
    
    		
    		out.close();
    		br.close();
    		writer.close();
    		
    		
    		//System.out.println(map);
    		
    		/*int sign=0;
    		BackUpVO vo=null;
    		while(vo==null){
    			vo=(BackUpVO) service.findById(map);
    			if(vo!=null||sign==100){
    				break;
    			}
    			sign++;
    		}
    	
    	
    		service.modify(map);*/
    		
    	}
    	
    	@SuppressWarnings("unchecked")
    	public String upbak() throws Exception{
    		HashMap map = new HashMap();
    		map = (HashMap)request.getSession().getAttribute("dbmap");
    		
    		int sign=0;
    		BackUpVO vo=null;
    		while(vo==null){
    			vo=(BackUpVO) service.findById(map);
    			if(vo!=null||sign==100){
    				break;
    			}
    			sign++;
    		}
    	
    	
    		service.modify(map);
    		
    		return list();
    	}
    
    	public String getMsg() {
    		return msg;
    	}
    
    	public void setMsg(String msg) {
    		this.msg = msg;
    	}
    
    	public String getMsg1() {
    		return msg1;
    	}
    
    	public void setMsg1(String msg1) {
    		this.msg1 = msg1;
    	}
    	
    }