Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/env bash
- # Name: MySQL_REBUILD_LIVE_SLAVE_from_MASTER
- # 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_REBUILD_LIVE_SLAVE_from_MASTER Master:Port Slave:Port {F|P}
- # eg. MySQL_REBUILD_LIVE_SLAVE_from_MASTER 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
- # Changes: 05 Dec 2008 First implementation of script
- # 10 Oct 2013 Added the File(F) transfer method, added --skip-lock-tables to the dump command, added the manual entry of root PW
- # Notes 1: In the case of using the Dump file method, the file will be transferred via rsync to the slave for importing.
- # Therefore the key of the user running the script must be already installed in slave before running the script
- #-----------------------------------------------------------------------------------
- # 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 --unprivileged --host-timeout 1600ms --max_rtt_timeout 1600ms -p $2 -P0 $1 2>/dev/null | grep -q "open" &>/dev/null); then
- hostOK=1
- fi
- };
- #
- usage () {
- echo "ERROR: Somethig is wrong with the given arguments"
- echo "Syntax: MySQL_REBUILD_LIVE_SLAVE_from_MASTER Master:Port Slave:port {F|P}"
- echo " eg. MySQL_REBUILD_LIVE_SLAVE_from_MASTER master1:3306 slave1:3306 P"
- exit 1
- }
- #
- # Check the command syntax
- if [ $# -ne 3 ]; then
- usage
- fi
- #
- # Get the mysql root password
- read -s -p "Please enter the MySQL root password: " rootpw
- #
- #- 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
- Mhost=$(echo $1 | cut -d: -f1)
- Mport=$(echo $1 | cut -d: -f2)
- #
- testhost $Mhost $Mport
- if [ $hostOK = "0" ]; then
- echo "ERROR: The master $Mhost:$Mport does not respond"
- exit 5
- fi
- #
- # Check the hosts connectivity of slave host
- #
- Shost=$(echo $2 | cut -d: -f1)
- Sport=$(echo $2 | cut -d: -f2)
- #
- testhost $Shost $Sport
- if [ $hostOK = "0" ]; then
- echo "ERROR: The slave $Shost:$Sport does not respond"
- exit 6
- fi
- #
- # Stop and reset the slave
- echo "STOP SLAVE; RESET SLAVE;" | mysql --host=$Shost --port=$Sport --user=root --password=$rootpw
- #
- databases=""
- for DB in $(echo "show databases;" | mysql --host=$Shost --port=$Sport --user=root --password=$rootpw | grep -v Database) ; do
- # Only delete/add the databases that are not in the Exceptions list
- if ! (echo $EXCEPTIONS | grep -q $DB); then
- # here I was deleting the databases one by one before recreating them in slave
- # I replaced this by the option --add-drop-database in mysqldump
- #echo "Deleting database $DB on Slave $Shost:$Sport"
- #echo "DROP DATABASE $DB;" | mysql -h $Shost --port=$Sport -u root --password=$rootpw
- # Corrected. Thanks to Dorian Kavaja
- if [ "$databases" == "" ]; then
- databases=$DB
- else
- databases="$databases $DB"
- fi
- fi
- done
- # The following lines is thanks to Dorian Kavaja
- #because sometimes the log file on the master was changed before the dump was finished
- echo "FLUSH LOGS; RESET MASTER;" | mysql -–host=$Mhost –-port=$Mport --user=root –password=$rootpw
- #
- # Now do the transfer
- # Added “–routines –triggers” to the mysqldump command, so triggers and functions were included in the dump.
- # Thanks to Dorian Kavaja
- case $method in
- P) # Transfer all databases from master to slave directly using a pipe(P)
- echo "Transfering the all databases from master $Mhost:$Mport into slave $Shost:$Sport directly"
- mysqldump --host=$Mhost --port=$Mport --user=root --password=$rootpw \
- --single-transaction --flush-logs --master-data=2 --skip-lock-tables \
- --add-drop-database --delete-master-logs --hex-blob –-routines –-triggers --databases $databases \
- | mysql --host=$Shost --port=$Sport --user=root --password=$rootpw
- ;;
- #
- F) # Transfer the databases using a dump file
- echo "Dumping the all databases from master $Mhost:$Mport into file $DUMPFILE"
- mysqldump --host=$Mhost --port=$Mport --user=root --password=$rootpw \
- --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 $Mhost to slave $Shost via compressed rsync"
- rsync -vz $DUMPFILE $Shost:$DMPFILE
- echo "Importing the dump file ($DUMPFILE) into slave MySQL server $Shost"
- ssh $Shost "mysql --host=$Shost --port=$Sport --user=root --password=$rootpw < $DUMPFILE"
- ;;
- #
- *) usage ;;
- esac
- #
- # Find out the master binlog file name
- masterlogfile=$(echo "SHOW MASTER STATUS\G;" | mysql --host=$Mhost --port=$Mport --user=root --password=$rootpw | 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 --host=$Shost --port=$Sport --user=root --password=$rootpw
- #
- # Start the replication on slave
- echo "START SLAVE;" | mysql --host=$Shost --port=$Sport --user=root --password=$rootpw
- sleep 3
- #
- # Show slave status to see if all is in sync
- echo "SHOW SLAVE STATUS \G;" | mysql --host=$Shost --port=$Sport --user=root --password=$rootpw
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement