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 root@192.144.144.119:/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 有空格不可以
