Automatic MySQL backups

[Ben]

I've using Textpattern on a couple of site and I'd quite like to automatically backup a handful of databases every week or so. I've got the rss_admin_db_manager but it's not automatic.

I've found a couple of (non textpattern related) things after a google, such as Auto MySQL Backup or code to setup a Cron Job.

I've never used a Cron job before, so I've got nerves about doing something wrong and bringing a server down or killing a kitten etc. Should I just use the code from the TXP site and stop worrying?

[sponna]

We use this to good effect - backs up and mails the db at whatever frequency you set in the cron job:

http://www.absoft-my.com/pondok/backup.php

[Ben]

Just wasted three hours trying to get something to work.

We use this to good effect - backs up and mails the db at whatever frequency you set in the cron job:

http://www.absoft-my.com/pondok/backup.php

Hmm.. I tried this, and it appears to work - except it never produces any backup files.

I've tried a couple of other programs and have no success with any! Thinking of giving up and continuing to use the phpmyadmin 

Used a couple of other programs and they all seem so complex, asking for so many details. I'm just looking for a script to drop into a folder, give it the various DB info and it emails me. Fedup looking for server-paths, script path, mysql bins, folder outside http and various other bits of information or requirements... then it doesn't work again.

[Toxteth OGrady]

Switch from textpattern to WordPress and get the automatic db backup plugin

[Ben]

Switch from textpattern to WordPress and get the automatic db backup plugin

Automatic DB backup..and all the lovely WP hacking that goes on as well I'm surprised TXP doesn't have an automatic plugin yet, it's got a good DB backup plugin, just not automatic.

Bizarrely, the above software did work, it took about 20 mins to output anything though.  Can't figure out how to automatically email the output file with the multiple DB script however, which I what I'm really looking for.

The Cron Job version sound like that I need, but it doesn't do multiple DB's?

[sponna]

Not sure - thought you could configure for mutliple dbs........

Alternative is to rename it and run it multiple times with different crons - bit messy but should work.

[sarahA]

If you can run the non automated DB backup plugin without needing to post data to it (ie. via a direct link), you could set that up as a CRON at say 2am, then at 3am you run a second CRON to email the backups to where ever.

and all the lovely WP hacking that goes on as well

If you keep it up to date it's unlikely you'd get hacked. I maintain over 20 and have never had any hacked.

[Ben]

and all the lovely WP hacking that goes on as well

If you keep it up to date it's unlikely you'd get hacked. I maintain over 20 and have never had any hacked.

I actually love Wordpress, but I'm still wary of it - I had a blog a wee while ago, went on holiday and it got hacked. 2.7 has autoupdate function though AFAIK, alongside the autobackup makes it rather appealing. 

I think I've managed to get this one working. I finally found simple enough instructions for me to follow, so I think it's setup. Got to just wait until midnight tomorrow to see if the cron job fires properly.

I've never used Cron jobs, however I'm basically telling the server to run x program at daily/weekly/monthly intervals? (I'm using dreamhost for this btw) I know that sounds silly, but I didn't realise that was what a cron job did - I thought you had to feed it complicated code.

Might make a good blog post for someone, "Auto MySQL backups for dummies...?" 
 

[sarahA]

2.7 has an update function, but it's not automatic, you still have to click a button, similar to the plugins update.

As for the CRON. Yes, all it is, is you giving it a path to a script (with a unix command I think) and then saying when to run it. It's no different to you going direct to the script. For example, the Flickr Bot to create a post for the DDN photos here is just a script that you can go to if you know the URL. Then in the CRON you just say 'run this script at 1am every night'.

But, fingers crossed what you've set up now works for you

[Ben]

But, fingers crossed what you've set up now works for you

Bollocks. Got a huge email of errors.

It's saying,

Code:

"mkdir: cannot create directory `/backups': Permission denied"
touch: cannot touch `/backups/mysql.domain.com-165502000.log': No such file or directory

..and stuff quite similar to that.

The only thing I can think of is that the script need the entire "/home/server/username/my_backups/backup" for the location of the backups to be put, rather than just relative "/backups" location from the script, as the example said.

The good thing is, at least the script is trying to do what I want, just need permission/location of the backup folder to be correct?

Me feeling ==

 

[sarahA]

The absolute path "/backups" would probably not work due to security restrictions on your server. You'll need the full document path, or the path relative to the script.

It's also saying permission denied, so clearly it's trying to create a directory and isn't allowed. Is /backups in your root? I would create the directory manually, then give the script 755 or 777 permissions to save the files there, otherwise you need to open up permissions on the root directory which isn't a good idea.

What you need to do is get the script working manually, ie. don't do it then wait until the cron runs it. There's no difference in you going to http://www.yourdomain.com/path-to-file/run-backup.php and you telling the cron to go there. So get it working manually then you can leave the cron to do its job.

[Ben]

The absolute path "/backups" would probably not work due to security restrictions on your server. You'll need the full document path, or the path relative to the script.

It's also saying permission denied, so clearly it's trying to create a directory and isn't allowed. Is /backups in your root? I would create the directory manually, then give the script 755 or 777 permissions to save the files there, otherwise you need to open up permissions on the root directory which isn't a good idea.

What you need to do is get the script working manually, ie. don't do it then wait until the cron runs it. There's no difference in you going to http://www.yourdomain.com/path-to-file/run-backup.php and you telling the cron to go there. So get it working manually then you can leave the cron to do its job.

Cool, ta - I've adjusted the path to the backup folder. I'd already created the folder and given it 777 permissions.

I've placed the files in the root, so they aren't publically available (so not sure how to run them manually) - would I be better moving them to a "visible" place i.e domain.com/backups?

[sarahA]

umm hmm not sure there. I'd get it working via a visible url then try moving it back to above the root for the cron and just alter the path to suit

[JasonD]

would I be better moving them to a "visible" place i.e domain.com/backups?

Never do that. Not even temporarily.

[Ben]

Hooray! 

It's working! I emailed the support, who emailed me back within 7 minutes to say the path in the script wasn't justcorrect, slight adjustment and a change in permissions - and bingo, a email full of lovely databases.

Cheers Sarah - really appreciate your help