Bash Script – Backup All Databases to Separate Files

Here’s a cheap ass script to tar up and dump all of your MySQL databases to separate files using the Debian system maintenance account. Sure there’s plenty of room for improvement here, but hey, it gets the job done and is perfect for a midnight cron job.

Based off of this with a few of my own customizations.

#!/bin/bash
DATE=$( date +%Y-%m-%d )
BACKUP_DIR='/root/backups'
MYSQL='/usr/bin/mysql'
MYSQLDUMP='/usr/bin/mysqldump'
MYSQL_USER=$( cat /etc/mysql/debian.cnf | grep '^user *=' -m 1 | awk {' print $3 '} )
MYSQL_PASS=$( cat /etc/mysql/debian.cnf | grep '^password *= ' -m 1 | awk {' print $3 '} )
DBS=$( ${MYSQL} --user=${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)" )
for DB in ${DBS}; do
  ${MYSQLDUMP} --single-transaction --user=${MYSQL_USER} -p${MYSQL_PASS} --databases "${DB}" > "/tmp/${DB}-${DATE}.sql"
  tar -cjvC /tmp -f "${BACKUP_DIR}/${DB}-${DATE}.tar.bz2" "${DB}-${DATE}.sql"
  rm "/tmp/${DB}-${DATE}.sql"
done