Posts tagged with “crontab”

A quick way to run a database update periodically by crontab

  1. put a .my.cnf file into your home directory, for example
$ cat ~/.my.cnf 
[client]
user=yourDbUserName
password=yourDbPassword
host=yourDbHost
  1. write a bash script to update the database
#!/bin/bash

set -ex

variable=`TZ="Africa/Abidjan" date '+%Y-%m-%d %H:%M:%S'`
sql="Your SQL statement with ${variable}"

logDir=/data/Logs/`date '+%Y'`/`date '+%m'`
mkdir -p "${logDir}"
echo "${sql}" >> "${logDir}/`date '+%d'`.sql"
mysql -v -v -e "${sql}" >> "${logDir}/`date '+%d'`.result.txt"
  1. put a line by run crontab -e

the end.

Using crontab to backup your mysql data

  1. on a server with huge disk space, run crontab -e and put the following code in it (you may need to change the directory names to fit your situation)

    30 3 * * * mysqldump --databases DB1 DB2 DB3 DB4 | gzip > /data/backups/mysql/db_`date '+\%Y\%m\%d-\%H\%M\%S'`.sql.gz
30 4 * * * find /data/backups/mysql/db_* -mtime -1 -exec scp {} anotherserver:/data/backups/mysql \;
30 5 * * * find /data/backups/mysql/db_* -mtime +30 -exec rm {} \;

  2. on another server, run crontab -e and put the following line into it:

    30 5 * * * find /data/backups/mysql/db_* -mtime +7 -exec rm {} \;

summary & explanation:

  1. at 3:30 backup MySQL data in a local directory and keep latest 30 days backup in that directory
  2. at 4:30 transfer the latest backup to another server in case the first backup may be destroyed by an accident.
  3. at 5:30 remove the oldest backup on the local backup server (the code shows that the latest 30 days backups are kept on the server).
  4. at 5:30 remove the oldest backup on the remote server (the code shows that only the latest 7 days backups are kept on that server).