mysql 数据库的备份与恢复

       最近在做Web项目，需要开发一个数据库备份恢复机能,以前也没做过,上网查了查,发现还是比较简单的。首先我的环境是在win7上运行的，所以就直接调用cmd命令进行数据库备份与恢复，linux系统我们就不多说了。首先也贴出直接在cmd里运行的指令。

备份数据库：mysqldump –uroot –proot  dbname > E:\db.sql

恢复数据库：mysql –uroot –proot dbname < E:\db.sql

这个是基于数据库在本机上的指令，如果你的数据库在服务器上，需要加上服务器的Ip地址。例如 mysqldump  –uroot –proot –h10.1.1.1 dbname > E:\db.sql。

如果你运行代码在本机上，数据库在服务器上，在输入命令时，需要在本机配置一下mysql的环境变量path，例如我的就是（C:\Program Files\MySQL\bin）。最好配置的时候把路径放在环境变量的第一个，不然在运行mysql时，有可能运行别的路径下的mysql.exe，会出现不是win32的程序之类的错误。配置好了才能够使用mysqldump,mysql命令，配置好环境变量，就无需进入bin下执行cmd命令了。我在本地装mysql时是直接用zip解压的，需要自己在cmd中设置一下，之后本地数据库就可以使用了。具体参照一下两个网址。

http://jingyan.baidu.com/article/f3ad7d0ffc061a09c3345bf0.html

http://www.ithao123.cn/content-10849306.html

接下来我们切入正题，开始进行数据库备份恢复的代码实现。

下面这段是数据库的备份与恢复

@Controller
public class DataBaseController {

	/**
	 * <summary> LOG日志管理类对象 </summary>
	 */
	private static final Logger logger = Logger.getLogger(FrameController.class.getName());

	/**
	 * <summary> SPRING 依赖注入对象 </summary>
	 */
	private DataBaseService dataBaseService;

	/**
	 * <summary> SPRING 自动装配 </summary>
	 */

	@Autowired(required = true)
	@Qualifier(value = "dataBaseService")
	public void setDataBaseService(DataBaseService ls) {
		this.dataBaseService = ls;
	}

	/**
	 * <summary> SPRING 依赖注入对象 </summary>
	 */
	private TimerHandler timerHandler;

	/**
	 * <summary> SPRING 自动装配 </summary>
	 */

	@Autowired
	public void setTimerHandler(TimerHandler ls) {
		this.timerHandler = ls;
	}

	@RequestMapping(value = "/DataBase/databaseList", method = RequestMethod.GET)
	public String DataBase() {
		return "DataBase/databaseList";
	}

	/**
	 * 数据库自动备份设置
	 */
	@RequestMapping(value = "/DataBase/databaseAuto", method = RequestMethod.GET)
	public String DeviceImportForm() {
		return "/DataBase/databaseAuto";
	}

	@Autowired
	private BPMS_Message database;

	@RequestMapping(value = "/DataBase/LoadAction", method = RequestMethod.GET)
	public @ResponseBody JqGridJson<DataBase> LoadAction(HttpServletRequest request, HttpSession httpSession) {

		// 返回结构初始化
		JqGridJson<DataBase> jqEntity = new JqGridJson<DataBase>();

		try {
			// 当前画面索引页数
			int pageIndex = Integer.valueOf(request.getParameter("pqGrid_PageIndex"));
			// 当前画面显示行数
			int pageSize = Integer.valueOf(request.getParameter("pqGrid_PageSize"));
			// 当前画面排序字段
			String orderField = request.getParameter("pqGrid_OrderField");
			// 当前画面排序类型（升序，降序）
			String orderType = request.getParameter("pqGrid_OrderType");
			// 参数
			String parm = request.getParameter("Parm_Key_Value");
			// 总体行数初期化
			Long totalCount = 0L;
			// 总体行数引用类型初期化
			RefObject<Long> refTotalCount = new RefObject<Long>(totalCount);

			// 用户一览取得并封装JSON数据返回处理
			jqEntity.setData(
					dataBaseService.GetPageList(parm, orderField, orderType, pageIndex, pageSize, refTotalCount));
			// 设置页面当前索引页
			jqEntity.setCurPage(pageIndex);
			// 设置页面了列表全体行数
			jqEntity.setTotalRecords(refTotalCount.argValue);

			// 返回查询结果
			return jqEntity;
		} catch (Exception e) {
			// 错误日志记录
			logger.error(e.toString());
			return jqEntity;
		}
	}

	/**
	 * 自动备份保存
	 */
	@RequestMapping(value = "/DataBase/AutoAcceptClick", method = RequestMethod.POST)
	public @ResponseBody JsonState AutoAcceptClick(HttpServletRequest request, HttpSession httpSession) {
		// 多国语资源取得
		RequestContext requestContext = new RequestContext(request);
		// 返回的信息
		String returnMsg = requestContext.getMessage("I0003");
		// 返回结果定义
		JsonState js = new JsonState();
		try {
			// 主键
			String period = request.getParameter("period");
			if (null != period && !period.isEmpty()) {
				timerHandler.stopTimer();
				boolean baseFlag = timerHandler.startTimer(period);
				if (baseFlag) {
					js.setStatus("success");
					returnMsg = requestContext.getMessage("I0001");
				}
			} else {
				js.setStatus("failure");
				returnMsg = requestContext.getMessage("I0003");
			}
			js.setReturnMsg(returnMsg);
			return js;
		} catch (Exception e) {
			logger.error(e.getMessage() + "		AutoAcceptClick");
			js.setStatus("failure");
			returnMsg = requestContext.getMessage("I0003");
			js.setReturnMsg(returnMsg);
			return js;
		}
	}

	/**
	 * 数据库备份操作
	 */
	@RequestMapping(value = "/Database/DatabaseBackup", method = RequestMethod.POST)
	public @ResponseBody JsonState DatabaseBackup(HttpServletRequest request, HttpSession httpSession) {
		// 多国语资源取得
		RequestContext requestContext = new RequestContext(request);
		// 返回的信息
		String returnMsg = requestContext.getMessage("I00015");
		// 返回结果定义
		JsonState js = new JsonState();
		js.setStatus("failure");
		try {
			String userId = SessionHelper.GetSessionUser(httpSession).getUserId();
			if (null != userId && !userId.isEmpty()) {
				boolean baseFlag = timerHandler.startUserIdTimer(userId);
				if (baseFlag) {
					js.setStatus("success");
					returnMsg = requestContext.getMessage("I00014");
				}
			}
			js.setReturnMsg(returnMsg);
			return js;
		} catch (Exception e) {
			logger.error(e.getMessage() + "		DatabaseRestore");
			js.setStatus("failure");
			returnMsg = requestContext.getMessage("I0003");
			js.setReturnMsg(returnMsg);
			return js;
		}
	}

	/**
	 * 数据库恢复操作
	 */
	@RequestMapping(value = "/Database/DatabaseRestore", method = RequestMethod.POST)
	public @ResponseBody JsonState DatabaseRestore(HttpServletRequest request, HttpSession httpSession) {
		// 多国语资源取得
		RequestContext requestContext = new RequestContext(request);
		// 返回的信息
		String returnMsg = requestContext.getMessage("I0003");
		// 返回结果定义
		JsonState js = new JsonState();
		js.setStatus("failure");
		try {
			// 主键
			String dataBaseId = request.getParameter("key");
			String name = request.getParameter("name");
			String division = request.getParameter("division");
			String userId = SessionHelper.GetSessionUser(httpSession).getUserId();
			if (null != name && !name.isEmpty()) {
				if ("1".equals(division)) {
					DataBase dataBase = dataBaseService.GetEntity(dataBaseId);
					if (null != dataBase.getDataBaseId() && !dataBase.getDataBaseId().isEmpty()) {
						if (restore(name)) {
							DataBase DataBase = new DataBase();
							String guid = java.util.UUID.randomUUID().toString();
							DataBase.setDataBaseId(guid); // 主键
							DataBase.setName(name); // 文件名
							DataBase.setPathName(database.getDbbasePathName()); // 路径
							DataBase.setDivision("0");
							DataBase.setCreateDate(new Date());
							DataBase.setCreateUserId(userId);
							DataBase.setUpdateDate(new Date());
							DataBase.setUpdateUserId(userId);
							dataBaseService.Insert(DataBase);
							js.setStatus("success");
							returnMsg = requestContext.getMessage("I0008");
						} else {
							returnMsg = requestContext.getMessage("I0009");
						}
					} else {
						returnMsg = requestContext.getMessage("I00011");
					}
				} else {
					returnMsg = requestContext.getMessage("I00013");
				}

			} else {
				returnMsg = requestContext.getMessage("I0003");
			}
			js.setReturnMsg(returnMsg);
			return js;
		} catch (Exception e) {
			logger.error(e.getMessage() + "		DatabaseRestore");
			js.setStatus("failure");
			returnMsg = requestContext.getMessage("I0003");
			js.setReturnMsg(returnMsg);
			return js;
		}
	}

	/*
	 * * 恢复数据库
	 * 
	 * @param input 输入流
	 * 
	 * @param dbname 数据库名
	 */
	public boolean restore(String name) {
		try {
			/****************原版代码****************/
			String filename = database.getDbbasePathName() + name;
			File file = new File(filename);
			if (!file.exists()) {
				return false;
			}
			String command;
			if (System.getProperty("os.name").indexOf("Windows") != -1) {  
			  command = "cmd /c mysql -u" + database.getJdbcUsername()
					  + " -p" + database.getJdbcPassword() + " --default-character-set=utf8 " + database.getJdbcDbName()
					  + "<" + filename;
			}
			else
			{
				command = "sh -c mysql -u" + database.getJdbcUsername()
				  + " -p" + database.getJdbcPassword() + " --default-character-set=utf8 " + database.getJdbcDbName()
				  + "<" + filename;
			}
			Process process = Runtime.getRuntime().exec(command);
			if (process.waitFor() == 0) {// 0 表示线程正常终止。
				return true;
			}
			return false;
			
/*			String filename = database.getDbbasePathName() + name;
			File file = new File(filename);
			if (!file.exists()) {
				return false;
			}
			Runtime runtime = Runtime.getRuntime();  
			String command = "cmd /c mysql -u" + database.getJdbcUsername()
 			+ " -p" + database.getJdbcPassword() + " --default-character-set=utf8 " + database.getJdbcDbName();
			Process child = runtime.exec(command);
	        OutputStream out = child.getOutputStream();//控制台的输入信息作为输出流
	        String inStr;      
	        StringBuffer sb = new StringBuffer("");      
	        String outStr;  
	        InputStream input = new FileInputStream(filename);  
	        BufferedReader br=new BufferedReader(new InputStreamReader(input,"utf8"));
	        while ((inStr = br.readLine()) != null) {  
	        	sb.append(inStr + "/r/n");   
	        	System.out.println(inStr);
	        }
	        outStr = sb.toString();      
	        OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");     
	        writer.write(outStr);      
	        writer.flush();      
	        out.close();      
	        br.close();      
	        writer.close();       */   
		} catch (Exception e) {
			logger.error(e.getMessage() + "		restore");
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 数据库删除操作
	 */
	@RequestMapping(value = "/Database/Delete", method = RequestMethod.POST)
	public @ResponseBody JsonState Delete(HttpServletRequest request, HttpSession httpSession) {
		// 多国语资源取得
		RequestContext requestContext = new RequestContext(request);
		// 返回的信息
		String returnMsg = requestContext.getMessage("I0007");
		// 返回结果定义
		JsonState js = new JsonState();
		try {
			// 主键
			String dataBaseId = request.getParameter("dataBaseId");
			String division = request.getParameter("division");
			String name = request.getParameter("name");
			DataBase dataBase = dataBaseService.GetEntity(dataBaseId);
			if (null != dataBase.getDataBaseId() && !dataBase.getDataBaseId().isEmpty()) {
				if (null != dataBaseId && !dataBaseId.isEmpty()) {
					dataBaseService.Delete(dataBaseId);
					if ("1".equals(division) && null != name && !name.isEmpty()) {
						boolean success = (new File(database.getDbbasePathName() + name)).delete();
						if (success) {
							js.setStatus("success");
							returnMsg = requestContext.getMessage("I0006");
							return js;
						}
					}
					js.setStatus("success");
					returnMsg = requestContext.getMessage("I0006");
				} else {
					js.setStatus("failure");
					returnMsg = requestContext.getMessage("I0007");
				}
			} else {
				returnMsg = requestContext.getMessage("I00011");
			}
			js.setReturnMsg(returnMsg);
			return js;
		} catch (Exception e) {
			logger.error(e.getMessage() + "		Delete");
			js.setStatus("failure");
			returnMsg = requestContext.getMessage("I0003");
			js.setReturnMsg(returnMsg);
			return js;
		}
	}

}

数据库的自动备份

public class TimerHandler {

	/** LOG注解. */
	private static final Logger logger = Logger.getLogger(TimerHandler.class.getName());

	/** timer */
	private Timer timer = null;

	/** 时间间隔 */
	private String stimePeriod = "30";
	
	/** 备份用户 */
	private String userId = null;
	
	/** 是否执行成功 */
	private boolean flag = true;
	
	
	/**
	 * <summary> SPRING 依赖注入对象 </summary>
	 */
	private DataBaseService dataBaseService;

	@Autowired
	private BPMS_Message database;

	/**
	 * <summary> SPRING 自动装配 </summary>
	 */

	@Autowired(required = true)
	@Qualifier(value = "dataBaseService")
	public void setDataBaseService(DataBaseService ls) {
		this.dataBaseService = ls;
	}

	/**
	 * 定时器已启动
	 * 
	 * @param event
	 */
	public void contextInitialized(ServletContextEvent event) {
		try {
			timer = new Timer(true);
			event.getServletContext().log("定时器已启动");// 添加日志，可在tomcat日志中查看到
			Date date;
			date = new SimpleDateFormat("yyyy-MM-dd mm:ss").parse("2016-05-20 00:00");
			timer.schedule(new MyTask(), date, 1 * 60 * 1000);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage() + "		contextDestroyed");
		}
	}

	/**
	 * 定时器销毁
	 * 
	 * @param event
	 */
	public void contextDestroyed(ServletContextEvent event) {
		try {
			timer.cancel();
			event.getServletContext().log("定时器销毁");
		} catch (Exception e) {
			logger.error(e.getMessage() + "		contextDestroyed");
		}
	}

	/**
	 * 定时器销毁
	 * 
	 */
	public void stopTimer() {
		try {
			if (null != timer) {
				timer.cancel();
			}
		} catch (Exception e) {
			logger.error(e.getMessage() + "		stopTimer");
		}
	}

	/**
	 * 定时器启动
	 * 
	 */
	public boolean startTimer(String period) {
		try {
			timer = new Timer(true);
			Date date;
			date = new SimpleDateFormat("yyyy-MM-dd mm:ss").parse("2016-05-26 14:10");
			stimePeriod = period;
			int itimePeriod = Integer.parseInt(period);
			timer.schedule(new MyTask(), date, itimePeriod * 60 * 1000);
			return flag;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage() + "		startTimer");
			return false;
		}
	}
	
	/**
	 * 定时器启动
	 * 
	 */
	public boolean startUserIdTimer(String uesrId) {
		try {
			  this.userId = uesrId;
			  MyTask task = new MyTask();
			  task.run();
			  return flag;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage() + "		startUserIdTimer");
			return false;
		}
	}

	class MyTask extends TimerTask {
		@Override
		public void run() {
			try {

				String name = new SimpleDateFormat("yyyyMMdd-HH-mm-ss").format(new Date()) + "-HYH.sql";
				String pathName = database.getDbbasePathName(); //路径
				String ip = database.getJdbcDbIP(); //Ip 地址
				String userName = database.getJdbcPassword(); //用户名
			    String paseword = database.getJdbcPassword(); //密码
			    String dbName = database.getJdbcDbName(); //数据库名字
				if (exportDatabase(ip, userName, paseword, pathName, name, dbName)) {
					DataBase DataBase = new DataBase();
					String guid = java.util.UUID.randomUUID().toString();
					DataBase.setDataBaseId(guid); // 主键
					DataBase.setName(name); // 文件名
					DataBase.setPathName(pathName); // 路径
					DataBase.setInterval(stimePeriod); // 时间间隔
					DataBase.setDivision("1");
					DataBase.setCreateDate(new Date());
					if(null != userId && !userId.isEmpty())
					{
						DataBase.setCreateUserId(userId);					
					}
					else
					{
						DataBase.setCreateUserId("auto");						
					}
					DataBase.setUpdateDate(new Date());
					if(null != userId && !userId.isEmpty())
					{
						DataBase.setUpdateUserId(userId);					
					}
					else
					{
						DataBase.setUpdateUserId("auto");
					}
					dataBaseService.Insert(DataBase);
					System.out.println("数据库成功备份！！！");
				} else {
					System.out.println("数据库备份失败！！！");
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				logger.error(e.getMessage() + "		run");
			}
		}

		/**
		 * Java代码实现MySQL数据库导出
		 * 
		 * @author GaoHuanjie
		 * @param hostIP
		 *            MySQL数据库所在服务器地址IP
		 * @param userName
		 *            进入数据库所需要的用户名
		 * @param userName
		 *            进入数据库所需要的用户名
		 * @param password
		 *            进入数据库所需要的密码
		 * @param savePath
		 *            数据库导出文件保存路径
		 * @param fileName
		 *            数据库导出文件文件名
		 * @param databaseName
		 *            要导出的数据库名
		 * @return 返回true表示导出成功，否则返回false。
		 */
		public boolean exportDatabase(String hostIP, String userName, String password, String savePath, String fileName,
				String databaseName) throws InterruptedException {
			// 取得文件夹目录
			File filePath = new File(savePath);
			if (!filePath.exists()) {// 如果目录不存在
				filePath.mkdirs();// 创建文件夹
			}
			// 判断是否以\结尾
			if (!savePath.endsWith(File.separator)) {
				savePath = savePath + File.separator;
			}
			PrintWriter printWriter = null;
			BufferedReader bufferedReader = null;
			try {
				printWriter = new PrintWriter(
						new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8")); // 输出到文件
				String command;
				if (System.getProperty("os.name").indexOf("Windows") != -1) {  
					 command = "cmd /c mysqldump --opt -u" + "root" + " -p" + "root" + " -h" + "10.1.1.42"
							+ " --set-charset=utf8 " + databaseName;
				}
				else
				{
					 command = "sh -c mysqldump --opt -u" + "root" + " -p" + "root" + " -h" + "10.1.1.42"
							+ " --set-charset=utf8 " + databaseName;
				}
				Process process = Runtime.getRuntime().exec(command); // cmd执行
				InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); // 输入流
				bufferedReader = new BufferedReader(inputStreamReader);
				String line;
				while ((line = bufferedReader.readLine()) != null) {
					printWriter.println(line);
				}
				printWriter.flush();
				if (process.waitFor() == 0) {// 0 表示线程正常终止。
					flag = true;
					return true;
				}
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				try {
					if (bufferedReader != null) {
						bufferedReader.close();
					}
					if (printWriter != null) {
						printWriter.close();
					}
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			flag = false;
			return false;
		}
	}
}