0

I need to make a master-slave replication for some joomla portals. However, high traffic means that a lot of inserts, deletes and updates happen on the %_session tables, something that isn't really necessary to replicate.

So, I've added the following line to my master's my.cnf file:

replicate-wild-ignore-table=%.%\_session

I've stopped and started the master server, but when I check the bin.logs, I can see that all operations with _session tables are still recorded and prepared to be sent to the slave.

So my question is, how do I force the master to ignore any and all operations on tables that end in _session?

0

first of all, the replication variables have effect on the slave only when activated on a replicated "slave" server. you need to understand that the filtering rules on master differ from the ones on slave. on master you can choose only to log a whole db or not. on slave you have more options. here is described http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html and here: http://dev.mysql.com/doc/refman/5.5/en/replication-rules-table-options.html I think, you want to skip replicating a set of tables with a given pattern on slave. So, the variables must be configured on the slave. Change the configuration file on the salve and add the db_name instead of % for db part.

replicate-wild-ignore-table=db_name.%_session line

After that, validate that the variable is activated (is shown in the output) by running:

show slave status \G

once the filter is activated, slave server will skip all the statements which meet the given pattern. but before doing any change read how MySQL handles replication rules because you can skip important tables if you don't understand the rules.

  • The slave part is actually working, but I wanted to do something similar on the master, given how fast the binlogs get full with unnecessary operations, filling disk space. I guess that's not possible then. – Arthur Jan 17 '17 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.