Mysql: optimize all tables in all databases

Bschelst/ July 14, 2019/ Linux/ 0 comments

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

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.

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
Share this Post

Leave a Comment

Your email address will not be published. Required fields are marked *