Executing mysqldump from Java and Pitfalls to Avoid

Using MySQL and Java? Check out an easier way: Yank

I just spent a lot of time figuring out how to run mysqldump from within my Java program. I came across several different problems. Each time it was not working, there was a different problem and solution, and never really easy to debug. Sometimes it would just not work at all. Sometimes it would create an empty file. In this blog, I document how I finally got it all to work and which pitfalls I fell into along the way.

Step 1: The first order of business in running mysqldump from within a Java program is to setup a function that you can pass shell commands to. The following method takes a command as an argument and runs it in a bash shell. I put this method in a class called ExecUtils.

public static void execShellCmd(String cmd) {
        try {
            Runtime runtime = Runtime.getRuntime();
            Process process = runtime.exec(new String[] { "/bin/bash", "-c", cmd });
            int exitValue = process.waitFor();
            System.out.println("exit value: " + exitValue);
            BufferedReader buf = new BufferedReader(new InputStreamReader(process.getInputStream()));
            String line = "";
            while ((line = buf.readLine()) != null) {
                System.out.println("exec response: " + line);
            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }

Step 2: Now we just need to run the proper mysqldump command through our execShellCmd method. The following shellCommand String will backup the database DB_XYZ and write it to a file called /test.sql.gz.

private void mysqldump() {

        String shellCommand = "mysqldump --user=username --password=pass --databases DB_XYZ | gzip -9 > " + "/test.sql.gz";
        ExecUtils.execShellCmd(shellCommand);

    }

Pitfall 1: LOCK TABLES user permission. In order for the mysqldump command to work, the user which you define using the --user argument must have the LOCK TABLES permission. If that user does not have that privilege, you can add it with the following commands in MYSQL:

GRANT lock tables ON DB_XYZ.* TO 'username'@'localhost' IDENTIFIED BY 'pass';
GRANT lock tables ON DB_XYZ.* TO 'username'@'%' IDENTIFIED BY 'pass';
flush privileges;

Pitfall 2: False file permissions. In order for the /test.sql.gz to be written to disk, the user in which the Java program is running must have permission to write a file there. In this case: '/'.

Pitfall 3: mysqldump cannot be found on system PATH. In the command above I used the unqualified 'mysqldump' as the command to execute. You could just as easily replace 'mysqldump' with the fully qualified version: '/usr/local/mysq/bin/mysqldump', as in my case on my local machine. One possible problem with this approach though is that your Java code doesn't become portable to other platforms. For example, on one of my Linux machines, mysqldump is found here: '/usr/bin/mysqldump'. To be able to use just 'mysqldump' you have to make sure it is on the system PATH. This is how you do that...

On my Linux machine:

$ locate mysqldump
/usr/bin/mysqldump

OK, using the command 'locate' I determined that 'mysqldump' is located in the directory '/usr/bin'. Now let's check if '/usr/bin' is on PATH:

$ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

Yes, it is. No problem using 'mysqldump' directly.

On my Mac machine:

$ locate mysqldump
/usr/local/mysql/bin/mysqldump

OK, using the command 'locate' I determined that 'mysqldump' is located in the directory '/usr/local/mysql/bin'. Now let's check if '/usr/local/mysql/bin' is on PATH:

$ echo $PATH
/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/usr/local/git/bin

Nope. Now we need to add /usr/local/mysql/bin to PATH on Mac OS X (10.5 Leopard). Here's how I did that. I created a file in /etc/paths.d called mysqldump

$ sudo vim /etc/paths.d/mysqldump

and added the following text:

/usr/local/mysql/bin

and restarted my computer.

Now let's check again if '/usr/local/mysql/bin' is on PATH:

$ echo $PATH
/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/X11/bin:/usr/local/git/bin:/usr/local/mysql/bin

Yup, all good!