Forked from sgtsquiggs/MySQL_REBUILD_LIVE_SLAVE_from_MASTER.sh
Last active
November 9, 2018 11:07
-
-
Save joshuasign/97b8f515ef28fdabf8fd79643509104c to your computer and use it in GitHub Desktop.
MySQL_REBUILD_LIVE_SLAVE_from_MASTER
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| # Name: MySQL_HOTREBUILD_SLAVE_from_MASTER.sh by Joshua_sign | |
| # original version was MySQL_REBUILD_LIVE_SLAVE_from_MASTER by Matthew Crenshaw | |
| # | |
| # Purpose: Rebuilds live for one or all Databases of a SLAVE replication server based on a good running MASTER server | |
| # This rebuilt action is done without stopping the MASTER MySQL server BUT by locking the MASTER server tables | |
| # during the backup of master databases with the F method (minimal duration possible) | |
| # or during the whole transfert with the P method. | |
| # Note that a main difference with the original script of Matthew Crenshaw, this version is used for a symetric MASTER/SLAVE. | |
| # In other words, the SLAVE can became a MASTER if the MASTER go down, and MASTER can became SLAVE if needed. | |
| # So we set MASTER/SLAVE informations in both servers. | |
| # | |
| # Syntax: MySQL_HOTREBUILD_SLAVE_from_MASTER.sh Master:Port Slave:Port {F|P} [this_dbname_only] | |
| # eg. MySQL_HOTREBUILD_SLAVE_from_MASTER.sh db1:3306 www1:3306 P myDatabase | |
| # {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 | |
| # 05 Jan 2017 Modifications about the main process : this rebuild cannot be made without locking tables on Master server during the backup | |
| # removes --routines and --triggers because my mysql does not support them | |
| # change the DUMPFILE emplacement for security reasons (because only root should use this script) | |
| # replace nmap in testhost function by a mysql test because nmap is not needed on Mysql Servers (my server cant get nmap) | |
| # remove usage of ssh and rsync | |
| # rebuild only one database is now possible with the 4th parameter (optional) | |
| # 03 Mar 2017 add default values for replication user and password, master / slave servers ip:ports, mode, database name | |
| # add description for error 1201 | |
| # 09 Oct 2018 remove --lock-all-tables because MariaDB catch an error : "mysqldump: You can't use --single-transaction and --lock-all-tables at the same time." | |
| # | |
| # Notes | |
| # 1: Joshua_sign : P method has been successfully tested on 1Gb lan with 2 databases sized at 7GB each (more than 350 tables each in InnoDB) | |
| # But, if you need the faster method, use F because the master server is only freezed during export to dumpfile | |
| # With F method we must freeze master during the whole transfert time. | |
| # | |
| # Todo: Change params setting because default params are avaiable and multiple databases can be sets | |
| # Detect the "ERROR 1201" automatically, and/or add ssh access to delete them by this script | |
| # | |
| # Known Errors: | |
| # "ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log" | |
| # I got this error sometimes on 64Bits server with debian 6.0.5 and mysql 5.1.63-0+squeeze1-log | |
| # This error may happens on starting slave when the RESET SLAVE command didn't delete files into /var/lib/mysql/mysqld-relay-bin.* | |
| # 'reset slave' should normaly cleanup directory /var/lib/mysql/, but sometimes it does not | |
| # So to fix this error just perform a STOP SLAVE; and RESET SLAVE; on the server (master or SLAVE, deppending where the error occurs) | |
| # You will see if files like /var/lib/mysql/mysqld-relay-bin.* are deleted automatically. | |
| # If they are not, you can do it with rm -f /var/lib/mysql/mysqld-relay-bin.* | |
| # Then you can try to START SLAVE; or launch this script again, and everythings should be ok. | |
| # | |
| #----------------------------------------------------------------------------------- | |
| # | |
| # Standard output exemple : | |
| # MYBDD1: 7GB database, more than 400 tables | |
| # MYBDD2: empty db just for test. | |
| # | |
| # root@myserver:~ $ ./MySQL_HOTREBUILD_SLAVE_from_MASTER.sh 192.168.2.4:3306 192.168.2.5:3309 F | |
| # Please enter the MySQL root password: | |
| # Please enter the MySQL replication user name: | |
| # Please enter the MySQL replication user password: | |
| # [09/01/2017-11:05:35] Ready to work! (starting sync in 10 seconds, press CTRL+C to abord) | |
| # [09/01/2017-11:05:45] Lock all tables on Master.... | |
| # [09/01/2017-11:05:50] Master is now freezed | |
| # [09/01/2017-11:05:50] Flush and reset slave... | |
| # [09/01/2017-11:05:50] Flush and reset master... | |
| # [09/01/2017-11:05:50] Dumping from master 192.168.2.4:3306 into file /root/mydump.sql | |
| # [09/01/2017-11:05:50] Databases : MYBDD1 MYBDD2 | |
| # [09/01/2017-11:07:52] UNLOCK all tables on Master.... | |
| # [09/01/2017-11:07:52] Killing SLEEP on pid 19017... | |
| # [09/01/2017-11:07:52] Master is now operational | |
| # [09/01/2017-11:07:52] Update master information on Slave... | |
| # [09/01/2017-11:07:52] Import dumpfile /root/mydump.sql into slave 192.168.2.5:3309 ... (this may take a while) | |
| # [09/01/2017-11:19:34] Update master information on Master... | |
| # [09/01/2017-11:19:34] Starting slave replication on Slave 192.168.2.5.... | |
| # *************************** 1. row *************************** | |
| # Slave_IO_State: Waiting for master to send event | |
| # Master_Host: 192.168.2.4 | |
| # Master_User: replication | |
| # Master_Port: 3306 | |
| # Connect_Retry: 60 | |
| # Master_Log_File: mysql-bin.000002 | |
| # Read_Master_Log_Pos: 300828 | |
| # Relay_Log_File: mysqld-relay-bin.000002 | |
| # Relay_Log_Pos: 300973 | |
| # Relay_Master_Log_File: mysql-bin.000002 | |
| # Slave_IO_Running: Yes | |
| # Slave_SQL_Running: Yes | |
| # Replicate_Do_DB: | |
| # Replicate_Ignore_DB: | |
| # Replicate_Do_Table: | |
| # Replicate_Ignore_Table: | |
| # Replicate_Wild_Do_Table: | |
| # Replicate_Wild_Ignore_Table: | |
| # Last_Errno: 0 | |
| # Last_Error: | |
| # Skip_Counter: 0 | |
| # Exec_Master_Log_Pos: 300828 | |
| # Relay_Log_Space: 301129 | |
| # Until_Condition: None | |
| # Until_Log_File: | |
| # Until_Log_Pos: 0 | |
| # Master_SSL_Allowed: No | |
| # Master_SSL_CA_File: | |
| # Master_SSL_CA_Path: | |
| # Master_SSL_Cert: | |
| # Master_SSL_Cipher: | |
| # Master_SSL_Key: | |
| # Seconds_Behind_Master: 0 | |
| # Master_SSL_Verify_Server_Cert: No | |
| # Last_IO_Errno: 0 | |
| # Last_IO_Error: | |
| # Last_SQL_Errno: 0 | |
| # Last_SQL_Error: | |
| # [09/01/2017-11:19:37] Starting slave replication on Master 192.168.2.4.... | |
| # *************************** 1. row *************************** | |
| # Slave_IO_State: Waiting for master to send event | |
| # Master_Host: 192.168.2.5 | |
| # Master_User: replication | |
| # Master_Port: 3309 | |
| # Connect_Retry: 60 | |
| # Master_Log_File: mysql-bin.000007 | |
| # Read_Master_Log_Pos: 295556762 | |
| # Relay_Log_File: mysqld-relay-bin.000002 | |
| # Relay_Log_Pos: 251 | |
| # Relay_Master_Log_File: mysql-bin.000007 | |
| # Slave_IO_Running: Yes | |
| # Slave_SQL_Running: Yes | |
| # Replicate_Do_DB: | |
| # Replicate_Ignore_DB: | |
| # Replicate_Do_Table: | |
| # Replicate_Ignore_Table: | |
| # Replicate_Wild_Do_Table: | |
| # Replicate_Wild_Ignore_Table: | |
| # Last_Errno: 0 | |
| # Last_Error: | |
| # Skip_Counter: 0 | |
| # Exec_Master_Log_Pos: 295556762 | |
| # Relay_Log_Space: 407 | |
| # Until_Condition: None | |
| # Until_Log_File: | |
| # Until_Log_Pos: 0 | |
| # Master_SSL_Allowed: No | |
| # Master_SSL_CA_File: | |
| # Master_SSL_CA_Path: | |
| # Master_SSL_Cert: | |
| # Master_SSL_Cipher: | |
| # Master_SSL_Key: | |
| # Seconds_Behind_Master: 0 | |
| # Master_SSL_Verify_Server_Cert: No | |
| # Last_IO_Errno: 0 | |
| # Last_IO_Error: | |
| # Last_SQL_Errno: 0 | |
| # Last_SQL_Error: | |
| # [09/01/2017-11:19:40] End of jobs. | |
| # | |
| #----------------------------------------------------------------------------------- | |
| # | |
| # Defaults Variables | |
| # | |
| # Default replication user and password : | |
| defRepUsr="replication" | |
| defRepPwd="replication_user_password" | |
| # | |
| # Default Master server ip:port | |
| defMaster="192.168.1.4:3306" | |
| # | |
| # Default Slave server ip:port | |
| defSlave="192.168.1.5:3306" | |
| # | |
| # Default mode of sync | |
| # mode : F (file) or P (pipe) | |
| defMode="F" | |
| # | |
| # Default database name | |
| defDatabase="MyDATABASE" | |
| # | |
| # Enable verbose mode : for debugging purpose | |
| verbose=1 | |
| # | |
| # Must we show password on verbose mode : only for verbose=1 | |
| # (passwords can be shown on verbose mode only) | |
| showPasswords=0 | |
| # | |
| # temporary file for F mode : | |
| DUMPFILE="/root/mydump.sql" | |
| # | |
| # Resync the databases except the following Databases | |
| # Joshua_sign : header "Database" must not be used | |
| EXCEPTIONS="information_schema mysql test Database" | |
| # | |
| #----------------------------------------------------------------------------------- | |
| # | |
| # Functions | |
| # | |
| # Syntax: testhost addr port pwd | |
| # Returns: hostOK=1 | |
| testhost () { | |
| mySrvTest=$(echo quit | mysql -u root -p$3 --port $2 -h $1) | |
| if [ $? -eq 0 ];then | |
| hostOK=1 | |
| else | |
| hostOK=0 | |
| fi | |
| }; | |
| fn_echo(){ | |
| echo -e "[`date '+%d/%m/%Y-%H:%M:%S'`] $1" | |
| } | |
| # standard help | |
| usage () { | |
| echo "" | |
| echo "ERROR: Somethig is wrong with the given arguments" | |
| echo "Syntax: $0 Master:Port Slave:port {F|P} [this_dbname_only]" | |
| echo " eg. $0 master1:3306 slave1:3306 P" | |
| echo "" | |
| exit 1 | |
| } | |
| # Get the mysql replication user name | |
| if [ -z ${defRepUsr+x} ]; then varStr=""; else varStr=" (leave empty for default)"; fi | |
| read -s -p "Please enter the MySQL replication user name$varStr: " replication_user | |
| echo "" | |
| if [ "$replication_user" == "" ];then | |
| replication_user="${replication_user:-$defRepUsr}" | |
| fi | |
| if [ -z ${defRepUsr+x} -a "$replication_user" == "" ];then | |
| echo "ERROR: The replication user name cannont be empty" | |
| exit 1 | |
| fi | |
| # Get the mysql replication user password | |
| if [ -z ${defRepPwd+x} ]; then varStr=""; else varStr=" (leave empty for default)"; fi | |
| read -s -p "Please enter the MySQL replication user password$varStr: " replication_pwd | |
| echo "" | |
| if [ "$replication_pwd" == "" ];then | |
| replication_pwd="${replication_pwd:-$defRepPwd}" | |
| fi | |
| if [ -z ${defRepPwd+x} -a "$replication_pwd" == "" ];then | |
| echo "ERROR: The replication user password cannont be empty" | |
| exit 2 | |
| fi | |
| #- Check the hosts info validity | |
| if [ -z ${defMaster+x} ]; then master=$(echo $1 | grep ':'); else master=$(echo $defMaster | grep ':'); fi | |
| if [ "$master" == "" ]; then | |
| echo "ERROR: The 2nd parameter(master) must be the combination 'host:port'" | |
| exit 3 | |
| fi | |
| # | |
| if [ -z ${defSlave+x} ]; then slave=$(echo $2 | grep ':'); else slave=$(echo $defSlave | grep ':'); fi | |
| if [ "$slave" == "" ]; then | |
| echo "ERROR: The third parameter must be the combination 'host:port'" | |
| exit 4 | |
| fi | |
| # | |
| if [ -z ${defMode+x} ]; then method=$3; else method=$defMode; fi | |
| if [ -z ${defDatabase+x} ]; then databases=$4; else databases=$defDatabase; fi | |
| if [ "$master" == "" -o "$slave" == "" -o "$method" == "" ];then | |
| echo "Wrongs parameters : mode $method - master : $master - slave : $slave" | |
| exit 1 | |
| fi | |
| # Get the mysql root password | |
| read -s -p "Please enter the MySQL root password: " rootpw | |
| echo "" | |
| if [ "$rootpw" == "" ];then | |
| echo "ERROR: The ROOT password cannont be empty" | |
| exit 2 | |
| fi | |
| # | |
| # Check the hosts connectivity of master host | |
| Mhost=$(echo $master | cut -d: -f1) | |
| Mport=$(echo $master | cut -d: -f2) | |
| # | |
| testhost $Mhost $Mport $rootpw | |
| if [ $hostOK = "0" ]; then | |
| echo "ERROR: The master $Mhost:$Mport does not respond for user root with the given password" | |
| exit 5 | |
| fi | |
| # | |
| # Check the hosts connectivity of slave host | |
| # | |
| Shost=$(echo $slave | cut -d: -f1) | |
| Sport=$(echo $slave | cut -d: -f2) | |
| # | |
| testhost $Shost $Sport $rootpw | |
| if [ $hostOK = "0" ]; then | |
| echo "ERROR: The slave $Shost:$Sport does not respond for user root with the given password" | |
| exit 6 | |
| fi | |
| MASTER="--host=$Mhost --port=$Mport --user=root --password=$rootpw" | |
| SLAVE="--host=$Shost --port=$Sport --user=root --password=$rootpw" | |
| MASTERNOPW="--host=$Mhost --port=$Mport --user=root --password=xxxxxxxxxxx" | |
| SLAVENOPW="--host=$Shost --port=$Sport --user=root --password=xxxxxxxxxxx" | |
| # if no database given on command line, get all databases (without exceptions ones) | |
| if [ "$databases" == "" ]; then | |
| for DB in $(echo "show databases;" | mysql $MASTER) ; 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 | |
| fi | |
| if [ "$databases" == "" ]; then | |
| echo "ERROR: No database found" | |
| exit 7 | |
| fi | |
| # | |
| # NOW All the tests are OK : the good job start now | |
| # | |
| if [ $verbose -eq 1 ];then | |
| fn_echo "DEBUG MODE - Ready to work! (starting sync in 30 seconds, press CTRL+C to abord)" | |
| fn_echo "DEBUG - Master Host:Port : $Mhost:$Mport" | |
| fn_echo "DEBUG - Slave Host:Port : $Shost:$Sport" | |
| fn_echo "DEBUG - Databases : $databases" | |
| fn_echo "DEBUG - Sync Method : $method" | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Replication user/pwd : [$replication_user/$replication_pwd]" | |
| else | |
| fn_echo "DEBUG - Replication user/pwd : [$replication_user/xxxxxxxxxxxx]" | |
| fi | |
| sleep 30 | |
| else | |
| fn_echo "Ready to work! (starting sync in 10 seconds, press CTRL+C to abord)" | |
| sleep 10 | |
| fi | |
| # added by Joshua_sign | |
| # Step 1 : lock all tables, and keep connection open to avoid auto unlock on disconnect | |
| # useless since mysqldump do the job, but we keep it to ensure locking | |
| fn_echo "Lock all tables on Master...." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on Master $Mhost:$Mport : \"FLUSH TABLES WITH READ LOCK;SELECT SLEEP(3600);\""; fi | |
| echo "FLUSH TABLES WITH READ LOCK;SELECT SLEEP(3600);" | mysql $MASTER &> /dev/null & sleep 5 | |
| fn_echo "Master is now freezed" | |
| # Step 2 : Stop and reset the slave and the master | |
| fn_echo "Flush and reset slave..." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on Slave $Shost:$Sport : \"FLUSH LOGS; RESET QUERY CACHE; RESET MASTER; STOP SLAVE; RESET SLAVE;\""; fi | |
| echo "FLUSH LOGS; RESET QUERY CACHE; RESET MASTER; STOP SLAVE; RESET SLAVE;" | mysql $SLAVE | |
| # The following lines is thanks to Dorian Kavaja | |
| # because sometimes the log file on the master was changed before the dump was finished | |
| fn_echo "Flush and reset master..." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on Master $Mhost:$Mport : \"FLUSH LOGS; RESET QUERY CACHE; RESET MASTER; STOP SLAVE; RESET SLAVE;\""; fi | |
| echo "FLUSH LOGS; RESET QUERY CACHE; RESET MASTER; STOP SLAVE; RESET SLAVE;" | mysql $MASTER | |
| # Step 3 | |
| # Now do the transfer | |
| # Added “–routines –triggers” to the mysqldump command, so triggers and functions were included in the dump. | |
| # Thanks to Dorian Kavaja | |
| # Joshua_sign : Remove "--routines" and "-–triggers" because my server does not support them : 5.1.63-0+squeeze1-log - (Debian) | |
| # Master-data to 1 (default) should be the best and easier way (https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html#option_mysqldump_master-data) but it does not work. | |
| # Remove --single-transaction and --skip-lock-tables because resyncing without locking is just a dream on production server with many connections | |
| # Added --lock-all-tables | |
| # Remove --lock-all-tables because MariaDB catch an error : "mysqldump: You can't use --single-transaction and --lock-all-tables at the same time." | |
| case $method in | |
| P) # Transfer all databases from master to slave directly using a pipe(P) | |
| fn_echo "Transfering from master $Mhost:$Mport into slave $Shost:$Sport directly" | |
| fn_echo "Databases : $databases" | |
| if [ $verbose -eq 1 ]; then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing : \"mysqldump $MASTER --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases | mysql $SLAVE\""; | |
| else | |
| fn_echo "DEBUG - Performing : \"mysqldump $MASTERNOPW --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases | mysql $SLAVENOPW\""; | |
| fi | |
| fi | |
| mysqldump $MASTER --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases | mysql $SLAVE | |
| # Find out the master binlog file name and position | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Catching the master_log_file and master_log_position on master $Mhost:$Mport by : \"SHOW MASTER STATUS\G;\""; fi | |
| masterlogfile_forslave=$(echo "SHOW MASTER STATUS\G;" | mysql $MASTER | grep "File:" | cut -d: -f2 | cut -d" " -f2) | |
| masterlogpos_forslave=$(echo "SHOW MASTER STATUS\G;" | mysql $MASTER | grep "Position:" | cut -d: -f2 | cut -d" " -f2) | |
| if [ $verbose -eq 1 ];then | |
| fn_echo "DEBUG - Catched 'master_log_file' on master $Mhost:$Mport : $masterlogfile_forslave" | |
| fn_echo "DEBUG - Catched 'master_log_position' on master $Mhost:$Mport : $masterlogpos_forslave" | |
| fi | |
| fn_echo "UNLOCK all tables on Master...." | |
| # kill the sleep | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Recovering sleep process PID on master $Mhost:$Mport"; fi | |
| pid=$(echo "SHOW PROCESSLIST" | mysql $MASTER | grep 'SLEEP(3600)' | cut -d" " -f1) | |
| if [ "$pid" != "" ]; then | |
| fn_echo "Killing SLEEP on pid $pid..." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"KILL CONNECTION $pid;\""; fi | |
| echo "KILL CONNECTION $pid;" | mysql $MASTER | |
| fi | |
| # this is useless because of killing sleep kill the connection that lock tables | |
| # and behavior is that "FLUSH TABLES WITH READ LOCK" is only true while connection doing it is active | |
| # but its better to do it, just in case... otherwise if behavior dont work, master stay freezed 1 hour | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"UNLOCK TABLES;\""; fi; | |
| echo "UNLOCK TABLES;" | mysql $MASTER | |
| fn_echo "Master is now operational" | |
| fn_echo "Update master information on Slave..." | |
| if [ $verbose -eq 1 ];then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;\""; | |
| else | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;\""; | |
| fi | |
| fi | |
| echo "CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;" | mysql $SLAVE | |
| ;; | |
| # | |
| F) # Transfer the databases using a dump file | |
| fn_echo "Dumping from master $Mhost:$Mport into file $DUMPFILE" | |
| fn_echo "Databases : $databases" | |
| if [ $verbose -eq 1 ];then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing : \"mysqldump $MASTER --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases > $DUMPFILE\""; | |
| else | |
| fn_echo "DEBUG - Performing : \"mysqldump $MASTERNOPW --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases > $DUMPFILE\""; | |
| fi | |
| fi | |
| mysqldump $MASTER --flush-logs --add-drop-database --delete-master-logs --hex-blob --databases $databases > $DUMPFILE | |
| # Find out the master binlog file name and position | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Catching the master_log_file and master_log_position on master $Mhost:$Mport by : \"SHOW MASTER STATUS\G;\""; fi | |
| masterlogfile_forslave=$(echo "SHOW MASTER STATUS\G;" | mysql $MASTER | grep "File:" | cut -d: -f2 | cut -d" " -f2) | |
| masterlogpos_forslave=$(echo "SHOW MASTER STATUS\G;" | mysql $MASTER | grep "Position:" | cut -d: -f2 | cut -d" " -f2) | |
| if [ $verbose -eq 1 ];then | |
| fn_echo "DEBUG - Catched 'master_log_file' on master $Mhost:$Mport : $masterlogfile_forslave" | |
| fn_echo "DEBUG - Catched 'master_log_position' on master $Mhost:$Mport : $masterlogpos_forslave" | |
| fi | |
| fn_echo "UNLOCK all tables on Master...." | |
| # kill the sleep | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Recovering sleep process PID on master $Mhost:$Mport"; fi | |
| pid=$(echo "SHOW PROCESSLIST" | mysql $MASTER | grep 'SLEEP(3600)' | cut -d$'\t' -f1) | |
| if [ "$pid" != "" ]; then | |
| fn_echo "Killing SLEEP on pid $pid..." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"KILL CONNECTION $pid;\""; fi | |
| echo "KILL CONNECTION $pid;" | mysql $MASTER | |
| fi | |
| # this is useless because of killing sleep kill the connection that lock tables | |
| # and behavior is that "FLUSH TABLES WITH READ LOCK" is only true while connection doing it is active | |
| # but its better to do it, just in case... otherwise if behavior dont work, master stay freezed 1 hour | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"UNLOCK TABLES;\""; fi; | |
| echo "UNLOCK TABLES;" | mysql $MASTER | |
| fn_echo "Master is now operational" | |
| # | |
| fn_echo "Update master information on Slave..." | |
| if [ $verbose -eq 1 ];then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;\""; | |
| else | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;\""; | |
| fi | |
| fi | |
| echo "CHANGE MASTER TO MASTER_HOST='$Mhost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_forslave', MASTER_LOG_POS=$masterlogpos_forslave;" | mysql $SLAVE | |
| # modified by Joshua_sign to avoid usage of various third parts | |
| # 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" | |
| fn_echo "Import dumpfile $DUMPFILE into slave $Shost:$Sport ... (this may take a while)" | |
| if [ $verbose -eq 1 ];then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"mysql $SLAVE < $DUMPFILE\""; | |
| else | |
| fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"mysql $SLAVENOPW < $DUMPFILE\""; | |
| fi | |
| fi | |
| mysql $SLAVE < $DUMPFILE | |
| ;; | |
| # | |
| *) usage ;; | |
| esac | |
| # Joshua-sign : fully init replication we need to construct query line like : | |
| # CHANGE MASTER TO MASTER_HOST='192.168.1.4', MASTER_USER='replication_user', MASTER_PASSWORD='replication_pwd', MASTER_LOG_FILE='mysql-bin.004258', MASTER_LOG_POS=426927318; | |
| # and play this on each MASTER and SLAVE (with the corrects values) | |
| # Find out the master binlog file name and position | |
| fn_echo "Update master information on Master..." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Catching the master_log_file and master_log_position on slave $Shost:$Sport by : \"SHOW MASTER STATUS\G;\""; fi | |
| masterlogfile_formaster=$(echo "SHOW MASTER STATUS\G;" | mysql $SLAVE | grep "File:" | cut -d: -f2 | cut -d" " -f2) | |
| masterlogpos_formaster=$(echo "SHOW MASTER STATUS\G;" | mysql $SLAVE | grep "Position:" | cut -d: -f2 | cut -d" " -f2) | |
| if [ $verbose -eq 1 ];then | |
| fn_echo "DEBUG - Catched 'master_log_file' on slave $Shost:$Sport : $masterlogfile_formaster" | |
| fn_echo "DEBUG - Catched 'master_log_position' on slave $Shost:$Sport : $masterlogpos_formaster" | |
| fi | |
| if [ $verbose -eq 1 ];then | |
| if [ $showPasswords -eq 1 ];then | |
| fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"CHANGE MASTER TO MASTER_HOST='$Shost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_formaster', MASTER_LOG_POS=$masterlogpos_formaster;\""; | |
| else | |
| fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"CHANGE MASTER TO MASTER_HOST='$Shost', MASTER_USER='$replication_user', MASTER_PASSWORD='xxxxxxxxxx', MASTER_LOG_FILE='$masterlogfile_formaster', MASTER_LOG_POS=$masterlogpos_formaster;\""; | |
| fi | |
| fi | |
| echo "CHANGE MASTER TO MASTER_HOST='$Shost', MASTER_USER='$replication_user', MASTER_PASSWORD='$replication_pwd', MASTER_LOG_FILE='$masterlogfile_formaster', MASTER_LOG_POS=$masterlogpos_formaster;" | mysql $MASTER | |
| # | |
| # Start the replication on slave | |
| fn_echo "Starting slave replication on Slave $Shost...." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"START SLAVE;\""; fi | |
| echo "START SLAVE;" | mysql $SLAVE | |
| sleep 3 | |
| # | |
| # Show slave status to see if all is in sync | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on slave $Shost:$Sport : \"SHOW SLAVE STATUS \G;\"";fi | |
| echo "SHOW SLAVE STATUS \G;" | mysql $SLAVE | |
| # | |
| # Start the replication on master | |
| fn_echo "Starting slave replication on Master $Mhost...." | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"START SLAVE;\""; fi | |
| echo "START SLAVE;" | mysql $MASTER | |
| sleep 3 | |
| # | |
| # Show slave status to see if all is in sync | |
| if [ $verbose -eq 1 ];then fn_echo "DEBUG - Performing on master $Mhost:$Mport : \"SHOW SLAVE STATUS \G;\""; fi | |
| echo "SHOW SLAVE STATUS \G;" | mysql $MASTER | |
| fn_echo "End of jobs." | |
| fn_echo "" | |
| fn_echo "If you experience error please read the solution at the begining of this file." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi all,
This script is a modified version of MySQL_REBUILD_LIVE_SLAVE_from_MASTER by Matthew Crenshaw,
I use it successfully on production servers when replication is broken.
I hope this will be usefull for anyone need it.
Have a good day!
Josh_