#!/bin/bash
# TODO:
# 	Write warning: sshpass 1.04 does not work. 1.05 required :/
# 	Add option to use another port than 3306 and 22
# DONE:
# 	2013-12-17 - Ignoring 'debian-sys-maint'-user when dumping privileges
# 	2013-12-17 - Flushing privileges at the end (to make sure new mysql permissions + passwords work)

# Verify sshpass + mysqldump + pigz
if [ "`whereis -b sshpass | awk {'print $2'}`" == "" ]; then
	echo "sshpass not found. Please install it - Install mysqldump while you're at it (mysqldump is needed on target too, sshpass is not).";
	exit
fi
if [ "`whereis -b mysqldump | awk {'print $2'}`" == "" ]; then
	echo "mysqldump not found. Please install it. (Needs to be available on target too)";
	exit
fi

# Get details
echo "Let's move that MySQL! First a couple of questions:";

# gzip or not?
echo "	Would you like to gzip the sql dump? Good for slow connections, bad for slow cpu/lack of cpu cores"
read gzipornot
if [ "$gzipornot" == "yes" ]; then
	gzipmethod="pigz"
	if [ "`whereis -b pigz | awk {'print $2'}`" == "" ]; then
		echo "pigz not found. Would you like to use gzip instead?"
		echo "pigz is basically gzip using multiple cores, so it'll be faster than regular gzip on multicore machines.";
		echo "Write \"no\" to exit and install pigz, or \"yes\" to continue with gzip";
		read pigzornot
		if [ "$pigzornot" == "yes" ]; then
			gzipmethod="gzip"
			if [ ! -f `whereis -b gzip | awk {'print $2'}` ]; then
				echo "gzip not found. You need gzip or pigz."
				exit
			fi
		else
			echo "Exiting. Please install pigz or gzip."
			exit
		fi
		echo "Moving on with $gzipmethod support in 3 seconds"
		sleep 3
	fi
else
	echo "Moving on without gzip in 3 seconds"
	sleep 3
fi

echo "	Would you like to sync the root mysql password too?";
read syncrootpwd
syncrootpwd=`echo $syncrootpwd | awk '{print tolower($0)}'`
if [ "$syncrootpwd" != "yes" ] && [ "$syncrootpwd" != "no" ]; then
	echo "yes or no please."
	exit
fi

echo "	Enter the local mysql root password (chars will not be shown in the terminal)"
read -s sqlrootpwd

echo "	Enter the remote mysql root password (chars will not be shown in the terminal)"
read -s dstsqlrootpwd

echo "	Enter the destination ssh username"
read dstuser

echo "	Enter the destination ssh password (chars will not be shown in the terminal)"
read -s dstpass

echo "	Enter the destination IP"
read dstip

echo -n "* Verifying local mysql connection: "
SQLCHECK=`mysql -N -s -u root --password="$sqlrootpwd" -e"SELECT 1;" 2>&1`
if [ "$SQLCHECK" != "1" ]; then
	sleep 0.5
	echo "FAILED"
	sleep 1
	echo " ----------------------------";
	echo "$SQLCHECK";
	sleep 3
	exit
else
	echo "OK"
fi

echo -n "* Verifying remote ssh access: "
SSHCHECK=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip echo WORKING 2>&1`
if [ "$SSHCHECK" != "WORKING" ]; then
	sleep 0.5
	echo "FAILED"
	sleep 1
	echo " ----------------------------";
	echo $SSHCHECK
	sleep 3
	exit
else
	echo "OK"
fi

echo -n "* Verifying remote mysql access: "
REMOTESQLCHECK=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip "mysql -N -s -u root --password=\"$dstsqlrootpwd\" -e\"SELECT 1;\"" 2>&1`
if [ "$REMOTESQLCHECK" != "1" ]; then
	sleep 0.5
	echo "FAILED"
	sleep 1
	echo " ----------------------------";
	echo $REMOTESQLCHECK
	sleep 3
	exit
else
	echo "OK"
fi

if [ "$gzipmethod" != "" ]; then
	echo -n "* Using $gzipmethod - Verifying remote executables: "
	GZIPCHECK=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip "whereis $gzipmethod | awk {'print $2'}"`
	if [ "$GZIPCHECK" == "" ]; then
		sleep 0.5
		echo "FAILED"
		sleep 1
		echo " ----------------------------";
		echo "$gzipmethod not found on remote server. Please install it."
		sleep 3
		exit
	else
		echo "OK"
	fi
fi

echo "* Fetching local user data"
sleep 0.5
# Change the IFS from space to newline temporarily
IFS=$'\n';
# Initialize array for usage in the loop below
USERDATA=()

# Grab user data
for x in `mysql -u root --password=$sqlrootpwd -B -N -e"SELECT user, host FROM user WHERE user != 'debian-sys-maint'" mysql`; do
	UNAME=`echo $x | awk {'print $1'}`;
	UHOST=`echo $x | awk {'print $2'}`;

	if [ "$syncrootpwd" == "no" ] && [ "$UNAME" == "root" ]; then
		continue;
	elif [ "$UNAME" == "" ] || [ "$UHOST" == "" ]; then
		echo " NOTICE: Skipped line. Missing data. UNAME: $UNAME - UHOST: $UHOST"
		continue;
	else
		USERDATA+=(`mysql -u root --password=$sqlrootpwd -B -N -e"SHOW GRANTS FOR '$UNAME'@'$UHOST'"`);
	fi
done

# Build array for quick transfer
ALLPRIVILEGES=""
for i in "${USERDATA[@]}"; do
	ALLPRIVILEGES="$ALLPRIVILEGES $i;"
done

# Add 'flush privileges'
ALLPRIVILEGES="$ALLPRIVILEGES FLUSH PRIVILEGES;"

# Revert IFS setting
unset IFS;

echo "* Grabbing SQL data";
sleep 1
FEND=""
if [ "$gzipornot" == "yes" ]; then
	mysqldump -u root -p$sqlrootpwd --all-databases -v | $gzipmethod -c > ~/sqlmigratedump.sql.gz
	FEND=".gz"
else
	mysqldump -u root -p$sqlrootpwd --all-databases -v > ~/sqlmigratedump.sql
fi
sleep 1

echo "* Transferring SQL dump";
sshpass -p "$dstpass" scp ~/sqlmigratedump.sql$FEND $dstuser@$dstip:/home/$dstuser/sqlmigratedump.sql$FEND

if [ "$gzipornot" == "yes" ]; then
	echo "* Decompressing SQL dump"
	sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip $gzipmethod -d /home/$dstuser/sqlmigratedump.sql$FEND
fi

echo "* Importing sql dump on remote system";
IMPORTDUMP=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip "mysql -N -s -u root --password=\"$dstsqlrootpwd\" < /home/$dstuser/sqlmigratedump.sql " 2>&1`

echo "* Importing sql user data on remote system";
IMPORTPRIVILEGES=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip "mysql -N -s -u root --password=\"$dstsqlrootpwd\" -e\"$ALLPRIVILEGES\" " 2>&1`

echo "* Flushing privileges on remote system";
FLUSHPRIVILEGES=`sshpass -p "$dstpass" ssh -o StrictHostKeyChecking=no $dstuser@$dstip "mysql -N -s -u root --password=\"$dstsqlrootpwd\" -e\"FLUSH PRIVILEGES;\" " 2>&1`

echo " ----------------------------";
echo " Import done, please verify data."
echo " ----------------------------";