How To Backup MySQL DBs Regularly Using Cron Jobs

[GameUltra 0]

I remember seeing a guide on setting up a cron job that would back up a MySQL database and e-mail it to a given e-mail address, w/o a php file (I think) does anybody know how to do this or where to find that guide? I can't find it now...

Edited October 29, 2005 by microscopic^earthling (see edit history)

[miCRoSCoPiC^eaRthLinG 0]

I remember seeing a guide on setting up a cron job that would back up a MySQL database and e-mail it to a given e-mail address, w/o a php file (I think) does anybody know how to do this or where to find that guide?  I can't find it now...

1064327854[/snapback]

????[/tab]It should be quite easily done with the command crontab - which creates the cron table for the current user. Crontab takes the following parameters:

crontab filename????????Install filename as your crontab file.

crontab -e????????Edit your crontab file.

crontab -l????????Show your crontab file.

crontab -r????????Remove your crontab file.

 

????You simply go about editing/creating a cron file. But first you need to check whether you have a cron file at all. You do that by issuing:

CONSOLE

shell> crontab -l

no crontab for root

As you can see, the message says that there's no cron file for me yet.

 

????So I go about creating a cron file by simply using the -e option (edit)

CONSOLE

shell> crontab -e

no crontab for root - using an empty one

 

...

Your standard editor opens up a blank file. Most likely it's going to be openend up in the VI editor, which is default on most systems.

 

????Now you need to put in the actual data. Each entry in a crontab file consists of six fields, specified in the following order:

 

minute(s) hour(s) day(s) month(s) weekday(s) command(s)

 

The fields are separated by spaces or tabs. The first five are integer patterns and the sixth is the command to be executed. The following table briefly describes each of the fields:

Field????Value????Description

minute????0-59????The exact minute that the command sequence executes.

hour????0-23????The hour of the day that the command sequence executes.

day????1-31????The day of the month that the command sequence executes.

month????1-12????The month of the year that the command sequence executes.

weekday????0-6????The day of the week that the command sequence executes. Sunday=0, Monday = 1, Tuesday = 2, and so forth.

command????TEXT[tab]The complete command sequence variable that is to be executed.

 

Source: Linux manpages

 

Each of the patterns from the first five fields may either be an asterisk (*) (meaning all legal values) or a list of elements separated by commas.

 

An example for backing up all the mysql databases at 12 midnight everyday would be:

0 0 * * * mysqldump -u YourUserName -pYourPassword --databases mydb1 mydb2 > /home/backups/mysqlbackup.sql

 

That's what should go into the crontab file in the editor. This will create regular backups of the databases mydb1 and mydb2 at sharp 12 midnight everyday of the month/year and dump the backup into a file called mysqlbackup.sql located in the /home/backups directory.

 

Next command would be to mail it to yourself. You can use the standard mail command to mail the file to yourself. Say do it a minute later than the backup command.

1 0 * * * mail ...

 

errr.. I forgot the exact syntax of the mail command. Maybe someone else can help you with it - or when I figure it out, will let you know

 

Hope this helps.

Regards,

m^e