-复制-狂野-忽略-桌子不起作用？

Question

根据有关复制和临时表的文档，我将--replicate-wild-ignore-table选项设置为%.norep%，以防止复制临时表：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000861
          Read_Master_Log_Pos: 332610533
               Relay_Log_File: mysqld-relay-bin.001578
                Relay_Log_Pos: 9061317
        Relay_Master_Log_File: mysql-bin.000861
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: %.norep%
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 330271548
              Relay_Log_Space: 13544924166
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'reportingdb.norep_cp_banner_tvc_temp_' doesn't exist' on query. Default database: 'reportingdb'. Query: 'UPDATE `ox_banners` A
    SET A.`status` =1
    WHERE A.`bannerid` IN (SELECT bannerid FROM norep_cp_banner_tvc_temp_)'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4

但是，从上面的show slave status\G输出中可以看到，这个语句仍然被复制到Slave中，并且它会导致错误。

我的查询有什么问题吗？它是否只对主表有影响(在本例中指ox_banners )？

回复@Aaron：

这可能有点令人吃惊，但此错误证明您的复制筛选器正在工作。

+1。

第一个要问自己的问题是，为什么我要过滤复制？这样做很少有好的理由，而且它通常会带来更多的问题。

由于临时表的问题：如何使MySQL复制可靠？

您肯定可以通过使用基于行的复制 (RBR)来解决这个问题，而不是使用基于语句的复制(SBR)。RBR复制对数据的更改，而不是语句。

我将考虑从基于混合的到基于行的转换。

Answer 1

这可能有点令人吃惊，但此错误证明您的复制筛选器正在工作。停止工作的语句是对另一个表的更新，但是更新使用表中的数据，您已经说过不要在SELECT子句中复制这些数据。注意，错误是表norep_cp_banner_tvc_temp_不存在。复制过滤器只适用于受影响的表-它不仅仅是一个直接的模式匹配。

复制过滤器有点复杂--我建议您阅读服务器如何评估复制筛选规则和表级复制选项的评估。

第一个要问自己的问题是，为什么我要过滤复制？这样做很少有好的理由，而且它通常会带来更多的问题。

您肯定可以通过使用基于行的复制 (RBR)来解决这个问题，而不是使用基于语句的复制(SBR)。RBR复制对数据的更改，而不是语句。

我还建议您将更新重构为联接。IN (SELECT...)在MySQL pre-5.6中的优化效果很差，而且性能要好得多：

UPDATE `ox_banners` A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1;

解决此问题的另一种方法是更新norep中的虚拟字段。就像这样：

UPDATE ox_banners A
JOIN norep_cp_banner_tvc_temp_ temp USING (bannerid)
SET A.`status` = 1, temp.dummy = 1;

这可能会欺骗MySQL过滤这条语句，但至少可以这么说。RBR很可能是你最好的选择。

更新2012-07-24

您已经提到，这样做是为了解决用于复制的临时表的一些问题。把它们完全过滤掉是处理这个问题的糟糕方法。如果你担心的话，可以考虑这样做。

创建一个单独的数据库来保存“临时”表。

mysql> create database temptables;
Query OK, 1 row affected (0.08 sec)

mysql> use temptables;
Database changed

通过使用CONNECTION_ID命名这些表..。

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              52 |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE 52_my_temp ( foo int ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

按常规方式使用该表，然后DROP表

mysql> DROP TABLE IF EXISTS 52_my_temp;
Query OK, 0 rows affected (0.05 sec)

最后，创建一个经常清理表的进程，以防DROP表没有被执行。如下所示(这里的Ruby后缀语法)：

# get a list of all the tables in the temp database
query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TABLE_SCHEMA=?"
db[query, 'temptables'].each do |row|
  # get the connection id
  id = row[:TABLE_NAME].split('_')[0].to_i
  # is the connection still active?
  query = "SELECT COUNT(*) AS cnt FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = ?"
  if db[query, id].first[:cnt] < 1
    db["DROP TABLE #{row[:TABLE_NAME]}"]
  end
end

Answer 2

• Why replication is bad for temporary tables**:** http://scale-out-blog.blogspot.in/2012/04/replication-is-bad-for-mysql-temp.html博客
• 使用临时表时安全的从站关机：

临时表被复制，除非您停止从服务器(不仅仅是从线程)，而且您已经复制了打开以用于尚未在从服务器上执行的更新中使用的临时表。如果停止从服务器，则当从服务器重新启动时，这些更新所需的临时表不再可用。若要避免此问题，请在奴隶打开临时表时不要关闭它。欲了解更多信息，请参阅：

http://dev.mysql.com/doc/refman/5.1/en/replication-features-temptables.html

• 基于行的日志记录、基于行的复制和临时表：

使用基于行的格式时不会复制临时表。当混合格式生效时，涉及临时表的“安全”语句将使用基于语句的格式记录。有关更多信息，请参阅

http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html

使用基于行的格式时不复制临时表，因为没有必要。此外，由于临时表只能从创建它们的线程中读取，所以即使使用基于语句的格式，也很少从复制它们中获得任何好处。