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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.