Optimize all your MySQL tables

Here is a small code snipped I’m using to optimize (or repair or defragment) mysql tables.

When for eg. mysqltuner shows something like “Total fragmented tables: 20” and you are awake at a very unpopular time (table optimization will lock your tables so it is possible your site is unreachable for a few moments) you can simply paste the following to your shell:

LOGIN="-u<user> -p<secret>"
mysql $LOGIN <<EOF
$(for db in `mysql $LOGIN -e 'show databases'|sed -e 's/\|//g'|egrep -v '^Database|information_schema|performance_schema'` ; do
echo "USE $db;" ; mysql $LOGIN -D$db -e 'show tables'|sed -e 's/\|//g'|egrep -v '^Tables_in_'|awk '{print "OPTIMIZE TABLE "$1";"}'
done)
EOF

You have to change your mysql login details or leave it empty if you are using the ~/.my.cnf config file.

Advertisements
This entry was posted in Database, Debian, Howto, Linux, MySQL, Shell, Snippet and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s