mysqldump is a database backup program provided by mysql. It dumps the data stored in the database into SQL statements that are output to standard output.

Basic Usage:

1

mysqldump -u[username] -p[password] [database] > [sql file]

The > symbol is a redirector under linux, redirecting the standard output to a file.

mysqldump You can refer to the official documentation for more detailed usage.

Use in spring boot applications

Start a new subprocess via commons-exec to execute mysqldump to complete the backup.

The use of commons-exec is simple. It includes features such as subprocess timeout control, asynchronous execution, and more.

1
2
3
4
5
6

<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-exec -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-exec</artifactId>
    <version>1.3</version>
</dependency>

DataBaseBackUpTest

 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

package io.springcloud.test;



import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.TimeUnit;

import org.apache.commons.exec.CommandLine;
import org.apache.commons.exec.DefaultExecutor;
import org.apache.commons.exec.ExecuteWatchdog;
import org.apache.commons.exec.PumpStreamHandler;
import org.junit.Test;
import org.junit.runner.RunWith;
import io.springcloud.BackUpTestApplication;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.junit4.SpringRunner;

import com.zaxxer.hikari.HikariDataSource;

import lombok.extern.slf4j.Slf4j;


@RunWith(SpringRunner.class)
@SpringBootTest(classes = BackUpTestApplication.class, webEnvironment = WebEnvironment.RANDOM_PORT)
@Slf4j
public class DataBaseBackUpTest {

    @Autowired
    private HikariDataSource hikariDataSource;

    @Test
    public void test () throws Exception {
        
        // sql file
        Path sqlFile = Paths.get("C:\\Users\\KevinBlandy\\Desktop\\db.sql");
        
        String database = null;

        // Execute SQL to get the name of the current database
        try (Connection connection = hikariDataSource.getConnection()) {
            try (ResultSet resultSet = connection.createStatement().executeQuery("SELECT DATABASE();")) {
                if (resultSet.next()) {
                    database = resultSet.getString(1);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // stdErr
        ByteArrayOutputStream stdErr = new ByteArrayOutputStream();
        
        
        // stdOut
        OutputStream stdOut = new BufferedOutputStream(Files.newOutputStream(sqlFile, StandardOpenOption.CREATE, StandardOpenOption.TRUNCATE_EXISTING));
        
        try (stdErr; stdOut) {

            // Watchdog. The execution timeout is 1 hour.
            ExecuteWatchdog watchdog = new ExecuteWatchdog(TimeUnit.HOURS.toMillis(1));
            
            DefaultExecutor defaultExecutor = new DefaultExecutor();
            defaultExecutor.setWatchdog(watchdog);
            defaultExecutor.setStreamHandler(new PumpStreamHandler(stdOut, stdErr));

            CommandLine commandLine = new CommandLine("mysqldump");
            commandLine.addArgument("-u" + hikariDataSource.getUsername()); // username
            commandLine.addArgument("-p" + hikariDataSource.getPassword()); // password
            commandLine.addArgument(database); // database

            log.info("Exporting SQL data...");
            
            // Synchronous execution. Blocking until the execution of the child process is complete.
            int exitCode = defaultExecutor.execute(commandLine);
            
            if(defaultExecutor.isFailure(exitCode) && watchdog.killedProcess()) {
                log.error("timeout...");
            }
            
            log.info("SQL data export completed: exitCode={}, sqlFile={}", exitCode, sqlFile.toString());

        } catch (Exception e) {
            log.error("SQL data export exception: {}", e.getMessage());
            log.error("std err: {}{}", System.lineSeparator(), stdErr.toString());
        }
    }
}

Obviously, this backup method only applies to mysql, and the mysql service is on the same machine as the current application.