BiggiWiki

This is an old revision of the document!

---

I needed to move an SQL server. As always I started out doing a small run'n'delete-script, but I decided to go all in and make it a bit user friendly and all that. The script grabs current users and packs them in a query. Then it dumps all the db's with mysqldump and imports it on the other machine via. SSH. Run the script on the machine you want to move FROM. Make sure that sshpass, mysqldump and pigz/gzip is installed. (Pigz = multicore gzip)

If you use it and make any changes, please send them to me (Biggi@Biggi.dk). Unfortunately comments and users are disallowed due to those retarded spam-bots always trying to rake in cash on every website, and I'm not about to spend an hour cleaning up this wiki once a week.

movesql.sh

#!/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 - Flushing privileges at the end (to make sure new mysql permissions + passwords work)
# 	2013-12-17 - Ignoring 'debian-sys-maint'-user

# 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 " ----------------------------";