Backing Up the Databases

SSH keys are definitely the way to go here.  Please see:

http://wiki.ocssolutions.com/Generating_an_SSH_key

for information on how to generate an SSH key.  With it, you can automatically log in.

PuTTY doesn't really do this itself, but you can create as script to do this and set it up on a cronjob to run automatically.  Your shell script would look something like:

#!/bin/sh

mysqldump -e -u yourcpuser yourdbname > ~/db-backups/yourdbname.sql
mysqldump -e -u yourcpuser yourdbname2 > ~/db-backups/yourdbname2.sql

Replace yourcpuser with your cPanel/FTP/SSH username, and yourdbname with your database name.  yourdbname2 represents another database name, and you can have as many as you want there.

Save the script as ~/bin/db-backup or similar and give it executable permissions:

chmod 0755 ~/bin/db-backup

Then make sure you have a ~/.my.cnf file in your home directory with the right permissions.  Its format is:

[client]
user = yourcpuser
pass = yourpass

then make sure the file has the right permissions:

chmod 0600 ~/.my.cnf

Test your backup script.  It should run fine.  Then, once it does, you can add it for nightly execution by running:

crontab -e

It will open up an editor.  On the last line, add:

00 9 * * * /home/youruser/bin/db-backup

(replace youruser with your cPanel/FTP/SSH user)

This does execute it at 9 AM, but since the servers use UTC/GMT, this is 3 AM for CST.

Hi Rob.
I tried out your above code and couldn't get it to work. (Maybe I just wasn't doing something right.) I did a lot of experimenting as well as a lot of searching on the internet. I came up with this code that works when I copy it into Putty.

mysqldump -u databaseusername -pdatabasepassword databasename > ~/db-backups/dumpfilename1.sql

It's a slight variation of the code I found on this page. Via SSH / MySQL CLI command interface: Dumping a database into a file:

When I paste the code into putty, it copies the database into the directory "~/db-backups". (I created the folder named "db-backups" in the home directory before I started.)

So, looking at your example, adding the "#!/bin/sh" to the top of the file, it would look like this?

#!/bin/sh
mysqldump -u databaseusername -pdatabasepassword databasename1 > ~/db-backups/dumpfilename1.sql
mysqldump -u databaseusername -pdatabasepassword databasename2 > ~/db-backups/dumpfilename2.sql

(the -p goes (without a space) in front of the databasepassword).
The dumpfilename can be the same or different than the databasename.

I have about 7 or more databases. The next thing I'd like to figure out is to put the code to backup all the databases into a file (I'm guessing somewhere in the home directory) that will be executed when it is accessed by cron. When I create that file, I guess that I'm supposed to put this code "#!/bin/sh" at the top of the file, as in the above example.  What does "#!/bin/sh" mean? What does it do in the file?

Edit: I found these links: #!/bin/sh and What does the line: #!/usr/bin/sh do?.

Best Wishes,
Stephen

Last edited by StephenW (Dec 27th, 2009 10:49:51 am)

One thing I forgot to ask: How do I test the backup script? Do I somehow test it using PuTTy, or in cPanel, or..... Where do I test the script and what commands do I use?

Best Wishes,
Stephen

Hi Rob,
I'm still trying to get this to work:

To see what I might have done wrong, and to see if I could set up a cron job using Putty, I decided to use the code you supplied, and then to try out some modifications
First I connected with PuTTy and entered this code

crontab -e

Then from the next window I entered this code:

10 * * * * /home/winterss/bin/db-backup

That didn't work.

A Successful Test on PuTTy
After some trial and error, I finally came up with this code. I pasted this into Putty....

~/bin/MySQL-db-backups.sh

.... and it executed my "MySQL-db-backups.sh" script file, I saw that it had worked. (I check in the db-backups folder and found that it made a backup of all my MySQL databases.) Now I'll set up a cron job and test that out.

I created this cron job to test it out. I think that this is set to run every 5 minutes, so that I don't have to wait as long to see if it works.

5      *      *      *      *       ~/bin/MySQL-db-backups.sh

That didn't work, so I created another cron job:

SUCCESS!!!
I set up this cron job to run every 5 minutes.....

*/5      *      *      *      *       ~/bin/MySQL-db-backups.sh

.... and found that it exported the copies into the ~/db-backups folder as it was supposed to. I let it run twice and then changed it to run once a day.

This is what the final cron job looks like.

0      21      *      *      *       ~/bin/MySQL-db-backups.sh

Am I correct in assuming that it will just overwrite the backup files each day?

This has been a looong day. I spent some hours figuring this out. (probably someone who knew what they were doing could have done it in only a few minutes.). Anyway, I feel good that I got this step finished.

Now, the next questions:
1.) Is it possible to have each database backup file have the date appended to the end of the name (so that the backups don't overwrite the existing backups), and then to have it automatically delete the previous backups that are more than x-days old (so that the backups don't fill up the disk space)?
2.) Is it possible to have those backup files automatically downloaded and backed up on my computer.

Bests Wishes,
Stephen

Best Wishes,
Stephen

Last edited by StephenW (Dec 28th, 2009 12:35:40 am)

Lastest Report

Hi Robert,
  Just a note to let you know that I finally have Putty with the SSH Keys set up and working.
I have set automatic login (without having to enter the passphrase each time) by setting that up in Pageant. So now I can just start Putty and it automatically logs in.
I've also learned to use Putty PSFTP. It also automatically logs in without having to enter a password or passphrase. I've learned that I can just FTP all the MySQL backup files from the website home/db-backups directory to my computer by just changing directories ~/db-backups and then entering "mget *.sql" into the  Putty PSFTP terminal window.

RECAP
So, as a recap: All the dabases are automatically backed up into the ~/db-backups directory each night. (I might end up changing that to once every few days or once a week. But I decide that later.)

Then, all I have to do is run the PuTTy PSFTP program, when it logs into my website home directory, change directory to  ~/db-backups and enter "mget *.sql" and all the databases are downloaded into the My Documents folder.

Although it was quite a bit of work and quite a challenge for me, this new way is so much simpler and quicker  than my old way to backup the databases.

Robert, thank you so very much for helping me to get transferred to you servers and helping me to get started in this new way of doing things. In the end I can see that this will be much simpler to keep the website files backed up.

WISH LIST
Here are some things I still hope to do.

• I still want to change the download directory that PSFTP downloads to on my computer.

• I'd like to figure out a way to automatically have the backup files downloaded onto my computer. I just found a website that might be able to help me figure this out:  Creating Unattended FTP Scripts. I still have to read it over and possibly do some experimenting.

• I'd also like to figure out how to have the date appended to the MySQL backup files when they are created on the server.

• I will probably set up ways to back up the other files in my account (such as the various Drupal and image files)

• I may have others things to add here as I remember them.

Best Wishes,
Stephen

Last edited by StephenW (Dec 28th, 2009 04:14:24 pm)

I don't have time to examine it closely right now, but I think I found some info about making a script to delete old backup files here:  4.5.4. mysqldump — A Database Backup Program

I don't know if I got all the necessary code from that page (If necessary, I'll look more later), but here is a sample of the code I found:

# DO NOT DELETE AUTOMATICALLY FOR NOW, MAYBE LATER

DELETE_EXPIRED_AUTOMATICALLY="TRUE"

# DELETE EXPIRED BACKUPS THAT ARE MORE THAN
# expire_minutes=$(( 1 * 30 )) # 30 minutes old
# expire_minutes=$(( 60 * 24 )) # 1 day old
# expire_minutes=$(( 60 * 24 * 7 )) # 7 days old
# expire_minutes=$(( 60 * 24 * 30 )) # 30 days old

expire_minutes=$(( 60 * 24 * 7 )) # 7 days old

if [ $expire_minutes -gt 1440 ]; then
    expire_days=$(( $expire_minutes /1440 ))
else
    expire_days=0
fi

....and I found a little more code on this page: 4.5.4. mysqldump — A Database Backup Program (Don't know if any of it will be useful, but I'll look at it when I have a little more time.

Best Wishes,
Stephen

Last edited by StephenW (Dec 29th, 2009 11:26:35 am)

No problem!  Glad to help.

For automatic downloads to your computer, check out rsync.  It's not for Windows, so you'll have to use Cygwin.   Setting this up is beyond the scope of this post, but you can use SSH keys with that, so you won't have to enter a password, and rsync downloads only what has changed, saving bandwidth and time.

You can append the date using something like this:

mysqldump dbname > dbname1-`date +%m-%d-%Y`

Hi Rob,
   I've tried to use the a bunch of times to use a script with the .my.cnf file, but I must be doing something wrong.
I created a test script called "dbtest.sh"

#!/bin/sh
mysqldump -e -u databaseuser databasename1 > ~/db-backups/databasename1.sql
mysqldump -u databaseuser databasename2 > ~/db-backups/databasename2.sql

# mysqldump -e -u yourcpuser yourdbname > ~/db-backups/yourdbname.sql

# mysqldump -u USER -p DATABASE > filename.sql

(The bottom two (commented out) lines are for my reference, one line of instructions from you and one from another place.)

I created this .my.cnf file first.

[client]
user = databaseusername
password = databasepassword

When I still  permission denied errors, I change the .my.cnf to this:

[client]
user = databaseusername
password = databasepassword
host = Localhost

When I have more time, I'll go through it again. At this point I still just don't understand how it is supposed to work and what to do. I don't have any more time to work on it tonight.

More later,
Best Wishes,
Stephen

Last edited by StephenW (Dec 31st, 2009 02:09:36 am)

Hi Robert,
   Thank you very much for your help. I followed your instructions in the previous message, corrected the .my.cnf file to include the cpanelusername and cpanelpassword, changed the database backup script to get rid of the username and password, and got the got the backup script to work by entering  "~/bin/MySQL-DatabaseBackups.sh*" into the PuTTy command line.
(*MySQL-DatabaseBackups.sh is the name of my backup script).

I have the script set up like this for each database.

mysqldump  cpaneluser_databasename | gzip > ~/db-backups/database-`date +%m-%d-%Y`.sql.gz

It creates a backup like this: databasename-01-01-2010.sql.gz for each database.
the gzipped file is about 1/5 to 1/8 of the regular .sql backup file. Does that sound about right, about the gzipped file being so much smaller than the .sql file?

I still have to do some tests on importing the gzipped backup files into some database files to make sure that they work.

Then corrected my entry in cron to use "~/bin/MySQL-DatabaseBackups.sh"

Using your instructions above, I've appended the date to the database backup name. I did a test run, via Putty, to verify that it worked, and it does. Next I've also change the cron job run to once a week, for now, so it won't fillup the disk space so rapidly.

0      0      *      *      0       ~/bin/MySQL-DatabaseBackups.sh

Do you know how to set up a script to automatically delete the old database backup copies when they get x-days old. (or however that might be done.)

Robert. Thank you so much for all the help that you've given me.

Best Wishes,
Stephen

Last edited by StephenW (Jan 1st, 2010 04:50:00 am)

Hi Rob,
Thank you very much for giving me that script. It is a great beginning point!

I tested your script using PuTTy and found that it deleted my backups that were only 3 days old, which wasn't what I wanted. So I searched the Internet and found this page about FIND. I had to make a slight change to your script.  The "-" before atime means it would find any files less than 5 days old. I then change the script to be like this:

cd ~/db-backups ; find +atime 5 | xargs rm *.gz

The "*.gz" at the end means that it will find all the files that end in ".gz".

It tested that code again using PuTTy, and it worked fine. It deleted all the files ending in .gz that were older than 5 days.

Then I created a file in ~/bin that I named "RemoveOldMySQL-backups.sh". This is the contents of the file:

#!/bin/sh
# change directory to home/db-backups
# find all files ending in .gz that are over 30 days old and remove (delete) them
cd ~/db-backups ; find +atime 30 | xargs rm *.gz

This code should find and delete all .gz files that are older than 30 days.

I then set up a cron job.....

30      0      1,15      *      *       ~/bin/RemoveOldMySQL-backups.sh

... to activate tha "RemoveOldMySQL-backups.sh" file twice a month, on the 1st and on the 15th of each month

I'll just need to check on the backup files, on the website and on my computer, now and then to make sure they continue to work as they are supposed to work.

Now that the website is set up to automatically back up the MySQL databases, I just have to figure out a way to automate the download of the backup files to my computer.

Best Wishes,
Stephen

Last edited by StephenW (Jan 13th, 2010 01:50:57 pm)