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();
        }
    }

}

效果图


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