MySQL / MariaDB backups to a NAS

Dump every database to its own compressed file, keep a few days, and fail loudly when something is wrong. 
 Requirements 
 
 
 mysqldump or mariadb-dump . Newer MariaDB installs may only have mariadb-dump , so detect which one exists. 
 
 gzip . 
 A stored login for root (see Store MySQL backup credentials safely ). 
 A mounted backup share, for example /mnt/backup . 
 
 The core loop 
 DUMP=$(command -v mysqldump || command -v mariadb-dump) || exit 1
CLI=$(command -v mysql || command -v mariadb) || exit 1
mountpoint -q /mnt/backup || { logger -t mysql-backup "backup share not mounted"; exit 1; }

dbs=$("$CLI" -N -e 'SHOW DATABASES') || { logger -t mysql-backup "cannot connect"; exit 1; }
for db in $dbs; do
 case $db in information_schema|performance_schema|sys) continue;; esac
 "$DUMP" --single-transaction --routines --triggers --complete-insert "$db" \
 | gzip > "/mnt/backup/$(hostname -s)/$(date +%Y.%m.%d_%H).$db.sql.gz" \
 || logger -t mysql-backup "dump of $db failed"
done
find "/mnt/backup/$(hostname -s)" -maxdepth 1 -type f -name '*.sql.gz' -mtime +7 -delete
 
 
 
 --single-transaction gives a consistent dump of InnoDB tables without locking them. 
 Check the SHOW DATABASES step. If the login fails and you don't check, the loop silently backs up nothing. 
 Add set -o pipefail so a failed dump isn't hidden by a successful gzip . 
 
 Schedule 
 Run it only on servers that actually run MySQL: 
 0 * * * * root /usr/local/bin/backup_mysql.sh >/dev/null 2>&1
 
 Hourly dumps kept for 7 days is 168 copies of each database. Daily, or hourly with fewer days kept, is usually plenty. 
 Restore 
 gunzip -c 2026.01.01_03.mydb.sql.gz | mysql mydb