Note: Please go to docs.rightscale.com to access the current RightScale documentation set. Also, feel free to Chat with us!
Home > ServerTemplates > Archive > 11H1 > Runbooks > Database Manager for MySQL Stripe Runbook > MySQL Stripe Recipes > Checking master or slave database status

Checking master or slave database status

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)
You must to post a comment.
Last modified
08:09, 16 Jul 2013

Tags

Classifications

This page has no classifications.

Announcements

None


© 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.