MySQL Backup Script

mysqlbackup.sh

#!/bin/bash

# License: Eclipse Public License - v 2.0 (https://www.eclipse.org/org/documents/epl-2.0/EPL-2.0.html)
# Updated on November 7, 2022

# Derived from
# http://blog.snowfrog.net/2005/11/16/backup-multiple-databases-into-separate-files/
# Backup each mysql db into a different file, rather than one big file
# as with --all-databases - will make restores easier

USER="<user>"
PASSWORD="<password>"
OUTPUTDIR="/home/sqlbackup"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
datum=`/bin/date +%Y%m%d-%H`

# clean up any old backups - save space. Delete older than 2 days
for file in "$( find /home/sqlbackup -type f -mtime +2 )"
do
  rm -f $file
done

# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema');" | tr -d "| " | grep -v "SCHEMA_NAME"`

# dump each database in turn
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db | gzip > "$OUTPUTDIR/$db-${datum}.bak.gz"
done