RE: Can the mysql replication limited to one database
Here's the use case that screws up a lot of people: USE db_that_is_not_replicated; INSERT INTO db_that_IS_replicate.tbl ...; Surprise! That INSERT is not replicated. Workaround: *wild will notice it. The other way around, the INSERT will be replicated, then hang replication because the table won't be on the Slave. From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Wednesday, May 02, 2012 10:25 AM To: Rick James Cc: Brown, Charles; mysql@lists.mysql.com Subject: Re: Can the mysql replication limited to one database I recommend to use the 'wild' modifier, if you have a default db and you specify the schema in the query like update schemanotreplicated.mytable. you will miss that. Claudio 2012/5/2 Rick James mailto:rja...@yahoo-inc.com>> Yes, doable. In my.cnf on master: Binlog-do-database = dbname1 Binlog-do-database = dbname2 Would replicate those two dbs only. There are many other combinations using binlog/replicate-do/ignore-db/table/wild. See the manual on replication, and especially the flowchart on how those interact (sometimes in unexpected ways). > -Original Message- > From: Brown, Charles [mailto:cbr...@bmi.com<mailto:cbr...@bmi.com>] > Sent: Wednesday, May 02, 2012 8:44 AM > To: Rick James > Cc: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com> > Subject: Can the mysql replication limited to one database > > Hi Gurus, > > Can the mysql replication limited to selected schema or database? I've > been asked to set up mysql for only 1 out 5 databases exist in > production. Please advise if this is doable > > Thanks > > > This message is intended only for the use of the Addressee and may > contain information that is PRIVILEGED and CONFIDENTIAL. > > If you are not the intended recipient, you are hereby notified that any > dissemination of this communication is strictly prohibited. > > If you have received this communication in error, please erase all > copies of the message and its attachments and notify us immediately. > > Thank you. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
RE: Can the mysql replication limited to one database
Doing both is redundant. Doing binlog (on the Master) is more efficient because it eliminates sending the other dbs. > -Original Message- > From: a.sm...@ukgrid.net [mailto:a.sm...@ukgrid.net] > Sent: Wednesday, May 02, 2012 8:50 AM > To: mysql@lists.mysql.com > Subject: Re: Can the mysql replication limited to one database > > Quoting "Brown, Charles" : > > > > Can the mysql replication limited to selected schema or database? > > Hi, yes it can. On the master side you control what is written to the > binlog with my.cnf entries for each DB like: > > binlog-do-db=DB1 > binlog-do-db=DB2 > > And on the slave side you use entries like: > > replicate-do-db=DB1 > replicate-do-db=DB2 > > thanks Andy. > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Can the mysql replication limited to one database
Nothing in the slave's my.cnf. On the Master, binlog-do/ignore-* filters before leaving the Master. On the Slave, replicate-do/ignore-* (if used) would filter after getting to the Slave. That is, you _could_ do the replicate-* instead of the binlog-*, but that would be inefficient. Doing both is redundant. > -Original Message- > From: Brown, Charles [mailto:cbr...@bmi.com] > Sent: Wednesday, May 02, 2012 11:15 AM > To: Rick James > Cc: mysql@lists.mysql.com > Subject: RE: Can the mysql replication limited to one database > > Hello Rick, > What goes into your my.cnf slave ? > Given dbname1 and dbname2 > > > > > -Original Message- > From: Rick James [mailto:rja...@yahoo-inc.com] > Sent: Wednesday, May 02, 2012 12:05 PM > To: Brown, Charles > Cc: mysql@lists.mysql.com > Subject: RE: Can the mysql replication limited to one database > > Yes, doable. In my.cnf on master: > Binlog-do-database = dbname1 > Binlog-do-database = dbname2 > Would replicate those two dbs only. > There are many other combinations using binlog/replicate-do/ignore- > db/table/wild. See the manual on replication, and especially the > flowchart on how those interact (sometimes in unexpected ways). > > > -Original Message- > > From: Brown, Charles [mailto:cbr...@bmi.com] > > Sent: Wednesday, May 02, 2012 8:44 AM > > To: Rick James > > Cc: mysql@lists.mysql.com > > Subject: Can the mysql replication limited to one database > > > > Hi Gurus, > > > > Can the mysql replication limited to selected schema or database? > I've > > been asked to set up mysql for only 1 out 5 databases exist in > > production. Please advise if this is doable > > > > Thanks > > > > > > This message is intended only for the use of the Addressee and may > > contain information that is PRIVILEGED and CONFIDENTIAL. > > > > If you are not the intended recipient, you are hereby notified that > > any dissemination of this communication is strictly prohibited. > > > > If you have received this communication in error, please erase all > > copies of the message and its attachments and notify us immediately. > > > > Thank you. > > > > This message is intended only for the use of the Addressee and may > contain information that is PRIVILEGED and CONFIDENTIAL. > > If you are not the intended recipient, you are hereby notified that any > dissemination of this communication is strictly prohibited. > > If you have received this communication in error, please erase all > copies of the message and its attachments and notify us immediately. > > Thank you. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Can the mysql replication limited to one database
Hello Rick, What goes into your my.cnf slave ? Given dbname1 and dbname2 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Wednesday, May 02, 2012 12:05 PM To: Brown, Charles Cc: mysql@lists.mysql.com Subject: RE: Can the mysql replication limited to one database Yes, doable. In my.cnf on master: Binlog-do-database = dbname1 Binlog-do-database = dbname2 Would replicate those two dbs only. There are many other combinations using binlog/replicate-do/ignore-db/table/wild. See the manual on replication, and especially the flowchart on how those interact (sometimes in unexpected ways). > -Original Message- > From: Brown, Charles [mailto:cbr...@bmi.com] > Sent: Wednesday, May 02, 2012 8:44 AM > To: Rick James > Cc: mysql@lists.mysql.com > Subject: Can the mysql replication limited to one database > > Hi Gurus, > > Can the mysql replication limited to selected schema or database? I've > been asked to set up mysql for only 1 out 5 databases exist in > production. Please advise if this is doable > > Thanks > > > This message is intended only for the use of the Addressee and may > contain information that is PRIVILEGED and CONFIDENTIAL. > > If you are not the intended recipient, you are hereby notified that > any dissemination of this communication is strictly prohibited. > > If you have received this communication in error, please erase all > copies of the message and its attachments and notify us immediately. > > Thank you. > This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can the mysql replication limited to one database
RBR solves the default db case.. S On Wed, May 2, 2012 at 1:25 PM, Claudio Nanni wrote: > I recommend to use the 'wild' modifier, if you have a default db and you > specify the schema in the query like update > schemanotreplicated.mytable. you will miss that. > > Claudio > > 2012/5/2 Rick James > > > Yes, doable. In my.cnf on master: > > Binlog-do-database = dbname1 > > Binlog-do-database = dbname2 > > Would replicate those two dbs only. > > There are many other combinations using > > binlog/replicate-do/ignore-db/table/wild. See the manual on replication, > > and especially the flowchart on how those interact (sometimes in > unexpected > > ways). > > > > > -Original Message- > > > From: Brown, Charles [mailto:cbr...@bmi.com] > > > Sent: Wednesday, May 02, 2012 8:44 AM > > > To: Rick James > > > Cc: mysql@lists.mysql.com > > > Subject: Can the mysql replication limited to one database > > > > > > Hi Gurus, > > > > > > Can the mysql replication limited to selected schema or database? I've > > > been asked to set up mysql for only 1 out 5 databases exist in > > > production. Please advise if this is doable > > > > > > Thanks > > > > > > > > > This message is intended only for the use of the Addressee and may > > > contain information that is PRIVILEGED and CONFIDENTIAL. > > > > > > If you are not the intended recipient, you are hereby notified that any > > > dissemination of this communication is strictly prohibited. > > > > > > If you have received this communication in error, please erase all > > > copies of the message and its attachments and notify us immediately. > > > > > > Thank you. > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql > > > > > > > -- > Claudio > -- -- Get an insider’s guide to ODA performance. Join Gwen Shapira, Oracle Ace Director and Senior Pythian Consultant, on May 10th as she presents her findings in an informative webinar. Register today, bit.ly/odaperfwebinar
Re: Can the mysql replication limited to one database
I recommend to use the 'wild' modifier, if you have a default db and you specify the schema in the query like update schemanotreplicated.mytable. you will miss that. Claudio 2012/5/2 Rick James > Yes, doable. In my.cnf on master: > Binlog-do-database = dbname1 > Binlog-do-database = dbname2 > Would replicate those two dbs only. > There are many other combinations using > binlog/replicate-do/ignore-db/table/wild. See the manual on replication, > and especially the flowchart on how those interact (sometimes in unexpected > ways). > > > -Original Message- > > From: Brown, Charles [mailto:cbr...@bmi.com] > > Sent: Wednesday, May 02, 2012 8:44 AM > > To: Rick James > > Cc: mysql@lists.mysql.com > > Subject: Can the mysql replication limited to one database > > > > Hi Gurus, > > > > Can the mysql replication limited to selected schema or database? I've > > been asked to set up mysql for only 1 out 5 databases exist in > > production. Please advise if this is doable > > > > Thanks > > > > > > This message is intended only for the use of the Addressee and may > > contain information that is PRIVILEGED and CONFIDENTIAL. > > > > If you are not the intended recipient, you are hereby notified that any > > dissemination of this communication is strictly prohibited. > > > > If you have received this communication in error, please erase all > > copies of the message and its attachments and notify us immediately. > > > > Thank you. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Claudio
RE: Can the mysql replication limited to one database
Yes, doable. In my.cnf on master: Binlog-do-database = dbname1 Binlog-do-database = dbname2 Would replicate those two dbs only. There are many other combinations using binlog/replicate-do/ignore-db/table/wild. See the manual on replication, and especially the flowchart on how those interact (sometimes in unexpected ways). > -Original Message- > From: Brown, Charles [mailto:cbr...@bmi.com] > Sent: Wednesday, May 02, 2012 8:44 AM > To: Rick James > Cc: mysql@lists.mysql.com > Subject: Can the mysql replication limited to one database > > Hi Gurus, > > Can the mysql replication limited to selected schema or database? I've > been asked to set up mysql for only 1 out 5 databases exist in > production. Please advise if this is doable > > Thanks > > > This message is intended only for the use of the Addressee and may > contain information that is PRIVILEGED and CONFIDENTIAL. > > If you are not the intended recipient, you are hereby notified that any > dissemination of this communication is strictly prohibited. > > If you have received this communication in error, please erase all > copies of the message and its attachments and notify us immediately. > > Thank you. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can the mysql replication limited to one database
Quoting "Brown, Charles" : Can the mysql replication limited to selected schema or database? Hi, yes it can. On the master side you control what is written to the binlog with my.cnf entries for each DB like: binlog-do-db=DB1 binlog-do-db=DB2 And on the slave side you use entries like: replicate-do-db=DB1 replicate-do-db=DB2 thanks Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
