Backup Multiple MySQL Or MariaDB Databases Automatically

Modified on: Mon, Mar 20, 2017 at 1:09 pm EST

Introduction

In this write-up, we'll walk through how to backup multiple MySQL or MariaDB databases that sit on the same machine using a custom bash script and setting up a cron job.

Database Backup(s)

We'll assume we have three MySQL databases on our Vultr instance named db-vultr-site, db-vultr-blog, db-vultr-app (Don't worry about creating these databases, you'll be able to substitute their names for yours hwere they are used in the script on Step 2 below).

  1. Log on to your MySQL or MariaDB database and run below query to create a database user db_user_backups to handle backups

    GRANT LOCK TABLES, SELECT, SHOW VIEW, REPLICATION CLIENT ON *.* TO 'db_user_backups'@'%' IDENTIFIED BY '{COMPLEX-PASSWORD}';
    

    Also run below to make sure MySQL is configured to properly restore stored procedures

    SET GLOBAL log_bin_trust_function_creators = 1;
    
  2. Setup the necessarily directory structure and files needed

    # create backup directory with environment and log file
    sudo mkdir /backups && cd /backups
    sudo touch .env db-backup.sh db-backup.log
    sudo chmod -R 775 /backups
    sudo chmod -R g+s /backups
    sudo chmod +x db-backup.sh
    
    # add mysql backup user credentials into environment file
    echo "export MYSQL_USER=db_user_backups" > /backups/.env
    echo "export MYSQL_PASS={COMPLEX-PASSWORD}" >> /backups/.env
    
  3. Open db-backup.sh nano /backups/db-backup.sh and paste the code below inside it, then save the file (Ctrl+X -> Y -> hit Enter).

    DB_NAMES=( 'db-vultr-site' 'db-vultr-blog' 'db-vultr-app' ) #replace with your own database name(s)
    BKUP_NAMES=()
    BKUP_DIR="/backups"
    
    # get total number of directories
    total_dbs=${#DB_NAMES[@]}
    
    # create backup file names
    for (( i=0; i<${total_dbs}; i++ )); do
        BKUP_NAMES[$i]="`date +%Y%m%d%H%M`-backup-$${DB_NAMES[$i]}.sql.gz"
    done
    
    # get backup users credentials
    source $BKUP_DIR/.env
    
    # create backups
    for (( i=0; i<${total_dbs}; i++ )); do
        # NOTE: --routines flag makes sure stored procedures are also backed up
        mysqldump --routines -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip > ${BKUP_DIR}/${BKUP_NAMES[$i]}
    done
    

    The code above is looping through an array with the name(s) of the database(s) you want to backup and doing so.

Cronjob Setup

Setup a cronjob to run every midnight that runs the backup script and saves the result/output to backup log.

  1. Open crontab

    crontab -e
    
  2. Add below entry to crontab

    0 0 * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

    Note: While testing you can set cronjob to run every 1 minute instead like below

    * * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

    -OR- every 5 minutes (replace 5 with the number of minutes you want)

    */5 * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log
    

What's Next

You will likely not want your backup(s) on the same server(s) running your database(s), but instead on a server in a different geographic location. There are several ways to do this, ranging from using SFTP, to using custom tools provided by the myriad of cloud storage providers available out there. One good alternative is Rsync as explained here - vultr.com/docs/setup-file-mirroring-using-rsync-in-debian-ubuntu

Get started in the SSD Cloud!