Checking master or slave database status

Objective

Manually check or verify the status of a master or slave database Server.

Background

In some instances it may be necessary to manually check the status of a database Server and verify it is what you think it is (e.g. a master or a slave).  For example, if the Servers are inappropriately named.  This can be the case if a slave database Server has been promoted to a Master, yet the Server name was never renamed in the Deployment.  Similarly, if a former master database Server was configured to be a Slave and launched, it may not have a name that reflects the change.  Things may not be exactly what they seem in this instance.  This brief tutorial steps you through checking the database itself.

Steps (Master)

• SSH into a running Master database Server (or what you think is the master ;-)
• Connect to the mySQL database:

mysql -u root

mysql> show master status \G *************************** 1. row ***************************
File: mysql-bin.000001
Position: 303
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Things to note from above are:

• File (e.g. mysql-bin.000001)  - Should match the Master_Log_File from the slave (shown below in the Slave Steps)
• Position - Should be similar to the position to the Read_Master_Log_Pos for the Slave (show below).  In our example, they are the exact same (303).  In an active database, they will be similar, but not exact because it is getting updated constantly.

If you run a "show slave status" from a master database Server you will get a "empty set" returned:

mysql> show slave status \G 
Empty set (0.00 sec)

Steps (Slave)

• SSH into a running Slave database Server (or what you think is the slave ;-)
• Connect to the mySQL database:

mysql -u root

mysql> show slave status \G *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: e2emaster.rightscaletraining.com
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 303
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 303
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

 In addition to what was highlighted (in bold) between the output of the Slave and Master status, note:

• Seconds_Behind_Master - Ideally, this will be zero, as it is in our example.  It could be non-zero if a huge operation is under way. If its not zero, a database administrator should know why its not zero.  As a general practice, if this number is not zero, and you don't have a good reason why its not zero, then that could be an indicator something is not quite right and further investigation is required.

If you run a "show master status" from a slave database Server you will get a "empty set" returned:

mysql> show master status \G
Empty set (0.00 sec)

See also

• Launch a new Master DB
• Launch a new Slave DB