SQL Commands for Replication
It is possible to control replication, and to monitor what is going on, using SQL queries. Here are a few SQL commands for doing these things.
You can control and monitor the master using the following SQL commands:
SHOW MASTER STATUS displays information on the state of the master binary logs.
RESET MASTER clears (deletes) all binary logs from the master; this is useful if you want to start with a “clean sheet” and copy a fresh version of the database to the slave.
You can control and monitor the slave using the following commands:
SHOW SLAVE STATUS displays information on the current state of the slave, including whether it is running, the master it is connected to, and what position it is at in reading the master's binary log (this number should increase as updates are processed).
SLAVE STOP tells the slave to cease reading from the master; replication stops immediately.
LOAD DATA FROM MASTER connects to the master and gets a complete, new copy of its databases; useful if you want to clear the slave's databases and start replicating again from the current position on the master. (Note that in the current version of MySQL, this command obtains a read lock on the master database before transferring the data; this prevents updates from occurring on the master. On a large database or with a slow Internet connection, this may take a considerable time while the transfer is taking place and cause unacceptable downtime on the master.)
RESET SLAVE tells the slave to forget its position in reading the master's logs; this is useful if you want to get a fresh copy of the database from the master.
Try out these commands so that you feel comfortable using a small set of SQL commands for monitoring the state of replication and making some changes to how it's operating.
It's now time to look at some architectures that go beyond the basic two-server model and explore the potentials of replication.