db_dump_and_restore.sh

#!/bin/bash

user="root"
pass="12345678"
excludeList="mysql|performance_schema|information_schema" #dont remove these databases
tmpDir="/tmp"


# PART1
# Uncomment first block to get your database dumps into tmpDir

# databases=`mysql -u ${user} -p${pass} -e 'show databases\G' | grep -i database | egrep -v "${excludeList}" | sed -e 's/^Database: '//`
# 
# for i in $databases
# do
#     echo "Creating backup files for $i"
#     mysqldump -u ${user} -p${pass} $i > ${tmpDir}/backup_$i.sql
#     echo "Dropping database: $i"
#     mysql -u ${user} -p${pass} -e "drop database \`$i\`"
# done
# 
# echo "Now Stop MySql (make sure process terminated) delete ibdata1 and ib_log files and then restart mysql"


# PART2
# After removing ibdata1 file comment block on PART1 and uncomment these to restore your database 

# for i in `ls ${tmpDir}/*.sql`
# do
#     dbname=`basename $i | sed -e 's/backup_//' | sed -e 's/\.sql//'`
#     echo "Importing data to $dbname"
#     mysql -u ${user} -p${pass} -e "create database \`$dbname\`"
#     mysql -u ${user} -p${pass} $dbname < $i
# done
#
# echo "Import finished, you can remove your backup files under ${tmpDir}"