1. Open an SSH connection to the server and log in as the root user.
2. Once logged in as the root user, create a file name optimize.sh with the following data:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/sh
DBNAME=$2
if [ -e /etc/psa/.psa.shadow ]; then
PLESKJAWNS=”-uadmin -p`cat /etc/psa/.psa.shadow`”
else
PLESKJAWNS=””
fi
printUsage() {
echo “Usage: $0″
echo ” –optimize <dbname>”
echo ” –optimizeall”
echo ” –repair <dbname>”
echo ” –repairall”
return
}
doAllTables() {
# get the table names
TABLENAMES=`mysql $PLESKJAWNS -D $DBNAME -e “SHOW TABLES\G;”|grep ‘Tables_in_’|sed -n ‘s/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p’`
# loop through the tables and optimize them
for TABLENAME in $TABLENAMES
do
mysql $PLESKJAWNS -D $DBNAME -e “$DBCMD TABLE $TABLENAME;”
done
}
doAllDatabases() {
# get the database names
DATABASES=`mysql $PLESKJAWNS -e “Show Databases” | grep -v + | grep -v psa`
for DATABASE in $DATABASES
do
# get the table names
TABLENAMES=`mysql $PLESKJAWNS -D $DATABASE -e “SHOW TABLES\G;”|grep ‘Tables_in_’|sed -n ‘s/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p’`
# loop through the tables and optimize them
for TABLENAME in $TABLENAMES
do
mysql $PLESKJAWNS -D $DATABASE -e “$DBCMD TABLE $TABLENAME;”
done
done
}
if [ $# -eq 0 ] ; then
printUsage
exit 1
fi
case $1 in
–optimize) DBCMD=OPTIMIZE; doAllTables;;
–optimizeall) DBCMD=OPTIMIZE; doAllDatabases;;
–repair) DBCMD=REPAIR; doAllTables;;
–repairall) DBCMD=REPAIR; doAllDatabases;;
–help) printUsage; exit 1;;
*) printUsage; exit 1;;
esac
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Update the permissions on the file using the following command:
[root@server~]# chmod +x optimize.sh
To repair and optimize a single database, run the following commands:
[root@server~]# ./optimize.sh –repair DBNAME
[root@server~]# ./optimize.sh –optimize DBNAME
To repair and optimize a all databases, run the following commands:
[root@server~]# ./optimize.sh –repairall
[root@server~]# ./optimize.sh –optimizeall
linuxspy 