MySql Replication Query/Suggestion

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....!!

solution/suggestion pls.....

regards

--amrush

 

You can back up your master database, which means that data deleted from it won't be backed up, or you can update the backup with any changes made to data that still exists in the master.

But you can't back up with the backup not being the same as what's being backed up - that's like having a yellow flower that's red. A backup is the same as the thing that's being backed up - a "backup" that's different than what's being backed up isn't a backup.

 

Hi Rukbat,

Thanks for your reply....

And can u suggest me anything about my query::

"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....!!"

regards,

--amrush

 

hi all,

so far what i have found is, which i have asked is not possible....what i have to do is to put some kind of trigger on delete in my slave database, so that before delete it will copy data into another table. I have replicate one database and my database consist 72 tables. I want put trigger for all tables...

can anybody help me with that....

regards

--amrush

 

I have used this simple trigger...


create trigger before_delete
before delete on table_name
for each row begin
insert into new_tablename(id, name) values (old.id, old.name);
end;


and this trigger will work on single table..

 

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

--amrush

 

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

--amrush