Simple MySql daily and weekly backups on Linux

Recently I had a task to create automatic Mysql database backups daily and weekly and keep some number of those backups.

After some short googling I come up with a quite small and simple shell script that just works.

To make this happen we need few following steps:

1. Create a new file in /etc/cron.daily/ folder with execute permission, supposed we switched to root user already

   touch /etc/cron.daily/db-backup-mydb
   chmod 700 /etc/cron.daily/db-backup-mydb
   vim /etc/cron.daily/db-backup-mydb
   

   I used 700 permission so only root user can read the file, cause it will store password to our database

2. Press key “i” and paste the following code:

   #!/bin/sh
   
   # Setup some variables
   backupfolder="/home/myapp/backup"
   user="user"
   prefix="db_daily_"
   now="$(date +'%Y%m%d-%H%M%S')"
   filename="$prefix$now.sql.gz"
   database="mydb"
   db_user="mydbuser"
   password="mypass"
   
   fullpathbackupfile="$backupfolder/$filename"
   logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
   
   # Do a backup
   echo "mysqldump started at $(date +'%Y-%m-%d %H:%M:%S')" >> "$logfile"
   mysqldump --user=$db_user --password=$password --default-character-set=utf8 --single-transaction $database | gzip > "$fullpathbackupfile"
   echo "mysqldump finished at $(date +'%Y-%m-%d %H:%M:%S')" >> "$logfile"
   
   # Change file permission
   chown $user:$user "$fullpathbackupfile"
   chown $user:$user "$logfile"
   echo "file permission changed" >> "$logfile"
   
   # Delete backup files older than 8 days
   find "$backupfolder" -name $prefix* -mtime +8 -exec rm {} \;
   echo "old files deleted" >> "$logfile"
   echo "operation finished at $(date +'%Y-%m-%d %H:%M:%S')" >> "$logfile"
   echo "*****************" >> "$logfile"
   
   exit 0
   

   Also edit variables you need and replace database credentials at line starting with mysqldump after it press “Esc”.

3. Then save the file by pressing “:” (colon), then “wq” and press enter.

4. Run it to rest that it creates backups successfully:

   /etc/cron.daily/db-backup-mydb
   

   When you open your /home/myapp/backups folder you will see sql.gz backup file and a log file with debug information about backup process.

To make weekly backups the only few things you need to change is to place the script into /etc/cron.weekly/ folder, replace filename part “daily” to “weekly” and change “-mtime +8” to “-mtime +56” to keep last 8 weekly backups.