Temporal tables and slave replication

  • Filter
  • Time
  • Show
Clear All
new posts

  • Temporal tables and slave replication


    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?


  • #2

    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