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();
}
(0)

相关推荐