Semi-automated MySQL Database Backup Using SSH :)

[Lifetalk]

With the fiasco on KWWH these days with webhosts going outta business, going down, getting compromised, etc I figured I would share a very simple command line bash sorta tool that I use to perform a semi-automated backup. The point is, with these host troubles, their clients are the ones losing out; not the webhost itself. And then everyone blames the clients for not taking backups.

True, taking a backup is a pain in the ass. What with all the logging in, entering passwords, downloading it, etc etc. Hell I wouldn't do that myself. But if you're even slightly serious about your site (which, btw, I am) then you'll know how important backups are.

Just a little disclaimer here:
This 'script' can only be used on a VPS or a Dedicated Server. Or anything besides the two where you have shell access with enough privileges to execute mysqldump. This will NOT work on a shared hosting account.

This script will also only backup your MySQL database. It can be modified to backup your files too, quite simple.

-------------------------------------------------------------------------------------------------------
REMEMBER! If there's something here that you are unable to understand fully, please PM me or post here. If there's something you're unsure of, DO NOT go experimenting. Ask me, I'll help you out. No, I will not charge you for it.
-------------------------------------------------------------------------------------------------------

1) Login to SSH
Pretty simple, right?

2) Pick a directory where you will store your backup(s). Now this directory can be anything, really. If you're looking to just store the backup on the same server as the MySQL database itself, don't even bother using this script as that's identical to not backing up. If, however, you're looking to download these backups to your own hard drive, then you're most likely going to want to store these backups in a location that is either publicly accessible or protected. I'd go with publicly accessible, but password protected.

To expand on this, say my files are located here:
"/home/user/domains/domain.com/public_html/index.php" (that's actually the default location pattern for DirectAdmin).

And I want the script to store my database backup here:
"/home/user/domains/domain.com/public_html/somerandomfoldername/" (this folder will also be protected with a basic HTTP authentication)

3) Now here's the bare script itself:

#Backs up the mysql database to dir
#
echo "Cleaning the backup dir"
#
#
rm -rf /home/user/domains/domain.com/public_html/somerandomfoldername/backup*
#
#
echo "Exporting current database copy"
#
#
mysqldump --add-drop-table -u DBUSERNAME -pDBPASSWORD DBNAME > /home/user/domains/domain.com/public_html/somerandomfoldername/backup.sql
#
#
echo "Export complete..."
#
echo "Gzipping sql file..."
#
#
gtar -czf /home/user/domains/domain.com/public_html/somerandomfoldername/backup.sql.tar.gz /home/user/domains/domain.com/public_html/somerandomfoldername/backup.sql
#
echo "Gzipped successfully..."
echo "Deleting SQL file..."
#
#
rm -rf /home/user/domains/domain.com/public_html/somerandomfoldername/backup.sql
#
#
echo "Deleted SQL file... Gzipped version is ready for transfer"
#
#
echo "Quitting..."

You're going to have to edit all the locations above to match your own locations. Once you've edited the above script to match your own locations, save it as anything. Save it without an extension, that's what I do anyway, makes no difference. Here's how I've saved it:
http://screensnapr.com/v/BCdXjo.png

You'll obviously need this file on your server/vps, so you can either run this command:
"nano backupmysql" OR "pico backupmysql" (whichever works for you)
And then copy the contents here and paste it to it (Shift + Insert to paste) and save it (Ctrl + O for that).

After that, you need to give it the bare minimum execute permissions, so you could run this command:
chmod 0755 backupmysql

Now, there's two ways to execute this file. One, you can move it to any location (say '/' or '/tmp' or '/usr/local/src') and run it from there (using '/tmp/backupmysql' or '/usr/local/src/backupmysql') or you could move it to '/usr/bin' and do a 'chmod +x backupmysql' to run it as a system command.

Everytime this script is run, it'll remove the previously backed up file, generate a new backup, and then compress it and delete the uncompressed file. The compressed file is then ready to be downloaded, transferred to another server, or whatever it is that you want to do with it. Here's a screenshot:




4) This deals with protecting your directory where you intend to store your backups using the basic http authentication (.htaccess/.htpasswd). I won't be covering the steps to do this since it's common knowledge and can be found almost everywhere. But, to help you guys out, here's an online utility that should get a lot of stuff done for you:
http://tools.dynamicdrive.com/password/

The .htaccess file must reside inside the directory you wish to protect. In this case, the directory where your mysql databases will be dumped.


I wrote this tutorial in a hurry right now, but I'll obviously refine it and make it more organized and probably add to it, etc over the days.

If you've got something you'd like to add to this tutorial, please be my guest and let me know. If there's an improvement to the script that you would like to suggest, please let me know. All I ask is that you do not take this script, add a few lines to it, give it a fancy name and start charging people money for it. Please, don't do that.

Lifetalk Reviewed by Lifetalk on 23rd Jan 2011. Semi-automated MySQL Database Backup Using SSH :) With the fiasco on KWWH these days with webhosts going outta business, going down, getting compromised, etc I figured I would share a very simple command line bash sorta tool that I use to perform a semi-automated backup. The point is, with these host troubles, their clients are the ones losing out; not the webhost itself. And then everyone blames the clients for not taking backups. True, taking a backup is a pain in the ass. What with all the logging in, entering passwords, downloading it, Rating: 5

[CJ]

oh nice share man. Definately would help ppl suffered from data loss

[vizoomer]

nice share m8

[Netguy]

Nice Share ! Very Useful for me

[Mr Happy]

Kudos lifetalk. Theirs way too much copy and paste from people on KWWHunction and it's great too see someone write something that's needed and also easy to follow. Great topic.

[desiboy]

Nice , many members need it .
Backups play a very important role .

something similar, I wrote 1 year ago is here
http://www.besthostingforums.com/showthread.php?t=21765 ,it can even upload to RS

[Lifetalk]

Kudos lifetalk. Theirs way too much copy and paste from people on KWWHunction and it's great too see someone write something that's needed and also easy to follow. Great topic.

Cheers

Nice , many members need it .
Backups play a very important role .

something similar, I wrote 1 year ago is here
http://www.besthostingforums.com/showthread.php?t=21765 ,it can even upload to RS

Now that's what I call 'actual' bash scripting. This here is just a couple commands strung together in a file to make it all the more easy to execute.


Like I said, this is still kinda under developed. I'm going to improve on this guide a lot more in the coming days. Probably make it upload to filehosts and/or remote FTP servers, etc too.