1、定时任务类
import com.mh.jishi.util.DbUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
@Component
@Slf4j
public class DbJob {
private final Environment environment;
public DbJob(Environment environment) {
this.environment = environment;
}
@Scheduled(cron = "0 0 5 * * ?")
public void backup() throws IOException {
LocalDateTime now = LocalDateTime.now();
log.info("*******************时间:【{}】, 系统开启定时任务数据库备份*******************", now);
String user = environment.getProperty("spring.datasource.druid.username");
String password = environment.getProperty("spring.datasource.druid.password");
String url = environment.getProperty("spring.datasource.druid.url");
int subStrIndex = url.indexOf(":", url.indexOf(":", url.indexOf(":") + 1) + 1);
String host = url.substring(url.indexOf("//") + 2, subStrIndex);
String subStr2 = url.substring(subStrIndex);
String port = subStr2.substring(1, subStr2.indexOf("/"));
String dataBaseName = subStr2.substring(subStr2.indexOf("/") + 1, subStr2.indexOf("?"));
String os = System.getProperties().getProperty("os.name");
log.info("备份环境信息:【{}】, 用户名:【{}】,密码:【{}】, 地址:【{}】, 端口:【{}】,数据库:【{}】", os, url, password, host, port, dataBaseName);
LocalDate localDate = LocalDate.now();
String fileName = localDate + ".sql";
File file = new File("backup", fileName);
file.getParentFile().mkdirs();
file.createNewFile();
DbUtil.backup("127.0.0.1", "3306", user, password, dataBaseName, file);
LocalDate before = localDate.minusDays(7);
String fileBeforeName = before + ".sql";
File fileBefore = new File("backup", fileBeforeName);
if (fileBefore.exists()) {
fileBefore.delete();
}
log.info("*******************时间:【{}】, 系统结束定时任务数据库备份*******************", now);
}
}
2、备份工具类
package com.mh.jishi.util;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
public class DbUtil {
public static void backup(String host, String port, String userName, String password, String dbName, File file) {
String cmd = "mysqldump --single-transaction" + " -h" + host + " -P" + port + " -u" + userName + " -p" + password + " " + dbName + " > " + file.getPath();
String os = System.getProperties().getProperty("os.name");
if(os.contains("Windows")){
cmd = "cmd /c " + cmd;
}
System.out.printf("cmd命令为:%s%n", cmd);
try {
Process process = Runtime.getRuntime().exec(cmd);
if (process.waitFor() == 0) {
System.out.printf(" 数据库:%s 备份成功!%n", dbName);
} else {
System.out.printf(" 数据库:%s 备份失败!%n", dbName);
}
} catch (IOException | InterruptedException e) {
e.printStackTrace();
}
}
public static void reduction(String host, String port, String userName, String password, String databaseName, File file) throws Exception {
if (!file.exists()) {
System.out.printf("文件:%s 不存在,请检查%n", file.getPath());
return;
}
String filePath = file.getPath();
String cmd = "mysql -h" + host + " -P" + port + " -u" + userName + " -p" + password + " " + databaseName + " < " + filePath;
String os = System.getProperties().getProperty("os.name");
if(os.contains("Windows")){
cmd = "cmd /c " + cmd;
}
System.out.printf("数据库还原命令:%s%n", cmd);
Process exec = Runtime.getRuntime().exec(cmd);
if (exec.waitFor() == 0) {
System.out.printf("数据库:%s 还原成功,还原的文件为:%s%n", databaseName, filePath);
} else {
System.out.println(databaseName + "数据库还原失败");
System.out.printf("数据库:%s 还原失败", databaseName);
}
}
public static void load(File file, String user, String password, String db) {
try {
Runtime rt = Runtime.getRuntime();
String command = "mysql -u" + user + " -p" + password + " --default-character-set=utf8 " + db;
Process child = rt.exec(command);
OutputStream outputStream = child.getOutputStream();
BufferedReader bufferedReader = new BufferedReader(
new InputStreamReader(Files.newInputStream(file.toPath()), StandardCharsets.UTF_8));
OutputStreamWriter outputStreamWriter = new OutputStreamWriter(outputStream, StandardCharsets.UTF_8);
String str;
while ((str = bufferedReader.readLine()) != null) {
outputStreamWriter.write(str + "\r\n");
}
outputStreamWriter.flush();
outputStream.close();
bufferedReader.close();
outputStreamWriter.close();
System.out.println("数据库导入完成");
} catch (Exception e) {
e.printStackTrace();
}
}
}