Auto backup database

[Lord Harry]

Hi, I search for Auto backup database and found this very usefull:

The guide is written for people who have Linux Webhosts with a CPanel interface, including access to CRON.

I assume that you know how to create folders, upload files, and CHMOD them on your webserver.

My host has set up my hosting account so my webspace is in the /home//public_html folder.
So I have created a /home//backup folder. This is where I have my backup scripts and where the backup file is created. The folder is CHMOD 755.

In the /home//backup folder I have a file called wbackup.crn It is an ASCII text file, CHMOD 755 to allow it to be executed. It contains Linux 'command line' instructions that may not be familiar to people who only access their webspace through browsers. These are the contents of the file:

rename .php .bak /home/<accountname>/public_html/forums/index.php
rename down index /home/<accountname>/public_html/forums/down.php
#
rename .php .bak /home/<accountname>/public_html/index.php
rename down index /home/<accountname>/public_html/down.php
#
mysqldump --user=<accountname> --password=<password> --opt <dbasename> | gzip > /home/<accountname>/backup/dbsitebackupw_`date +%Y_%m_%d`.gz
#
tar -cf /home/<accountname>/backup/tarbackupw_`date +%Y_%m_%d`.tar `find /home/<accountname>/public_html/forums/uploads/ -type f -mtime -7`
#
gzip /home/<accountname>/backup/tarbackupw_`date +%Y_%m_%d`.tar
#
rename index down /home/<accountname>/public_html/index.php
rename .bak .php /home/<accountname>/public_html/index.bak
#
rename index down /home/<accountname>/public_html/forums/index.php
rename .bak .php /home/<accountname>/public_html/forums/index.bak
#
exit

The # lines are comments to make the file more readable. Let's look at what these lines do.

rename .php .bak /home/<accountname>/public_html/forums/index.php
rename down index /home/<accountname>/public_html/forums/down.php

Here I rename the FORUMS home page index.php to index.bak, and a file of my own (down.php) is renamed to index.php. down.php is a small HTML file that tells the user that the boards are temporarily down while the database is backed up. I like to do this to prevent any users from being able to read or write the database. Even taking the board offline allows database reads, so I prefer this technique.

rename .php .bak /home/<accountname>/public_html/index.php
rename down index /home/<accountname>/public_html/down.php

Here I do the exact same thing in the public_html folder to the SITE home page. This is because the site home page has various database driven features that I want disabling while I run the backup.

mysqldump --user=<accountname> --password=<password> --opt <dbasename> | gzip > /home/<accountname>/backup/dbsitebackupw_`date +%Y_%m_%d`.gz

This line is the command to backup the database using mysqldump, and then 'pipe' the backup into a file through gzip. This produces a compressed zip file called /home//backup/dbsitebackupw_`date +%Y_%m_%d`.gz .

There are 2 important things here. The backup file is put in the backup folder and so is unreachable by typing a URL in your browser. This makes things a bit more secure. The other thing is the filename. The `date +%Y_%m_%d` command means that the file has a datestamp built into its name thereby making the filename unique, providing that backups are not run more than once per day!

tar -cf /home/<accountname>/backup/tarbackupw_`date +%Y_%m_%d`.tar `find /home/<accountname>/public_html/forums/uploads/ -type f -mtime -7`
#
gzip /home/<accountname>/backup/tarbackupw_`date +%Y_%m_%d`.tar

Here a 'tarball' is created of all files in the upload folder that have changed in the last 7 days. A 'tarball' is essentially one file that contains several files all scrunched together, so the gzip line then zips this file to compress it. Again a datestamp is included in the file name to make it unique.

rename index down /home/<accountname>/public_html/index.php
rename .bak .php /home/<accountname>/public_html/index.bak
#
rename index down /home/<accountname>/public_html/forums/index.php
rename .bak .php /home/<accountname>/public_html/forums/index.bak
#
exit

These lines rename everything back the other way, reversing what was done at the start of the script. When these commands have completed the site and forums will be back online.

The exit command tells CRON that the script is finished.

So now I have my backup script file I need to find a way to make sure it gets run every week. For this, I use the CPanel CRON feature. CRON is a scheduling tool. It runs all the time on the server and looks to see whether there are any scripts to be run. It runs them at specified times that it finds in the CRONtab. The CPanel interface allows you to add and delete scripts to the CRONtab. On my CPanel it is in the Advanced section.

Clicking on the CPanel Cron jobs link, I am asked whether I want to use Standard Mode or Advanced Mode. I choose Standard Mode.

In the text box labelled 'command to run:' I type '/home//backup/wbackup.crn' This is the full path and filename to the script above. Then, using the lists that are there I choose a day of the week and time for the script to run.

It's important to choose a time when your site is quiet. It's also important to understand that the time you choose is SERVER time, which may or may not be the same as the time zone that you live in!

In Cpanel , clicking 'Save Crontab' adds your script to the CRON schedules.

That's all there is to it. You now have a backup script scheduled to run on your server that creates a couple of zipped files for you to download later. Even better, it runs regularly without you needing to do anything at all. At your leisure, you can download the backups from the server to your PC.

I have made this script deliberately simple. There's all sorts of extras you could implement like emailing the zip files to you, FTPing them to another server, emailing you if the script succeeds (you normally get an email if it fails!)

Notes:

1. To make this script work for you, you need to change anything in <> brackets to the correct names for your server. You should also check that the file paths that I use in these examples are adjusted to suit your own.
2. To produce your own .crn files, you can use any editor that allows you to save in text format. The .crn file extension is not mandatory, but recommended.

I want it send the database to my email every time it dose a backup, so I add this command:

mutt -s "Your Database" -a /home//backup/dbsitebackupw_`date +%Y_%m_%d`.gz myemail@yahoo.com < /home//backup/mailmessage.txt

but it can only work with a small database, if my forum have a large database, it will not send.

can someone help me to solve this?
thank you very much