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

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

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

@Controllerpublic 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> */@Autowiredpublic 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";}@Autowiredprivate 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;@Autowiredprivate 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 blocklogger.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 blocklogger.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 blocklogger.error(e.getMessage() + "startUserIdTimer");return false;}}class MyTask extends TimerTask {@Overridepublic 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 blocklogger.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;}}}