Today I needed to use Groovy to create a dump of all MySQL databases on the MySQL server running on my local machine.

So I looked at my earlier blog entry on how to dump MySQL databases using the "mysqldump" command.

So when I issued the command create a dump from console, the contents of the database are dumped to a sql file as expected.

// Works in command line
mysqldump --all-databases -u [username] -p[password] -C > alldatabases.sql

As you might know, it is fairly straight-forward to execute shell commands from Groovy. You simply put the command in quotes and call the execute() method.

However, when I issued the same command using Groovy, the command did not work... well, at least not at first.

After some head-scratching and pondering over the help documentation of Groovy and MySQL, I found that the correct way to call the mysqldump command in Groovy is:

// Works in Groovy (as well as command line)
/usr/local/mysql/bin/mysqldump --all-databases -u [username] -p[password] -C --result-file=alldatabases.sql

The basic issue was that the ">" symbol (to redirect output to file) does not work as one expects in a console. If you use Groovy to issue command that contains the ">" symbol, the command will silently fail (which is rather frustrating).

Thankfully the mysqldump command provides an alternate way to dump SQL to a file -- the "--result-file" option (which does the same job as ">"). Using this option instead of the ">" symbol does the job very nicely.

Another minor issue was that when issuing the command from Groovy, I needed to fully path the mysqldump call. So, in my case the call was to /usr/local/mysql/bin/mysqldump rather than mysqldump.

Oh, and BTW, this is my first post on Groovy. More will come :)

Comments

Malte wrote on 04/22/08 10:01 PM

Thanks for the article. Helped me a lot. I'm wondering how the corresponding import would look like because something like "myslq -u user -ppass < import.sql" doesn't work either.

GTF wrote on 09/25/08 8:03 AM

If you are running on a unix box your error might have gone to stderr, whatever that might have been for your situation.

(Unix Script often uses 2> but since you were having issues with > the 2> might not work either. But hey command line args are excellet too!)

Brian Johnsen wrote on 07/15/09 10:10 PM

Brilliant! Saved me a major headache!
Thanks a bundle!
/brian

Leave a comment