If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > MySQL > MySql Replication Query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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-23-12 at 23:58. Reason: add text
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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 00:23.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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 00:17. Reason: spell check
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 1
hi thanks for discussion................
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On