mysql 主从同步的问题，请教

Question

最近在做mysql的主从同步，遇见一个很麻烦的问题，有些表的更新操作涉及到了临时表，而且更新操作调用频繁，主数据库会出现同时有多个同名临时表的情况，这在主数据库没有什么问题，因为在不同的线程中执行没有干涉，但是这部分更新同步到从数据库时就会报临时表已存在之类的错误使同步中断，请问这种情况该如何处理？

Answer 1

临时表在复制中是个很难处理的问题。高性能MYSQL里面建议用实表，用完删除。

Answer 2

我把create临时表dorp临时表都写在一个事务内都无法避免，mysql的同步太不省心了，不应该呀，看来就为同步就要弃用临时表，稍微搞复杂点怎么可能

Answer 3

用实体表用完就删实际是一回事,因为是并发引起的,除非每次表名不同才可能避免,如果表名能不同临时表和实体表在这个问题上就没有区别,mysql有表名运行时产生的机制吗?如果是产生一条sql字串再执行就算了,这样访问临时表的代码也要大大的变化就没意义了.

Answer 4

你可以根据每个连接的IP进行命名临时表，
SELECT CURRENT_USER();得到IP，然后临时表的名称就肯定不会重复了。

Answer 5

请教 zuoxingyu临时表的名字可以使用变量吗?我现在只知道组织一个SQL字串并执行的方法,如果只创建临时表还是可以的,如果引用临时表部分都用这种办法,那复杂程度过高,那我宁可不用这种办法喽

Answer 6

可以考虑更改复制类型，改为Row_Base，这种模式下会忽略主机对临时表的操作(当然也就不存在楼主所说的错误了)。当然，这种模式相对于Statement_Base有一些优缺点，请楼主自行权衡。

Answer 7

to coleling这个怎么改？我就觉得应该有这个嘛，只是一直不知道有，基于序列化查询的同步之外还有基于记录变化的模式吗？

Answer 8

to zuoxingyu试了一下确实可以用变量作为表名，这个比较救命，不过变量似乎不能作为limit后面的参数是吧？

Answer 9

不能，必须是常数。如果是变量的话，则一般是通过CONCAT生成相对的SQL语句的字符串。然后用PREPARE去执行。

Answer 10

这个在5.1.5以上版本才有。有两种修改方式：1、在启动配置文件中增加/修改binlog-format=row 
2、运行时修改
  SET GLOBAL binlog_format = 'ROW';
  或：
  SET GLOBAL binlog_format = 2;

Answer 11

to lanbaibai临时表是用于后面更新用的忽略了一样有问题，除非临时表只用于查询，顺便问一下怎么设置忽略？从什么版本开始支持？to coleling你提供的方法太好了，但是我用的版本还没有支持，我是用的5.0的，暂时没法用，麻烦再问一下，row_base方式除了删除大表时要将被删除的记录的主键序列化过来显得费劲一点之外还有没有明显的代价？库结构的变化应该还是会同步的是吧？to zuoxingyu您能不能告诉我一下怎么用变量的值作为表名的办法，我用变量作为表名，后来发现只是用变量的名创建了表，并没有用变量的值作为表名

Answer 12

《高性能MYSQL》里有一节“丢失的临时表”，里面有很详细的讲解。

Answer 13

to zuoxingyu第1章 MySQL架构
第2章 寻找瓶颈
第3章 架构优化和索引
第4章 查询性能优化
第5章 MySQL高级特性
第6章 优化服务器设置
第7章 操作系统和硬件优化
第8章 复制
第9章 伸缩性与高可用性
第10章 应用层面的优化
第11章 备份与还原
第12章 安全
第13章 MySQL服务器的状态
第14章 用于高性能MySQL的工具在哪一章?我很急

Answer 14

这里说错了，不是用IP，而是要用连接ID，mysql> select CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|         3900988 | 
+-----------------+
1 row in set (0.00 sec)
 
mysql> 
同一个IP可以产生很多链接，但是链接ID肯定是唯一的。

Answer 15

to zuoxingyu我自己维护了一个计数，每次得到一个不同的临时表名并不是问题，也不一定非要调CONNECTION_ID()作为临时表名活的那部分，问题主要在如果使用表名上，我希望存储过程里创建和引用临时表时表名可以是活的，迄今为止只知道concat一个SQL语句并执行的这个办法，这个办法代价太大我不准备用，您知道有什么其他更简单的办法吗？

Answer 16

在每个链接里面拼出创建临时表的语句，并操作临时表，这个不复杂啊。删除“实体临时表”，可以用外面的程序来做，SHOW PROCESSLIST得到链接的ID，已经不在这个ID集合里的临时表都可以删除。

Answer 17

to zuoxingyu创建表，如果单直接create应该还能忍受，如果是create table select...就麻烦，还有引用临时表就头皮发麻，所以这种不敢采用

Answer 18

在从机上面设置
● Replicate_Do_DB：设定需要复制的数据库（Schema），多个DB 用逗号（“,”）分隔；
● Replicate_Ignore_DB：设定可以忽略的数据库（Schema）；
● Replicate_Do_Table：设定需要复制的Table；
● Replicate_Ignore_Table：设定可以忽略的Table；
● Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以带通配符来进行设置；
● Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可带通配符设置；我的mysql5.1可以这样设置，5.0不知道有无。