MySQL Database Backup & Restore Using Java

Hello Programmers !!

It’s been a while since I’ve written a blog post. Luckily I’m in a mood to write something at the moment. Today I thought about posting something I’ve learnt recently. It’s about database backups and restores through java.

If anyone wants to backup or restore a database without going through all these troubles, you can simply use MySQL GUI Tools Bundle freely distributed by the Oracle Corporation.

Sometimes it is necessary to integrate a backup & restore system to an application we are developing. In my case it was a java application. In this post I’m expecting to give you guys a rough idea about backing up and restoring your MySQL database through a Java Application.

1. Creating A Backup

When we install MySQL open source database it provides us a client called mysqldump for backup purposes. Here we execute this mysqldump command using the java Runtime.

Here’s a sample method to create a complete database backup including add, drop MySQL statements.

public boolean backupDB(String dbName, String dbUserName, String dbPassword, String path) {

        String executeCmd = "mysqldump -u " + dbUserName + " -p" + dbPassword + " --add-drop-database -B " + dbName + " -r " + path;
        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup created successfully");
                return true;
            } else {
                System.out.println("Could not create the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

Basically what we are doing here is similar executing the following command in the windows command line.

mysqldump -u Username -pPassword --add-drop-database -B databaseName -r backupPath

Here  -u, -p, -B, -r are options to indicate that we are inserting the database username, password, database name and location to save the backup respectively. The --add-drop-database option is to create a complete backup. It means that when we are restoring the backup file, if the database does not exist in that particular database the restoring process itself create the database automatically.

If by any chance you want to create a backup without add, drop database command. You can use the following command.

mysqldump -u Username -pPassword databaseName -r backupPath

For multiple databases use

mysqldump -u Username -pPassword --add-drop-database -B db1 db2 db3 -r backupPath

If you want to backup All the databases. You can use the command as follows.

mysqldump -u Username -pPassword --add-drop-database -A -r backupPath

Likewise you can create your backup according to the requirements. There are many other options that you can add to your command. You can visit mysqldump Docs for more options or simply type mysqldump --help on the command line to view the options.

That’s the basic idea about backing up the database. Let’s see how we can do the restoring process through java.

2. Restoring A Backup

I had hard time finding a working code for the restoring process. After making a few changes on the commands I’ve found on the internet, I’ve manage to create a workable runtime  command for the restore process.

Note: The following method is to restore a complete database backup (backup with add, drop database command)

public boolean restoreDB(String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, "-e", "source "+source};

        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup restored successfully");
                return true;
            } else {
                System.out.println("Could not restore the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

If the the backup doesn’t have the add, drop database, you can use the following method.

public static boolean restoreDB(String dbName, String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup restored successfully");
                return true;
            } else {
                System.out.println("Could not restore the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

I have tested all the commands and the methods mentioned in this post and they worked fine in Windows environment. That’s all about creating & restoring backups. Hope you guys learnt something new.
If you have any questions please post them as a comment & I’ll try my best to answer them. I’m backing up for now then. See you guys with another post. Hopefully! 🙂

Update

Make sure to add the path of  your ‘MySQL bin folder’ to the path variable in ‘Windows Environment Variables’.

References:
http://sureshk37.wordpress.com/2009/09/07/mysql-backup-and-restore-using-java/
http://forums.mysql.com