java代码定时备份mysql数据库及注意事项——基于 springboot

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>

<groupId>com.kevin</groupId>
<artifactId>backupdatabase</artifactId>
<version>1.0-SNAPSHOT</version>

<packaging>jar</packaging>
<properties>
<java.version>1.8</java.version>
</properties>

<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>2.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.awaitility</groupId>
<artifactId>awaitility</artifactId>
<version>3.1.2</version>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>

spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.100.120:3306/test01?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
username: kevin02
password: Kevin123!sqlbackup:
path: /export/servers/db_backup/

package com.kevin.manager;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;

/**
* @author dell
* @version： task.java v 1.0, 2020年01月18日 12:47
* @Description 数据库操作
**/
@Service
public class BackUpDataBaseManager {

private static final Logger log = LoggerFactory.getLogger(BackUpDataBaseManager.class);

@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.username}")
private String userName;
@Value("${spring.datasource.password}")
private String password;

@Value("${sqlbackup.path}")
private String sqlPath;

/**
* 获取数据库名
*/
public String getDataBaseName() {
return url.substring(url.indexOf("3306"), url.indexOf("?")).replaceAll("/", "").replaceAll("3306", "");
}

/**
* 获取主机地址
*/
private String getHost() {
return url.substring(url.indexOf("mysql"), url.indexOf("3306")).replace(":", "").replace("//", "").replace("mysql", "");
}

/**
* 导出 sql 并返回相关信息
*/
public void exportSql(String time) {
// 指定导出的 sql 存放的文件夹
File saveFile = new File(sqlPath);
if (!saveFile.exists()) {
saveFile.mkdirs();
}

String host = getHost();
String dataBaseName = getDataBaseName();
String fileName = time + "_" + "cloudpm.sql";

StringBuilder sb = new StringBuilder();
// 拼接备份命令
sb.append("mysqldump").append(" --opt").append(" -h ").append(host).append(" --user=").append(userName).append(" --password=").append(password);
sb.append(" --result-file=").append(sqlPath + fileName).append(" --default-character-set=utf8 ").append(dataBaseName);

try {
Process exec = Runtime.getRuntime().exec(sb.toString());
if (exec.waitFor() == 0) {
log.info("数据库备份成功，保存路径：" + sqlPath);
} else {
System.out.println("process.waitFor()=" + exec.waitFor());
}
} catch (IOException e) {
log.error("备份 数据库 出现 IO异常 ", e);
} catch (InterruptedException e) {
log.error("备份 数据库 出现 线程中断异常 ", e);
} catch (Exception e) {
log.error("备份 数据库 出现 其他异常 ", e);
}
}
}

package com.kevin.schedule;

import com.kevin.manager.BackUpDataBaseManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;

/**
* @author dell
* @version： ScheduledTasks.java v 1.0, 2020年02月11日 11:38
* @Description 定时任务
**/
@Component
public class ScheduledTasks {

private static final Logger log = LoggerFactory.getLogger(ScheduledTasks.class);

private static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSS");

@Autowired
private BackUpDataBaseManager backUpDataBaseManager;

/**
* 每天下午4点50分30秒执行
*/
@Scheduled(cron = "30 50 16 * * ?")
public void reportCurrentTime() {
String format = dateFormat.format(System.currentTimeMillis());
log.info("The time is now {}", format);
backUpDataBaseManager.exportSql(format);
}
}

package com.kevin;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableScheduling;/**
* @author dell
* @version： TaskAppliacation.java v 1.0, 2020年02月11日 12:12
* @Description
**/
@SpringBootApplication
@EnableScheduling
public class TaskApplication { public static void main(String[] args) {
SpringApplication.run(TaskApplication.class);
}
}