How to Back Up a MySQL Database Programmatically Using mysql-backup4j

In this article, we're going to be looking at mysql-backup4j, a very flexible Java library that we can use to back-up our database periodically.

Once our app is in production, we can't afford to not have a timely backup in case of eventualities. Usually, what makes the process somewhat arduous is if we have to manually trigger the process all the time.

Imagine a scenario where we have both automated and manual process of database backup - that's what we're about to do.

Dependency Installation

Let's add the dependency to our project's pom.xml:

<dependency>
    <groupId>com.smattme</groupId>
    <artifactId>mysql-backup4j</artifactId>
    <version>1.0.0</version>
</dependency>

The latest version can be found here.

Exporting MySQL Database Programmatically

Exporting a MySQL database programmatically is very straightforward with mysql-backup4j. We only need to instantiate it and pass it a Java Properties object that has the right configuration properties set:

//required properties for exporting of db
Properties properties = new Properties();
properties.setProperty(MysqlExportService.DB_NAME, "database-name");
properties.setProperty(MysqlExportService.DB_USERNAME, "root");
properties.setProperty(MysqlExportService.DB_PASSWORD, "root");

//properties relating to email config
properties.setProperty(MysqlExportService.EMAIL_HOST, "smtp.mailtrap.io");
properties.setProperty(MysqlExportService.EMAIL_PORT, "25");
properties.setProperty(MysqlExportService.EMAIL_USERNAME, "mailtrap-username");
properties.setProperty(MysqlExportService.EMAIL_PASSWORD, "mailtrap-password");
properties.setProperty(MysqlExportService.EMAIL_FROM, "test@smattme.com");
properties.setProperty(MysqlExportService.EMAIL_TO, "backup@smattme.com");

//set the outputs temp dir
properties.setProperty(MysqlExportService.TEMP_DIR, new File("external").getPath());

MysqlExportService mysqlExportService = new MysqlExportService(properties);
mysqlExportService.export();

From the snippet above, we created a new Properties object and then added the required properties for the database connection, which are: the database name, username and password.

Supplying just these properties will make mysql-backup4j assume that the database is running on localhost at port 3306. It will, therefore, attempt connection using these values alongside the supplied username and password.

At this point, the library can export our database and generate a ZIP file containing the SQL dump file. The file is named in the format:

randomstring_day_month_year_hour_minute_seconds_database_name_dump.zip

Since we have supplied complete email credentials as part of the properties used to configure it, the zipped database dump will be sent via email to the configured address. If no email config is set, then nothing happens after backup.

Another important config that we set is the TEMP_DIR this is the dir that will be used by the library to temporarily store the generated files while still processing. This dir should be writable by the running program. 

The TEMP_DIR will be automatically deleted once the backup operation is complete. Sweet and simple right? Yeah.

Sending Generated Zipped Files to any Cloud Storage

Though the library can send the backup to a pre-configured email address, it also provides a means for us to get the generated file as a Java File object so we can do whatever we want with it.

For us to achieve that we've got to add this configuration property:

//...
properties.setProperty(MysqlExportService.PRESERVE_GENERATED_ZIP, "true");

 This property instructs mysql-backup4j to preserve the generated zip file so that we can access it:

File file = mysqlExportService.getGeneratedZipFile();

Now that we have a file object, we can upload it to any cloud storage of our choice using appropriate SDKs and libraries.

Once we're done, we have to manually clear the ZIP file from the TEMP_DIR by calling:

mysqlExportService.clearTempFiles(false);

This aspect is very important so we won't have redundant files in our local storage. If we want to get the raw exported SQL dump as a String we only need to call this method:

String generatedSql = mysqlExportService.getGeneratedSql();

I love the flexibility of this library. Other properties that can be set are:

properties.setProperty(MysqlExportService.DELETE_EXISTING_DATA, "true");
properties.setProperty(MysqlExportService.DROP_TABLES, "true");
properties.setProperty(MysqlExportService.ADD_IF_NOT_EXISTS, "true");
properties.setProperty(MysqlExportService.JDBC_DRIVER_NAME, "root.ss");
properties.setProperty(MysqlExportService.JDBC_CONNECTION_STRING, "jdbc:mysql://localhost:3306/database-name");

DELETE_EXISTING_DATA will add a DELETE * FROM table SQL statement before an INSERT INTO table SQL statement(s).

DROP_TABLES will add a DROP TABLE IF EXISTS SQL statement before CREATE TABLE IF NOT EXISTS statement.

ADD_IF_NOT_EXISTS which is by default true will an IF NOT EXISTS clause to CREATE TABLE statements.

We can specify the JDBC_DRIVER_NAME and the JDBC_CONNECTION_STRING also via the properties.

If our database happens to be running on another host or port other than localhost:3306 then we can use the JDBC_CONNECTION_STRING property to configure the connection. The DB_NAME will be extracted from the supplied connection string.

We can automate this process by using Java job schedulers like Quartz or other means. Moreover, in a typical web application, we can just create a path for it, that will trigger the backup process in a Service or a Controller.

We can even integrate it into the web application such that, the backup will be triggered when the database has a significant record update. The possibilities are limited only by our creativity.

Importing the Database Dump

Yep! We've been able to backup our database and lock it away in a secure vault. But how do we import the database and do a restoration?

First, we have to unzip the generated ZIP file and extract the SQL dump into a folder. Then we can use database clients like HeidiSQL and Adminer to import the database. Using a database manager client will provide a visual aid and other great tools that come with it.

However, let's say we find ourselves in need of restoring the database programmatically, within the app, while it's still running.

All we need to do is read the content of the generated SQL dump as a String and pass it to the MySqlImportService of the library with minimum configurations:

String sql = new String(Files.readAllBytes(Paths.get("path/to/sql/dump/file.sql")));

boolean res = MysqlImportService.builder()
        .setDatabase("database-name")
        .setSqlString(sql)
        .setUsername("root")
        .setPassword("root")
        .setDeleteExisting(true)
        .setDropExisting(true)
        .importDatabase();

assertTrue(res);

From the snippet above, we read the SQL from a file system, and then we used the MySqlImportService to perform the import operation.

We configured MySqlImportService to delete any existing content in the table and to drop existing tables. We can always fine tune these parameters to suit our need. The service will return true on successful operation and false otherwise.

What if our database is running on another server and port other than localhost:3306? We can configure that as well using the setJdbcConnString() method.

Although we read the SQL file from a local file system, If we're in a web interface, we can actually provide an interface that will allow the file be selected from the file system, and the content read and sent as an HTTP POST request to the server.

Conclusion

Wheew! that's some productivity tool we've just looked at. Remember to star mysql-backup4j on GitHub.

Now, go and utilize it in your project. Questions? Contributions? Appreciation? Kindly drop them in the comment section below.