Auto MySQL Backup

[Owen]

Hey, I searched for something like this in the forums, but couldnt find it, so I apologise if someone has already asked for this.

Would it be possible to have an auto-mysql backup feature within SMF?  I know there are many many different mysql backup scripts, but to have something like that intigrated into SMF would be very useful, say have it setup like the cPanel backup, where you can either store it locally at regular intevals, or download it manually, and have a "restore from backup" option.
This would also be nice for all those people who have just a basic knowledge of MySQL/PHP and don't want to get confused by the different scripts.

[Max Shanly]

This would be great if you had Automated Download, so that your Harddrive when connected to the internet gets sent a message from you Server that the scripts are ready to be downloaded and are downloaded automatically without you having to bother clicking things...

[Tristan Perry]

I think a feature that you could store back-ups locally would be great. Vene if you had to do it manually. It'd help me out lots.

This would be great if you had Automated Download, so that your Harddrive when connected to the internet gets sent a message from you Server that the scripts are ready to be downloaded and are downloaded automatically without you having to bother clicking things...

That'd be hard (Maybe impossible?) to do. Also would you actually trust such a feature that connects you to the internet, and then downloads files onto your PC? It'd be completely un-safe.

[Tony Reid]

Create a file called daily.sh

Code Select Expand


#! /bin/sh
DATESTAMP=`date +%m-%d-%Y-%H%M`
cd /home/username/backup
rm *.gz
mysqldump --opt -pPASSWORD -uUSERNAME USERNAME_DATABASE | gzip >$DATESTAMP.gz
cd /home/username/
echo $DATESTAMP >> msg.txt
cat msg.txt | /usr/lib/sendmail -t


Create another file called msg.txt

Code Select Expand


From: [email protected]
To: [email protected], [email protected]
Subject: Daily Backup Routine

Begin your message here. It is very important to leave a blank line before you start the body of your message.
The Backup Routine has been executed


Place them both above your public_html directory - in your user directory.

You then need to use Control Panel to setup a cron job with these paremeters.... Feel free to change them, but they work out to be 2am everyday when its less busy.

Minute: 0
Hour: 2
Day: *
Month: *
Weekday: *

So it looks like this...........

Code Select Expand

0 2 * * * {username} /home/username/daily.sh


Oh, you obviously have to change the username/dbnames in the scripts and also take out the curly brackets

And CHMOD the bash script to chmod +x daily.sh

Good luck

Tony,

What this does is backs up your database into a backup directory(non public side) and then emails you to tell you its backed up.



Then all you have to do is set up your FTP program to connect to the internet and download the file at a scheduled time.

[Tristan Perry]

Thanks for that Tony, it looks useful, although would there be anyway of doing this without having to connect to the internet and then connect to your FTP account?

[Tony Reid]

It does it automatically regardless and the backup file will be zipped in a backup directory every night at 2am.

You dont have to download it - but if you want to then you will need to connect via FTP or use an FTP download script. or set the server to FTP the file to your PC at a set time (you will need to run an FTP server on your home machine for that to work)

I suggested that your FTP program should be scheduled only because superbean asked "downloaded and are downloaded automatically without you having to bother clicking things..."

You can just leave the backup on the server if you prefer.

[Max Shanly]

see... I have good ideas...

[Tristan Perry]

It does it automatically regardless and the backup file will be zipped in a backup directory every night at 2am.

You dont have to download it - but if you want to then you will need to connect via FTP or use an FTP download script. or set the server to FTP the file to your PC at a set time (you will need to run an FTP server on your home machine for that to work)

I suggested that your FTP program should be scheduled only because superbean asked "downloaded and are downloaded automatically without you having to bother clicking things..."

You can just leave the backup on the server if you prefer.

Ah right I see. So the script you provided, would automatically back-up my forums database (Or all of them?) and then put them into a back-up folder, without me doing anything? (Except making the files etc) If so that'd be great 

[Tony Reid]

Yes thats correct ...... you could add more databases to the

"mysqldump --opt -pPASSWORD -uUSERNAME USERNAME_DATABASE | gzip >$DATESTAMP.gz" line as long as the username and passwords were the same.

Unless of course you used your cpanel username/password instead of the DB usernames and passwords I wouldn't recommend it though.

Try it out with a single database first though.

[Vinoth]

nice one, if implemented

[Tristan Perry]

Thanks for the help  1 last question, is the cron job below correct:

http://tauonline.org/cron.jpg

That's the only part I didn't really understand.

[Tony Reid]

nope - my guess is that your path should be ......

/home/tauonli/daily.sh

[[Unknown]]

I'm sorry, but how is this something SMF should do?

It seems like the server is much better equipped to do it itself....

-[Unknown]

[Tony Reid]

Thats why I gave them a shell script (above)

I was thinking that this needs to be moved out of here into another board.

[Peter Duggan]

I was thinking that this needs to be moved out of here into another board.

Done!

[Tristan Perry]

I wanted to see whether this would work, so I changed the time that the cron job should execute. I got e-mailed  the following error message:

/bin/sh: line 1: /home/tauonli/daily.sh: Permission denied

The line is:

Code Select Expand

#! /bin/sh

If it helps.
  Thanks

[Tony Reid]

You need to chmod the daily.sh file

[Tristan Perry]

You need to chmod the daily.sh file

Ah right! I did do that, although I then moved the daily.sh and forgot to chmod it..anyway I've tested the script and it works great. Thanks so much for all your help 

[Tony Reid]

Your welcome.

Glad it worked out well

Tony,

[WizardHawk]

Create a file called daily.sh [nofollow]

Code Select Expand


#! /bin/sh
DATESTAMP=`date +%m-%d-%Y-%H%M`
cd /home/username/backup
rm *.gz
mysqldump --opt -pPASSWORD -uUSERNAME USERNAME_DATABASE | gzip >$DATESTAMP.gz
cd /home/username/
echo $DATESTAMP >> msg.txt
cat msg.txt | /usr/lib/sendmail -t


Create another file called msg.txt

Code Select Expand


From: [email protected]
To: [email protected], [email protected]
Subject: Daily Backup Routine

Begin your message here. It is very important to leave a blank line before you start the body of your message.
The Backup Routine has been executed


Place them both above your public_html directory - in your user directory.

You then need to use Control Panel to setup a cron job with these paremeters.... Feel free to change them, but they work out to be 2am everyday when its less busy.

Minute: 0
Hour: 2
Day: *
Month: *
Weekday: *

So it looks like this...........

Code Select Expand

0 2 * * * {username} /home/username/daily.sh


Oh, you obviously have to change the username/dbnames in the scripts and also take out the curly brackets

And CHMOD the bash script to chmod +x daily.sh [nofollow]

Good luck

Tony,

What this does is backs up your database into a backup directory(non public side) and then emails you to tell you its backed up.



Then all you have to do is set up your FTP program to connect to the internet and download the file at a scheduled time.

Anyone know how you could add something to this script to make a copy of your attachments folder as well. Having a full backup of the sql AND attachments folder is the only want to really protect the full forum.