Skip to content

Instantly share code, notes, and snippets.

@bonyiii
Created July 4, 2012 07:25
Show Gist options
  • Save bonyiii/3045895 to your computer and use it in GitHub Desktop.
Save bonyiii/3045895 to your computer and use it in GitHub Desktop.
mysql fregmentation remover
#http://blog.softlayer.com/2011/mysql-slow-check-for-fragmentation/
#!/bin/bash
MYSQL_LOGIN='-u root --password=password'
for db in $(echo "SHOW DATABASES;" | mysql | grep -v -e "Database" -e "information_schema" -e "mysql" -e "performance_schema" -e "^test")
#for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN | grep -v Tables_in_)
echo "Switching to database $db"
for table in $TABLES
do
echo -n " * Optimizing table $table ... "
echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null
echo "done."
done
done
#######
# This is independent from the scrip above it is only reminder how to set mysql use per table namespace
# Moving mysql tables from global namespace to per table space
#http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
-- Move table from system tablespace to its own tablespace.
SET GLOBAL innodb_file_per_table=1;
# And run the script above
-- Move table from its own tablespace to system tablespace.
SET GLOBAL innodb_file_per_table=0;
# And run the script above
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment