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.ponent;import java.io.File;import java.io.IOException;import java.time.LocalDate;import java.time.LocalDateTime;/*** 数据库定时备份任务* 在backup文件夹中备份最近七日的数据库文件, 备份文件夹 与当前程序同一目录*/@Component@Slf4jpublic class DbJob {private final Environment environment;public DbJob(Environment environment) {this.environment = environment;}/*** 定时时间是每天凌晨5点。* <h3>cron 标识符</h3>* 从左到右用空格隔开分别是：秒 分 时 日 月 周 年(可省略)<br/>* <p>* 第一个位置：Seconds 秒：区间 0-59 秒，代表一分钟内的秒数。<br/>* <p>* 第二个位置：Minutes 分：区间 0-59 分，代表一小时内的分钟数。<br/>* <p>* 第三个位置：Hours 时：区间 0-23 时，代表一天中的小时数。<br/>* <p>* 第四个位置：Day of month 日：区间 1-31 (?根据每月有多少天来)，代表一月中的多少号。<br/>* <p>* 第五个位置：Month 月：区间 1-12 ，代表一年中的月份。<br/>* <p>* 第六个位置：Day of week 周：区间 1-7或者英文星期的缩写，代表星期几。<br/>*/@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);// IPString 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);// 删除七天前数据库备份文件 LocalDateLocalDate 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 {/*** 导出sql文件** @param hostip地址* @param port端口* @param userName 用户名* @param password 密码* @param dbName 数据库名* @param file文件对象*/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")){// Windows 需要加上 cmd /ccmd = "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();}}/*** 导入sql文件** @param host ip地址* @param port 端口* @param userName用户名* @param password密码* @param databaseName 数据库名* @param file 文件对象*/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")){// Windows 需要加上 cmd /ccmd = "cmd /c " + cmd;}System.out.printf("数据库还原命令：%s%n", cmd);//拼接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);}}/*** 导入sql文件** @param file文件对象* @param user用户* @param password 密码* @param db 数据库*/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();}}/*public static void main(String[] args) throws Exception {File file = new File("C:\\Users\\hansonh\\Desktop\\jar", "backup.sql");System.out.println("系统环境：" + System.getProperties().getProperty("os.name"));String subStr = "jdbc:mysql://127.0.0.1:3306/pms?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&verifyServerCertificate=false&useSSL=false&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull";// 第三个 ：号下标int subStrIndex = subStr.indexOf(":", subStr.indexOf(":", subStr.indexOf(":") + 1) + 1);// IPString host = subStr.substring(subStr.indexOf("//") + 2, subStrIndex);System.out.println("IP：" + host);// 端口String subStr2 = subStr.substring(subStrIndex);String port = subStr2.substring(1, subStr2.indexOf("/"));System.out.println("端口：" + port);// 数据库名String dataBaseName = subStr2.substring(subStr2.indexOf("/") + 1, subStr2.indexOf("?"));System.out.println("数据库名：" + dataBaseName);// 备份数据库DbUtil.backup( "127.0.0.1","3306", "dev1", "dev1", "pms", file);// 恢复数据库// DbUtil.reduction( "127.0.0.1","3306", "root", "123456", "pms", file);}*/}
Java定时备份MySql数据库 Java定时备份数据库 Java备份MySql数据库 java定时备份mysql数据库 java备份mysql数据库
