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. Could I and how?

The idea is to have some records that are archived on the slave. 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.

2 Answers 2


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="${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 !!!

  • 3
    +1 sorry for the couple years of delay in selecting this as an answer, I didn't see this until now :).
    – Neo
    Commented Nov 1, 2014 at 0:07

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.