SLAVE - Execution pointer not moving
Posted by:
Eric Coll
Date: November 13, 2012 05:15PM
Main problem:
- Slave replication seems to be working but the Exec_Master_Log_Pos never changes.
- The server shows high CPU utilization although is actually doing nothing.
- There is a large amount of data inserted on the master that doesn't appear in the slave
The master has been running with no errors for months. The Relay_Master_Log_File exists on the master and it's 1.1 GB, the execution pointer is valid.
Context:
- We had one slave running for several months with no errors
- We added a second slave, and now they run fine for a few weeks, and then both of them stop at the same Exec_Master_Log_Pos and don't move.
- Normally, the seconds behind master start increasing.
- We stopped one of the slaves and reboot the second one. The reboot server now shows 0 seconds behind master, but still the execution pointer doesn't move.
Processes on the slave:
1 system user Connect 5270 Waiting for master to send event
2 system user Connect 18414 Reading event from the relay log
34 dev servername Sleep 409
35 dev servername Query 0 show processlist
Show slave status:
Slave_IO_State: Waiting for master to send event
Master_Host: ec2-landfill-db-iad-6001.iad6.amazon.com
Master_User: landfill_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.006965
Read_Master_Log_Pos: 34462794
Relay_Log_File: mysql-relay-bin.000993
Relay_Log_Pos: 5162530
Relay_Master_Log_File: mysql-bin.006960
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: dashboard.raw_usage_staging,capacity.pool_import,requests.request,capacity.pool_usage_import,requests.metadata,capacity.v_reservation_import,capacity.v_pool_import,capacity.reservation_import,requests.unfiltered_fault,dashboard.reservation_staging,requests.async_fault,requests.request_zone,capacity.family_pool,capacity.v_pool_usage_import
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: admission_control.%,temp.%,test.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5162384
Relay_Log_Space: 5419639660
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1