On the master:
- Run the
FLUSH TABLES WITH READ LOCKcommand.
- Run the
SHOW MASTER STATUScommand.
- Record the master binary log and position.
On the slaves:
SELECT MASTER_POS_WAIT(binary_log_name, position).
On each remaining active slave connected to this master, examine the results of
SHOW SLAVE STATUSuntil the slave has synchronized with the master. Once the slave reaches this state, it is safe to shut down the slave.
When you restart your replication topology, all slaves will automatically start without any lag. This procedure is useful in cases where there has been a disruption and you want to get things going again to avoid a prolonged slave lag.
Learn more about this topic from MySQL High Availability.
Server bottlenecks and failures are a fact of life in any database deployment, but they don't have to bring everything to a halt. MySQL provides several features that can protect you from outages, whether you're running directly on the hardware, on virtual machines, or in the cloud. This book shows you how to use these features effectively, and helps you determine which combination of features will give you the most reliable system for a price you can afford.