Dump MySQL Tables into a File

This script dumps MySQL databases with a directory per database and a file per table.

backup-mysql

#!/bin/bash

# dump databases with a file per table

# this script is based on 
# https://stackoverflow.com/questions/3669121/dump-all-mysql-tables-into-separate-files-automagically

# check that the script is being run by root
if [[ "$(id -u)" != "0" ]] ; then
  echo "You must run $0 as root or via sudo" 
  exit 2
fi

# Check that /root/.my.cnf exists
test -f /root/.my.cnf || echo "This script needs a /root/.my.cnf" 
# /root/.my.cnf format:
# [client]
# user = root
# password = PASSWORD 
# host = localhost


# list of databases, omitting the system databases 
DATABASES=$(mysql -NBA -e 'show databases' | grep -v 'mysql' | grep -v 'information_schema' | grep -v 'performance_schema')
# backup base directory
BASE_DIR="/var/backups/mysql/databases"

# check $BASE_DIR exists
test -d ${BASE_DIR} || mkdir -p ${BASE_DIR} || exit 1

for d in ${DATABASES} ; do
  # make a directory for each database it  
  test -d ${BASE_DIR}/${d} || mkdir -p ${BASE_DIR}/${d} || exit 1
  echo "Backing up database ${d} to ${BASE_DIR}/${d}"
  # set a counter to zero
  tbl_count="0"
  # get a list of the database tables
  TABLES=$(mysql -NBA -D ${d} -e 'show tables')
  for t in ${TABLES}
    do
    echo "Dumping table: ${t}"
    mysqldump ${d} ${t} > ${BASE_DIR}/${d}/${t}.sql || exit 1
    (( tbl_count++ ))
  done
  echo "${tbl_count} tables dumped from database ${d} into ${BASE_DIR}/${d}"
done

echo "Databases backup successful"
exit 1