达梦dm数据库远程备份与恢复
达梦数据库远程操作进行备份与恢复在网络上的参考资料与博客比较少，这里记录下项目中用到的方法。
1、工具类
首先是编写工具类，用于连接远程服务器、生成相应操作的命令、执行相关命令。
package com.fongtech.cli.common.util;
import ch.ethz.ssh2.Session;
import com.jcraft.jsch.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ch.ethz.ssh2.Connection;
import ch.ethz.ssh2.StreamGobbler;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
*工具类
*/
@Slf4j
public class DMruntimeUtil {
private static final String DEFAULT_CHARSET = "utf-8";
private static final Logger LOGGER = LoggerFactory.getLogger(DMruntimeUtil.class);
/**
* 登录主机
*
* @return 登录成功返回true，否则返回false
*/
public static Connection login(String ip, String userName, String userPwd) {
boolean flg = false;
Connection conn = null;
try {
conn = new Connection(ip);
conn.connect();// 连接
flg = conn.authenticateWithPassword(userName, userPwd);// 认证
if (flg) {
log.info("=========登录服务器成功=========" + conn);
return conn;
}
} catch (IOException e) {
log.error("=========登录服务器失败=========" + e.getMessage());
e.printStackTrace();
}
return conn;
}
/**
* 远程执行shll脚本或者命令
*
* @param cmd 即将执行的命令
* @return 命令执行完后返回的结果值
*/
public static String execute(Connection conn, String cmd) {
String result = "";
try {
if (conn != null) {
Session session = conn.openSession();// 打开一个会话
session.execCommand(cmd);// 执行命令
result = processStdout(session.getStdout(), DEFAULT_CHARSET);
// System.out.println("-----------result:"+result);
// 如果为得到标准输出为空，说明脚本执行出错了
if (StringUtils.isBlank(result)) {
LOGGER.info("得到标准输出为空,链接conn:" + conn + ",执行的命令：" + cmd);
result = processStdout(session.getStderr(), DEFAULT_CHARSET);
} else {
LOGGER.info("执行命令成功,链接conn:" + conn + ",执行的命令：" + cmd);
}
// System.out.println(result);
conn.close();
session.close();
}
} catch (IOException e) {
LOGGER.info("执行命令失败,链接conn:" + conn + ",执行的命令：" + cmd + " " + e.getMessage());
e.printStackTrace();
}
return result;
}
/**
* 解析脚本执行返回的结果集
*
* @param in 输入流对象
* @param charset 编码
* @return 以纯文本的格式返回
*/
private static String processStdout(InputStream in, String charset) {
InputStream stdout = new StreamGobbler(in);
StringBuffer buffer = new StringBuffer();
;
try {
BufferedReader br = new BufferedReader(new InputStreamReader(stdout, charset));
String line = null;
while ((line = br.readLine()) != null) {
buffer.append(line + "
");
}
} catch (UnsupportedEncodingException e) {
LOGGER.error("解析脚本出错：" + e.getMessage());
e.printStackTrace();
} catch (IOException e) {
LOGGER.error("解析脚本出错：" + e.getMessage());
e.printStackTrace();
}
return buffer.toString();
}
/**
* 同城-部分备份
* 数据库的指定表(包括表结构与数据)
*
* @param hostip 数据库IP地址
* @param dbName 数据库名称
* @param username 用户名
* @param password 密码
* @param tables 待备份的表数组 ./dexp USERID=SYSDBA/SYSDBA@ip:port
* FILE=db_str2.dmp LOG=db_str2.log TABLES=DEV.SYS_USER
* DIRECTORY=/opt/dameng/dmdbms/dm7data/EVAL/dexp"
*/
public static String dumpByTables(String hostip, String dbName, String username, String password, String tables,
String name,String dexpFileUrl) {
StringBuffer command = new StringBuffer("./dexp ");
command.append("USERID=").append(username).append("/").append(password).append("@").append(hostip)
.append(" FILE=").append(name).append(".dmp ").append(" LOG=").append(name).append(".log ")
.append(" TABLES=").append(tables);
// int length = command.length();
String newCommand = command.toString() + " DIRECTORY="+dexpFileUrl;
log.info("命令= " + newCommand);
return newCommand;
}
/**
* 同城-全部备份
* 数据库(包括表结构与数据)
*
* @param hostip 数据库IP地址
* @param dbName 数据库名称
* @param username 用户名
* @param password 密码
* <p>
* 整个数据库导出 ./dexp USERID=SYSDBA/SYSDBA@ip:port
* FILE=db_str2.dmp
* DIRECTORY=/opt/dameng/dmdbms/dm7data/EVAL/dexp
* LOG=db_str2.log FULL=Y
*/
public static String dumpDB(String hostip, String dbName, String username, String password, String name,String dexpFileUrl) {
StringBuffer command = new StringBuffer("./dexp ");
command.append("USERID=").append(username).append("/").append(password).append("@").append(hostip)
.append(" FILE=").append(name).append(".dmp")
.append(" DIRECTORY=").append(dexpFileUrl)
.append(" SCHEMAS="SYSDBA" ")
// .append(" LOG=").append(name).append(".log FULL=Y");
.append(" LOG=").append(name).append(".log ");
log.info("命令= " + command.toString());
return command.toString();
}
/**
* 同城 - 删除备份文件和备份日志
*
* @param fileName
* @return
* 删除命令： rm -rf 20200610182738198data.log 20200610182738198data.dmp 20200611153735300dataRecover.log
*/
public static String delFile(String fileName) {
StringBuffer command = new StringBuffer("rm -rf ");
command.append(fileName+".log ").append(fileName+".dmp ")
.append(fileName+"Recover.log ");
log.info("命令= " + command.toString());
return command.toString();
}
/**
* 本地 ---数据库恢复
* @param hostip
* @param dbname
* @param username
* @param password
* @param dir
* @return
* 恢复命令./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log
* DIRECTORY=/mnt/data/dimp
*/
public static String dimpByTables(String hostip,String dbname, String username, String password, String dir,String fileName){
StringBuffer command = new StringBuffer("./dimp ");
command.append("USERID=").append(username).append("/").append(password).append("@").append(hostip)
// .append(" FILE=").append(dbname).append(".dmp")
.append(" FILE=").append("/home/fongtech/statistic/backup/").append(fileName).append(".dmp")
.append(" SCHEMAS="SYSDBA" ")
.append(" DIRECTORY=").append(dir)
.append(" TABLE_EXISTS_ACTION=APPEND ")//恢复被删除的数据
// .append(" LOG=").append(dbname).append(".log FULL=Y");
.append(" LOG=").append(fileName).append("Recover.log "); //去掉 FULL=y
log.info("命令= " + command.toString());
return command.toString();
}
/**
* 异域--部分备份
* @param hostip
* @param username
* @param password
* @param name
* @param reurl
* @param pwd
* @param port
* @param installPath
* @param dexpFileUrl
* @return
*/
public static String foreignDumpDB(String hostip, String username, String password,
String name, String reurl, String pwd,
String port, String installPath,String dexpFileUrl,String tables) {
StringBuffer command = new StringBuffer("cd ");
command.append(installPath).append(";")
.append("./dexp ").append("USERID=").append(username).append("/").append(password).append("@").append(hostip)
.append(" FILE=").append(name).append(".dmp")
.append(" DIRECTORY=").append(dexpFileUrl)
.append(" LOG=").append(name).append(".log ")
.append(" TABLES=").append(tables);
return command.toString();
}
/**
* 异域-全部备份
*
*/
public static String foreignFullDumpDB(String hostip, String username, String password, String name,
String reurl, String pwd, String port,
String installPath,String dexpFileUrl) {
StringBuffer command = new StringBuffer("cd ");
command.append(installPath).append(";")
.append("./dexp ").append("USERID=").append(username).append("/").append(password).append("@").append(hostip)
.append(" FILE=").append(name).append(".dmp")
.append(" DIRECTORY=").append(dexpFileUrl)
.append(" LOG=").append(name).append(".log FULL=Y;");
return command.toString();
}}
2、实现
登录服务器和数据库的 ip 和账户密码等按照实际情况设置。
文件存储位置和达梦数据库 /bin 等按照实际情况设置。
//用于登录服务器
private String userIp = "xx.xxx.xxx.xx";
private String userName = "root";
private String userPwd = "123456";
//用于登录数据库
private String hostip = "xx.xxx.xxx.xx";
private String dbname = "SYSDBA";
private String username = "SYSDBA";
private String password = "SYSDBA";
//存储目录
private String dir = "/home/fongtech/statistic/backup";
//达梦数据库的 bin 的位置
private String installPath = "/opt/dmdbms/bin";
/**
* 异步对数据库进行备份，并且更新对应任务与生成系统消息
* @param userIp
* @param userName
* @param userPwd
* @param hostip
* @param dbname
* @param username
* @param password
* @param filename
* @param dir
* @param copyType
* @param user
* @param ip
* @param pwd
* @param port
* @param installPath
* @return
*/
@Transactional(rollbackFor = Exception.class)
@Async
public Map<String, Object> backups(String userIp,String userName,String userPwd,String hostip,
String dbname,String username,String password,String filename,
String dir,String copyType,String user,String ip,String pwd,String port,String installPath,Integer task_id){
String result = null;
String fileSize = "";
Connection conn = DMruntimeUtil.login(userIp, userName, userPwd);
// 本地备份
if (copyType.equals("local")) {
String dexpStr = DMruntimeUtil.dumpDB(hostip, dbname, username, password, filename,dir);
String cmd = "cd /opt/dmdbms/bin;" + dexpStr;
result = DMruntimeUtil.execute(conn, cmd);
} else if (copyType.equals("foreignLands")) { //异地备份
String reurl = user + "@" + ip + ":" + dir;
//方式1：运行命令
String cmd = DMruntimeUtil.foreignFullDumpDB(hostip, username, password, filename, reurl, pwd, port,installPath,dir);
//方式2：运行脚本
// String cmd = "sh /opt/dameng/dmdbms/shelldata/fulldexphand.sh " + filename + " " + user + "@"
// + ip + ":" + dir + " " + pwd + " " + port;
result = DMruntimeUtil.execute(conn, cmd);
}
Map<String, Object> map = new HashMap<String, Object>();
if (result != null) {
map.put("flag", "success");
//获取文件大小 todo 待测试！！！！！！！！！！！！！
conn = DMruntimeUtil.login(userIp, userName, userPwd);
String sizeCmd = "cd "+dir+";"+" du -sh "+filename+".dmp";
String resultSize = DMruntimeUtil.execute(conn, sizeCmd);
if(resultSize.contains("M")){
String[] ms = resultSize.split("M");
fileSize = ms[0]+"M";
}else if (resultSize.contains("K")){
String[] ms = resultSize.split("K");
fileSize = ms[0]+"K";
}else if (resultSize.contains("G")){
String[] ms = resultSize.split("G");
fileSize = ms[0]+"G";
}
log.info("--------获取备份文件的大小 fileSize:"+fileSize);
// System.out.println("-------resultSize:"+resultSize);
// System.out.println("-------fileSize:"+fileSize);
} else {
map.put("flag", "failed");
map.put("msg", "备份失败！");
}
// 如果执行成功，对任务进行更新,, 并且需要有消息提示
CommonTask one = commonTaskService.query().eq(CommonTask::getId, task_id).getOne();
// one.setTask_start(new Date());
// System.out.println(one);
if(result!=null){
one.setTask_status("task_12");
one.setTask_end(new Date());
}else {
one.setTask_status("task_15");
one.setTask_end(new Date());
}
commonTaskService.saveBackupsTask(one,filename,fileSize,dir);
return map;
}
上面的实现是备份，恢复和删除同理。