Crontab timed mysql dump and commands

Crontab is timing program included in Linux systems. With crontab you can execute commands automatically on specific times and dates.

To open crontab give command $crontab -e 

You can run this command with root or non root person. If you want to do root things or you need to do command as root use root.

* * * * * command
┬ ┬ ┬ ┬ ┬
│ │ │ │ │
│ │ │ │ └──── day of the week (0–7) (sunday = 0 or 7)
│ │ │ └────── month (1–12)
│ │ └──────── day (1–31)
│ └────────── hours (0–23)
└──────────── minutes (0–59)

Here are few examples how to use crontab. For example I want to make a tarball from my homepage. This command would do the tarball under /home/shnigi and tarball name would be mysite.tar.gz. The tarball is created from /home/shnigi/public_html the command would run every day 15:12

12 15 * * * tar -czvf /home/shnigi/mysite.tar.gz /home/shnigi/public_html

To backup your MySQL database use command:

30 15 * * * /usr/bin/mysqldump --all-databases > /home/shnigi/dump.sql -u root -pPASSWORD

This would run every day 15:30 and take backup about all databases to /home/shnigi/dump.sql. Note that password is inserted right after the -p without space. The code under this is the same, but it generates date for every mysql dump file.

12 16 * * * /usr/bin/mysqldump --all-databases > /home/dump-$(date +\%d\%m\%y).sql -u root -p

You can try these commands without crontab, just use these:

#SQL dump
32 16 * * * /usr/bin/mysqldump --routines --flush-privileges --all-databases > /home/dump-$(date +\%d\%m\%y).sql -u root -pPASSWORD

#backup all home folders
26 16 * * * tar -czvf /home/homefolders.tar.gz /home

Sources:
https://www.linode.com/docs/databases/mysql/back-up-your-mysql-databases
http://www.thegeekstuff.com/2011/07/cron-every-5-minutes/