Changes between Initial Version and Version 1 of DumpMysqlTables

Timestamp:

Jun 11, 2015, 2:31:12 PM (3 years ago)

Author:

chris

Comment:

--

DumpMysqlTables

@@ +1 @@
+This script dumps MySQL databases with a directory per database and a file per table.
+
+{{{
+#!/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
+}}}