Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    24

    MySql Replication Query

    Hi,

    This is my first in this forum...so please take it easy if i made any mistake...thanks

    So here is my case::

    In our production system, One MASTER database and Two SLAVE databases...now i want to use one of my slave database as BACKUP database...need to mention that sometimes I delete some data from master database(company requirement) which replicate to two slave databases...

    now my query is:: Is there any possible way to put some kind of condition on Master/slave so that my 2nd slave database will not delete any data....!!

    any kind of suggestion will also be appreciated.....


    regards

    --rush
    Last edited by rush; 12-24-12 at 00:58. Reason: add text

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    This is not possible. The problem with replication is that you either replicate all transactions or none, by ignoring databases or individual tables. What you could do is introduce a trigger on deletes on the slave database which then inserts into an archive table those entries. That way you get to maintain all the deleted records but in a separate table.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  3. #3
    Join Date
    Dec 2012
    Posts
    24
    Hi Ronan,

    Thanks for your reply...now can you please give some kind of clue or any link which will help me to create that trigger...in addition, i have replicate one database and my database consist 72 tables..i want to put trigger for all tables....

    Mary Christmas...

    regards,

    --rush

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    860
    Hi, unfortunately you would need to add in 72 triggers on the delete action. Here is an example of creating a trigger MySQL – Triggers IT Integrated Business Solutions
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com

  5. #5
    Join Date
    Dec 2012
    Posts
    24
    Hi Ronan,

    job done....

    and

    Thanks for your help and suggestion...!!

    regards

    --rush
    Last edited by rush; 12-27-12 at 01:23.

  6. #6
    Join Date
    Dec 2012
    Posts
    24
    Hi all,

    I am facing a little problem on mysql replication...

    In my test system,

    master-slave replication(it replicate only one table putting replicate-do-table value in slave my.cnf)

    In Master database,

    prod_table(where i want to insert data from other source)
    trigger after insert(this trigger will insert data to replicate table when data inserted into prod_table)
    replicate_table(after data inserted, data will replicate to slave table)

    now the problem is,

    when i manually insert data into replicate_table, it replicate to slave database but when i insert data into prod_table, in insert data using trigger into replicate_table but does not replicate to slave database....

    can anyone tell me where i am doing wrong???

    regards

    --rush
    Last edited by rush; 01-01-13 at 01:17. Reason: spell check

  7. #7
    Join Date
    Jan 2013
    Posts
    1
    hi thanks for discussion................

  8. #8
    Join Date
    Dec 2012
    Posts
    24
    I found the answer from mysql reference(FAQ triggers):

    B.5.12: Do triggers work with replication?

    Triggers and replication in MySQL 5.0 work in the same way as in most other database systems: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.

    For more information, go to this link
    'http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-B-5-1-12'

    regards

    --rush

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •