Thursday, July 28, 2011

Executing mysqldump from Java and Pitfalls to Avoid

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.

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.

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:

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:

Open Source & Freeware

Blogroll

Blog Visits

RSS Feed

Followers

Copyright Notice!

All images and copyrighted materials which appear on this site are the properties of their respective owners and are herein used according to "fair use" doctrine and in accordance with United States copyright laws for the purposes of discussion and reference to original content.