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
- 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) - 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_userin the example) has to have the proper permissions to log in from the slave into the master and access the specified database. Example: ExecuteGRANT 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. - 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
Positionfield 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 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
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.
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!)