I have a couple databases in mysql on my machines (either for stuff like my weblog or more serious applications like for work 
. The machines are probably backed-up regularly using file-system-based tools… so I suppose that my database is backed-up once in a while if it is stored somewhere in /var/lib/mysql…
However, if for some reason the mysql server crashed, and had a problem opening the “raw” backup (maybe no longer backward-compatible after un upgrade), it would be difficult to get back my data…
The idea is to complement the raw backup with one of the data in “full-text”. mysqldump can be used to do so.
I found the mysql_backup(.pl) script developped by Peter Falkenberg Brown really useful. This GPL’ed tool handles the backup of selected mysql databases into files (one for each table), which are then compressed (.tar.gz) and named after the date of the backups, then rotated ala logrotate (btw, for uses of logrotate for similar needs, see this post by benj). It’s easily setup and configured with many options. A must-have in my opinion.
Thought I had blogged about it before… but it seems that I did not
Update 2008/04/15 : Btw, if you can’t access the database directly, but have phpMyAdmin available, see the linked post in the trackbacks of this post for a tool I wrote.
Pingback: WebLog Pro Olivier Berger » Scripting mysql database backups on phpMyadmin with CURL
Just discovered a nive mix of two programs that can offer a good alternative, I suppose : backupninja and duplicity : must try !
hi Olivier,
1. the new version is broken with phpmysql 2.11.10: i had to switch back to revision 7 on github!
2. did you see this script ? That guy seems to have to say thanks to your script…
bye, thanks again (too github too!)!
marco
@marco thanks for your comments.