Mysql跨库主从热备失效问题

 

Mysql跨库主从热备失效问题

在InnoDB引擎下发现，Mysql的主从热备存在数据不一致的问题，一些数据没有成功同步到备机。在use databases后，更新的表必须是当前选择的database才同步。譬如连上Mysql服务后操作：

USE test2;

UPDATE client SET name='test' WHERE uid=1;

数据未能同步到备机，而使用use  test 后，才可以成功同步，如下方式：

USE test;

UPDATE client SET name='test' WHERE uid=1;

 

仔细看Mysql 手册，发现同步启动选项中还有玄机，只设置replicate-do-db 指定同步数据库还不够。是没有设置replicate-wild-do-table 导致的跨库同步问题。Mysql 默认是同步指定数据库下的更新操作，若要跨库操作更新同步，就必须指定replicate-wild-do-table 参数。下面是Mysql 手册中对replicate-do-db 和replicate-wild-do-table 启动选项的说明：

 

–replicate-do-db=db_name

告诉slave 只同步那些缺省数据库是 db_name ( 也就是用 USE 选中的) 的语句。想要指定更多的数据库，只需多次使用该选项，每次指定一个数据库。注意，类似 UPDATE some_db.some_table SET foo='bar' 这样的跨库操作语句以及没有选中数据库的操作都不会被同步。如果必须使用跨库操作，要确保使用MySQL 3.23.28 或更高，并且使用 –replicate-wild-do-table=db_name.% 选项。请仔细阅读最后面的注意事项。

下面是一个不能按照预期工作的例子：如果启动slave 时使用 –replicate-do-db=sales 选项，并且在master 上执行下列语句，那么这个 UPDATE 语句不会被同步：

USE prices;

UPDATE sales.january SET amount=amount+1000;

如果需要同步跨库操作，只需使用 –replicate-wild-do-table=db_name.% 选项 。这个" 只检查缺省数据库" 特性的主要原因是因为想要单从一个语句中判断是否要被同步比较困难( 例如，使用多表 DELETE 或者 UPDATE ，这就跨库了) 。不过想要检查是否是缺省数据库却很快。

 

–replicate-wild-do-table=db_name.tbl_name

限制slave 只同步那些匹配指定模式的数据表。模式中可以包含通配符 `%` 和 `_` ，它们的含义和 LIKE 模式一样。想要指定更多的数据表，只需多次使用该选项，每次指定一个数据表。请仔细阅读最后面的注意事项。

例如： –replicate-wild-do-table=foo%.bar% 会同步所有以 foo 开头的数据库下的以 bar 开头的数据表上的更新操作。

如果匹配模式是 % ，则匹配所有的表名，且应用到数据库级语句(CREATE DATABASE, DROP DATABASE, 和 ALTER DATABASE) 。例如，使用 –replicate-wild-do-table=foo%.% 选项的话，所有匹配 foo% 模式的数据库级操作都会被同步。

如果想要在数据库/ 表模式中包含原义通配符，需要用反斜杠来转义它们。例如，想要同步 my_own%db 数据库下的所有表，但是不想同步 my1ownAABCdb 数据库下的表，就需要转义字符 `_` ： –replicate-wild-do-table=my\_own\%db 。如果是在命令行中使用这个选项，就可能需要两个反斜杠来转义，这依赖于命令行解释器。例如，在 bash shell 下，就需要输入： –replicate-wild-do-table=my\\_own\\%db 。

 

还存在一些问题

不管有没有replicate-wild-do-table 选项，更新操作必须是mysql 连接已经有选择的数据库了才进行，譬如新建的mysql （新连接是没有默认选择的database 的）连接中执行：

UPDATE test.client SET name='test' WHERE uid=1;

这条更新无法同步到备机，必须在update 前use database 操作，该database 必须是replicate-wild-do-table 中指定的database 。

保险的解决方式 ：连接上mysql 后，调用mysql_select_db() 选择数据库，之后进行的更新操作就可以自动同步了。

 

SQL 模式匹配

SQL 的模式匹配允许你使用“_ ”匹配任何单个字符，而“% ”匹配任意数目字符( 包括零个字符) 。在 MySQL 中，SQL 的模式缺省是忽略大小写的。下面显示一些例子。注意在你使用SQL 模式时，你不能使用= 或!= ；而使用LIKE 或NOT LIKE 比较操作符。