Mysql: optimize all tables in all databases

In order to optimize all databases & tables within your mysql instance, you can use the code below.

mysqlcheck --all-databases --optimize --skip-write-binlog

mysqlcheck --all-databases --optimize --skip-write-binlog

You can also use a more specific script, where only the tables are optimized which have 10MB free space and 10% free space.

MYSUPERUSER=My_SuperUser

MYPASSWORD=My_Password

mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -e "SHOW DATABASES" | while read database therest; do

mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -D$database -e "SHOW TABLE STATUS WHERE Data_free>10000000 AND Data_free/Data_length > 0.1" | while read tblname therest; do

mysqlcheck -u ${MYSUPERUSER} -p${MYPASSWORD} --optimize --skip-write-binlog $database $tblname

done

MYSUPERUSER=My_SuperUser MYPASSWORD=My_Password mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -e "SHOW DATABASES" | while read database therest; do mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -D$database -e "SHOW TABLE STATUS WHERE Data_free>10000000 AND Data_free/Data_length > 0.1" | while read tblname therest; do mysqlcheck -u ${MYSUPERUSER} -p${MYPASSWORD} --optimize --skip-write-binlog $database $tblname done done

MYSUPERUSER=My_SuperUser
MYPASSWORD=My_Password
mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -e "SHOW DATABASES" | while read database therest; do
        mysql -u ${MYSUPERUSER} -p${MYPASSWORD} -D$database -e "SHOW TABLE STATUS WHERE Data_free>10000000 AND Data_free/Data_length > 0.1" | while read tblname therest; do
                mysqlcheck -u ${MYSUPERUSER} -p${MYPASSWORD} --optimize --skip-write-binlog $database $tblname
        done
done