How to optimize all tables in all databases

Silent Ninja

Well-Known Member
Apr 18, 2006
196
0
166
Buenos Aires, Argentina
I've made up this code (based on another one from Son Nguyen) to optimize all databases on the server so they index what they can run faster and smoothly.

If you want to, it could be a good addon on the WHM / cPanel scripts :)
It's a bash script, but it can be easily rewrote in Perl or PHP if necesary

Code:
#!/bin/bash

# Este script optimiza todas las tablas de todas las bases de datos del servidor

for DBNAME in `ls -1A /var/lib/mysql/ | egrep -v "\.sock|\.user|\,/" | cut -d/ -f1`
do
	# Busca todas las tablas de la base de datos en esta iteración
	TABLENAMES=`mysql -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
	# Hace un loop para optimizar a cada una
	for TABLENAME in $TABLENAMES
	do
		mysql -D $DBNAME -e "REPAIR TABLE $TABLENAME;"
		mysql -D $DBNAME -e "OPTIMIZE TABLE $TABLENAME;"
	done
done
EDIT: Oops, comments are in spanish, hehe, forgot to translate them, they say...
# This script optimizes all tables in all databases from the server
# Get all tables from the dbase within the loop
# Starts a new loop to optimize each one
 
Last edited:

Keegan

Well-Known Member
Oct 22, 2001
97
1
318
cPanel Access Level
DataCenter Provider
Silent Ninja, you've been WACKED!

Its actually commendable that you'd post a helpful item, Susan just shortened it up a bit ()Thank you Susan!()
 

rachelm

Well-Known Member
Verifed Vendor
Jan 26, 2005
94
1
158
And I'm replacing the single line with a cpanel script that'll both optimize and repair all the db's via our cpanel addon :P Just enter your pass and click a button.
 

sebby

Well-Known Member
Jun 15, 2004
70
0
156
Canada
cPanel Access Level
Root Administrator
Hello everyone,

What is the recommended standard practice regarding server wide MySQL DB optimization? Is it recommended to do this on a regular basis as a preventive measure (and if so at what fequency? every week? every month?) or should one admin wait for performance related issues before running it?

Thanks for your insights.
 
Last edited: