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.
Let's add the dependency to our project's pom.xml:
<dependency> <groupId>com.smattme</groupId> <artifactId>mysql-backup4j</artifactId> <version>1.0.1</version> </dependency>
The latest version can be found here
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, "email@example.com"); properties.setProperty(MysqlExportService.EMAIL_TO, "firstname.lastname@example.org"); //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:
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.
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:
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");
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.
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 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 by supplying true to the setDeleteExisting method and to drop existing tables by supplying true to the setDropExisting method.
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 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.
If you find this post helpful, learnt anything at all, just share it with your friends on Facebook and Twitter. Be proud of the quality content you read, especially in these days of junk everywhere
Maven Central to the World, deploy your open-source Java library for other people to use in their projects as a dependency
Being able to read log files on a production server can boost the quality of your emergency readiness and response. Read on to learn how.
Why upload the file, when all you need is the data in it? Cleaner, faster avoid connection timeouts, corrupted data just by adding excel_uploader js
Interacting with the Formstack API v2 just got better with this new fstackapi_php library. We'll use it to submit form dynamically and more
Here is a curation of the different technologies that power the web and the ones you should learn to make a living as a web developer
Ever wish there is a way you insert default data into the database during application startup in a SpringBoot environment? Read on!
We'll build a complete desktop application that'll translate English text to Morse Code and vice-versa plus an overview of Java GUI
This is a recap of how I moved from Laragon 2 to v3, my mistakes and how you can avoid them. Plus undocumented changes I discovered