Problem with MySql Replication

MySQL & MariaDB Other MySQL® Questions
1

I have Multi Server Replication setup which is described below:

  1. Database DB1 is hosted on Server A (Transactional MyISAM DB)
  2. Database DB2 and DB3 are hosted on Server B (Transactional MyISAM DB)
  3. DB1 is replicated on Server B
  4. DB1, DB2 and DB3 are replicated from Server B to Server C which is used for reporting purposes.

I am facing the the following issues :

  1. There is a huge lag in replication from Server A to Server B. Update statements are taking a lot of time to execute at Server B.
  2. Data for DB1 is not being replicated to Server C though it is coming on Server B.

Please help…!!!

2

  1. what version of MySQL you are using ? there can be many causes, as you using MyISAM tables so table level locking comes into play, slow SQL you may want to check my posts how to enable slow query logs and to analyze it here [url]http://www.mysqlperformanceblog.com/2014/03/14/tools-and-tips-for-analysis-of-mysqls-slow-query-log/[/url] and check this too for possible causes of replication delay and how to fix it here [url]http://www.mysqlperformanceblog.com/2014/05/02/how-to-identify-and-cure-mysql-replication-slave-lag/[/url]

  2. Make sure you have log-slave-updates enable on Server B in order to replicate DB1 to server C.

3

Thanks for the prompt response…!!
I have made the changes in my.cnf of Server B and restarted it.

The problem is still with the update statements. I ran show processlist and found out a simple update query coming from master to this slave which is taking huge time.

Query : UPDATE ticket SET escalation_response_time = ‘0’ WHERE id = ‘1990757’
Time taken till now : 14660
Status: locked

this is a very simple update statement i am unable to understand why it is taking so much time.
there are almost 3,50000 records in this table.

​Any ideas…???

4

I hope after enabling log-slave-updates replicaion should be stream to server C.
For update query as you can see,

Status:locked

Which means some other query preventing this query to execute as it holding the lock. So, this query is locked waiting for lock to acquire quite long. I would suggest to inspect processlist to identify what query is blocked this update. This clearly suggests that some other query is really slow taking more than 14660 seconds.

5

Also I changed the Engine of Few of my main tables to InnoDB that seems to help with the locking.