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.