package com.yuanchu.mom.backup;
|
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.scheduling.annotation.EnableScheduling;
|
import org.springframework.scheduling.annotation.Scheduled;
|
import org.springframework.stereotype.Component;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
|
@Component
|
@EnableScheduling
|
@Slf4j
|
public class MysqlDataBackup {
|
/**
|
* 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要
|
*/
|
Boolean isDbVersion8 = false;
|
|
/**
|
* 备份命令
|
* USERNAME 账号
|
* PASSWORD 密码
|
* SERVERPATH 服务器IP/域名
|
* DBNAME 数据库名称
|
* FILEPATH 备份文件存放地址+名称
|
* 说明
|
* cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)）
|
* cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装)
|
* --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错
|
*/
|
String cmdMysql8 = "mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVERPATH} -P3306 --databases {DBNAME}"; // > {FILEPATH}.sql
|
String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVERPATH} -P3306 --databases {DBNAME}"; // > {FILEPATH}.sql
|
|
/**
|
* 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限)
|
*/
|
@Value("${backup.path}")
|
private String filePath;
|
|
@Value("${spring.datasource.url}")
|
private String dbUrl;
|
|
@Value("${spring.datasource.username}")
|
private String dbUserName;
|
|
@Value("${spring.datasource.password}")
|
private String dbPassWord;
|
|
@Value("${backup.destiny}")
|
private Integer destiny;
|
|
@Value("${backup.mysqldump}")
|
private String mysqldump;
|
|
/**
|
* 每天晚上23点05秒执行 【 0 0 4 1/1 * ? 】
|
* 测试 20 秒一次【 0/20 * * * * ? 】@Scheduled(cron = "5 * 23 * * ?")
|
*/
|
@Scheduled(cron = "5 0 23 * * ?")
|
private void configureTasks() {
|
log.info("【备份数据库】--START");
|
String dbUrl2 = dbUrl.replace("jdbc:mysql://", "");
|
|
// 获取数据库名称
|
String dbName = dbUrl2.substring(dbUrl2.lastIndexOf("/") + 1, dbUrl2.indexOf("?"));
|
// 获取数据库地址
|
String serverPath = dbUrl2.substring(0, dbUrl2.lastIndexOf(":"));
|
// 数据库账号
|
String username = dbUserName;
|
// 数据库密码
|
String password = dbPassWord;
|
|
// 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql)
|
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
|
String format = simpleDateFormat.format(new Date());
|
String timeStr = format
|
.replaceAll("-", "_")
|
.replaceAll(" ", "_")
|
.replaceAll(":", "");
|
timeStr = timeStr.substring(0, 15);
|
String pathFileName = filePath + "/" + dbName + "_" + timeStr + ".sql";
|
String newCmd = "";
|
if (isDbVersion8) {
|
newCmd = cmdMysql8;
|
} else {
|
newCmd = cmd;
|
}
|
// 执行命令
|
newCmd = newCmd.replace("{USERNAME}", username)
|
.replace("{PASSWORD}", password)
|
.replace("{SERVERPATH}", serverPath)
|
.replace("{DBNAME}", dbName)
|
.replace("{FILEPATH}", pathFileName);
|
PrintWriter printWriter = null;
|
BufferedReader bufferedReader = null;
|
try {
|
// 创建存放sql的文件
|
existsFile(new File(pathFileName));
|
printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8"));
|
Process process = null;
|
String property = System.getProperty("os.name");
|
System.out.println(property);
|
if (property.indexOf("Linux") != -1) {
|
// linux
|
process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd});
|
} else {
|
// 本地win
|
String mysqldumpPath = "cmd /c " + mysqldump + "/" + newCmd;
|
System.out.println(mysqldumpPath);
|
process = Runtime.getRuntime().exec(mysqldumpPath);
|
}
|
InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
|
bufferedReader = new BufferedReader(inputStreamReader);
|
String line;
|
while ((line = bufferedReader.readLine()) != null) {
|
printWriter.println(line);
|
}
|
// 此次会执行过长时间,直到备份完成
|
printWriter.flush();
|
printWriter.close();
|
//0 表示线程正常终止。
|
if (process.waitFor() == 0) {
|
// 线程正常执行
|
log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
log.info("【备份数据库】FAILURE");
|
} finally {
|
try {
|
if (bufferedReader != null) {
|
bufferedReader.close();
|
}
|
if (printWriter != null) {
|
printWriter.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
log.info("【备份数据库】--END");
|
}
|
|
|
/**
|
* 每天晚上23点0分10秒执行 【 0 0 4 1/1 * ? 】
|
* 测试 20 秒一次【 0/20 * * * * ? 】
|
*/
|
@Scheduled(cron = "6 0 23 * * ?")
|
private void TimerDeleteFile(){
|
Date date = new Date();
|
SimpleDateFormat sdf = new SimpleDateFormat("d");
|
Integer currentDay = Integer.valueOf(sdf.format(date));
|
File file = new File(filePath);
|
File[] files = file.listFiles();
|
if (files != null) {
|
for(File f : files){
|
if(f.isFile()){//若是文件，直接打印
|
String[] splitFile = f.getName().split("_");
|
Integer fileDay = Integer.valueOf(splitFile[splitFile.length - 2]);
|
Integer i = currentDay - fileDay;
|
if (i.equals(destiny)){
|
f.delete();
|
log.info("备份sql文件过多进行删除！");
|
}
|
}
|
}
|
}
|
}
|
|
/**
|
* 判断文件是否存在，不存在创建
|
*/
|
private static void existsFile(File file) {
|
// 判断文件路径是否存在,不存在新建
|
if (!file.getParentFile().exists()) {
|
file.getParentFile().mkdirs();
|
}
|
if (!file.exists()) {
|
try {
|
file.createNewFile();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
