How to create a weekly database backup Cron job

The following procedure is on how to create a Cron job for a weekly backup of all databases. If your website is updated more often, then once-a-week backup is obviously insufficient. Note that there are very complex scripts elsewhere which do a better job at backup.

SSH access is required for this procedure. If your hosting package is Basic or Home User equivalent, you may not have SSH access.

Note: this procedure assumes that you need to schedule Bash-like commands. You can schedule Perl, PHP, or any other command and / or programming language operation to run.

1) Create a file on your server with the extension .sh. For example, name it CronBackup.sh

 

2) Add the following lines in that file, with one line for every database you have. If your databases are all on localhost (cPanel, for example) or on one server, then only a single line can be used. On 1and1 hosting, each database has a different address.

mysqldump -h databaseaddress --user=username --password=password databasename > databasename-backup-'date "+%YY%mM%dD%HH:%MM"'.sql
mysqldump -h databaseaddress2 --user=username2 --password=password2 databasename2 > databasename2-backup-'date "+%YY%mM%dD%HH:%MM"'.sql

......

You can use an editor such as Notepad++, or create and edit the file thru the SSH interface. Note that in a client such as Putty, selecting the text with the left mouse button automatically copies it into the clipboard, and right mouse click pastes contents of the clipboard into the screen.

Remember also that all Linux commands AND FILENAMES are case-sensitive. Type in everything exactly as shown. Do not change capitalization of commands, and specify filenames to those commands exactly as you have created them.

When working with a client such as Putty, do not use your numpad to enter numbers. That client does not properly accept the NumLock status.

 

3) If you need help remembering the passwords, then configuration files of your installed CMS, wiki, or forum software contains those passwords in their configuration files.

Remember that this shell file will contain passwords of all your databases. Therefore, edit its permissions. Take away all "Group" and "Public" permissions, but give the file "Owner" "Execute" permission.

 

4) Test the script by running

bash CronBackup.sh

and verify that backups were created in the root directory of your server space.

 

5) Find out the executable directory of the bash file. Run

whereis bash

and write down (or copy-paste into a separate file) this location.

 

6) Find out the full path to the .sh file we have created. Run

pwd

and write down the result.

 

7) Now we need to set up the Cron task. Type in

crontab -e

to edit a list of Cron tasks. VI editor opens up. Type i to start "insert" editing mode.

Go to a blank line shown as a tilde (~). Type in

0 0 * * 6 /bin/bash /full_path_to_your_file/BackupCron.sh

Briefly, this means:

Run on the 0th minute

On the 0th hour

On any day of the month

On every month

On the fifth day of the week (Sunday is zero)

 

/bin/bash is the path to our binary (executable) for this particular file extension (.sh)

and

/full_path_to_your_file is the server path which you have obtain with the pwd command.

 

8) Press the Esc button, and then type in :x <ENTER> to save

Bash will say "crontab: installing new crontab"

 

 

 

Schedule Field Minute Hour Day of Month Month Day of Week
Example * * * * *
Explanation every minute past the hour of every hour (24hr time) every day of every month every day of the week (Sun (0)-Sat (6))


˅˅˅ Additional valuable information is available at one of the links below: ˅˅˅

 

Did you like the article? Let Google Search know by clicking this button: . Please link to content that you find useful on this website on your own website, forum or blog! You can also comment on this page below, or contact me to ask a question or suggest a topic for me to research. There is a user-editable Wiki available on my website, as well as a Forum that you can contribute to. Site Map.

Page last modified 13-Apr-12 20:55:39 EDT
Comments on this page:


Add a comment to this page
Comment Title:
Your Name:
Your Email Address:
Notify me of new comments to this item:
Additional Comments:
This is a captcha-picture. It is used to prevent mass-access by robots. (see: www.captcha.net)