java代码备份mysql数据库

生成环境的数据库我们需要不断的进行备份,不然服务器出现故障,会是灾难性的
直接添加我的代码

package com.hrp.task;

import com.alibaba.fastjson.JSON;
import com.base.exception.SysException;
import com.base.util.UUIDLong;
import com.hrp.sys.dao.SysLogMapper;
import com.hrp.sys.service.BackUpService;
import com.hrp.sys.service.SysLogService;
import org.apache.log4j.Logger;
import org.springframework.core.io.support.PropertiesLoaderUtils;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * @Title: com.task
 * @Date: 2020/7/29 5:10
 * @Author: wfg
 * @Description:
 * @Version:
 */
@Service("backUpMysqlScheduled")
@EnableScheduling
public class BackUpMysqlScheduled {
    private static Logger logger = Logger.getLogger(BackUpService.class);

    private static String SAVE_PATH="/ecs/back/";
    private String userName;
    private String password;
    //操作系统
    private String os;
    private String backupIP;
    private String databaseName;
    private String odbcIP;
    private String backPath;
    @Resource(name = "sysLogService")
    private SysLogService sysLogService;
    @Resource(name = "sysLogMapper")
    private final SysLogMapper sysLogMapper = null;


    public void initParame() throws IOException {

        Properties properties = PropertiesLoaderUtils.loadAllProperties("system.properties");
        userName = properties.getProperty("username").toString();
        password = properties.getProperty("password").toString();
        os = properties.getProperty("os").toString();
        backupIP = properties.getProperty("backupip").toString();
        String[] jdbcPars = properties.getProperty("url").toString().split("\\?")[0].split("/");
        databaseName = jdbcPars[3];
        odbcIP=jdbcPars[2].split(":")[0];
        backPath = properties.getProperty("backPath").toString();
    }

    @Scheduled(cron = "0 0 13,23 * * ?")
    public void backUpMysql() {
        System.out.println("backupmysql============================");
        Map<String, Object> resMap = new HashMap<String, Object>();
        Map<String, Object> mapVo=new HashMap<String, Object>();
        this.saveLog(mapVo);
        resMap.put("state", "false");

        try {
            initParame();
            if("win".equals(os)){  //操作系统是window备份程序
                backUpMysqlInWin(resMap);
            }else if("linux".equals(os)){  //操作系统是linux备份程序
                backUpMysqlInLinux(resMap);
            }
        }  catch (Exception e) {
            e.printStackTrace();
            resMap.put("msg", "数据库备份失败！"+e.getMessage());
            throw new SysException(e.getMessage(),e);
        } finally {
            sysLogService.updateSysLogByInterface(mapVo.get("uuid").toString(), JSON.toJSONString(resMap));
        }
    }

    public void backUpMysqlInWin(Map<String, Object> resMap) throws Exception {
        String fileName = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss").format(new Date())+".sql";
        String pathSql = backPath+fileName;
        System.out.println(pathSql);
        File fileSql = new File(pathSql);
        //创建备份sql文件
        if (!fileSql.exists()){
            fileSql.createNewFile();
        }

        //mysqldump -hlocalhost -uroot -p123456 db > /home/back.sql
        StringBuffer sb = new StringBuffer();
        //sb.append(" C:\\Users\\zht>");
        String url = System.getProperty("hrp.root")+"/WEB-INF/classes/";
        //sb.append("E:\\svn\\HRPCloud\\HRP_ECS\\out\\artifacts\\HRP_ECS_Web_exploded\\WEB-INF\\classes\\");
        sb.append(url);
        sb.append("mysqldump");
        sb.append(" -h"+odbcIP);
        sb.append(" -u"+userName);
        sb.append(" -p"+password);
        sb.append(" "+databaseName+" >");
        sb.append(pathSql);
        logger.debug("cmd命令为："+sb.toString());
        Runtime runtime = Runtime.getRuntime();
        logger.debug("开始备份："+databaseName);
        Process p = runtime.exec("cmd /c"+sb.toString());
        if(p.waitFor()==0){
            resMap.put("state", "true");
            resMap.put("msg", "数据库备份成功！");
        } else {
            resMap.put("state", "false");
            resMap.put("msg", "数据库备份失败！线程异常终止");
        }
    }
    public void backUpMysqlInLinux(Map<String, Object> resMap) throws Exception {
        String fileName = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss").format(new Date())+".gz";
        File saveFile = new File(SAVE_PATH);
        if (!saveFile.exists()) {// 如果目录不存在
            saveFile.mkdirs();// 创建文件夹
        }
        //mysqldump -uroot -pDhcc1024 hrp_ecs > /ecs/back/.sql
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("mysqldump");
        stringBuilder.append(" -h").append(odbcIP).append(" -u").append(userName).append(" -p").append(password).append(" ").append(databaseName).append(" | gzip");
        stringBuilder.append(" > ").append(SAVE_PATH + fileName);
        Process process = Runtime.getRuntime().exec(new String[] {"sh", "-c", stringBuilder.toString() });
        if (process.waitFor() == 0) {// 0 表示线程正常终止。
            resMap.put("msg", "数据库备份成功！");
            //scp /root/.ssh/id_rsa [email protected]:/backup/data/
            StringBuilder stringScpBuilder = new StringBuilder();
            stringScpBuilder.append("scp");
            stringScpBuilder.append(" ").append(SAVE_PATH+fileName).append(" ").append("root@"+backupIP).append(":/ecsbackup/data/");
            Process processScp = Runtime.getRuntime().exec(new String[] {"sh", "-c", stringScpBuilder.toString() });
            if (processScp.waitFor() == 0) {// 0 表示线程正常终止。
                resMap.put("state", "true");
                resMap.put("msg", "数据库备份成功！");
                //删除昨天的sql文件
                //deleteYestdaySql();
            }
        } else {
            resMap.put("state", "false");
            resMap.put("msg", "数据库备份失败！线程异常终止");
        }
    }
    public void saveLog( Map<String, Object> mapVo){

        String logId= UUIDLong.absStringUUID();
        mapVo.put("uuid",logId );
        mapVo.put("opuser", 0);
        mapVo.put("optype", 2); //2 任务
        mapVo.put("opcode", "backUpMysql");
        mapVo.put("oparg", "");
        sysLogMapper.saveSysLogByInterface(mapVo);
    }

}

window系统下进行备份的时候出现了几个问题

1.用户没有权限的问题根据错误代码进行百度解决即可
2.可以访问但是我们备份的文件为空的问题
a.将mysql安装目录下的/bin\mysqldump.exe 拷贝到项目中
b. 路径中不能有特殊字符,空格之类的,由于mysql直接默认安装在\Program Files 有空格不可以