云之飞踢 2019-12-31
本系列文章用Java实现达梦数据库的备份还原功能,分为本地备份(本服务器),异地备份(备份到其他服务器),手动备份和定时任务备份,及数据还原功能。
1. 达梦数据库备份还原分类:
代码主要通过dexp和dimp命令实现全库和表级别的逻辑备份还原
//yum文件 dameng: dev: ip: 39.98.218.179 userName: dmdba userPwd: 123456 /** *本地服务器的ip,username,pwd写到配置文件yum中 */ @Value("${dameng.dev.ip}") private String userIp; @Value("${dameng.dev.userName}") private String userName; @Value("${dameng.dev.userPwd}") private String userPwd; /** * 手动备份 -- 部分备份 *@tnames 要备份的表名 *@copytype 备份类型 local本地 foreignLands异地 *@ip,port,user,pwd,dir 异地备份服务器的ip,port,用户名,密码,备份文件存放目录 */ public Map<String, Object> dumpSQL(String tnames, String copyType, String ip, String port, String user, String pwd, String dir) { System.out.println("备份类型:" + copyType); String result = null; // 保存备份列表,备份文件名称=数据库id+T+时间,还原使用 SjglSjbflbParam param = new SjglSjbflbParam(); param.setCjlx(copyType); // 获取服务器连接 if (ip != null && port != null && user != null && pwd != null && dir != null) { param.setIp(ip); param.setPort(port); param.setUsername(user); param.setRepassword(pwd); param.setDir(dir); } StringBuffer tids = new StringBuffer(); for (String s1 : ids) { tids.append(s1).append(","); } param.setTids(tids.toString().substring(0, tids.toString().length() - 1)); param.setBflx("1"); String filename = sjglSjbflbService.add(param);//将备份记录保持到数据库,还原使用 Connection conn = DMruntimeUtil.login(userIp, userName, userPwd); // 本地备份 if (copyType.equals("local")) { String dexpStr = DMruntimeUtil.dumpByTables(hostip, dbname, username, password, tnames, filename); String cmd = "cd /opt/dameng/dmdbms/bin;" + dexpStr; result = DMruntimeUtil.execute(conn, cmd); } else if (copyType.equals("foreignLands")) { // sh dexplocal.sh test2 DEV.SYS_USER :/opt/data pwd 22 String cmd = "sh /opt/dameng/dmdbms/shelldata/dexphand.sh " + filename + " " + tnames + " " + user + "@" + ip + ":" + dir + " " + pwd + " " + port; result = DMruntimeUtil.execute(conn, cmd); } /* * HttpHeaders headers = new HttpHeaders(); * headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); * headers.setContentDispositionFormData("attachment", "eval.sql"); */ Map<String, Object> map = new HashMap<String, Object>(); if (result != null) { map.put("flag", true); } else { map.put("flag", false); map.put("msg", "备份失败!"); } return map; } /** * 手动备份 -- 全部备份 */ @RequestMapping("/dumpDB") @ResponseBody public Map<String, Object> dumpDataBase(String[] ids, String copyType, String ip, String port, String user, String pwd, String dir) { System.out.println("备份地点:" + copyType); String result = null; SjglSjbflbParam param = new SjglSjbflbParam(); param.setCjlx(copyType); // 获取服务器连接 if (ip != null && port != null && user != null && pwd != null && dir != null) { param.setIp(ip); param.setPort(port); param.setUsername(user); param.setRepassword(pwd); param.setDir(dir); } param.setBflx("1"); String filename = sjglSjbflbService.add(param);//保持备份记录,还原使用 Connection conn = DMruntimeUtil.login(userIp, userName, userPwd); // 本地备份 if (copyType.equals("local")) { String dexpStr = DMruntimeUtil.dumpDB(hostip, dbname, username, password, filename); String cmd = "cd /opt/dameng/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); //方式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", true); } else { map.put("flag", false); map.put("msg", "备份失败!"); } return map; } }
工具类DMruntimeUtil
import ch.ethz.ssh2.Session; import com.jcraft.jsch.*; 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; /** *工具类 */ 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) { LOGGER.info("=========登录成功=========" + conn); return conn; } } catch (IOException e) { LOGGER.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); // 如果为得到标准输出为空,说明脚本执行出错了 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 hostip 数据IP地址 * @param dbName 数据库名称 * @param username 用户名 * @param password 密码 * @param excludeTables 待去除的指定表数组 */ public static String dumpByExcludeTables(String hostip, String dbName, String username, String password, String[] excludeTables) { if (ArrayUtils.isEmpty(excludeTables)) { return null; } StringBuffer result = new StringBuffer(); StringBuffer command = new StringBuffer("dmdump "); command.append(" -u").append(username).append(" -p").append(password).append(" -h").append(hostip).append(" ") .append(dbName).append(" --ignore-table="); // 遍历表数组 for (String table : excludeTables) { command.append(dbName).append(".").append(table).append(","); } return command.toString(); } /** * 备份数据库的指定表(包括表结构与数据) * * @param hostip 数据库IP地址 * @param dbName 数据库名称 * @param username 用户名 * @param password 密码 * @param tables 待备份的表数组 ./dexp USERID=SYSDBA/:52996 * 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) { 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=/opt/dameng/dmdbms/dm7data/EVAL/dexp"; System.out.println("命令= " + newCommand); return newCommand; } /** * 备份数据库(包括表结构与数据) * * @param hostip 数据库IP地址 * @param dbName 数据库名称 * @param username 用户名 * @param password 密码 * <p> * 整个数据库导出 ./dexp USERID=SYSDBA/:52996 * 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) { StringBuffer command = new StringBuffer("./dexp "); command.append("USERID=").append(username).append("/").append(password).append("@").append(hostip) .append(" FILE=").append(name).append(".dmp") .append(" DIRECTORY=/opt/dameng/dmdbms/dm7data/EVAL/dexp") .append(" LOG=").append(name).append(".log FULL=Y"); System.out.println("命令= " + command.toString()); return command.toString(); } /** * 异域全部导出 * * @Author: Shaoyy * @Date: 2019/11/1 */ public static String foreignFullDumpDB(String hostip, String username, String password, String name, String reurl, String pwd, String port) { StringBuffer command = new StringBuffer("cd /opt/dameng/dmdbms/bin;"); command.append("./dexp ").append("USERID=").append(username).append("/").append(password).append("@").append(hostip) .append(" FILE=").append(name).append(".dmp") .append(" DIRECTORY=/opt/dameng/dmdbms/dm7data/EVAL/dexp") .append(" LOG=").append(name).append(".log FULL=Y;") .append("sshpass -p ").append(pwd).append(" scp -P ").append(port) .append(" /opt/dameng/dmdbms/dm7data/EVAL/dexp/").append(name).append(".dmp ") .append(reurl).append("/").append(name).append(".dmp"); return command.toString(); } public static String dimpByTables(String hostip, String dbName, String username, String password, String name, String tnames) { // ./dimp USERID=SYSDBA/SYSDBA FILE=/mnt/data/dexp/db_str.dmp LOG=db_str.log // DIRECTORY=/mnt/data/dimp StringBuffer command = new StringBuffer("./dimp "); Date date = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss"); String str = dateFormat.format(date); String[] names = name.split("T"); command.append("USERID=").append(username).append("/").append(password).append("@").append(hostip) .append(" FILE=/opt/dameng/dmdbms/dm7data/EVAL/dexp/").append(name).append(" LOG=").append(names[0]) .append(str).append(".log").append(" TABLES=").append(tnames).append(" DIRECTORY=") .append("/opt/dameng/dmdbms/dm7data/EVAL/dimp ignore=y TABLE_EXISTS_ACTION=TRUNCATE"); String newCommand = command.toString(); System.out.println("命令= " + newCommand); return newCommand; } }