java mysql 資料庫備份和還原操作

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;
    }
    
}