I have recently set up some new MySQL Databases on a remote site. They are all 5.5.47, running on Debian Servers.
They are set up as one Master with multiple slaves. The Master is itself a slave from our main site.
But periodically replication on one of them (never the same one, and only one at a time) gets stuck. The servers at my main site which have been running for years, are fine. The config of the servers at the new site is identical to the main site. The servers (VM's) are the same as the main site.
I can see the relay logs continuing to increase in size, but the Exec_Master_Log_Pos value just sits there.
The error logs are of no use as I have
log_warnings=0 due to our website generating hundreds of
unsafe statements every second.
Everything else continues to work fine, until I execute a
STOP SLAVE command which just hangs. After that all other related queries (
show slave status,
start slave etc. also just hang.)
I've tried killing the connections / queries, but it makes no difference with each of them sitting there saying 'killed', but not completing.
I've checked disk space, and that is always fine. I can log in to MySQL via the terminal and run basic commands (until I run
I have compared everything I can think of to the other servers, but there is never any difference.
If I try and shutdown the database (
mysqladmin . . . shutdown), it hangs at
Event Scheduler: Purging the queue. 0 events
Finally I resort to killing the mysqld_safe process (
sudo kill 12345), which forces it to restart, and goes into crash receovery. After that it seems to be fine.
Is there anything else I can check to try and find out what is happening.