To manually check or verify the status of a Master or Slave database Server.
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.
Follow the steps below to verify that a Server is a Master-DB.
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:
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)
Follow the steps below to verify that a Server is a Slave-DB.
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:
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)
© 2006-2014 RightScale, Inc. All rights reserved.
RightScale is a registered trademark of RightScale, Inc. All other products and services may be trademarks or servicemarks of their respective owners.