MySQL: Using CONNECT to Quickly Verify Replication Health

Jan 27, 2009 / By Gerry Narvaja

Tags:

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.

Example

  1. Log into the slave using the mysql client and issue SHOW SLAVE STATUS:
    mysql> show slave statusG
    *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.0.1
                    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
                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: 212148903
                Relay_Log_Space: 5526119
                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)
  2. Connect to the master using CONNECT:
    mysql> CONNECT database 192.168.0.1
    
    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'@'192.168.0.2' (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.

Conclusion

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.

4 Responses to “MySQL: Using CONNECT to Quickly Verify Replication Health”

  • Stefan Hinz says:

    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
    done
    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
    done

    The output is pretty concise and straightforward. :-)

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

  • chetanM says:

    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`”

  • Phil says:

    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

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>