How to Backup and Restore MySQL Databases in Spring Boot

Back-end engineers or site reliability engineers often need to backup databases to ensure that the data can be restored when the hard drive is damaged. Apart from manually backing up data, we can write a scheduler to regularly backup databases. Let’s take a loot how to backup and restore databases in a Spring Boot application.

Bankup

The database backup is mainly achieved by using the mysqldump command. mysqldump exports database data into .sql files. It has many parameters. The following is the basic usage:

% mysqldump -uroot -p123456 --add-drop-table --database Example -r example.sql

• -uroot: root is your MySQL login username。
• -p123456: 123456 is your MySQL login password。
• –add-drop-table: Write DROP TABLE in the output file。
• –database: Example is the name of the database you want to export。
• -r example.sql: example.sql is the output file。

You can test whether the instruction is correct on the command line first. After confirming that the instruction is correct, we can start to execute the command with Spring Boot.

Create DatabaseUtil, in which we implement backup() method. It use Runtime.getRuntime().exec() to execute the command.

public final class DatabaseUtil {
    public static boolean backup(String dbUsername, String dbPassword, String dbName, String outputFile)
            throws IOException, InterruptedException {
        String command = String.format("mysqldump -u%s -p%s --add-drop-table --databases %s -r %s",
                dbUsername, dbPassword, dbName, outputFile);

        Process process = Runtime.getRuntime().exec(command);
        int processComplete = process.waitFor();
        return processComplete == 0;
    }
}

Then, we can implement RestController to let the front-end initiate a backup job, or implement a scheduler to schedule backup jobs regularly.

Restore

Like backup, restore is also achieved by a command. It can be restored with the mysql command. The usage is as follows:

% mysql -uroot -p123456 -e "source example.sql"

• -e command, –execute=command: This option means to execute the following SQL statement.
• source example.sql: This SQL statement means to execute all SQL statements in example.sql.

So, importing data is actually executing all the SQL statements in the .sql file. This is why we add --add-drop-table when exporting databases,. Otherwise, when the .sql file is executed, an error will occur in it CREATE TABLE, because the table already exists, so this CREATE TABLE statement will fail. Without DROP TABLE, you will get errors while executing CREATE TABLE because the table already exists.

Add restore()method in DatabaseUtil. It also uses Runtime.getRuntime().exec() to execute the command. That is because source example.sql is an argument, you can not execute it by put all arguments into a string.

public final class DatabaseUtil {
    public static boolean restore(String dbUsername, String dbPassword, String dbName, String sourceFile)
            throws IOException, InterruptedException {
        String[] command = new String[]{
                "mysql",
                "-u" + dbUsername,
                "-p" + dbPassword,
                "-e",
                " source " + sourceFile,
                dbName
        };

        Process runtimeProcess = Runtime.getRuntime().exec(command);
        int processComplete = runtimeProcess.waitFor();
        return processComplete == 0;
    }
}

Finally, implement a RestController to let the front-end initiate a restoration job.