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

   1 2 3

   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:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

   #!/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:

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