Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 3 down vote favorite
1

Specifically I have a set of InnoDB tables and we are backing these up into MyISAM tables to provide full text search. The slave DB routinely falls behind the Master. I am looking for advice on diagnostics or commands that can be used to keep track of the lag. Both machines are on the same network. Many thanks.

share|improve this question

2 Answers

up vote 8 down vote accepted

SHOW SLAVE STATUS is your command

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 3
              Master_Log_File: gbichot-bin.005
          Read_Master_Log_Pos: 79
               Relay_Log_File: gbichot-relay-bin.005
                Relay_Log_Pos: 548
        Relay_Master_Log_File: gbichot-bin.005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 79
              Relay_Log_Space: 552
              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: 8
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0

The relevant parts are:

               Slave_IO_State: Waiting for master to send event

"Waiting for master..." is what you usually want to see

              Master_Log_File: gbichot-bin.005
          Read_Master_Log_Pos: 79
          Exec_Master_Log_Pos: 79

This, combined with "SHOW MASTER STATUS" on the master server lets you compare how much has been transferred and properly replicated

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Is replication running?

        Seconds_Behind_Master: 8

Lag estimation

                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

If there's an error, this show what's wrong. Also check your error log and SHOW PROCESSLIST for more info

share|improve this answer
What actually is the difference between Slave_IO_Running and Slave_SQL_Running? I always check for Yes and Yes, but what actually do imply? – Coops Jul 31 '09 at 15:39
1  
Replication in MySQL works in two threads. The IO thread "brings" data from the master, and the SQL thread applies it on the slave. – Jorge Bernal Aug 1 '09 at 17:35
up vote 2 down vote

nagios's plugin does that

-bash-3.2# ~nagios/libexec/check_mysql -h | grep check-slave
 -S, --check-slave
-bash-3.2#
share|improve this answer
1  
Good grief... Just yesterday I wrote a Nagios plugin from scratch to pull the slave info. Thanks! – Insyte Jul 31 '09 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.