Dumping MySQL databases using Groovy

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 🙂