Java+xxl-job实现MySql的定时备份

原创已于 2024-12-23 10:18:31 修改 · 397 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java #spring boot

于 2024-12-23 10:18:01 首次发布

java 专栏收录该内容

9 篇文章

订阅专栏

原因

当实现数据库备份的时候又不想在服务器以脚本文件的形式暴露数据库账号密码时，我们可以使用java代码来运行shell脚本代码（不生成脚本文件）实现mysql的dumb文件的生成。

准备

我这里的定时任务是交给xxl-job来配置的，这里不多叙说了，不清楚的可以使用注解@Scheduled来实现定时调用。

实现

1.pom文件

<?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.5.7</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>org.example.activiti</groupId>
    <artifactId>ttoa</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.4</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!-- thymeleaf和springSecurity的扩展依赖-->
        <dependency>
            <groupId>org.thymeleaf.extras</groupId>
            <artifactId>thymeleaf-extras-springsecurity5</artifactId>
        </dependency>

        <dependency>
            <groupId>com.xuxueli</groupId>
            <artifactId>xxl-job-core</artifactId>
            <version>2.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.2</version>
        </dependency>

        <!-- mybatis-plus代码生成器依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.2</version>
        </dependency>

        <!-- 默认模板引擎 -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.1</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-extension</artifactId>
            <version>3.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <finalName>${project.artifactId}-${project.version}</finalName>
        <defaultGoal>package</defaultGoal>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <!-- 如果加了上面的配置，必须加入这个配置，不然resources目录下的东西不会进编译目录 -->
                    <include>**/*.*</include>
                </includes>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <includeSystemScope>true</includeSystemScope>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                    <!-- 指定该Main Class为全局的唯一入口 -->
                    <mainClass>com.example.ActivitiApplication</mainClass>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>9</source>
                    <target>9</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.application.yml文件

spring:
  #thymeleaf模板引擎的配置
  thymeleaf:
    #开发环境，不建议开启缓存
    cache: false
  ####################################数据源配置#######################################
  datasource:
    druid:
      #数据源配置
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://19.12.100.123:3306/oa?useSSL=false&serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&rewriteBatchedStatements=true&connectTimeout=1000&socketTimeout=30000&allowMultiQueries=true
      username: root
      password: ****

####################################自定义文件配置###################################
sqlbackup:
  path: /home/oa/files/sqlDumb/

3.BackupsMysqlJob数据库备份实现

package com.example.task;

import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.annotation.XxlJob;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.IOException;
import java.util.Date;

/**
 * <p>
 * 定时备份dumb文件
 * </p>
 *
 * @custom.date 2024/12/20 16:32
 */
@Component
public class BackupsMysqlJob {

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

    @Value("${spring.datasource.druid.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.druid.url}")
    private String url;
    @Value("${spring.datasource.druid.username}")
    private String userName;
    @Value("${spring.datasource.druid.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 并返回相关信息
     */
    @XxlJob("regularBackupsMysql")
    public ReturnT<String> regularBackupsMysql() {
        //如果文件超过3天删除最早的文件
        // 获取文件夹路径
        File folder = new File(sqlPath);
        // 获取文件夹中的所有文件
        File[] files = folder.listFiles();
        if(files.length>3){
            // 初始化最早文件
            File earliestFile = files[0];
            // 遍历所有文件，找到最早的文件
            for (File file : files) {
                if (file.lastModified() < earliestFile.lastModified()) {
                    earliestFile = file;
                }
            }
            // 删除最早的文件
            earliestFile.delete();
        }
        // 指定导出的 sql 存放的文件夹
        File saveFile = new File(sqlPath);
        if (!saveFile.exists()) {
            saveFile.mkdirs();
        }
        String host = getHost();
        String dataBaseName = getDataBaseName();
        String fileName = new Date().getTime()+"_"+dataBaseName+ ".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);
                return ReturnT.SUCCESS;
            } else {
                System.out.println("process.waitFor()=" + exec.waitFor());
            }
        } catch (IOException e) {
            log.error("备份 数据库 出现 IO异常 ", e);
            return ReturnT.FAIL;
        } catch (InterruptedException e) {
            log.error("备份 数据库 出现 线程中断异常 ", e);
            return ReturnT.FAIL;
        } catch (Exception e) {
            log.error("备份 数据库 出现 其他异常 ", e);
            return ReturnT.FAIL;
        }
        return ReturnT.SUCCESS;
    }
}

我这里额外添加了文件限制在4个之内的代码，超过3个的时候会删除最早备份的文件。

思路：使用

mysqldump --opt -h hostname --user=username --password=password --result-file=/dir/filename --default-character-set=utf8 dbname

命令

hostname ：数据库所在主机

username：数据库连接用户名

password：数据库连接密码

result-file：结果文件。指定目录+文件名

dbname：需要导出的数据库名