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:

*************************** 1. row ***************************
 Id: 16
 User: system user
 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 O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.