How to Backup 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.
2. Dependency Installation
Let’s add the dependency to our project’s
The latest version can be found here
3. 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:
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
It will, therefore, attempt connection using these values alongside the supplied
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 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.
TEMP_DIR will be automatically deleted once the backup operation is complete. Sweet and simple right? Yeah.
4. Sending Generated Zipped File 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:
This property instructs mysql-backup4j to preserve the generated zip file so that we can access it:
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:
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:
I love the flexibility of this library. Other properties that can be set are:
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
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 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
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.
5. Importing the Database Dump
Yep! We’ve been able to back-up 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
of the library with minimum configurations:
From the snippet above, we read the SQL from a file system, and then we used the
MySqlImportService to perform the import operation.
MySqlImportService to delete any existing content in the table by supplying
setDeleteExisting method and to drop existing tables by supplying
true to the
We can always fine tune these parameters to suit our need. The service will return
true on successful operation and
What if our database is running on another server and port other than
We can configure that as well using the
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 to be selected from the file system,
and the content read and sent as an HTTP
POST request to the server.
Now, go and utilize it in your project. Questions? Contributions? Appreciation? Kindly drop them in the comment section below.