In this article, we are going to look at a very flexible Java library, mysql-backup4j, which we can use to back up our database from time to time.
Once our app goes into production, we can’t afford to not back up on time in case of contingencies. Usually, what makes the process somewhat difficult is if we have to manually trigger the process all the time.
Imagine a scenario in which we have both automated and manual processes of database backup – that’s what we’re going to do.
Programmatically exporting MySQL database
Programmatically exporting a MySQL database is pretty straightforward with mysql-backup4j. We only need to instantiate it and pass it a Java Properties object that has the correct configuration properties set:
From the above snippet, we created a new Properties object and then added the required properties for the database connection, which are: database name, username, and password.
Supplying only these properties will make mysql-backup4j assume that the database is running on localhost on port 3306. Therefore, the connection will be attempted using these values along with 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:
Since we have provided full email credentials as part of the assets used to configure it, the zipped database dump will be sent via email to the configured address. If no email configuration is set, nothing happens after the backup.
Another important config we set is TEMP_DIR; This is the directory that will be used by the library to temporarily store files still generated during processing. This dir must be writable by the running program.
TEMP_DIR will be deleted automatically when the backup operation is complete. Sweet and simple isn’t it? Yes.
Sending Generated Zipped Files to Any Cloud Storage
Although the library can send the backup to a pre-configured email address, it also provides us with a means to receive the generated file as a Java File object so that we can do whatever we want with it.
This aspect is very important so we will not have unnecessary files in our local storage. If we want to get the raw exported SQL dump as a string, we just need to call this method:
If our database is running on a host or port other than localhost:3306 then we can use the JDBC_CONNECTION_STRING property to configure the connection. DB_NAME will be extracted from the supplied connection string.
We can automate this process by using Java Job Scheduler like Quartz or other means. Also, in a normal web application, we can just create a path to it which will trigger the backup process in a service or controller.
We can also integrate this into a web application such that a backup will be triggered when an important record is updated in the database. The possibilities are limited only by our creativity.
Importing Database Dumps
Yes! We are able to back up our database and lock it in a secure safe. But how do we import the database and do a restore?
First, we need to unzip the generated zip file and extract the SQL dump to a folder. We can then use database clients like HeidiSQL and Adminer to import the database. Using the Database Manager client will provide a visual aid and other great tools that come with it.
However, let’s say we need to restore the database programmatically within the app while it is still running.
From the above snippet, we read SQL from the file system, and then we use MySqlImportService to perform the import operation.
We have configured MySqlImportService to remove any existing content in the table and drop existing tables. We can always fine-tune these parameters to suit our needs. The service will return true on successful operation or false otherwise.
What if our database is running on a server and port other than localhost:3306? We can also configure it using setJdbcConnString() method.
Although we read a SQL file from the local file system, if we are in a web interface, we can actually provide an interface that will allow selecting a file from the file system. The content can then be read and sent to the server as an HTTP POST request.