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 🙂
5 thoughts on “Dumping MySQL databases using Groovy”
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.
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!)
Brilliant! Saved me a major headache!
Thanks a bundle!
thanks a lot
This was very helpful. Thank you.