How to Schedule a Backup of All MySQL Databases on Ubuntu

    Posted in Linux Servers on Nov 16, 2018

    Step 1: Create the Backup Script

    Create a folder to store your backup script in. I suggest /scripts for this example: sudo mkdir /scripts

    Create a file called mysql-backup.sh inside the scripts folder:

    sudo vim /scripts/mysql-backup.sh

    Add the following code to the file and save it:

    #!/bin/bash
    #----------------------------------------
    # OPTIONS
    #----------------------------------------
    USER='root'       # MySQL User
    PASSWORD='webdev' # MySQL Password
    DAYS_TO_KEEP=0    # 0 to keep forever
    GZIP=1            # 1 = Compress
    BACKUP_PATH='/backups/mysql'
    #----------------------------------------
    
    # Create the backup folder
    if [ ! -d $BACKUP_PATH ]; then
    mkdir -p $BACKUP_PATH
    fi
    
    # Get list of database names
    databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`
    
    for db in $databases; do
    
    if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
    echo "Skipping database: $db"
    continue
    fi
    
    date=$(date -I)
    if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"      
    mysqldump -u $USER -p$PASSWORD --databases $db > $BACKUP_PATH/$date-$db.sql
    else
    echo "Backing up database: $db with compression"
    mysqldump -u $USER -p$PASSWORD --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
    fi
    done
    
    # Delete old backups
    if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
    echo "Deleting backups older than $DAYS_TO_KEEP days"
    find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
    fi

    You will notice 5 configurable options at the beginning of this script. The main ones you will need to edit are the USER and PASSWORD. This should be changed to a MySQL user and password that has permissions to list and backup databases.

    Once you have modified the options, make the script executable with the following command:

    sudo chmod +x mysql-backup.sh

    You can now test the backup script by running:

    sudo ./mysql-backup.sh

    Step 2: Create the Crontab Scheduled Task

    Now we will schedule the backup script to be run daily. We will do this by adding a call to the mysql-backup.sh script to the root crontab.

    Run the following command to open the root crontab file:

    sudo crontab -e

    Now add to the last line of the file the following:

    @daily sh /scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

    Save the file and then wait for the script to run. You can check for errors in /var/log/mysql-backup.log. Once you are happy it is working, you can remove the >> /var/log/mysql-backup.log 2>&1 from the crontab file.