Snort mailing list archives
MySQL DB optimizing
From: "Jason Lewis" <jlewis () packetnexus com>
Date: Sun, 25 Nov 2001 09:16:29 -0500
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
Current thread:
- MySQL DB optimizing Jason Lewis (Nov 25)