MySQL: Using CONNECT to Quickly Verify Replication Health

Posted in: MySQL, Technical Track

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.

According to the MySQL Reference Manual’s section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it’s still not perfect.

The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use CONNECT.


  1. Log into the slave using the mysql client and issue SHOW SLAVE STATUS:
    mysql> show slave status\G
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_User: repl_user
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000008
            Read_Master_Log_Pos: 212148903
                 Relay_Log_File: mysqld-relay-bin.000017
                  Relay_Log_Pos: 5526119
          Relay_Master_Log_File: mysql-bin.000008
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                     Last_Errno: 0
                   Skip_Counter: 0
            Exec_Master_Log_Pos: 212148903
                Relay_Log_Space: 5526119
                Until_Condition: None
                  Until_Log_Pos: 0
             Master_SSL_Allowed: No
          Seconds_Behind_Master: 0
    1 row in set (0.00 sec)
  2. Connect to the master using CONNECT:
    mysql> CONNECT database
    Connection id:    154123120
    Current database: avail

    Note that for this point to be successful, you will need to make sure that the same username on the localhost (repl_user in the example) has to have the proper permissions to log in from the slave into the master and access the specified database. Example: Execute GRANT ALL ON database.* TO 'repl_user'@'' (or similar) on the master server. Make sure the password is the same on both servers.

  3. Once you have connected, check the master status using SHOW MASTER STATUS:
    mysql> show master status;
    | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    | mysql-bin.000008 | 212198968 |              |                  |
    1 row in set (0.00 sec)

    Since neither of the servers was stopped or locked, the Position field continues to advance as data is written to the master.

If replication is healthy, the Read_Master_Log_Pos and/or Exec_Master_Log_Pos from SHOW SLAVE STATUS would be very similar to the Position value from SHOW MASTER STATUS. The same applies to Master_Log_File on the slave and File on the master—most of the time these two should be the same.


By setting up the proper credentials on the slave and the master, you can quickly test replication by logging in only to the slave and comparing the slave and master information from there.



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

I have been working with databases for the last 20 years. Prior to Pythian worked for MySQL for 6 years.

4 Comments. Leave new

For my small replication network, I’m using this shell script:

#! /bin/sh
echo “Master servers ————————————————–”
for host in themis iconnect2; do
echo $host
mysql -h $host -e “show master statusG” | grep Position
echo “Slave servers —————————————————”
for host in iconnect2 artemis astraia athena aphrodite hermes; do
echo $host
mysql -h $host -e “show slave statusG” | grep Exec_Master_Log_Pos

The output is pretty concise and straightforward. :-)

Gerry Narvaja
January 29, 2009 4:15 pm

Pretty concept. I guess that the awk and perl experts can take it and add some warning messages based on the info. Thanks for sharing.


slave_pos=”`mysql -uroot -ppasswd -e ‘show slave status G;’ |grep -r ‘Read_Master_Log_Pos’ |awk ‘{ printf ” %s “, $2 }’ |xargs`”

master_pos=”` mysql -uroot -ppasswd -e ‘show master status;’ | grep mysql-bin |awk ‘{ printf ” %s “, $2 }’ |xargs`”


chetanM > Thank you for that. Personnaly, I use “Exec_Master_Log_Pos” instead of “Read_Master_Log_Pos” (our tests have shown that Read_Master_Log_Pos isn’t what we want to detect loss of replication!)


Leave a Reply

Your email address will not be published. Required fields are marked *