Auto Database Backup Script 1.0

[MattW]

MattW submitted a new resource:

Auto Database Backup Script (version 1.0) - Export and Upload your Database to Offsite location with Cron

These are 2 scripts I wrote to automate the nightly backup of my databases and upload them to a remote location via FTP.

There is a bash script that you call from cron at a time of your choice. This uses mysqldump to make a copy of your database, compress is using pbzip2 (adjust to bzip2 or gzip if you don't have pbzip2 installed), and then calls the upload.pl script to upload the file via FTP to the remote site (keeping 5 rolling days worth of...

Read more about this resource...

 

[Stemis]

Restoring is going via phpMyadmin or something else?

 

[MattW]

Restoring is going via phpMyadmin or something else?

If you need to restore it, I copy the remote backup back onto the server, uncompress it, and load the in via command line
So:

mysql -u USERNAME -p DATABASE < backupfile.sql

 

[Andy.N]

Can you also support uploading to Amazon S3 as well in your script?
Slavik posted his script before.
http://xenforo.com/community/thread...tall-database-to-amazon-s3.40343/#post-471732

 

[MattW]

Can you also support uploading to Amazon S3 as well in your script?
Slavik posted his script before.
http://xenforo.com/community/thread...tall-database-to-amazon-s3.40343/#post-471732

Having a look at that, the only real difference between mine and Slavik's is that I have my own perl script to upload to my remote sites via FTP, where his is calling the java client to support S3. I've never used S3, but all you'd do is change my script to remove the upload.pl reference, and put the below in.

java -jar /var/glacieruploader.jar --endpoint https://glacier.eu-west-1.amazonaws.com --vault vaultname --upload /var/backup/$suffix.tar

 

[faeronsayn]

Is it possible to have it automatically uploaded to your dropbox account?

 

[MattW]

Is it possible to have it automatically uploaded to your dropbox account?

I'm not sure, again, I don't use dropbox for my backups. I have a second VPS server and a NAS at home that allows FTP access.

 

[karll]

Thanks for the script. Maybe a bit off-topic, but I googled to see if there is such a thing as free and secure ftp storage that I could use for this ...
http://answers.yahoo.com/question/index?qid=20070404014508AAIVIt7

They suggest to encrypt the files if you use any of these, and I can imagine that is good advice.

 

[MattW]

You can build that in using OpenSSL. I used to do that:

# Encrypt Files using AES256
openssl enc -aes-256-cbc -salt -in $DIR/z22seforum.$(/bin/date +%Y-%m-%d).sql.bz2 -out $DIR/z22seforum.$(/bin/date +%Y-%m-%d).sql.bz2.aes256 -pass pass:$ENCPASS

 

[Renegade]

Is there a benefit of using a cron based backup over the WHM backup functionality?

 

[Kent]

You can use curl for FTP uploads:

curl -T /path/to/some.sql.gz.gpg ftp://remote.example.com --user backups:areawesome

The day may change during the execution of the script causing it to fail. Would only likely happen if executed near midnight.

You can build that in using OpenSSL. I used to do that:

# Encrypt Files using AES256
openssl enc -aes-256-cbc -salt -in $DIR/z22seforum.$(/bin/date +%Y-%m-%d).sql.bz2 -out $DIR/z22seforum.$(/bin/date +%Y-%m-%d).sql.bz2.aes256 -pass pass:$ENCPASS

Although AES is probably faster, I'd recommend GPG:

gpg --encrypt -o /path/to/some.sql.gz.gpg --recipient "Example Website (Database Backup) <someone@example.org>" /path/to/some.sql.gz

This will encrypt some.sql.gz with someone@example.org's public key and save it as some.sql.gz.gpg, which means at this point nobody else can decrypt it but the holder of the corresponding private key.

 

[erich37]

MattW submitted a new resource:

Auto Database Backup Script (version 1.0) - Export and Upload your Database to Offsite location with Cron



Read more about this resource...

Hi Matt,

I am wondering if this script is also doing a backup of the Files (for backing up Avatar-images and Forum-attachments / RM attachments, etc.) ?

Is there a chance to include the code mentioned by Brogan ?
http://xenforo.com/community/threads/backup-file-system-images.56883/#post-605885


Many thanks!

 

[MattW]

I've replied in the other thread with a better way of backing up if you have access to rsync.

If you wanted to back up the directories and upload via FTP using these two scripts.

In the dbbackup.sh script, just add the instructions @Brogan posted

#Zip Data Directory 
/bin/tar -czf $DIR/data_$(date +%d.%m.%y).tar.gz /path/to/data 
#Zip Internal Data Directory 
/bin/tar -czf $DIR/internal_data_$(date +%d.%m.%y).tar.gz /path/to/internal_data

You would then include the new data_$(date +%d.%m.%y).tar.gz and internal_data_$(date +%d.%m.%y).tar.gz files into the upload.pl script, by adding them to the array of files to be uploaded/deleted.

Then at the end of the dbbackup.sh script, add the command to remove the files when you are done with them

# Remove the database file now we are done
rm $DIR/*.$(/bin/date +%Y-%m-%d).sql.bz2
rm $DIR/internal_data_$(date +%d.%m.%y).tar.gz
rm $DIR/data_$(date +%d.%m.%y).tar.gz

 

[rdn]

Will work even if no root access?

 

[MattW]

Will work even if no root access?

Yes, as it uses the local FTP account and MySQL account

 

[countryside]

@MattW - is it possible to get it to backup then sent it to your local computer?

 

[MattW]

@MattW - is it possible to get it to backup then sent it to your local computer?

That depends on how you want to get it onto your local PC