Temporal tables and slave replication

MySQL & MariaDB Other MySQL® Questions
1

.Hello,

I’m using a master based in Percona 5.1 and two slaves.

One of them use percona 5.1 too and replication has no problem, but in the other one which uses percona 5.5 has a problem during temp tables replication.

In percona 5.1 using:

binlog_format = ROW

there’s no problem, but in percona 5.5 I must set it to:

binlog_format = MIXED

In my.cnf I setted two innodb replication options for the slave:

replicate-ignore-table = powersystem.temp%
replicate-wild-do-table = powersystem.%

and I triyed so with:

replicate-wild-ignore-table = powersystem.temp%
replicate-wild-do-table = powersystem.%

but both ignore that options and doesn’t apply, and the slave try temporal tables that doesn’t exist.

I don’t know what I’m doing wrong. If you can help me. There’s an other way to sync an slave avoiding temp tables?

Thanks!

2

Hi,

When you will use replicate-do-table with replicate-ignore-table, mysql will always first read replicate-do-table and it will execute the statement as per that. Check below link for how exactly it works on slave side.
http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db- options.html

So In your case, it looks like first its checking replicate-do-table and its getting green signal for powerststem DB and executing that statement without checking replicate-ignore-table option.

Try below replication options for the slave and I hope it will work.

replicate-wild-ignore-table = powersystem.temp%
replicate-do-db = powersystem