Jan
27
2009
MySQL: Using CONNECT to Quickly Verify Replication Health
Posted by Gerardo Narvaja on Tue 27 Jan 2009 23:44 UTC
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 status\G
*************************** 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.