What are your favorite MySQL replication filtering rules?
As I wrote a few days ago, I’m writing the replication chapter for the second edition of High Performance MySQL. I’m writing about replication filtering rules right now, and I thought it would be good to get input on this. If you have favorite replication filtering tricks you’d like to share, or tasks that always frustrate and/or confuse you, please post them in the comments. I’m making a section that shows how to accomplish common filtering and rewriting needs, such as preventing GRANT statements from replicating to the slaves.
Thanks very much! I hope the community involvement will make this book more useful for everyone.
–slave-skip-errors=all
I know most people don’t agree with this however.
Marc
20 Oct 07 at 9:36 pm
Well, in a book about how to make replication work right, we should probably avoid this setting and concentrate on helping solve whatever’s creating the need for it :-)
Xaprb
21 Oct 07 at 8:30 am
Yes, information on replication of permissions would be really useful.
Info about other config options in the mysql database (timezones etc) would also be helpful
Ethan
21 Oct 07 at 8:59 am
Ethan, do you mean timezones as they relate to replication, or just timezones in general?
Xaprb
21 Oct 07 at 9:22 am
Well, one trick is when you’re replicating only one database but want to use another without having to change replication, do everything using dot notation from the allowed db.
ie,
use alloweddb;
INSERT INTO different.tbl1 (col1, col2, col3) VALUES (val1,val2,val3);
But that’s of course a huge hack.
One thing that’s interesting that came up as a question at work the other day — if you do the following:
BEGIN;
INSERT INTO TimeTbl (beginTime) SELECT NOW();
— some long query here
INSERT INTO TimeTbl (endTime) SELECT NOW();
COMMIT;
does it replicate properly? I’d like to see things like highlighting where replication falls apart (like with SET statements, user variables, etc….and maybe how one can get around those).
Sheeri
21 Oct 07 at 3:29 pm
Good suggestions, thanks. I’m pretty sure the elapsed-time and user-variable problems are solved by meta-data added to the binlog events, but I’ll check.
An interesting question: do user-variables cause issues like temp tables in case the slave crashes and restarts? I think they don’t but I’m not sure. I’ll check that too.
Xaprb
21 Oct 07 at 3:33 pm