Hi Rory,

For those of us that are not so well versed on Linux, could you perhaps post an example how you call your script, with the appropriate parameters. This makes it easier for us Windows users (me) dabbling in Linux to actually get your script working.

The one sentence I do not understand is “I added the database to the mysql command where appropriate to reduce the number of commands run once the mysql client was started as an optimization”. An example would probably help resolve my questions.

Regards

Rudolf

Von: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Im Auftrag von Rory
Gesendet: Freitag, 26. August 2011 16:34
An: User questions and discussions about OTRS.
Betreff: Re: [otrs] Otrs with lighttpd + fastcgi

That's a great little optimisation script.
I hope you don't mind that I made some changes to it.
I've added code to ask the user for the DB username and password rather than include them in the command line. I don't want my passwords to be readable in plain text just by looking at the shell history. Also the password is not echoed to the terminal in this version. If either username or password is blank the script exits.
I added the database to the mysql command where appropriate to reduce the number of commands run once the mysql client was started as an optimization. This probably makes no difference.
I also added in a success or failed keyword depending on whether or not the term "ERROR" is returned from the OPTIMIZE TABLE mysql command which then displays the Error returned.


#############################

#!/bin/bash

echo "Enter DB User: "
read user
echo "Enter DB Password: "
read -s pass

if [ -z "${user}" ];
then
        echo "Username is blank. Exiting script"
        exit
elif [ -z "${pass}" ];
then
        echo "Password is blank. Exiting script"
        exit
fi

for db in $(echo "SHOW DATABASES;" | mysql -u$user --password=$pass | grep -v -e "Database" -e "information_schema")
do
        echo "Switching to database $db"
        TABLES=$(echo "SHOW TABLES;" | mysql -D$db -u$user --password=$pass |  grep -v Tables_in_)

        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                result=$(echo "OPTIMIZE TABLE $table" | mysql -D$db -u$user --password=$pass 2>&1)
                if [[ $result == *ERROR* ]]
                then
                        echo "FAILED"
                        echo ".... $result"
                else
                        echo "Success"
                fi
        done
done

#############################

Rory