MySQL databases optimization

[Alec:D]

This article goes over mass optimization of MySQL databases and tables. This can be done in a per database basis or to all databases.

Optimize MySQL Databases in Plesk :

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


Done.... Browse your website and and EnjoY the Speed...

[LEWIS]

Hi,

A very informative information you have provided to all of us. But i have a question that this could be only done if a client has root access, will you please share for the person don't have a root access but need to optimization MySQL databases. In other words if i have a shared web hosting plan then how can i do the same to optimize MySQL database.

[Alec:D]

Yes, you can repair/optimize database from Cpanel as well as PHPMyadmin

just you do not optimize all database at once, it can be done individually

Cpanel >> Mysql databases >> under modify databases tag, you will see 2 buttons :

1) Check DB
2) Repair DB

By clicking on Repair DB, it will repair and optimize your database, it will repair all tables of databases,

If you want to repair and optimize any particular table, then you need to do it through PHPMyadmin :

Cpanel >> phpMyAdmin >>

log in to your phpMyAdmin and select the database whose tables you wish to optimize/repair.

Select the tables that need repair/optimize and pick the Optimize/Repair table action from the drop-down menu located below the tables' list.

If you tick on check all , action will be applied for all.

The tables will be repaired/Optimized and you will get a confirmation screen.

Done..

[Jeffrey]

@ AlecD :

Cool information, keep it up

Regards
Moderator

[himansu]

I use MySQL databases. Nice information. I like it