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.

There's 2 ways to do this.

If you're using Linux or a Mac, you can use rsync to download files:

rsync -av cpuser@yoursite.com:/home/cpuser/backup/yourbackupfile.tar.gz /home/yourlocaluser/wherever

Just add a cronjob for this and you're done.

If you're using Windows, you have 2 options.  Install Cygwin to get access to the rsync and crontab commands (probably best method long term), or use a program like DeltaCopy.

StephenW wrote:

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.)

You can use find and xargs like this:

cd ~/your-backups ; find -atime 5 | xargs rm

to delete files created more than 5 days ago.

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

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?

Yes.  SQL dump files are usually just text, so they compress very well.

You can use find and xargs like this:

cd ~/your-backups ; find -atime 5 | xargs rm

to delete files created more than 5 days ago.

Not a problem, we're here to help!

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

[client]
user = cpaneluser
password = cpaneluserpw

Replace cpaneluser with your cPanel/FTP/SSH username, and cpaneluserpw with the password to that user.  To ensure it has secure permissions, run in shell:

chmod 0600 ~/.my.cnf

Once you've done that, you can test it my simply running:

mysql

Once you do that, you should see something similar to:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 546048
Server version: 5.0.87-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

which means its working.  You can then run mysqldump commands without user and password parameters, like:

mysqldump dbname > dbname.sql

and it should work fine.

#!/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

I strongly recommend using the ~/my.cnf file instead of putting the password in your script.  When doing this, you can use the 'mysql' command without having to enter the password either.  If you ever change your password you'll just have to change it in one place and not modify your script.  Plus this file is more secure since its permissions are only 0600.

Hi Robert,
   I think you have convinced me that I shoul dus the my.cnf file, I don't know how to use it. Did you explain it already, or give me a link to it already and I just missed it? If you  haven't explained it, could you give me a link or tell me how to use it?  I'll look again through your messages, etc, when I get time. If I don't find anything, I'll do some more searching on the internet to see if I can find info about how to use a my.cnf file.

Thanks for you advice. Much appreciated.

Edit: I looked back in this message  thread at the info you gave about the my.cnf file. I don't understand how to use it. So I looked on the Internet and found this page: How do I create a .my.cnf MySQL preference file?. I'll read it over a little later. It looks promising.

Best Wishes,
Stephen

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.

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`

The ~/.my.cnf File

StephenW wrote:

My.Cnf File
Then I created a ~/.my.cnf in my home directory and entered this info into it.

[client]
user =   databaseuser
password = databasepassword

I gave this file permissions of 0600

With the way that I have cron set up, I don't see that this ~/.my.cnf file is of any use. Do I really need this file?

Best Wishes,
Stephen

Steve,

I strongly recommend using the ~/my.cnf file instead of putting the password in your script.  When doing this, you can use the 'mysql' command without having to enter the password either.  If you ever change your password you'll just have to change it in one place and not modify your script.  Plus this file is more secure since its permissions are only 0600.

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

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

• 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.

Just a note: I found some of the things on my wish list:
-->On this web page Compressing mysqldump output I found a way to compress the output of the MySqlDump files.  I have already added this to my backup script.
-->On this web page: Backing up MySQL on the crontab with a shell script is a way to append the date to the MySqlDump backup files. The output file looks like this: "FileName_�Dec-29-09�.sql". However, before I use this, I need to find a script that will delete the saved files after x-days, otherwise the backup files could fill up the disk space.

Although thebelow message didn't give the script, the idea given here (of totally automatic database backups, compression (& my date added to file name), and automatic download to my computer) is something like what I had in mind, with perhaps some variations:
Looking for MySQL Backup program

Stephen

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

With the way that I have cron set up, I don't see that this ~/.my.cnf file is of any use. Do I really need this file?

Best Wishes,
Stephen

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

Script File
I created a folder in the home directory named "bin"
I created the script file in the ~/bin folder and named it "MySQL-db-backups.sh", and then pasted this script into it. (I pasted my actual database names and passwords into the actual file instead of those shown here)

#!/bin/sh
mysqldump -u databaseusername -pdatabasepassword databasename1 > ~/db-backups/dumpfilename1.sql
mysqldump -u databaseusername -pdatabasepassword databasename2 > ~/db-backups/dumpfilename2.sql
mysqldump -u databaseusername3 -pdatabasepassword databasename3 > ~/db-backups/dumpfilename3.sql
mysqldump -u databaseusername4 -pdatabasepassword databasename3 > ~/db-backups/dumpfilename4.sql
mysqldump -u databaseusername5 -pdatabasepassword databasename3 > ~/db-backups/dumpfilename5.sql
mysqldump -u databaseusername6 -pdatabasepassword databasename3 > ~/db-backups/dumpfilename6.sql
mysqldump -u databaseusername7 -pdatabasepassword databasename3 > ~/db-backups/dumpfilename7.sql

I gave the "MySQL-db-backups.sh" file permissions of 0755

Created a folder in the home directory of ~/db-backups  with permissions of 0755.

My.Cnf File
Then I created a ~/.my.cnf in my home directory and entered this info into it.

[client]
user =   databaseuser
password = databasepassword

I gave this file permissions of 0600

Cron Job Test
Since I didn't know how else to test the script, I set up this temporary  cronjob to run every 10 minutes. (which is a short enough time to see if it will actually work.)

*/10      *      *      *      *       wget ~bin/MySQL-db-backups.sh >/dev/null

As I waited about 30 minutes (as I wrote out this message), I kept checking the ~/db-backups folder (where the backups should be going). However, the folder is still empty, so obviously I'm doing something wrong.  I then deleted that cron job.

What am I doing wrong? What am I missing?

Best wishes,
Stephen