• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Auto Database Backup Script 1.0

No permission to download
Export and Upload your Database to Offsite location with Cron

MattW

Well-known member
#1
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...
 

MattW

Well-known member
#3
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:
Code:
mysql -u USERNAME -p DATABASE < backupfile.sql
 

MattW

Well-known member
#5
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
 

MattW

Well-known member
#9
You can build that in using OpenSSL. I used to do that:

Code:
# 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
 

Kent

Active member
#11
You can use curl for FTP uploads:
Code:
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:

Code:
# 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:
Code:
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

Well-known member
#12
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!

:)
 
Last edited:

MattW

Well-known member
#13
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
Code:
#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

Code:
# 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