Optimize all mysql tables by shell


Time it’s up and you have to maintenance database and optimize all tables to get better performance.
For that reason you can use following script as well as OPTIMIZE TABLE command.

Optimize table

OPTIMIZE TABLE <TABLE NAME>

Usage of simple mysqlcheck command is also possibe

mysqlcheck -uroot -p --auto-repair --optimize --all-databases

Optimize all tables at once by shell script

#!/bin/bash
echo "Optimize all tables from MySql DB";
echo "Enter DBA credentails";
echo -n "User:";
read USER
echo -n "Password:";
read -s PASSWORD

echo "select concat(concat(table_schema,'.'),table_name) from INFORMATION_SCHEMA.TABLES;"|mysql -s -f --user=$USER --password=$PASSWORD|grep -v -e '^#'|grep -v -e '^information_schema\.'|while read t
do
echo $t
echo "Optimize table $t;"|mysql -s -f --user=$USER --password=$PASSWORD
done

Nice version with text dialog and progress bar

Screen of process with nice dialog version

Screen of process with nice dialog version

#!/bin/bash
echo "Optimize all tables from MySql DB";
echo "Enter DBA credentails";
echo -n "User:";
read USER
echo -n "Password:";
read -s PASSWORD
tmp=".tmp"`date +%s`;

echo "select concat(concat(table_schema,'.'),table_name) from INFORMATION_SCHEMA.TABLES;"|mysql -s -f --user=$USER --password=$PASSWORD|grep -v -e '^#'|grep -v -e '^information_schema\.' > "$tmp"
wait
tc=`wc -l "$tmp"|cut -d" " -f1`;
n1=`expr $tc / 100`;
n=0;
cat "$tmp"|while read t
do
echo "Optimize table $t;"|mysql -s -f --user=$USER --password=$PASSWORD 2>&1 > /dev/null
n=`expr $n + 1`;
p=`expr $n / $n1`;
echo $p
done|dialog --guage "Optimizing all tables" 7 60 100

Do not forget, set proper rights to enable execution of script.

chmod 755 optimizealltables.sh
./optimizealltables.sh

Now you should just wait when process is done. Finnally you should use cron for schedule job daily, for that you should set password and user name manualy. What means, replace lines read USER and read PASSWORD with USER=”root” and PASSWORD=”” or use another DBA credentails.

Cron configuration

Make link to script file with following command like.

ln -s ~/optimizealltables.sh /etc/cron.daily/optimizealltables.sh
Příspěvek byl publikován v rubrice Practics in Bash. Můžete si uložit jeho odkaz mezi své oblíbené záložky.