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 (8 answers total)

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

Perhaps a simple shell-script making use of either mysqlhotcopy or mysqldump to backup to a filename of your choice, then use scp/rsync/ftp for uploading the file to a remote machine?

These mysql commands should be able to run from the command line (or a shell script) using something like:
mysql --username=username --password=password --execute="mysqldump/hotcopy {options}"

The shell script you create would then get put in a cron-job to run at whatever times you required.
posted by odinsdream at 11:04 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

* make sure you read the user-submitted comments in the documentation for mysqldump, as some people have offered little snippets of their daily backup routines there.
posted by odinsdream at 11:06 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

After all, if you didn't care about it being fubar you wouldn't be doing backups, no?

Any sensible database administrator knows you never back up your database without first having a backup of... your...

..nevermind.
posted by odinsdream at 11:29 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