Script para optimizar tablas de MySQL fragmentadas

VERSION=»0.7.2″
log=»$PWD/mysql_error_log.txt»

echo «MySQL fragmentation finder (and fixer) v$VERSION, written by Phil Dufault ( http://www.dufault.info/ )»

showHelp() {
echo -e «tThis script only repairs MyISAM and InnoDB tables»
echo -e «t–help or -httthis menu»
echo -e «t–user usernametspecify mysql username to usentttusing this flag means the script will ask for a password during runtime, unless you supply…»
echo -e «t–password «yourpassword»»
echo -e «t–host hostnametspecify mysql hostname to use, be it local (default) or remote»
}

#s parse arguments
while [[ $1 == -* ]]; do
case «$1» in
–help|-h) showHelp; exit 0;;
–user) mysqlUser=»$2″; shift 2;;
–password) mysqlPass=»$2″; shift 2;;
–host) mysqlHost=»$2″; shift 2;;
–) shift; break;;
esac
done

# prevent overwriting the commandline args with the ones in .my.cnf, and check that .my.cnf exists
if [[ ! $mysqlUser && -f «$HOME/.my.cnf» ]]; then
if grep «user=» «$HOME/.my.cnf» >/dev/null 2>&1; then
if grep «pass=» «$HOME/.my.cnf» >/dev/null 2>&1; then
mysqlUser=$(grep user= < «$HOME/.my.cnf» | awk -F» ‘{print $2}’);
mysqlPass=$(grep pass= < «$HOME/.my.cnf» | awk -F» ‘{print $2}’);
if grep «host=» «$HOME/.my.cnf» >/dev/null 2>&1; then
mysqlHost=$(grep host= < «$HOME/.my.cnf» | awk -F» ‘{print $2}’);
fi
else
echo «Found no pass line in your .my.cnf,, fix this or specify with –password»
fi
else
echo «Found no user line in your .my.cnf, fix this or specify with –user»
exit 1;
fi
fi

# set localhost if no host is set anywhere else
if [[ ! $mysqlHost ]]; then
mysqlHost=»127.0.0.1″
fi

# error out
if [[ ! $mysqlUser ]]; then
echo «Authentication information not found as arguments, nor in $HOME/.my.cnf»
echo
showHelp
exit 1
fi

if [[ ! $mysqlPass ]]; then
echo -n «Enter your MySQL password: «
read -s mysqlPass
fi

# Test connecting to the database:
mysql -u»$mysqlUser» -p»$mysqlPass» -h»$mysqlHost» –skip-column-names –batch -e «show status» >/dev/null 2>&1
if [[ $? -gt 0 ]]; then
echo «An error occured, check $log for more information.»;
exit 1;
fi

# Retrieve the listing of databases:
databases=( $(mysql -u»$mysqlUser» -p»$mysqlPass» -h»$mysqlHost» –skip-column-names –batch -e «show databases;» 2>»$log») );
if [[ $? -gt 0 ]]; then
echo «An error occured, check $log for more information.»
exit 1;
fi

echo -e «Found ${#databases[@]} databases»;
for i in ${databases[@]}; do
# get a list of all of the tables, grep for MyISAM or InnoDB, and then sort out the fragmented tables with awk
fragmented=( $(mysql -u»$mysqlUser» -p»$mysqlPass» -h»$mysqlHost» –skip-column-names –batch -e «SHOW TABLE STATUS FROM $i;» 2>»$log» | awk ‘{print $1,$2,$10}’ | egrep «MyISAM|InnoDB» | awk ‘$3 > 0’ | awk ‘{print $1}’) );
if [[ $? -gt 0 ]]; then
echo «An error occured, check $log for more information.»
exit 1;
fi
tput sc
echo -n «Checking $i … «;
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 1 ]]; then
echo «found ${#fragmented[@]} fragmented tables.»
else
echo «found ${#fragmented[@]} fragmented table.»
fi
fi
for table in ${fragmented[@]}; do
let fraggedTables=$fraggedTables+1;
echo -ne «tOptimizing $table … «;
mysql -u»$mysqlUser» -p»$mysqlPass» -h»$mysqlHost» -D «$i» –skip-column-names –batch -e «optimize table $table» 2>»$log» >/dev/null
if [[ $? -gt 0 ]]; then
echo «An error occured, check $log for more information.»
exit 1;
fi
echo done
done
else
tput rc
tput el
fi
unset fragmented
done

# footer message
if [[ ! $fraggedTables -gt 0 ]]; then
echo «No tables were fragmented, so no optimizing was done.»;
else
if [[ $fraggedTables -gt 1 ]]; then
echo «$fraggedTables tables were fragmented, and were optimized.»;
else
echo «$fraggedTables table was fragmented, and was optimized.»;
fi
fi

if [[ ! -s $log ]]; then
rm -f «$log»
fi