您的位置:澳门新葡萄京最大平台 > 最大平台 > java实现mysql的备份还原

java实现mysql的备份还原

发布时间:2019-11-04 05:15编辑:最大平台浏览(174)

    此小说是依赖

      1. 搭建Jquery+SpringMVC+Spring+Hibernate+MySQL平台

      2. jquery+springMVC完成公文上传

     

    一. 简介

      备份和导入是一个互逆的进程。 
      备份:程序调用mysql的备份命令,读出调控台输入流消息,写入.sql文件; 
      导入:程序调用mysql的导入命令,把从.sql文件中读出的新闻写入调整台的输出流 
      只顾:用Java试行,每便只可以进行一条command命令,重定向符">"和"<"是无法用的

     

    二. 相关程序代码介绍

      1. BackupRestoreBSImpl.java

    最大平台 1最大平台 2

    package com.ims.service.sys.impl;
    
    import java.io.BufferedReader;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.io.OutputStream;
    import java.io.OutputStreamWriter;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.ims.service.sys.DataBaseBS;
    import com.ims.common.DateUtil;
    import com.ims.common.FileUtil;
    import com.ims.service.sys.BackupRestoreBS;
    
    @Service("backupRestoreBS")
    public class BackupRestoreBSImpl implements BackupRestoreBS{
        private static Log logger = LogFactory.getLog(BackupRestoreBSImpl.class);
    
        private static final String uploadPath = System.getProperty("webapp.root")+"uploadFile\backupRestore\";
    
        @Autowired
        public DataBaseBS dataBaseBS;
    
        /**
         * 备份单个数据库
         * @param dbName 数据库名称
         * @return 备份成功或者失败
         */
        @Override
        public boolean backup(String dbName){    
            InputStream in = null;
            InputStreamReader inReader = null;
            BufferedReader br = null;
            OutputStreamWriter writer = null;
            FileOutputStream fout = null;
    
            try {
                logger.info(dbName + "开始备份!");
                // mysqldump的安装路径,支持带空格
                String cmd = """ ""+dataBaseBS.getInstallPath() +"bin\mysqldump" -hlocalhost -uroot -p123456 " + dbName;
                // cmd命令在后台执行,没有命令窗口出现或者一闪而过的情况
                Process process = Runtime.getRuntime().exec("cmd /c start /b " + cmd);
                // 把进程执行中的控制台输出信息写入.sql文件,即生成了备份文件。
                // 注:如果不对控制台信息进行读出,则会导致进程堵塞无法运行   
                in = process.getInputStream();// 控制台的输出信息作为输入流                             
                inReader = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8,否则从流中读入的是乱码   
    
                String inStr;   
                StringBuffer sb = new StringBuffer("");   
                String outStr;   
                // 组合控制台输出信息字符串   
                br = new BufferedReader(inReader);   
                while ((inStr = br.readLine()) != null) {   
                    sb.append(inStr + "rn");   
                }   
                outStr = sb.toString();   
    
                // 要用来做导入用的sql目标文件:   
                fout = new FileOutputStream(uploadPath + dbName + ".sql");   
                writer = new OutputStreamWriter(fout, "utf8");   
                writer.write(outStr);   
                // 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免   
                writer.flush();   
    
            } catch (Exception e) {   
                logger.error(dbName + "备份失败!",e);
                return false;   
            } finally{
                // 别忘记关闭输入输出流   
                try {
                    in.close();
                    inReader.close();   
                    br.close();   
                    writer.close();   
                    fout.close(); 
                } catch (Exception e) {
                    logger.error(dbName + "备份失败!",e);
                    return false;   
                }              
            }
            logger.info(dbName + "备份成功!");
            return true;
        }
    
        /**
         * 备份所有的数据库
         */
        @Override
        public Map<String, Object> backupAll(){
            Map<String, Object> result = new HashMap<String, Object>();
            String[] dataBases = dataBaseBS.getDataBases();    
            if(FileUtil.deleteAll(uploadPath)){
                File[] srcfile = new File[dataBases.length];
                for(int i=0;i<dataBases.length;i++){            
                    if(backup(dataBases[i])){
                        srcfile[i] = new File(uploadPath+dataBases[i]+".sql");
                    }else{
                        result.put("status", false);
                        result.put("msg", dataBases[i] + "数据备份失败");
                        return result;                    
                    }
                }
                String filename = DateUtil.getCurrDate() + "_backup.zip";
                File zipfile = new File(uploadPath + filename);
                if(FileUtil.zip(srcfile, zipfile)){
                    result.put("status", true);
                    result.put("msg", filename);
                }else{
                    result.put("status", false);
                    result.put("msg", "文件压缩失败");                
                }            
            }else{
                result.put("status", false);
                result.put("msg", "文件夹清空失败");
            }
            return result;
        }
    
        /**
         * 还原单个数据库
         * @param dbName 数据库名称
         * @return 还原成功或者失败
         */
        @Override
        public boolean restore(String dbName){
            OutputStream out = null;
            BufferedReader br = null;
            OutputStreamWriter writer = null;
    
            try {
                logger.info(dbName + "开始还原!");
                // mysql的安装路径,支持带空格
                String cmd = """ ""+dataBaseBS.getInstallPath() +"bin\mysql" -hlocalhost -uroot -p123456 " + dbName;
                // cmd命令在后台执行,没有命令窗口出现或者一闪而过的情况
                Process process = Runtime.getRuntime().exec("cmd /c start /b " + cmd);  
                out = process.getOutputStream();//控制台的输入信息作为输出流   
                String inStr;   
                StringBuffer sb = new StringBuffer("");   
                String outStr;   
                br = new BufferedReader(new InputStreamReader(   
                        new FileInputStream(uploadPath + dbName + ".sql"), "utf8"));   
                while ((inStr = br.readLine()) != null) {   
                    sb.append(inStr + "rn");   
                }   
                outStr = sb.toString();   
    
                writer = new OutputStreamWriter(out, "utf8");   
                writer.write(outStr);   
                // 注:这里如果用缓冲方式写入文件的话,会导致中文乱码,用flush()方法则可以避免   
                writer.flush();   
            } catch (Exception e) {
                logger.error(dbName + "还原失败!",e);
                return false;
            } finally {
                // 别忘记关闭输入输出流                
                try {
                    out.close();
                    br.close();
                    writer.close();   
                } catch (IOException e) {
                    logger.error(dbName + "还原失败!",e);
                    return false;
                }              
            }
            logger.info(dbName + "还原成功!");
            return true;
        }
    
        /**
         * 还原所有的数据库
         */
        @Override
        public Map<String, Object> restoreAll(String zipFile){
            Map<String, Object> result = new HashMap<String, Object>();
            String[] dataBases = dataBaseBS.getDataBases();
            if(zipFile.length()>0&&checkFile(zipFile)){
                if(FileUtil.unZip(new File(uploadPath+zipFile), uploadPath)){
                    for(int i=0;i<dataBases.length;i++){
                        if(!restore(dataBases[i])){                        
                            result.put("status", false);
                            result.put("msg", dataBases[i] + "数据还原失败");
                            return result;                    
                        }
                    }
                    System.gc();// 强制回收内存垃圾,否则zip文件一直被占用删除不了
                    result.put("status", true);
                    result.put("msg", "数据还原成功");
                }else{
                    result.put("status", false);
                    result.put("msg", "解压缩包失败");
                }
            }else{
                result.put("status", false);
                result.put("msg", "没有找到可还原的数据压缩文件");
            }
            return result;
        }
    
        /**
         * 根据文件名验证文件是否合法
         * @param fileName
         * @return
         */
        public boolean checkFile(String fileName){
            String[] strs = fileName.split("_");
            if(strs.length>1){
                String checkStr = strs[strs.length-1];
                if("backup.zip".equals(checkStr)){
                    return true;
                }else{
                    return false;
                }
            }else{
                return false;
            }
        }
    
        @Override
        public String getUploadpath() {
            return uploadPath;
        }
    }
    

    View Code

      2. DataBaseBSImpl.java,可设置要备份的数据库名称,如:sys

    最大平台 3最大平台 4

    package com.ims.service.sys.impl;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.ims.persistence.base.SqlXmlParser;
    import com.ims.persistence.dao.sys.DataBaseDao;
    import com.ims.service.sys.DataBaseBS;
    
    @Service("dataBaseBS")
    public class DataBaseBSImpl implements DataBaseBS{
        private static final String sqlXml = "sys/dataBase.xml";
        private static final String[] dataBases = new String[]{"sys"};
    
        @Autowired
        private DataBaseDao dataBaseDao;
    
        @Override
        public String getInstallPath() {        
            String installPath = (String)dataBaseDao.findUniqueResultBySql(new SqlXmlParser(sqlXml).parse("installPath", null));
            return installPath;
        }
    
        @Override
        public String[] getDataBases() {
            return dataBases;
        }
    }
    

    View Code

      3. TestController.java

    最大平台 5最大平台 6

    package com.ims.web.controller;
    
    import java.io.BufferedInputStream;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.multipart.MultipartFile;
    import org.springframework.web.multipart.MultipartHttpServletRequest;
    import org.springframework.web.servlet.ModelAndView;
    
    import com.alibaba.fastjson.JSON;
    import com.ims.common.FileUtil;
    import com.ims.service.sys.BackupRestoreBS;
    
    @Controller
    @RequestMapping("test")
    public class TestController extends BaseController{
        @Autowired
        private BackupRestoreBS backupRestoreBS;
    
        @RequestMapping("view")
        public ModelAndView test(){
            ModelAndView view = new ModelAndView("test.jsp");
            return view;
        }
    
        @RequestMapping("backupRestore!backup")
        public void backup(){
            Map<String, Object> result = backupRestoreBS.backupAll();            
            ajaxJson((Boolean)result.get("status")?STATUS_SUCCESS:STATUS_ERROR, result.get("msg"));
        }
    
        @RequestMapping("backupRestore!download")
        public void download(@RequestParam Map<String, Object> params){
            InputStream in=null;  
            OutputStream out=null; 
            String fileName = (String)params.get("fileName");
            try{
                response.setContentType("application/x-download;charset=GBK");
                response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("GBK"),"ISO8859_1"));  
                out=response.getOutputStream();  
                //将内容写入输出流并把缓存的内容全部发出去  
                in=new BufferedInputStream(new FileInputStream(backupRestoreBS.getUploadpath()+fileName));   
                int len = 0;  
                byte[] buffer = new byte[1024];                
                while((len = in.read(buffer)) > 0) {  
                    out.write(buffer,0,len);  
                }                  
                out.flush();  
            }catch(Exception e){
                ajaxJson(STATUS_ERROR, "文件下载失败");
            }finally{
                if(in!=null){
                    try{                
                        in.close();
                    }catch(IOException e){
                        ajaxJson(STATUS_ERROR, "输入流关闭失败");
                    }
                }
    
                if(out!=null){
                    try{
                        out.close();
                    }catch(IOException e){
                        ajaxJson(STATUS_ERROR, "输出流关闭失败");
                    }
                }                        
            }
        }
    
        @RequestMapping("backupRestore!restore")
        public void restore(@RequestParam Map<String, String> params,
                MultipartHttpServletRequest multipartRequest){
            Map<String, Object> result = new HashMap<String, Object>();
            try {
                if(FileUtil.deleteAll(backupRestoreBS.getUploadpath())){
                    MultipartFile restoreFile = multipartRequest.getFile("restoreFile");
                    String fileName = restoreFile.getOriginalFilename();
                    if(FileUtil.saveFileFromInputStream(restoreFile.getInputStream(), 
                            backupRestoreBS.getUploadpath()+fileName)){
                        Map<String, Object> restoreResult = backupRestoreBS.restoreAll(fileName);
                        result.put("status", STATUS_SUCCESS);
                        result.put("message", restoreResult.get("msg"));                            
                    }else{
                        result.put("status", STATUS_ERROR);
                        result.put("message", "文件保存失败");
                    }
                }else{
                    result.put("status", STATUS_ERROR);
                    result.put("message", "文件夹清空失败");
                }            
            } catch (IOException e) {
                result.put("status", STATUS_ERROR);
                result.put("message", "数据还原失败");
            }    
            ajax(JSON.toJSONString(result),"text/html");    
        }
    }
    

    View Code

     

      4. backupRestore.jsp

    最大平台 7最大平台 8

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
    <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
    <html>
     <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>测试</title>
        <%@ include file="/common/basePath.jsp"%>
     </head>
     <body>
     ~~~~~~~~~~~~~~~~~~~~~~mysql数据库的备份与还原~~~~~~~~~~~~~~~~~~~~~~~~
     <br><br>
       数据备份:<button type="button" onclick="backup();">备份</button>
     <br><br>
       数据选择:<input type="file" id="restoreFile" style="width: 350px;"/>
     <br><br>
       导入还原:<button type="button" onclick="restore();">还原</button>
     <br><br><br>
     <script type="text/javascript" src="content/js/jquery/jquery-1.8.1.min.js"></script>
     <script type="text/javascript" src="content/js/core/utils.js"></script>
     <script type="text/javascript" src="content/js/core/common.js"></script>
     <script type="text/javascript" src="content/js/jquery-plugin/fileUpload/jquery.ajaxFileUpload.js"></script>
     <script type="text/javascript">
    
        function backup(){
            $.ajax({
                async:true,
                url: rootPath+"/test/backupRestore!backup.do",
                success: function (d) {    
                    if(d.status=='success'){            
                        var exporter = com.exporter();
                        exporter.params.action = rootPath+"/test/backupRestore!download.do?fileName="+d.message;
                        exporter.download('zip');
                        alert("备份成功!"); 
                    } else { 
                        alert(d.message);
                    }
                }
            });
        }
    
        function restore() {
            $.ajaxFileUpload({  
                url:rootPath+"/test/backupRestore!restore.do",
                secureuri:false,
                fileElementId: ['restoreFile'], 
                dataType: 'json',
                success: function (data){  
                    if(data.status=='success'){  
                        alert('还原成功!');  
                    } else {
                        alert(data.message);  
                    }                          
                },
                error: function(data){
                }
            });          
        }
     </script>
     </body>
    </html>
    

    View Code

    最大平台,三. 测试

      访问:

    最大平台 9

      1. 备份:点击 备份 开关,浏览器会下载压缩后的备份文件,格式如:2017-03-16_backup.zip

      2. 上升:选择刚下载的减弱后的备份文件,点击 还原 按键,数据库被成功还原

     

    本文由澳门新葡萄京最大平台发布于最大平台,转载请注明出处:java实现mysql的备份还原

    关键词: