Mysql Slave – Stop applying changes from a particular user

archivebinlogMySQLreplication

I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday.

But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB.

So, How can I achieve this?

Im using a seperate user for archive.

Is there any options to prevent this operation from the Particular slave?
Or any way to achieve this?

Best Answer

I think you'll be able to achieve this goal but there isn't really anything native that will give you this functionality out of the box.

You won't be able to use replication filters because they're not granular enough to filter by DML type. So that's off the list early.

You might approach it (optionally using partitions based on your archive routine) dropping the data (partition) outside the bounds of binary logging (SET SESSION LOG_BIN=0) and therefore not sending the DELETE/DROP messages through the replication topology. This could be managed through MySQL's built-in EVENTS scheduler or could be something that runs outside of MySQL in a script controlled through crond.

A quick scan of the documentation of Percona Toolkit's pt-archiver suggests that you could run archival jobs that connect to >1 mysqld instance and can do so in a manner that the session evades the binary logging (see DSN option 'b').

A word of warning, topologies of this design can be pretty delicate especially if you forget that the downstream replicas have more data in certain tables. I have completely forgotten about this when running tools such as pt-online-schema-change where the table is rebuilt on the MASTER and the REPLICA's version of the table ends up the same as the MASTER's.

Good luck!

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

Mysql – Rows already DELETEd on MySQL slave; How to DELETE on master

Here is something quick and dirty you can do

Run the DELETE like this on the Master:

SET SQL_LOG_BIN=0;
DELETE FROM ... ;

The first line tells the DB Session not to record the SQL that follows. Thus, when you run the DELETE on the Master, the SQL will not be written in the Master's binary logs. Consequently, the Slave will never receive the DELETE in its relay logs.

This will only affect the session you use to run these two lines. All other DB Connections will replicate as usual. Once you disconnect, any new session will replicate properly.

Give it a Try !!!

Best Answer

Related Solutions

MySQL Master/Slave Master/Master setup

Mysql – Rows already DELETEd on MySQL slave; How to DELETE on master

Related Question