Learn Backup Multiple MySQL Or MariaDB Databases Automatically

February 14, 2020

Table of Contents

    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 IT Web Services instance named db-itweb.services-site, db-itweb.services-blog, db-itweb.services-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-itweb.services-site' 'db-itweb.services-blog' 'db-itweb.services-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

    https://www.itweb.services/tutorials/linux-guides/setup-file-mirroring-using-rsync-in-debian-ubuntu

    Need help?

    Do you need help setting up this on your own service?
    Please contact us and we’ll provide you the best possible quote!