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.

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

Leave a Comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
*
*

16 + nine =