Dump MySQL Tables into a File

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



# dump databases with a file per table

# this script is based on 

# 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

# 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

# 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
  # get a list of the database tables
  TABLES=$(mysql -NBA -D ${d} -e 'show tables')
  for t in ${TABLES}
    echo "Dumping table: ${t}"
    mysqldump ${d} ${t} > ${BASE_DIR}/${d}/${t}.sql || exit 1
    (( tbl_count++ ))
  echo "${tbl_count} tables dumped from database ${d} into ${BASE_DIR}/${d}"

echo "Databases backup successful"
exit 1
Last modified 3 years ago Last modified on Jul 24, 2015, 12:19:19 PM