Looking for MySQL Backup program

[slobizman]

I'm looking for a program I can buy that will help me to automate the process of backing up/restoring MySQL tables on a UNIX dedicated server.

I'd like something that would create a compressed backup that automatically downloads to my local computer, or emails the compressed file to me.

[Shannon Moore]

Hopefully someone else can provide info on a specific program or script to run to do exactly what you want.

What I have to offer is just the simple command line code my host provided years ago so I knew how to set up cron jobs (events that are scheduled to run at preset times on Apache web servers--may work on all Unix-based servers, but I'm not positive) to save out copies of my databases.
The script doesn't ZIP or tar the files, however, and I still manually download them daily.

/usr/bin/mysqldump -uYOURdbUSERNAME -pYOURdbPASSWORD --database YOURdbNAME > /path/to/save/SQLdumpData/to/FILENAME.sql

I set up multiple cron jobs, so the line above is repeated once for each database I need to backup, with appropriate values changed (YOURdbNAME, FILENAME.sql, etc.)

[bobbee]

Mysqldump is what you want (it is standard on most installations which have MySQL).

The following code dumps all tables to 1 file & pipes it to gzip for compression:

mysqldump -q MYdbase -u MYuser -pMYpassword ¦ gzip > PATH/all-sql-11-21-03.sql.gz;

You can invoke the code on a schedule with a cron job. Your script can change the file name based on the date or day of the week or email you the file as an attachment.

I hope this helps,

Bob

[slobizman]

Can anyone suggest a book or web site that really explains all this MySQLDump, Gzip & Cron stuff well?

[amznVibe]

If you run mysql you should have [phpmyadmin.net...] as well.
Makes life alot easier for mysql problems and general management.

Since you are on a dedicated box, you won't have cpanel on there but I rely on that for super easy, single click, mysql backups/restores. Someone must know of an alternative out there (or how cpanel does it).

[slobizman]

Can't use phpmyadmin becuase my tables are too large. times out. Plus, I need compression.

[shady]

If you have shell access you could write a simple script (or get someone to write it for you) like the one I have written which does the following:

mysqldump (as mentioned above)
gzip (to compress)
ftp (copy backup offsite)

This script is scheduled using cron the run every 12 hours on my machine.