0

I have a master-slave replication for a schema products which is being replicated with binlog_do_db = products.

one of the the tables in the master schema is being archived by calling a stored procedure which is in schema products_archive.The data is moved to a different schema on the same database from products to products_archive.

START TRANSACTION;
        INSERT INTO products_archive.dial
        SELECT * FROM products.dial
        WHERE products.dial.dial_datestamp <= DATE(NOW() - INTERVAL 30 DAY);
        DELETE FROM products.dial
        WHERE producst.dial.dial_datestamp <= DATE(NOw() - INTERVAL 30 DAY);
COMMIT;

But the slave has only one schema products which is not being archived. Since stored procedure is on the schema which is not being replicated the master should be archived and the slave shouldn't.

But what instead happened is the master archive was successful and the same data got deleted on the slave.

How did the data get purged on the slave as the schema products_archive is not added to list of replicated schemas.

  • Back up a step and state what you want. product_archive lives only on Master? All rows exist in one table or the other on both? Or do you intend to remove 'old' rows from one of the servers? – Rick James Jan 3 '18 at 14:31
  • product_archive schema is on master only.All the rows exist on both master and slave table but i want to archive only master table and not slave.The stored procedure is put in the non-replicated schema products_archive to avoid archiving happening on slave. – shadow0359 Jan 4 '18 at 5:32
0

It seems what is happening is that your stored procedure deletes rows from products.dial, and these DELETE statements are simply replicated to the slave. It doesn't matter that the stored procedure itself is in a non-replicated schema.

  • Is this how its supposed to work?Is there a way to avoid the delete happening on the slave? – shadow0359 Jan 4 '18 at 5:36
  • Yep, this is how replication works. It's possible to avoid the DELETE statements taking effect on the slave, see e.g. stackoverflow.com/questions/15388907/… – dbdemon Jan 4 '18 at 14:20
  • You are walking into a mine field. – Rick James Jan 4 '18 at 21:52
  • Agreed, this is a minefield! I suppose the main thing to avoid is reusing deleted primary key values. – dbdemon Jan 4 '18 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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