How to exclude some tables from RDS Mysql replication

Question

i have a cluster structure on RDS (master/slave)...and all the temporary tables created on the master are replicated on the slave. I don't want that....

I want that statements like:

create temporary table tmp as (select * from tb);

...do not gets replicated.

I would want that NO temporary table were replicated. I realize now that i must user the option "replicate-ignore-table=name" ...

But any one knows how t do that on RDS? I think i have to user the client tool right?

Someone has an example?

Pyrex

there any option on "parameter group" that allow me to do that? – Pyrex

Answer 1

Indeed, RDS contains a section call Parameter Groups.

(UPDATE)

For RDS Mysql, there is no such options. Although SHOW SLAVE STATUS\G or SHOW MASTER STATUS\G will show those parameter.

For Mariadb 10, you can select those allowed custom RDS parameter e.g. replicate_ignore_db and create a parameter group, then attach to RDS instance DB Parameter Group.

Foison

no, RDS parameter groups do not have that option.... i opened a ticket on amazon, and they said that this is an old request that are not available yet.... – Pyrex

@Pyrex Just notice it is only available for Mariadb10, which I assume it mysql should have it, but not. – Foison

Answer 2

You can also switch your binlog format to ROW instead of STATEMENT/MIXED.

This because temporary tables in ROW binlog format are not replicated.

Source: https://dev.mysql.com/doc/refman/5.6/en/replication-features-temptables.html

Edit: In RDS MariaDB only, you can use the following options:

• replicate_ignore_db
• replicate_ignore_table
• replicate_wild_ignore_table

Source: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MariaDB.Parameters.html

Suggestible

This answer is incorrect, or outdated two months, later. One is unable to modify these parameters on the parameter group editor now. – Lossa