Understanding replica status

MySQL replication status can be found by executing following command on the standby node:

mysql> show replica status\G 
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: SOURCE_IP
Source_User: repluser
Source_Port: 23343
Connect_Retry: 60
Source_Log_File: binlog.000904
Read_Source_Log_Pos: 1248490291
Relay_Log_File: relay.001821
Relay_Log_Pos: 405
Relay_Source_Log_File: binlog.000904
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 196
Relay_Log_Space: 1248490783
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File: /etc/pki/service-provider-ca-bundle.crt Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 1728
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: SERVER_ID
Source_UUID: c6e12e9e-ac75-11eb-8cc8-42010af00009
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Slave has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: c6e12e9e-ac75-11eb-8cc8-42010af00009:28-1252 Executed_Gtid_Set: c6e12e9e-ac75-11eb-8cc8-42010af00009:1-1252 Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:

We mainly concern about the lag transactions/seconds here, they are calculated by connecting to the master node and comparing GTIDs (Retrieved_Gtid_Set in the printout above) and get time between the last and the newest. This approach will give a better indication of lag, since a long running transaction on the primary when viewed from the point of the standby would seem like nothing is happening and there is no need to replicate.

Seconds_Behind_Source as shown in the printout is not a reliable indicator of lag for monitoring, but is still useful. Details can be found here.

You can also run the following query to see if replication is currently performing any work on the standby node. If there are tasks but not progressing, there is probably an issue.

SELECT * FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/sql/Applying batch of row changes%'

Query locks on the standby node

If a row or table is locked you replication will not progress.

If it is a table level lock it will show in:

mysql> show processlist
+-------+-----------------+------------------------------+--------+---------+-------+---------------------------------+-----------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
| 1800 | avnadmin | HOST_IP:53938 | teammy | Query | 58798 | Waiting for table metadata lock | /* ApplicationName=DataGrip 2020.3.2 */ LOCK TABLES users WRITE

If nothing shows up there, you may find them by running:

show open tables where In_Use > 0

Or

show engine innodb status

Look for the section: TRANSACTION.

There are also cases where it could be multiple queries and load causing the replication problem.

Long running transactions

Long running transactions with large binlogs will result in problems.

We calculate time since progress, once the binlog has been replicated, the standby node will effectively be stuck processing one massive GTID. If Read_Source_Log_Pos in the printout is updating this requires no action and the service should be left alone for replication to continue.

No disk space on the standby

show processlist will indicate that there is an issue with disk space:

mysql> show processlist;
+---------+-----------------+-------------------------------+-------+---------+---------+----------------------------+--------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-----------------+-------------------------------+-------+---------+---------+----------------------------+--------------------------------------+
| 17 | system user | connecting host | NULL | Connect | 4789695 | Waiting for disk space | NULL |

Got here by accident? Learn how Aiven’s hosted and managed MySQL solution will simplify your workflow:

Hosted MySQL as a Service

Did this answer your question?