java 運算元據庫備份

    /**用於執行某表的備份(內部類)執行緒
     * @from    fhadmin.cn
     * Callable 有返回值的執行緒介面
     */
    class DbBackUpCallable implements Callable<Object>{
        String tableName = null;
        public DbBackUpCallable(String tableName){
            this.tableName = tableName;
        }
        @Override
        public Object call() {
            try {
                String remoteDB = pros.getProperty("remoteDB");            //是否遠端備份資料庫 yes or no
                String DBSeverport = pros.getProperty("DBSeverport");    //遠端伺服器備份程式埠
                String dbtype = pros.getProperty("dbtype");                //資料庫型別
                String username = pros.getProperty("username");            //使用者名稱
                String password = pros.getProperty("password");            //密碼
                String address = pros.getProperty("dbAddress");            //資料庫連線地址
                String databaseName = pros.getProperty("databaseName");    //資料庫名
                String dbpath = pros.getProperty("dbpath");            //資料庫的安裝路徑
                String sqlpath = pros.getProperty("sqlFilePath");        //儲存路徑
                String ffilename = DateUtil.getSdfTimes();
                String commandStr = "";

                if(!"sqlserver".equals(dbtype)){
                    sqlpath = sqlpath+DateUtil.getDays()+"/";            //日期當路徑分支
                    if("yes".equals(remoteDB)){//資料庫另外一臺伺服器上(和tomcat不在同一臺伺服器上)
                        commandStr = DbFH.getExecStr(dbtype,dbpath,"localhost",username,password,sqlpath,tableName,databaseName,ffilename); //命令語句
                        Socket ss = null;
                        DataOutputStream bb = null;
                        DataInputStream dat = null;
                        ss = new Socket(address, Integer.parseInt(DBSeverport));//連線遠端伺服器資料庫備份程式
                        bb = new DataOutputStream(ss.getOutputStream());
                        dat = new DataInputStream(ss.getInputStream());
                        bb.writeUTF("FHQ313596790"+commandStr+"FH313596790"+sqlpath);    //傳送指令給服務端
                        bb.flush();
                        Boolean llm = true;
                        while(llm){
                            String returnstr = dat.readUTF();
                            if("errer".equals(returnstr)){
                                return returnstr;    //遠端伺服器備份失敗或超時
                            }
                            llm = false;
                            ss.close();
                            bb.close();
                            dat.close();
                        }
                    }else{                            //資料庫在本地(和tomcat在同一臺伺服器上)
                        FileUtil.createDir(sqlpath+"/fh.fh");
                        commandStr = DbFH.getExecStr(dbtype,dbpath,address,username,password,sqlpath,tableName,databaseName,ffilename); //命令語句
                        Runtime cmd = Runtime.getRuntime();
                        Process p = cmd.exec(commandStr);
                        p.waitFor();                 // 該語句用於標記，如果備份沒有完成，則該執行緒持續等待
                    }
                }else{//當資料庫為sqlserver時 只能備份整庫，不能單表備份
                    String spath = sqlpath + databaseName + "_"+ffilename + ".bak";// name檔名  
                    String bakSQL = "backup database "+databaseName+" to disk=? with init";// SQL語句  
                    PreparedStatement bak = DbFH.getFHCon().prepareStatement(bakSQL);  
                    bak.setString(1, spath);// path必須是絕對路徑  
                    bak.execute();             // 備份資料庫  
                    bak.close(); 
                }
                String fileType=".bak";
                if("mysql".equals(dbtype)){
                    fileType=".sql";
                }else if("oracle".equals(dbtype)){
                    fileType=".DMP";
                }
                if("".equals(tableName)){
                    return sqlpath+databaseName+"_"+ffilename+fileType;
                }else{
                    return sqlpath+tableName+"_"+ffilename+fileType;
                }
            } catch (Exception e) {
                logger.error("備份操作出現問題", e);
                return "errer";
            }finally{
                backUpTableList.remove(tableName); // 最終都將解除
            }
        }
    }

    /**資料庫備份命令字串
     * @param dbtype 資料庫型別
     * @param dbpaths 資料庫的路徑
     * @param address 資料庫連線地址
     * @param username 使用者名稱
     * @param password 密碼
     * @param sqlpath 儲存路徑
     * @param tableName 表名
     * @param databaseName 資料庫名
     * @param ffilename 日期當路徑和儲存檔名的後半部分
     * @return 完整的命令字串
     */
    public static String getExecStr(String dbtype,String dbpath,String address,String username,String password,String sqlpath,String tableName,String databaseName,String ffilename){
        StringBuffer sb = new StringBuffer();
        if("mysql".equals(dbtype)){
            address = "localhost";
            sb.append(dbpath);
            sb.append("mysqldump ");
            sb.append("--opt ");
            sb.append("-h ");
            sb.append(address);
            sb.append(" ");
            sb.append("--user=");
            sb.append(username);
            sb.append(" ");
            sb.append("--password=");
            sb.append(password);
            sb.append(" ");
            sb.append("--lock-all-tables=true ");
            sb.append("--result-file=");
            sb.append(sqlpath);
            sb.append(("".equals(tableName)?databaseName+"_"+ffilename:tableName+"_"+ffilename)+".sql");
            sb.append(" ");
            sb.append("--default-character-set=utf8 ");
            sb.append(databaseName);
            sb.append(" ");
            sb.append(tableName);//當tableName為“”時，備份整庫
        }else if("oracle".equals(dbtype)){
            if("".equals(tableName)){//備份整庫
                sb.append("EXP "+username+"/"+password+" BUFFER=880000 FILE="+sqlpath+username+"_"+ffilename+".DMP LOG="+sqlpath+username+"_"+ffilename+".LOG OWNER="+username);
            }else{//備份單表
                sb.append("EXP "+username+"/"+password+" BUFFER=880000 FILE="+sqlpath+tableName+"_"+ffilename+".DMP LOG="+sqlpath+tableName+"_"+ffilename+".LOG TABLES=("+username+"."+tableName+")");
            }
        }
        return sb.toString();
    }

本作品採用《CC 協議》，轉載必須註明作者和本文連結