#!/bin/bash

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 -h\t\tthis menu"
	echo -e "\t--user username\tspecify mysql username to use\n\t\t\tusing this flag means the script will ask for a password during runtime, unless you supply..."
	echo -e "\t--password \"yourpassword\""
	echo -e "\t--host hostname\tspecify 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="localhost"
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

unset fraggedTables