Use Crontab Backup Mysql Everyday

Every database system need backup its data. There are serval methods to backup database.
If you use unix-like system, use crontab is a very good and easy method.

1. what is crontab

The crontab is a list of commands that you want to run on a regular schedule, and also the name of the command used to manage that list. Crontab stands for “cron table,” because it uses the job scheduler cron to execute tasks; cron itself is named after “chronos,” the Greek word for time.

To edit the crontab, use this command:

1
$ crontab -e
  • The default crontab file looks like this:

crontab default

  • The information you must include is like this:

crontab default

  • We need to add a line to the bottom of the file which looks like this:
1
0 5 * * * /path/to/do-every-day.sh (shell file must executable, or use sh do-everyday.sh)

2. crontab commands and examples

  • To view your crontab, you can use this command:
1
crontab -l
  • to remove your crontab so that there no jobs are ever executed by cron, use this command:
1
crontab -r
  • Examples Of crontab Entries
1
2
3
4
5
15 6 2 1 * sh /path/to/backup.sh
# Run the shell script /home/melissa/backup.sh on January 2 at 6:15 A.M.

0 9,18 * * Mon sh /path/to/bachup.sh
# Run /home/wendy/script.sh every Monday, at 9 A.M. and 6 P.M.

3. mysql database backup shell

1
2
3
4
5
6
7
8
9
10
11
#!/bin/bash
# database authentacation
user="root"
password="root"
host="localhost"
db_name="da_name"
# backup file save path
backup_path="/path/to/your/home/_backup/mysql"
date=$(date +"%Y%m%d")
# dump command
mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql