Replication States
In order to be able to monitor replication effectively, you need to
know and understand the various states that the master and slave can
occupy. Server states can be displayed by using the SHOW PROCESSLIST
statement on the
master and the slave. At least one line of the results will be related to
the replication activities for the user account associated with
replication. Following the examples of this chapter, the account is
replicant on the master and system
user on the slave. In the Command
column, on
the master the value will be Binlog Dump
, meaning a
binary log thread; on the slave the value will be
Connect
. The results will also contain a field called
State
, in which the state of the thread will be given.
Here is an example from a slave:
SHOW PROCESSLIST \G *************************** 1. row *************************** Id: 16 User: system user Host: db: NULL Command: Connect Time: 119255 State: Waiting for master to send event Info: NULL
These results show only one thread, the I/O thread waiting on the master. If the server were processing entries from the master’s binary log, there would probably be another row shown in the results for the SQL thread. What follows is a list of all of the possible server states that you may see on master and slave servers, along with descriptions of each. In addition to understanding these traits, you may want to develop a script to check that replication is running on the slave and not stalled and to notify you if it’s not ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.