This article would tell you how to create back of database automatically on a regular basis.

Solution (for Unix users):

So, there is a program called cron. It allows to run processed at specified time or with specified frequency. Cron is for Unix only, so if you use Windows OS for your hosting, please contact your hosting-provider to get know how run processes at specified time.

Run crontab in unix shell and create the following rule of running database backup process:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz

This command is executed every day at midnight, creates dump of your database (DBNAME) and compress it with gzip archiver to file with name corresponding to current date. For example, if we create dump on Jan, 3, 2002, the file will be named 2002-01-03.gz.

To get files which names displays their creation date, we use date command. It is standard for all unix-systems. The command allows creating optional date format (in our example: date "+%Y-%m-%d").  We placed this command to backtics – this inserts result of execution one command to another command in unix shell.

Save this rule for cron and wait for result. You will get archived copy of your database on harddrive every day. You can easy find the required archive and restore database. Besides, if you wish to automate deleting old archives, try to use cron & find command which is usual for unix.

Running find ~/folder-with-archives -name "*.gz" -mtime +7 time  after time, you will delete archives which are older than 7 days. Please carefully read documentation for find – it is available by man find in unix shell.

If you have PC which is always connected to the Internet, you can copy created backup there. Provider’s host computer is a good thing but caution is the parent of safety.

Use ftp and scp commands for copying on another PC. If your PC maintains ssh connection, use secure copy client for copying of files. Read documentation for this command on man-page man scp.

Example:

 scp 2002-01-03.gz login@your.host.ru: – 2002-01-03.gz download file on your.host.ru PC logged in with login.