How to backup multiple MySQL or MariaDB databases automatically

We'll show you how to back up MySQL or MariaDB, which are on the same server, using your own bash script and cron.

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).

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 ON *.*, SHOW VIEW ON *.*, 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;

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

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.

Open crontab

crontab -e

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