How To Backup MySQL DBs Regularly Using Cron Jobs

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...

Comment/Reply (w/o sign-up)

    [/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[tab]    [/tab]Install filename as your crontab file.
crontab -e[tab]    [/tab]Edit your crontab file.
crontab -l[tab]    [/tab]Show your crontab file.
crontab -r[tab]    [/tab]Remove your crontab file.

[tab]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:

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

    [/tab]So I go about creating a cron file by simply using the -e option (edit)

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.

[tab]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:


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

 

 

 

Comment/Reply (w/o sign-up)