Automatic mySQL backup
May 13, 2005 10:47 AM Subscribe
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!
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!
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
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
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
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:
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
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
This thread is closed to new comments.
posted by nitsuj at 10:52 AM on May 13, 2005