How would I run DELETE queries on a Master Without Executing Same DELETEs on a Slave?

Question

As it is obvious in the title of the question, I am looking for any solutions, even an alternative, to how I am trying to solve it.

I am open to ideas, and I would appreciate any advice and insight to any concern. This is what I assumed the solution to look like:

If I want to run a query that will only execute on the master mysql server and not the slave. assuming we have an environment with replication through query logs. Can I and how would I create a special statement like:

DELETE FROM tablename WHERE column='our filters' AND (**mysql.server_id=1**);

or maybe something where master_status=true, the idea is to have some value that is different on master from slave. And I want to know is there any complications? Lets say the master is a production website and only requires data that has been entered in the past month to operate correctly but the slave is where data from years will be saved and later analyzed to change variable on the main (production/master) server.

Answer 1

There is a much, much simpler way

A Replication Slave relies on the Master's binary logs so as to ship SQL from them into the Slave's local relay logs. Just tell the Master not to record the SQL

SQLSTMT="SET SQL_LOG_BIN=0;"
SQLSTMT="${SQLSTMT} DELETE FROM tablename WHERE column='our filters'"
mysql -u... -p... -Ae"${SQLSTMT}"

The key here is to disable binary logging in its session. The Slave won't know what hit the Master because the Master will execute the DELETE and not record the DELETE.

Give it a Try !!!

Answer 2

The only way is for you to have another table with the same data and exclude that from replication in my.cnf.

MySQL's replication is going to copy and execute every statement you perform on the relevant tables and/or databases. So when you execute something on the master, the slave will also execute it given that it was targeted at a table on the do-replicate list.

Now, your solution would be to create table B and copy over all contents of table A. Add an additional field to table B called custom for example. Run a cronjob every X minutes/hours/days/months to:

1. Delete all rows in table B where custom = 0
2. Insert all rows from table A into table B: INSERT INTO b (SELECT * FROM a);
3. You can optionally use a date range for deleting and inserting new rows which will considerably reduce the amount of work needing to be done for this operation.
4. Run your queries on the master server and to table B, with custom = 1.

Hope this helps.