MySQL DB optimizing

["Jason Lewis" <jlewis () packetnexus com>]

While searching for ways to speed up my DB, I found the OPTIMIZE TABLES
command for mysql.  I wrote this script to optimize ALL db's and tables on
my server.  Your tables must be type MYISAM or the optimize won't work.

BE WARNED.... YOU USE THIS AT YOUR OWN RISK!!!!

That said, I haven't had any problems with it.  I run it frequently if I
have deleted a lot of data from the db.  I figured someone here would find
it handy.  It could easily be modifed to run from cron, but that might be
risky.

> From the MySQL docs.....

"In MyISAM tables, deleted records are maintained in a linked list and
subsequent INSERT operations reuse old record positions. To reclaim unused
space and reduce file sizes, use the OPTIMIZE TABLE statement or the
myisamchk utility to reorganise tables. "

Jason Lewis
http://www.packetnexus.com
It's not secure "Because they told me it was secure".
The people at the other end of the link know less
about security than you do. And that's scary.





#!/bin/sh
#  optall.sh
#  Account used must have access to all databases and tables.

clear
echo ""
echo "#########################################################"
echo ""
echo "This script will optimize ALL tables that are type MYISAM"
echo "                  Use at your own risk!"
echo ""
echo "#########################################################"
echo ""

sleep 5


userid=root
password=password

echo -n "Enter the db user [$userid]: "
read TMP
if [ $TMP ]; then
    userid=$TMP
fi

echo -n "Enter the db user password [$password]: "
read TMP1
if [ $TMP1 ]; then
        password=$TMP1
fi


getdb="/usr/local/bin/mysql -u${userid} -p${password}"

$getdb -e 'SHOW DATABASES' | sed 's/|//g' | grep -v 'Database' \
 | while read dbname; do
        echo "Optimizing db ${dbname}"
        gettables="/usr/local/bin/mysql -u${userid} -p${password} ${dbname}"
                $gettables -e 'SHOW TABLES' | sed 's/|//g' | grep -v
'Tables' \
                 | while read table; do
                        echo "Optimizing table ${table}"
                        echo "OPTIMIZE TABLE ${table};"|$gettables
                done

done


_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users