Find MySQL Master and Slave Status

Find MySQL Slave Status

Is MySQL Master and Slave are in sync ?

In this tutorial i will be sharing both queries to find out MySQL slave status and basics of tracing root cause.

Check MySQL slave status
  1. Connect to MySQL slave database 'ex.: mysql -u user -p ....etc'
  2. Run below queries

SHOW SLAVE STATUS\G

Output example (In case Slave is up and in sync)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001079
Read_Master_Log_Pos: 269214454
Relay_Log_File: slave-relay.000130
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

Output example (In case Slave is up and in sync)

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001079
Read_Master_Log_Pos: 269214454
Relay_Log_File: slave-relay.000130
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: No
Slave_SQL_Running: No

Note: Keep in mind that while doing the backup usually the replication will stop, before proceeding to fix anything confirm that this is not part of normal behavior.

 

Check MySQL errors

mysql --help | grep "Default options" -A 1

Above command will locate the cnf file, then locate the error file by running below.

cat /etc/my.cnf | grep -i error

Then open the file to trace the errors:

Example:

vi /mysql/data2/logs/mysql3306_error.log

 

How to repair a broken replica ?

First you need to stop the slave

mysql> stop slave ;

In case there is an invalid sql query, you need to skip it for now

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Start slave again

mysql> start slave ;

Check the MySQL slave status again

mysql> SHOW SLAVE STATUS\G

 

 


Subscribe to
for video tutorials updates