DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Migrate, Modernize and Build Java Web Apps on Azure: This live workshop will cover methods to enhance Java application development workflow.

Modern Digital Website Security: Prepare to face any form of malicious web activity and enable your sites to optimally serve your customers.

Kubernetes in the Enterprise: The latest expert insights on scaling, serverless, Kubernetes-powered AI, cluster security, FinOps, and more.

E-Commerce Development Essentials: Considering starting or working on an e-commerce business? Learn how to create a backend that scales.

Related

  • Querying The DBpedia Open Knowledge Graph With Standard SQL
  • Best Methods To Backup and Restore Database in SQL Server
  • REST API Microservice AI Design and Spreadsheet Rules
  • Sample Data Generation With Built-In Database Capabilities

Trending

  • Java Collection Overhead
  • Ways To Reduce JVM Docker Image Size
  • API-Driven Integration
  • A Roadmap to True Observability
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Back Up a MySQL Database Programmatically Using mysql-backup4j

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

Interested in setting up programmatic backups for your MySQL DB? See how the mysql-backup4j library can let you do just that in Java.

Seun Matt user avatar by
Seun Matt
DZone Core CORE ·
Mar. 23, 18 · Tutorial
Like (7)
Save
Tweet
Share
25.5K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database dump MySQL Property (programming) Backup

Published at DZone with permission of Seun Matt. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Querying The DBpedia Open Knowledge Graph With Standard SQL
  • Best Methods To Backup and Restore Database in SQL Server
  • REST API Microservice AI Design and Spreadsheet Rules
  • Sample Data Generation With Built-In Database Capabilities

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: