How to Backup Site HTML Files and MySql Databases and have them emailed to me daily

[HughKazoo]

So it is apparent that bluehost does not do backups, and that if you do backups you cannot store them on your server.

Looking at a few other threads I am trying some cron jobs to backup my site daily. These are the two cron jobs I am trying (first try tonight):

date=`date -I` ; tar -zcf backup_$date.tgz ./public_html

That should backup the whole public_html folder into a .tgz file with the backup date in the filename.

mysqldump --opt --user=dbusername --password=dbuserpw dbname | gzip > /home/homeuser/mysqldbbackup.`date +\%F`.gz

That should backup the whole mysql database into a .gz file with the backup date in the filename.

Anyone know if this will work??

Anyone know an easy way to get these files from my hosting server space to my local computer? by email or by automated ftp?

IS THERE A BETTER WAY TO MAKE A RESTORABLE BACKUP OF MY WEBSERVER FILES AND DATABASE?

[felgall]

The database backups I run basically use cron to run that command to create a backup copy of the database and I then have another cron that emails that file to me a few minutes later (so as to allow time for the file to be created). The next time I read my emails the file gets automatically downloaded to my own computer. I had already written my own PHP mail script that can handle attachments so I used that (see http://www.felgall.com/php2.htm for more info)

I only backup the database since all the other files are copied from my computer to the hosting in the first place and so I take backup copies of the originals on my computer for those files.

The only thing that I have had problems with is that a couple of times BlueHost have deleted my crons (both times where they were upgrading something) and the only way I have found to keep a backup of those is as entries in a text file that I need to copy and paste back into the crons option in cpanel if they disappear.

[EricJ]

If you're feeling very ambitious you should do this :D :
1. Get a home server up and running with either FreeBSD or CentOS on it.
2. Get a dedicated IP from your ISP.
3. Read (and understand) this entire page: http://www.manpagez.com/man/1/rsync/
4. Once you have an rsync command that is working set it in a cron job to run every week at 2 am MST.

You can set it up to where after the first rsync it will only update files that have changed since the last backup.

As for databases, you're on the right track with that cron job.. here is what mine looks like, I just have it set up to go to an email address that i have setup with POP, so my backups are only on the server for ~24 hours at a time if that.

My SQLdump Cron Job:


mysqldump -ce --user=<user_name> --password=******** <database_name> | mail *********@mydomain.org

I also set up a special mysql user with only the necessary permissions for doing a mysqldump, (the only needed permissions are SELECT and LOCK TABLES).. and i can just use my backup user for all of my db's. not having to keep track of all the different users and passwords that you would need for lots of databases.

[HughKazoo]

You know, all I want to do is have a simple way to backup my public html files and the associated database.

I did not think it should be that complicated.

My files backup worked fine last night. So I've added your mail command to my files backup, it looks like this now:


date=`date -I` ; tar -zcf backup_$date.tgz ./public_html | mail myemail@yahoo.comI have no clue if this will work but I don't want to have to go check it every night and download and delete it so if I can get it to email to me that would be great.

My database backup did not work. I have changed it to what you suggested:


mysqldump -ce --user=zzzzzzzzzzzz --password=yyyyyyyyyyyyy vvvvvvvvvvvv | mail myemail@yahoo.comI don't know, I'm probably missing something important.

Unfortunately one of the features of the website we've created includes a cool cms system. The users are adding inventory items, and if they lose their work because something happens and it is not backed up, I'm on the line.



Web Designers Chicago (http://www.web-designer-chicago.com/) o Michigan Web Designers Forum (Closed) (http://www.grand-rapids-michigan-web-design.info/) o Business Web Design Help (http://www.kallenweb.com/)

[EricJ]

Your SQL dump string looks good, it should work as long as the user has the appropriate permissions, and as long as you didn't make any typing errors.

Sorry if I offended you with pushing rsync, thats just what I would do if i was in your situation. :) rsync is complicated at first, but once you have it setup it is one of the most efficient systems for backing up files that has ever been devised by man.

[HughKazoo]

No insult taken. I am paying 3 times as much to host with BlueHost than I do most of our client's websites. Yet my less expensive host provides regular backups. I want to design websites and have my site backed up regularly, not deal with crazy technical things like cron jobs utilizing geeky unix commands just to keep my data safe. This is just kind of becoming a pet peeve for me, I did not mean to take it out on you.

[EricJ]

Well at least you have a team of helpful customers here to help you out :)..
sorry you feel that way though..

[telefone]

My files backup worked fine last night. So I've added your mail command to my files backup, it looks like this now:


date=`date -I` ; tar -zcf backup_$date.tgz ./public_html | mail myemail@yahoo.com

I have no clue if this will work but I don't want to have to go check it every night and download and delete it so if I can get it to email to me that would be great.


Hi, did you get your script to work?

[redsox9]

Hi, did you get your script to work?

Apparently not; he updated his original post to state that the database backup did not work.

[jamsr]

Apparently not; he updated his original post to state that the database backup did not work.

Thought I would share the cron job that DOES email the bzipped db backup successfully as a file attached to an email.


mysqldump -ce --user=USERNAME --password=PASSWORD database_name | bzip2 > ~/DBBackup.`date +\%F`.tar.bz2; uuencode ~/DBBackup.`date +\%F`.tar.bz2 DBBackup.`date +\%F`.tar.bz2 | mail -s "The new DB Backup" Sendtoemail@address.com; rm ~/DBBackup.`date +\%F`.tar.bz2

I.e. three steps separated by semicolons: mysqldump; uuencode and send mail; delete the file once it has been sent. Hope this comes in handy.

[vwlfan]

I have setup a cron job to backup my wp database using the following format:
mysqldump -ce --user=<user_name> --password=******** <database_name> | mail *********@mydomain.org

The cron job runs and I do get an email in my gmail account. My question is related to the email. The email does not have an attachment file of the dump/backup but is basically a text email with the dump/backup as the email body/message. Is this what I should expect? I am trying to make sure that I have a backup/dump which would be sufficient to restore the database if needed. - Thanks

[jamsr]

What you did is OK, but it'd be easier to send a file attachment, when it comes to restoring. Here's one of mine that does this, adds the date when creating the file, compresses very small:

mysqldump -ce --user=<username> --password=<pass> <databasename> | bzip2 > path/filename.`date +\%F`.tar.bz2; uuencode ~/filename.`date +\%F`.tar.bz2 filename.tar.bz2 | mail -s "This week's DB backup" receiptEmailAddress@gmail.com

After you've done it, tested it a while, etc., you might add a command here that deletes the file once it's been sent.

[vwlfan]

Thanks. I saw your previous post about the cron job you setup. I am going to use your code about and try to get a similar backup job working tonight.

[vwlfan]

jasmr

It took me one or two nights but I got you backup script working, very nice. Everything is working except the name of the attached file that gets sent to my emial. The date tag is correctly applied/attached to the file saved to my root directory but the uuencoded file name that comes as the email attachment does not have keep/have the date. I made another change to my job tonight to see if it pickups up the date.

Again thanks your script works great!

[gibsongk55]

Thought I would share the cron job that DOES email the bzipped db backup successfully as a file attached to an email.


mysqldump -ce --user=USERNAME --password=PASSWORD database_name | bzip2 > ~/DBBackup.`date +\%F`.tar.bz2; uuencode ~/DBBackup.`date +\%F`.tar.bz2 DBBackup.`date +\%F`.tar.bz2 | mail -s "The new DB Backup" Sendtoemail@address.com; rm ~/DBBackup.`date +\%F`.tar.bz2

I.e. three steps separated by semicolons: mysqldump; uuencode and send mail; delete the file once it has been sent. Hope this comes in handy.

Hi,

Thanks for the example. I want to try this but save in gzip. So can i just do a replace of ".tar" to ".gzip" ?

Thanks,

Gibs