Inno DB ibdata resizing to reclaim data

Inno DB ibdata resizing

# Reducing InnoDB ibdata file, converting from monolithic file to individual files per database. See:
# http://dba.stackexchange.com/questions/8982/what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql
# http://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
# http://vitobotta.com/smarter-faster-backups-restores-mysql-databases-with-mysqldump/#sthash.VCU3nJHa.SldueNKm.dpbs


# user, password, host, port opts stored in ~/.my.cnf

# dump databases to one file, streamed compression
/Applications/MAMP/Library/bin/mysqldump --all-databases --routines --opt --verbose | gzip > backup.sql.gz

# dump databases individually
for I in $(/Applications/MAMP/Library/bin/mysql -e 'show databases' -s --skip-column-names); do /Applications/MAMP/Library/bin/mysqldump --routines --opt --verbose $I | gzip > "$I.sql.gz"; done

# drop all databases but essential ones
/Applications/MAMP/Library/bin/mysql -e "show databases" | grep -v Database | grep -v mysql | grep -v performance_schema | grep -v information_schema | awk '{print "drop database " $1 ";"}' | /Applications/MAMP/Library/bin/mysql

(
    echo "SET AUTOCOMMIT=0;"
    echo "SET UNIQUE_CHECKS=0;"
    echo "SET FOREIGN_KEY_CHECKS=0;"
    gunzip < backup.sql.gz
    echo "SET FOREIGN_KEY_CHECKS=1;"
    echo "SET UNIQUE_CHECKS=1;"
    echo "SET AUTOCOMMIT=1;"
    echo "COMMIT;"
) | /Applications/MAMP/Library/bin/mysql