#!/bin/bash
# Name:		mysql-slave-resync
# Purpose:	Rebuilds live all Databases of a replication SLAVE based on a good running MASTER
#		This rebuilt action is done without stopping the MASTER MySQL server or even locking the tables
#		or the destination server (SLAVE)
# Syntax:	mysql-slave-resync Master:Port Slave:Port {F|P}
#		eg. mysql-slave-resync db1:3306 www1:3306 P
#               {F|P} - Dump method     F) Uses a full temporary dump file method     P) Uses the pipe method
#               Note: In some situation where the databases are very big (GB) the Pipe (P) method might not work well
#                     In these case it is recommended to use the File(F) method
# Assumption:
#   * Installed: nmap rsync ssh
#   * Both MySQL servers share the same user and password combination
#   * SSH access for the same username on slave host in case of rsync synchronization
#-----------------------------------------------------------------------------------

# Some constants:
DUMPFILE="/tmp/mydump.sql"

# Resync the databases except the following Databases
EXCEPTIONS="information_schema mysql test"

#
# Functions
# Syntax: testhost addr port. Returns: hostOK=1
testHost () {
    hostOK=0
    if (nmap -n --host_timeout 1600 --max_rtt_timeout 1600 -p $2 -P0 $1 2>/dev/null | grep -q "open" &>/dev/null); then
	hostOK=1
    fi
};

#
usage () {
    echo "ERROR: Something is wrong with the given arguments"
    echo "Syntax: mysql-slave-resync Master:Port Slave:port {F|P}"
    echo "    eg. mysql-slave-resync master1:3306 slave1:3306 P"
    exit 1
}

#
# Check the command syntax
if [ $# -ne 3 ]; then
    usage
fi


read -p "Please enter MySQL username: " user
#
# Get the mysql password
read -s -p "Please enter the MySQL ${user} password: " userPasswd

#
#- Check the hosts info validity
if ! (echo $1 | grep ':'); then
    echo "ERROR: The 2nd parameter(master) must be the combination 'host:port'"
    exit 3
fi

#
if ! (echo $2 | grep ':'); then
    echo "ERROR: The third parameter must be the combination 'host:port'"
    exit 4
fi

#
method=$3

#
# Check the hosts connectivity of master host
MasterHost=$(echo $1 | cut -d: -f1)
MasterPort=$(echo $1 | cut -d: -f2)

#
testhost ${MasterHost} ${MasterPort}
if [ ${hostOK} = "0" ]; then
    echo "ERROR: The master $MasterHost:$MasterPort does not respond"
    exit 5
fi

#
# Check the hosts connectivity of slave host
#
SlaveHost=$(echo $2 | cut -d: -f1)
SlavePort=$(echo $2 | cut -d: -f2)

#
testhost ${SlaveHost} ${SlavePort}
if [ ${hostOK} = "0" ]; then
    echo "ERROR: The slave $SlaveHost:$SlavePort does not respond"
    exit 6
fi

#
# Stop and reset the slave
echo "STOP SLAVE; RESET SLAVE;" | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd}

#
databases=""
for DB in $(echo "show databases;" | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd} | grep -v Database) ; do
    # Only delete/add the databases that are not in the Exceptions list
    if ! (echo ${EXCEPTIONS} | grep -q ${DB}); then
        if [ "$databases" == "" ]; then
            databases=${DB}
        else
            databases=${databases}' '${DB}
        fi
    fi
done

echo "FLUSH LOGS;" | mysql --host=${MasterHost} --port=${MasterPort} -u ${user} --password=${userPasswd}
# Not safe and will possibly break other replications
#echo “RESET MASTER;” | mysql –host=$MasterHost –port=$MasterPort -u $user –password=$userPasswd

#
# Find out the master binlog file name
MasterLogFile=$(echo "SHOW MASTER STATUS\G;" | mysql --host=${MasterHost} --port=${MasterPort} -u ${user} --password=${userPasswd} | grep "File:" | cut -d: -f2 | cut -d" " -f2)
MasterLogPosition=$(echo "SHOW MASTER STATUS\G;" | mysql --host=${MasterHost} --port=${MasterPort} -u ${user} --password=${userPasswd} | grep "Position:" | cut -d: -f2 | cut -d" " -f2)

#
# Now do the transfer
case ${method} in
    P)	# Transfer all databases from master to slave directly using a pipe(P)
	echo "Transferring the all databases from master ${MasterHost}:${MasterPort} into slave ${SlaveHost}:${SlavePort} directly"
	mysqldump -h ${MasterHost} --port=${MasterPort} -u ${user} --password=${userPasswd} \
        --single-transaction --flush-logs --master-data=2 --skip-lock-tables \
        --add-drop-database --delete-master-logs --hex-blob --routines --triggers --databases ${databases} \
            | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd}
    ;;
#
    F)  # Transfer the databases using a dump file
	echo "Dumping the all databases from master ${MasterHost}:${MasterPort} via dumpfile ${DUMPFILE}"
	mysqldump -h ${MasterHost} --port=${MasterPort} -u ${user} --password=${userPasswd} \
        --single-transaction --flush-logs --master-data=2 --skip-lock-tables \
        --add-drop-database --delete-master-logs --hex-blob --routines --triggers --databases ${databases} > ${DUMPFILE}
#
	echo "Transferring the dump file ${DUMPFILE} from Master ${MasterHost} to slave ${SlaveHost} via compressed rsync"
	rsync -vz ${DUMPFILE} ${SlaveHost}:${DUMPFILE}
	echo "Importing the dump file (${DUMPFILE}) into slave MySQL server ${SlaveHost}"
	ssh ${SlaveHost} "mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd} < ${DUMPFILE}"
    ;;
#
    *) usage ;;
esac

#
# Sync the slave with master binlog position 4
echo "CHANGE MASTER TO master_log_file='${MasterLogFile}',master_log_pos=${MasterLogPosition};" | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd}

#
# Start the replication on slave
echo "START SLAVE;" | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd}
sleep 3

#
# Show slave status to see if all is in sync
echo "SHOW SLAVE STATUS \G;" | mysql -h ${SlaveHost} --port=${SlavePort} -u ${user} --password=${userPasswd}