I’m looking for an easy way to automate database backups. I’ve got a MySql DB on a Linux VPS with Parallels/Plesk, and need to be able to backup to another FTP location, or at least email a copy of the DB every day. I’m not great with servers, and everything I’ve found by googling seems to involve some quite full-on scripting.
I’m thinking there must be some open source software that I can install to do this? Something where I can just specify the FTP destination or email address, DB details and let it do the rest?
Have you tried setting up a cron job? I’m not too sure how it’s done as I’ve never needed to set one up but it seems like a good solution for this problem.
Yeah that’s what people seem to do - write a script that backs up the DB, compresses it, FTPs it somewhere, then a cron job to run the script. But I’m wondering if there’s an easier way, that doesn’t involve all this coding? Like a bit of software with a GUI that lets you set all this up? Thanks, Frank
There are probably tools that can code-generate this for you. That said, if you take the 15 minutes to learn the little bits of scripting to make this happen, you probably will do yourself a world of good when the GUI code generation tool fails.
I wrote a custom php script to handle all of this for me, then I just call the script once a night with a cron job. Script runs, db’s backed up, zipped and ftp’d to another server. I don’t have my laptop with me right now (my scripts are on it) or I would share the dump with you.
I used php to execute a shell command using shell_execute() (I believe that’s the function). Then I think I use ‘mysqldump’ with a zip flag so the resulting .sql file is zipped. I completely forget what script I used to ftp it.
Google search mysqldump, fire up your ssh and start trying commands until you get it right. Then just copy/paste the command into the shell_execute() command in your php script.
Some basic bash commands and scripting would go a long way to ensure good management of your VPS.
Have a look at some basic tutorials and then try using a bash script that will email you the db dump and even a tar / gz of your webroot (there are a few samples around if you google for bash, mysqldump, email etc).
Put the script into cron and you have an ongoing point in time backup of your web structure and db.
Thanks for your post - I actually found that I could set up automatic backups via FTP from Plesk, so I’ve gone down that route. But I might take a look at some basic bash stuff - might be useful in the future…