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.
SHOW SLAVE STATUS is your command
The relevant parts are:
"Waiting for master..." is what you usually want to see
This, combined with "SHOW MASTER STATUS" on the master server lets you compare how much has been transferred and properly replicated
Is replication running?
If there's an error, this show what's wrong. Also check your error log and SHOW PROCESSLIST for more info
nagios's plugin does that
-bash-3.2# ~nagios/libexec/check_mysql -h | grep check-slave -S, --check-slave -bash-3.2#