| Index: trunk/tools/switch-master/10-master-readonly |
| — | — | @@ -3,7 +3,10 @@ |
| 4 | 4 | . config |
| 5 | 5 | |
| 6 | 6 | ssh root@$master 'sed -i~ '\''s/#\s*read-only/read-only/'\'' /etc/my.cnf' |
| 7 | | -echo "set global read_only=1; flush tables;" | mysql -h $master |
| | 7 | +echo "set global read_only=1;" | mysql -h $master |
| | 8 | +echo "killing queries running over 10 seconds" |
| | 9 | +mysql -h $master -e "show processlist" | awk '{ if (($6 > 9) && ($2 ~ "^wiki")) { print "kill " $1 ";" } }' | mysql -h $master |
| | 10 | +echo "set sql_log_bin=0; flush tables;" | mysql -h $master |
| 8 | 11 | echo '\sselect @@read_only' | mysql -h $master |
| 9 | 12 | echo |
| 10 | 13 | |
| Index: trunk/tools/switch-master/MasterSwitcher.php |
| — | — | @@ -74,6 +74,7 @@ |
| 75 | 75 | // Stop slave on the new master and reset it so it can't start again |
| 76 | 76 | $this->log( 'Configuring the new master' ); |
| 77 | 77 | $newMasterDB = $this->getConnection( $newMaster ); |
| | 78 | + $this->killOldQueries( $newMasterDB ); |
| 78 | 79 | $newMasterDB->query( 'STOP SLAVE' ); |
| 79 | 80 | $newMasterDB->query( 'CHANGE MASTER TO master_host=\'\'' ); |
| 80 | 81 | $newMasterDB->query( 'RESET SLAVE' ); |
| — | — | @@ -116,6 +117,7 @@ |
| 117 | 118 | $this->log( "Cannot change master on $slave: connection error" ); |
| 118 | 119 | continue; |
| 119 | 120 | } |
| | 121 | + $this->killOldQueries( $conn ); |
| 120 | 122 | $this->doQueryLogErrors( $conn, $slave, 'SLAVE STOP' ); |
| 121 | 123 | $this->doQueryLogErrors( $conn, $slave, $changeMasterSql ); |
| 122 | 124 | $this->doQueryLogErrors( $conn, $slave, 'SLAVE START' ); |
| — | — | @@ -330,10 +332,7 @@ |
| 331 | 333 | return true; |
| 332 | 334 | } |
| 333 | 335 | |
| 334 | | - function prepareOldMaster( $hostName, $conn ) { |
| 335 | | - // Set the old master to read-only |
| 336 | | - $conn->query( 'SET GLOBAL read_only=1' ); |
| 337 | | - |
| | 336 | + function killOldQueries ( $conn ) { |
| 338 | 337 | // Kill long-running queries |
| 339 | 338 | $res = $conn->query( 'SHOW PROCESSLIST' ); |
| 340 | 339 | $killQueries = array(); |
| — | — | @@ -349,10 +348,18 @@ |
| 350 | 349 | $conn->query( $query ); |
| 351 | 350 | } catch ( DBQueryError $e ) {} |
| 352 | 351 | } |
| | 352 | + } |
| 353 | 353 | |
| | 354 | + function prepareOldMaster( $hostName, $conn ) { |
| | 355 | + // Set the old master to read-only |
| | 356 | + $conn->query( 'SET GLOBAL read_only=1' ); |
| | 357 | + |
| | 358 | + // Kill Long Running Queries |
| | 359 | + $this->killOldQueries( $conn ); |
| | 360 | + |
| 354 | 361 | // Flush tables |
| 355 | 362 | // This ensures that pending transactions are committed |
| 356 | | - $conn->query( 'FLUSH TABLES' ); |
| | 363 | + $conn->query( 'SET SQL_LOG_BIN=0; FLUSH TABLES' ); |
| 357 | 364 | |
| 358 | 365 | // Sanity check |
| 359 | 366 | $res = $conn->query( 'SELECT @@read_only as read_only' ); |
| Index: trunk/tools/switch-master/30-slaves |
| — | — | @@ -2,6 +2,8 @@ |
| 3 | 3 | |
| 4 | 4 | . config |
| 5 | 5 | |
| | 6 | +# kill long running wiki queries on new master |
| | 7 | +mysql -h $newmaster -e "show processlist" | awk '{ if (($6 > 9) && ($2 ~ "^wiki")) { print "kill " $1 ";" } }' | mysql -h $newmaster |
| 6 | 8 | # Turn off slave on new master, to avoid replication loop |
| 7 | 9 | echo "slave stop; change master to master_host=''" | mysql -h $newmaster |
| 8 | 10 | |
| — | — | @@ -18,6 +20,7 @@ |
| 19 | 21 | for slave in $slaves $master |
| 20 | 22 | do |
| 21 | 23 | echo $slave |
| | 24 | + mysql -h $slave -e "show processlist" | awk '{ if (($6 > 9) && ($2 ~ "^wiki")) { print "kill " $1 ";" } }' | mysql -h $slave |
| 22 | 25 | echo "$sql" | mysql -f -h $slave |
| 23 | 26 | done |
| 24 | 27 | |