Java定时备份MySql数据库，定时删除数据库备份文件，数据库备份文件恢复

1：配置数据库定时备份，删除等参数

backup:
  datasource:
    ip: 127.0.0.1
    db: jeecg_test_local   #定义数据库名
    table: ntcp_data_      #定义数据库表名
    port: 3306
    username: root
    password: root
  backupDay: 2              #备份多少天前的数据库数据
  deleteDay: 2              #删除多少天前数据库备份文件
  backupWPath: D:\backup    #Windows备份路径
  backupLPath: home/backup  #Linux备份路径
1
2
3
4
5
6
7
8
9
10
11
12

2：先写一个mysql操作工具类

package com.hujy.demo.utils;

import lombok.extern.slf4j.Slf4j;

import java.io.*;
import java.nio.charset.StandardCharsets;

/**
 * @Author xu
 * @create 2022/11/19
 */

@Slf4j
public class DbUtil {

    public static void backup(File file, String ip, String user, String password, String dbTable) {
        try {
            //要配置好mysql的环境变量，如果链接远程需要注意修改ip地址，还有支持访问端口开通
            Runtime rt = Runtime.getRuntime();
            String command = "mysqldump --single-transaction -h" + ip + " -u" + user + " -p" + password + " --set-charset=utf8 " + dbTable;
            log.info("数据库备份执行命令为：{}", command);
            Process child = rt.exec(command);
            InputStream inputStream = child.getInputStream();
            try(BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
            OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8);){
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                outputStreamWriter.write(str + "\r\n");
            }
            outputStreamWriter.flush();}
            inputStream.close();
        } catch (Exception e) {
            log.error("数据库备份错误,错误信息{}", e.getMessage(), e);
            e.printStackTrace();
        }
    }

    public static void load(File file, String ip, String user, String password, String db ) {
        try {
            Runtime rt = Runtime.getRuntime();
            String command = "mysql -h" + ip + " -u" + user + " -p" + password + " --default-character-set=utf8 " + db ;
            log.info("数据库还原执行命令为：{}", command);
            Process child = rt.exec(command);
            OutputStream outputStream = child.getOutputStream();
            try(BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(file), 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();
        } catch (Exception e) {
            log.error("数据库恢复错误,错误信息{}", e.getMessage(), e);
            e.printStackTrace();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

3：java代码操作数据库定时备份，定时删除

package com.hujy.demo.service.impl;

import com.hujy.demo.utils.DbUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.env.Environment;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.time.LocalDate;
import java.util.Objects;

/**
 * Description
 *
 * @author xu
 * @date 2022-12-19 10:47
 */
@Slf4j
@Component
@EnableScheduling
public class DbHistoryConfig {


    /**
     * 数据库用户名
     */
    @Value("${backup.datasource.username}")
    private String user;

    /**
     * 数据库密码
     */
    @Value("${backup.datasource.password}")
    private String password;

    /**
     * 数据库地址
     */
    @Value("${backup.datasource.ip}")
    private String ip;

    /**
     * 数据库
     */
    @Value("${backup.datasource.db}")
    private String db;

    /**
     * 表名
     */
    @Value("${backup.datasource.table}")
    private String table;

    /**
     * 备份多少天前的数据库数据
     */
    @Value("${backup.backupDay}")
    private String backupDay;

    /**
     * 删除多少天前数据库备份
     */
    @Value("${backup.deleteDay}")
    private String deleteDay;

    /**
     * Windows备份路径
     */
    @Value("${backup.backupWPath}")
    private String backupWPath;

    /**
     * Linux备份路径
     */
    @Value("${backup.backupLPath}")
    private String backupLPath;

    @Scheduled(cron = "*/10 * * * * * ")
    private void dbBackup() throws IOException {
        log.info("系统开启定时任务数据库振动器表备份");

        // 当前时间 年月日格式
        LocalDate localDate = LocalDate.now();
        LocalDate backupbefore = localDate.minusDays(Long.parseLong(backupDay));

        // 如果是windows的路径是\\，linux的路径是/ ，例子path是文件路径的前缀，filename是文件的名字
        String pathName;
        if (isWindows()) {
            pathName = backupWPath + "\\";
        } else {
            pathName = backupLPath + "/";
        }


        for (int i = 0; i < 24; i++) {
            // 备份文件名称
            String fileBeforeName = table + backupbefore.toString().replaceAll("-", "");
            File file = circul(pathName, i, fileBeforeName);
            file.getParentFile().mkdirs();
            file.createNewFile();
            // 备份多少天前的数据数据库
            DbUtil.backup(file, ip, user, password, db + " " + file.getName().toString().replaceAll(".sql", ""));
        }

        // 删除多少天前数据库备份文件
        LocalDate before = localDate.minusDays(Long.parseLong(deleteDay));
        for (int i = 0; i < 24; i++) {
            String fileBeforeName = table + before.toString().replaceAll("-", "");
            File file = circul(pathName, i, fileBeforeName);
            if (file.exists()) {
                file.delete();
            }
        }
        log.info("系统结束定时任务数据库备份");

    }

    private File circul(String pathName, int i, String fileBeforeName) {
        String hour;
        if (i < 10) {
            hour = "0" + i;
            fileBeforeName = fileBeforeName + (hour + ".sql");
        } else {
            hour = "" + i;
            fileBeforeName = fileBeforeName + (hour + ".sql");
        }
        return new File(pathName, fileBeforeName);
    }


    /**
     * @param fileName 文件名，根据具体情况修饰
     * 传时间过来 例如 2022111917
     * 返回true恢复成功，false恢复失败
     */

    public Boolean fileRenew(String fileName) {

        String pathName;
        if (isWindows()) {
            pathName = backupWPath + "\\";
        } else {
            pathName = backupLPath + "/";
        }
        File fileRenew = new File(pathName + table + fileName + ".sql");
        return DbUtil.load(fileRenew, ip, user, password, db);
    }

    private static boolean isWindows() {
        return System.getProperty("os.name").toLowerCase().contains("windows");
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

4：数据库恢复，要调用DbHistoryConfig.fileRenew(String arg)方法

dbHistoryConfig.fileRenew("2022111702");
1

5：最简单的做法

    public static void main(String[] args) {
        String username = "your_username";
        String password = "your_password";
        String database = "your_database";
        String backupPath = "/app/backup/luhe.sql";

        try {
            ProcessBuilder processBuilder = new ProcessBuilder(
                "mysqldump",
                "-u", username,
                "-p" + password,
                database,
                "-r", backupPath
            );

            Process process = processBuilder.start();
            int exitCode = process.waitFor();

            if (exitCode == 0) {
                System.out.println("数据库备份成功！");
            } else {
                System.out.println("数据库备份失败！");
            }
        } catch (IOException | InterruptedException e) {
            e.printStackTrace();
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

6：通过命令直接备份，可以发现是否有任何问题，可以发现密码不对，数据库不对，缺少允许库等

mysqldump --single-transaction -h192.168.11.50 -uroot -proot --default-character-set=utf8 jeecg-boot-luhe > test.sql
1