Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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 -s "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
- #
- # 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
- #
- # 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)
- #
- # Sync the slave with master binlog position 4
- echo "CHANGE MASTER TO master_log_file='${MasterLogFile}',master_log_pos=4;" | 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}
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement