Update the setup variables in the script below and create in root’s home folder with permissions set to 700 (e.g. /root/.scripts/mysql_backup)

#!/bin/bash
 
# MySQL Server Setup
MUSER="root"
MPASS="rootpassword"
MHOST="localhost"
MPATH="/mnt/data/backups/mysql"
DATENUM=`date +%u`
 
# Get all database names
DBNAME="$(mysql -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
 
for db in $DBNAME
do
  if [ ! -d $MPATH/$db ]; then
    mkdir -p $MPATH/$db
  fi
  mysqldump -c -u $MUSER -p$MPASS -h $MHOST --databases $db --single-transaction | gzip -9 > $MPATH/$db/$db.$DATENUM.sql.gz
done

Read more MySQL/MariaDB Database Backup Script for Linux ›