Checking master or slave database status
Table of Contents
Objective
To manually check or verify the status of a Master or Slave database Server.
Background
In some situations, you may want to verify that your database instances are functioning as expected with regards to their intended role (i.e. Master or Slave). For example, keeping track of which database servers are your real master or slave can become confusing if you explicitly add "master" or "slave" in their nickname, which is not a recommended best practice because over the lifecycle of your Deployment, you may perform several slave-to-master promotions. Follow the steps below to verify which instance is your true master or slave.
Get more details and additional reference architectures in our white paper: Build Scalable Applications in the Cloud: Reference Architecture & Best Practices.
Steps
Check a Master-DB
Follow the steps below to verify that a Server is a Master-DB.
- SSH into a running Master-DB Server...or what you think is the master.
- Connect to the MySQL database:
mysql -u root
Run the following MySQL command:
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 in the "Check a Slave-DB" below)
- Position - Should be similar to the position to the Read_Master_Log_Pos for the Slave (shown in the "Check a Slave-DB" below). In our example, they are the same (303). In an active database, they will be similar, but not exactly the same because it's constantly getting updated.
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)
Check a Slave-DB
Follow the steps below to verify that a Server is a Slave-DB.
- SSH into a running Slave database Server...or what you think is the slave.
- Connect to the mySQL database:
mysql -u root Run the following MySQL command:
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 shown in our example. However, it could be non-zero if a huge operation is under way. If it's not zero, a database administrator should know why it's not zero. As a general rule, if this number is not zero and you don't have a good explanation as to why it's not zero, then it could be an indicator that something is not quite right and further investigation is probably required.
If you run a "show master status" from a Slave-DB Server you will get a "empty set" returned:
mysql> show master status \G Empty set (0.00 sec)
- © Copyright 2015 RightScale Technical Support