MKRD.info - Thorough articles on a variety of topics, and no ads!

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"

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