Java备份Mysql数据库

这里是将mysql生成的备份文件放在项目中，列表读取项目中文件的sql文件来进行还原功能。

DataBaseBackupController.java

import java.io.File;import java.util.Collections;import java.util.Comparator;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.shiro.authz.annotation.RequiresPermissions;import org.springframework.stereotype.Controller;import org.springframework.ui.Model;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.servlet.mvc.support.RedirectAttributes;import com.google.common.collect.Lists;import com.alun.common.config.Global;import com.alun.common.utils.DateUtils;import com.alun.common.utils.FileUtils;import com.alun.common.utils.mysql.DatabaseBackup;import com.alun.common.web.BaseController;import com.alun.modules.sys.entity.DataBase;/** * 数据库备份处理 * @author ALun * @version 2017-06-29 */@Controller@RequestMapping(value = "${adminPath}/sys/database")public class DataBaseBackupController extends BaseController {    private static String path = "database";    @RequestMapping(value = "")    public String showDataBase(HttpServletRequest request,Model model){        String realPath=request.getServletContext().getRealPath("/");//项目绝对路径        List<DataBase> list = Lists.newArrayList();        File file = new File(realPath+path);        if(file.exists()){            File[] files = file.listFiles();            for(File f:files){                if(!f.isDirectory()){                    String name = f.getName();                    if("sql".equals(name.substring(name.lastIndexOf(".")+1))){                        DataBase d = new DataBase();                        d.setName(name);                        d.setUpdatedate(f.lastModified());                        d.setLength(f.length()/1024+"KB");                        list.add(d);                    }                }            }        }else{            file.mkdir();        }        if(list.size()>1){            Collections.sort(list,new Comparator<DataBase>(){                 public int compare(DataBase o1, DataBase o2) {                      //按照学生的年龄进行升序排列                      if(o1.getUpdatedate() < o2.getUpdatedate()){                          return 1;                      }                      if(o1.getUpdatedate() == o2.getUpdatedate()){                          return 0;                      }                      return -1;                  }              });        }        model.addAttribute("list", list);        return "modules/sys/databaseList";    }    /**     * 数据库备份     * @return     */    @RequiresPermissions("sys:database:add")    @RequestMapping(value = "backup")    public String backup(HttpServletRequest request,RedirectAttributes redirectAttributes){        String realPath=request.getServletContext().getRealPath("/")+path;//项目绝对路径        String filename = realPath +"/"+Global.getConfig("jdbc.database")+"_"+DateUtils.getDateTimes()+".sql";        try {        // 账号、密码、数据库名我这边是从配置文件中获取的            DatabaseBackup.backup(Global.getConfig("jdbc.username"), Global.getConfig("jdbc.password"), Global.getConfig("jdbc.database"),filename);            addMessage(redirectAttributes, "备份数据库成功！");        } catch (Exception e) {            //e.printStackTrace();            addMessage(redirectAttributes, "备份数据库失败！");        }        return "redirect:" + adminPath + "/sys/database/";    }    /**     * 数据库还原     * @param request     * @param response     * @param redirectAttributes     * @return     */    @RequiresPermissions("sys:database:edit")    @RequestMapping(value = "restore")    public String restore(HttpServletRequest request, HttpServletResponse response,RedirectAttributes redirectAttributes){        String sqlname = request.getParameter("sqlname");        String realPath=request.getServletContext().getRealPath("/")+path;//项目绝对路径        String sqlfile = realPath+"/"+sqlname;         try {            DatabaseBackup.recover(Global.getConfig("jdbc.database"), Global.getConfig("jdbc.username"), Global.getConfig("jdbc.password"), sqlfile);            addMessage(redirectAttributes, "数据库还原成功！");        } catch (Exception e) {            e.printStackTrace();            addMessage(redirectAttributes, "数据库还原失败！");        }        return "redirect:" + adminPath + "/sys/database/";    }    /**     * 删除数据库备份     * @param request     * @param response     * @param redirectAttributes     * @return     */    @RequiresPermissions("sys:database:edit")    @RequestMapping(value = "delete")    public String delete(HttpServletRequest request, HttpServletResponse response,RedirectAttributes redirectAttributes){        String sqlname = request.getParameter("sqlname");        String realPath=request.getServletContext().getRealPath("/")+path;//项目绝对路径        if(FileUtils.deleteFile(realPath+"/"+sqlname)){            addMessage(redirectAttributes, "数据库备份删除成功！");        }else{            addMessage(redirectAttributes, "数据库备份删除失败！");        }        return "redirect:" + adminPath + "/sys/database/";    }}

DataBase.java 实体类

import com.alun.common.persistence.DataEntity;public class DataBase extends DataEntity<DataBase>{    private static final long serialVersionUID = 1L;    private String name;    //sql名称    private Long updatedate;    //文件最后修改时间    private String length;  //文件大小    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Long getUpdatedate() {        return updatedate;    }    public void setUpdatedate(Long updatedate) {        this.updatedate = updatedate;    }    public String getLength() {        return length;    }    public void setLength(String length) {        this.length = length;    }}

DatabaseBackup.java 数据库备份工具类

import java.io.BufferedReader;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStreamReader;import java.io.OutputStream;import java.io.OutputStreamWriter;import org.slf4j.Logger;import org.slf4j.LoggerFactory;/** * mysql数据库备份 * @author ALun * */public class DatabaseBackup {    private static Logger logger = LoggerFactory.getLogger("Mysql数据库备份");    /**     * 备份数据库     * @param user  数据库用户名     * @param pwd   数据库密码     * @param dataname 数据库名     * @param savePath 保存的路径     * @return     * @throws Exception     */    public static void backup(String user,String pwd,String dataname,String savePath) throws Exception {          String[] execCMD = new String[] {"mysqldump", "-uroot", "-proot", "powergrid",                  "-r" + savePath, "--skip-lock-tables"};          Process process = Runtime.getRuntime().exec(execCMD);          int processComplete = process.waitFor();          if (processComplete == 0) {              logger.info(savePath+"备份成功。");        } else {              throw new RuntimeException("备份数据库失败.");        }      }      /**     * 还原数据库       * @param database     * @param user     * @param pwd     * @param sqlfile     * @throws IOException     */    public static void recover(String database,String user,String pwd, String sqlfile) throws IOException{        Runtime runtime = Runtime.getRuntime();        //-u后面是用户名，-p是密码-p后面最好不要有空格，-family是数据库的名字，--default-character-set=utf8，这句话一定的加        //注意程序的和数据库的编码必须要一致。        Process process = runtime.exec("mysql -u "+user+" -p"+pwd+" --default-character-set=utf8 "+database);        OutputStream outputStream = process.getOutputStream();        BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(sqlfile)));        String str = null;        StringBuffer sb = new StringBuffer();        while((str = br.readLine()) != null){            sb.append(str+"\r\n");        }        str = sb.toString();        System.out.println(sqlfile);        //System.out.println(str);        OutputStreamWriter writer = new OutputStreamWriter(outputStream,"utf-8");        writer.write(str);        writer.flush();        outputStream.close();        br.close();        writer.close();    }    public static void main(String[] args) {        try {            recover("a", "root", "root", "E:/workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/webapps/a/database/a_20170629160042.sql");        } catch (Exception e) {            e.printStackTrace();        }    }}

效果图


其中用到了自己写的时间和文件处理工具类就没有写出来了~。~！