Automatic mySQL backup

May 13, 2005 10:47 AM

Is there a way to automate the task of doing a daily mySQL dump? I'm looking for something (a program perhaps) that I could automate to dump mySQL into a file and save it to my PC.

I know there's a bunch of apps designed to administer mySQL locally (i.e. EMS SQL Manager Lite, etc.), but I need to backup the databases on my remote server. Is this even possible? I don't want to open phpMyAdmin and manually dump the databases everyday... argh!

posted by bjork24 to Computers & Internet (5 answers total)

Best answer: Write a PHP script that dumps the databases and FTP's it to your local machine. Set Scheduled Tasks on your local machine (or in the Cron on the remote server) to run the PHP script (remotely) on a daily basis.
posted by nitsuj at 10:52 AM on May 13, 2005

Just use a cron job, if on a Unix platform.

crontab -e, then add a line like:

0 0 * * * /usr/bin/mysqldump -h HOSTNAME DATABASE -uUSER -pPASSWORD > /somedirectory/somefile.sql

That runs it at the 0 minute of the 0 hour, aka midnight.

Crontab format..

If on Windows (my condolences), you can look up scheduling a job via AT.
posted by xmutex at 11:05 AM on May 13, 2005

So yeah, in that example, mysqldump is the actual program. It's included in the mySQL distribution.

You can run it w/o piping it (leave off the > and everything after) to see the actual results.
posted by xmutex at 11:07 AM on May 13, 2005

If you're going to cron up something with the password on the command line you should create an account with nothing but read permission for doing so.

grant select on *.* to 'backupuser'@'localhost' identified by 'backupuserspassword'

Even if nobody else has access to the machine it's Just Good Practice and will keep you/your script from accidentally fubaring your DB. After all, if you didn't care about it being fubar you wouldn't be doing backups, no?
posted by phearlez at 11:07 AM on May 13, 2005

This is what I use, as a cron job script:

gzip -9 /path/to/mysqldump/*.sql
mysqldump -A --user=whoever --password=whatever > /path/to/mysqldump/`date +%Y%m%d%H%M%S`.sql

That way the dumps get label with the appropriate date, and compressed the next day. Tweak as you see fit.
posted by Remy at 11:59 AM on May 13, 2005