MySQL Replication – Slave Not Executing Long Running Update Query

master-slave-replicationMySQLreplication

I am running a Master-slave replication servers of MySQL. As part of clean up of old data, i ran a delete query which deletes huge number of records from the database. It ran fine on Master server but on slave server, it is giving me following error:

Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.

The slave server machine is less powerful than master one. How can I get past this?

The query is single line delete query. I am running MySQL 5.6.

Best Answer

You need to stop replication, make the Slave have the same specs as the Master, then start replication.

Make sure the Slave has no incoming connections. Otherwise, that will make the SQL thread on the Slave compete with incoming connections that are running SELECT queries against the same table you are running DELETE.

If you cannot reroute the incoming connections, you will have to rerun the DELETE in chunks (perhaps 5000 rows at a time) on the Slave locally.

As a last resort, rebuild the Slave (after scaling up the Slave's hardware and configs).

Related Solutions

MySQL Master/Slave Master/Master setup

You might look into MySQL Proxy. Features include:

load balancing
failover
query analysis
query filtering and modification

I haven't run it myself, but it seems to cover what you want.

UPDATE

Apparently the Read/Write splitting is a proof of concept and not for production use

Thesql replication deadlock slave stopped

This query fails on slave: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id'

You report a strange Error_code: 0, but try to run this query on slave? When it run succesfully then run as root:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
SLAVE START;

Best Answer

Related Solutions

MySQL Master/Slave Master/Master setup

Thesql replication deadlock slave stopped

Related Question