mysql同步之3

核心提示：手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，出处：http://iMySQL(和PHP搭配之最佳组合).cn，mysql同步之3，转载请注明译者和出处，并且不能用于商业用途，因此在使用 RAND() 更新时采用 RAND(some_non_rand_expr) 格式，例如，违者必究，6.5 不

　　手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，并且不能用于商业用途，违者必究。
6.5 不同MySQL(和PHP搭配之最佳组合)版本之间的同步兼容性

最早的二进制格式是在MySQL(和PHP搭配之最佳组合) 3.23中开发出来的。在MySQL(和PHP搭配之最佳组合) 4.0中改进了，MySQL(和PHP搭配之最佳组合) 5.0又改进了。在配置同步时需要升级服务器的话，它们之间的因果关系在"6.6 Upgrading a Replication Setup"中描述了。

如果只关心同步，任何MySQL(和PHP搭配之最佳组合) 4.1.x版本和MySQL(和PHP搭配之最佳组合) 4.0.x是一样的，因为它们都使用相同格式的二进制日志。所以，这些版本是互相兼容的，它们之间可以无缝地运行同步。一个例外的情况是，MySQL(和PHP搭配之最佳组合) 4.0.0到4.0.2由于开发的较早，无法和后来的版本互相兼容，所以不要使用它们(它们是4.0版本的alpha系列。它们之间的兼容性在发布包的手册中均有相关文档)。
下表展示了不同版本的MySQL(和PHP搭配之最佳组合)之间的master/slave同步兼容性。
Master Master Master
3.23.33 and up 4.0.3 and up or any 4.1.x 5.0.0
Slave 3.23.33 and up yes no no
Slave 4.0.3 and up yes yes no
Slave 5.0.0 yes yes yes
一个通常的规则是，我们建议使用最近的MySQL(和PHP搭配之最佳组合)版本，因为同步兼容性一直在改善。我们也建议master和slave都使用同一个版本。
6.6 升级同步
如果升级服务器时涉及到配置同步，升级设置的步骤跟当前版本以及升级后的版本不同而异。
6.6.1 升级同步到 4.0 或 4.1

本节适用于从MySQL(和PHP搭配之最佳组合) 3.23升级到4.0或者4.1的情况。4.0的服务器必须是4.0.3或者更新，"6.5 Replication Compatibility Between MySQL(和PHP搭配之最佳组合) Versions"中提到了。

把master从MySQL(和PHP搭配之最佳组合) 3.23升级到4.0或4.1时，首先要确认这个master的所有slave都已经是4.0或4.1了，否则的话，要先升级slave：挨个关闭，升级，重启，重启同步等。
通过以下步骤可以实现安全地升级，假定master要升级到3.23，而slave已经是4.0或4.1了。注意，master升级后，不要重启使用任何旧的二进制日志的同步，因为它会干扰到4.0或4.1 slave的同步。

1. 在master上执行 FLUSH TABLES WITH READ LOCK 语句，阻止所有的更新。
2. 等到所有的slave都跟上了master的数据更新。在master上执行 SHOW MASTER STATUS 语句取得二进制日志以及偏移位置。然后，再slave用这些值执行 SELECT MASTER_POS_WAIT() 语句，它会阻止slave上的同步且返回它已经同步的偏移位置。然后在slave上执行 STOP SLAVE 语句。
3. 关闭master，将它升级到MySQL(和PHP搭配之最佳组合) 4.0或4.1。
4. 重启master，记下它的新的二进制文件名。可以在master上执行 SHOW MASTER STATUS 语句来取得这些信息。然后在每个slave上都执行如下语句：

MySQL(和PHP搭配之最佳组合)> CHANGE MASTER TO MASTER_LOG_FILE='binary_log_name',
-> MASTER_LOG_POS=4;
MySQL(和PHP搭配之最佳组合)> START SLAVE;

6.6.2 升级同步到 5.0

本节适用于从MySQL(和PHP搭配之最佳组合) 3.23，4.0或4.1升级到5.0的情况。4.0的服务器必须是4.0.3或者更新，"6.5 Replication Compatibility Between MySQL(和PHP搭配之最佳组合) Versions"中提到了。

首先，注意到MySQL(和PHP搭配之最佳组合) 5.0还是alpha发布系列。它在各方面都比旧版本好(更容易升级一些同步中重要的会话变量，例如 sql_mode；详情请看"C.1.3 Changes in release 5.0.0 (22 Dec 2003: Alpha")。不过，它还没经过广泛测试。由于是alpha版本，我们不建议用于任何生产环境(现在已经可以用于生产了，译者注)。

把master从MySQL(和PHP搭配之最佳组合) 3.23，4.0或4.1升级到5.0.0时，首先要确认这个master的所有slave都已经是5.0.0了，否则的话，要先升级slave：挨个关闭，升级，重启，重启同步等。5.0.0的slave可以读取升级前写入的执行语句的中继日志。升级完后的slave创建的中继日志就是5.0格式了。

当所有的slave都升级完了，关闭master，升级到5.0.0，然后重启。5.0.0的master也可以读取旧格式的二进制日志。slave能识别旧的格式并且合理处理它们。master上新建的二进制日志都是5.0.0格式的。slave也能识别这格式。

换言之，在升级到5.0.0时无需特殊的规定，除非在升级master到5.0.0之前slave必须使用旧版本的。注意，把5.0.0降级到旧版本中不能自动地做了：必须确保所有的5.0.0格式二进制日志和中继日志都已经处理完了，然后才能把它们删除完成降级。
6.7 同步特性及已知问题

以下列出了同步支持什么，不支持什么。附加的 InnoDB 特殊相关的信息以及同步请看"16.7.5 InnoDB and MySQL(和PHP搭配之最佳组合) Replication"。

* AUTO_INCREMENT, LAST_INSERT_ID(), 和 TIMESTAMP 的值都能被正常同步。
* USER(), UUID(), 和 LOAD_FILE() 函数都完完全全地同步到slave，因此可能不大可靠。MySQL(和PHP搭配之最佳组合) 4.1.1以前的版本中的 CONNECTION_ID() 函数也是如此。从MySQL(和PHP搭配之最佳组合) 4.1.1及更高以后，新的 PASSWORD() 函数可以正常同步，当然了，slave必须是4.1.1或更高或者不同步它。如果有旧版本的slave必须要同步 PASSWORD() 函数，那么master启动时必须增加 --old-password 选项，这样在master上就用旧的方法来实现 PASSWORD() 了(注意，MySQL(和PHP搭配之最佳组合) 4.1.0的 PASSWORD() 函数实现跟其他的版本都不同，最好不要同步4.1.0)。
* 从MySQL(和PHP搭配之最佳组合) 4.0.14开始同步 FOREIGN_KEY_CHECKS 变量。从5.0.0开始同步 sql_mode, UNIQUE_CHECKS,和 SQL_AUTO_IS_NULL 变量。 SQL_SELECT_LIMIT 和 table_type 变量目前还不能被同步。
* 现在讨论使用不同字符集的MySQL(和PHP搭配之最佳组合)服务器间同步的问题。
1. 首先，在master和slave上必须总是使用同样的全局字符集以及校验字符集(--default-character-set, --default-collation 都是相关的全局变量)。否则，slave上可能会出现键重复(duplicate-key)的错误，因为用master的字符集认为该键可能是唯一的，但是用slave的字符集则未必然。
2. 第二，如果master必须低于MySQL(和PHP搭配之最佳组合) 4.1.3，则会话(session)的字符集必须和全局值一样(也就是说，不能执行 SET NAMES, SET CHARACTER SET 等语句)，因为这些对字符集的修改在slave不能识别。如果master是4.1.3或者更新，slave也是这样的话，那么会话字符集就可以随便修改了(执行 NAMES, CHARACTER SET, COLLATION_CLIENT, COLLATION_SERVER 等)，并且这些修改都会被记录到二进制日志中，然后同步到slave上，它就知道怎么做了。该会话还会阻止试图修改这些全局变量的操作；就如前面所说，master和slave必须使用同样的全局字符集。
3. 如果在master上有和全局变量 collation_server 不一样字符集的数据库，那么就要设计 CREATE TABLE 语句使得数据表不隐式地使用该数据库的默认字符集，因为这目前还是一个bug(Bug #2326)；一个变通的办法是在 CREATE TABLE 语句中显式地声明数据表的字符集以及校验字符集。
* 有时可能会把master上的事务表同步到slave后变成非事务表。例如，可以在slave上把master的 InnoDB 表当成 MyISAM 表。不过，slave在一个 BEGIN/COMMIT 区块中停止的话就有问题了，因为slave会从 BEGIN 重新开始。这个问题已经放到TODO中，很快会被修复。
* 更新语句中如果用到了用户自定义变量(例如变量 @var_name)的情况下，在MySQL(和PHP搭配之最佳组合) 3.23和4.0不能被正确同步。在 4.1 这已经修复了。注意，从MySQL(和PHP搭配之最佳组合) 5.0开始，用户变量就不区分大小写了。在做MySQL(和PHP搭配之最佳组合) 5.0和旧版本间的同步需要考虑到这个问题。
* 从4.1.1以及更新版本中，slave可以用SSL方式连接到master。
* 在master上执行的 CREATE TABLE 语句如果包括了 DATA DIRECTORY或 INDEX DIRECTORY 子句，那么它也会应用于slave上。如果slave上不存在对应的目录或者没有权限时便出现问题。从MySQL(和PHP搭配之最佳组合) 4.0.15开始，有个 sql_mode 选项叫 NO_DIR_IN_CREATE。如果slave的SQL模式包含这个选项，那么它在同步 CREATE TABLE 语句前会忽略前面提到的2个子句。结果就是 MyISAM 的数据和索引文件都只能放在该表的数据库目录下。
* 尽管没听说过发生过类似的情况，不过理论上确实存在这种可能性：如果一个查询被设计为非确定方式的修改数据，那么可能导致master和slave的数据不一致。那么，就把决定的权力交给查询优化器吧。(通常这不是一个好的做法，甚至超出了同步的范围，详情请看"1.8.7.3 Open Bugs and Design Deficiencies in MySQL(和PHP搭配之最佳组合)")
* 在MySQL(和PHP搭配之最佳组合) 4.1.1之前，FLUSH, ANALYZE TABLE, OPTIMIZE TABLE,和 REPAIR TABLE 语句没有写入到二进制日志中，因此也不会同步到slave上。这通常不会引发问题，因为它们并没有修改数据。不过在特定情况下可能导致问题。如果同步 MySQL(和PHP搭配之最佳组合) 数据库下的权限表，在更新时不是用 GRANT 语句，那么必须在slave上执行那么必须在slave上执行 FLUSH PRIVILEGES 语句才能使之生效。同样地，如果还有一个 MyISAM 表是 MERGE 表的一部分，那么必须在slave上手工执行 FLUSH TABLES 语句。从MySQL(和PHP搭配之最佳组合) 4.1.1开始，这些语句都写入二进制日志了(除非指定选项 NO_WRITE_TO_BINLOG 或它的同名选项 LOCAL)。一些例外的情况是 FLUSH LOGS， FLUSH SLAVE, 和 FLUSH TABLES WITH READ LOCK (它们中的任何一个同步到slave的话都可能导致问题)。例子可见"14.5.4.2 FLUSH Syntax"。
* MySQL(和PHP搭配之最佳组合)只支持一个master多个slave的机制。以后我们会增加一个表决算法，如果当前master出现问题时能自动切换。同时也会引进一个"代理"进程来帮助将 SELECT 查询发送到不同的slave上达到负载均衡。
* 当服务器关闭，重启后，所有的 MEMORY (HEAP) 表都清空了。从MySQL(和PHP搭配之最佳组合) 4.0.18开始，master用以下方式同步它们：一旦master开始使用一个 MEMORY 表，它会在用完这些表之后在二进制日志中写入一个 DELETE FROM 语句告诉slave把它们删除。详情请看"15.3 The MEMORY (HEAP) Storage Engine"。
* 临时表也会同步，除非关闭slave(只是关闭slave线程)并且在slave上已经同步了一些还未被执行的需要用到临时表的更新语句。关闭slave再重启后更新所需的临时表就不复存在了。为了避免这个问题，在有临时表时就不要关闭slave。或者，使用以下步骤：
1. 提交一个 STOP SLAVE 语句。
2. 使用 SHOW STATUS 语句检查变量 Slave_open_temp_tables 的值。
3. 如果它的值是0，运行 MySQL(和PHP搭配之最佳组合)admin shutdown 命令关闭slave。
4. If the value is not 0, restart the slave threads with START SLAVE.
5. 如果它的值不是0，用 START SLAVE 语句重启slave线程。
6. Repeat the procedure later to see if you have better luck next time.
7. 如果还有这样的好运气就再次执行同样的步骤吧。^_^
我们会尽快解决这个问题。
* 如果在一个循环master/slave同步关系中指定 --log-slave-updates 选项，那么就可以安全地连接到各个服务器上。注意，很多语句可能在这种设置环境下不能正常工作，除非程序中已经特别注意避免这种更新时潜在的问题了，因为可能在不同服务器上不同的顺序上发生更新问题。这意味着可以设定像下面的循环：

A -> B -> C -> A

服务器ID都已经编码到二进制日志中了，因此服务器A知道那些自己创建的日志，从而不会去执行它们(除非在服务器A上启动时增加 --replicate-same-server-id 选项，这个选项只在很少的设置时有意义)。因此，这就不会存在无限循环了。不过这个循环只有在更新表时没有发生冲突才不会发生问题。换言之，如果在A和C 中同时插入一条记录，那么可能在A中不可能插入数据，因为它的键可能跟C的键冲突了。同样地，也不能在两个服务器上更新同一条记录，除非2次更新操作间有足够的时间间隔。
* 如果在slave上执行一个SQL语句后产生错误，那么slave的SQL线程就终止，然后它在错误日志中写入一条信息。可以连接到slave上，解决问题(例如，不存在表)后，运行 START SLAVE 语句重启它。
* 可以放心地关闭master(干净地)之后再重启它。如果slave到master的连接断开了，它会立刻重连。如果失败了，slave会定期重试(默认是每60秒重试一次，可通过 --master-connect-retry 选项来修改)。slave也会处理网络断开的情况。不过，slave会在 slave_net_timeout 秒之后还没接收到来自master的数据才会处理网络断开情况的。如果断开时间不长，可以减少 slave_net_timeout 的值。详情请看"5.2.3 Server System Variables"。
* 也可以放心地关闭slave(干净地)，它会记录停止的地方。不干净地关闭slave可能产生问题，特别是系统关闭了但缓存还没刷新到磁盘时。可以提供不间断电源来提高系统容错性。master的不干净关闭可能导致表和二进制内容的不一致；如果是 InnoDB 表，使用 --innodb-safe-binlog 选项在master上就能避免这个问题。详情请看"5.9.4 The Binary Log"。
* 由于 MyISAM 表的非事务本质，就可能发生一个语句只更新了部分表就返回错误代码的情况。例如，一个多重插入语句中，有一条记录违反了约束键规则，一个更新语句在更新了一些记录后辈杀掉了。如果在master上发生这种情况了，那么slave线程会推出，等待数据库管理员决定要怎么做，除非这个错误代码是合法的并且这个语句的执行结果也是一样的错误代码。并没有关于错误代码是否合法的详细描述，一些错误代码可以用 --slave-skip-errors 选项屏蔽掉。这个选项从MySQL(和PHP搭配之最佳组合) 3.23.47开始就可以用了。
* 如果把非事务表同步到事务表时在一个 BEGIN/COMMIT 段内更新数据表了，如果在非事务表提交之前有其他线程更新它了，那么这个更新操作就不会正确地同步到二进制日志中。这是因为只有整个事务成功提交了才会写到二进制日志中。
* 在4.0.15之前，任何在非事务表的更新操作会在它执行的时候立刻写入到二进制日志中，然而事务表的更新只有在 COMMIT 后才写入，ROLLBACK 的话就不写入了。因此在一些事务中更新事务表或非事务表时就需要考虑这个情况了(不只是同步时会碰到这个问题，想要把二进制日志作为备份时也一样)。在 MySQL(和PHP搭配之最佳组合) 4.0.15中，我们已经修改了更新事务和非事务表混合的情况下的日志记录行为，它解决了这个问题(对于二进制日志来说顺序地记录语句是比较不错的做法，所有必须的语句都会写进去，ROLLBACK 也一样)。当第二个连接更新非事务表而第一个连接的事务还没结束时，就会有同样的问题了；仍会发记录语句顺序发生错误的问题，因为第二个连接会在更新完成后立刻写入到日志中。
* 当4.x的slave从3.23的master上同步 LOAD DATA INFILE 时，SHOW SLAVE STATUS 中的 Exec_Master_Log_Pos 和 Relay_Log_Space 字段的值就不正确了。Exec_Master_Log_Pos 值不正确的话在重启slave之后会导致问题；因此最好在重启前修改一下这个值，只需在master上运行 FLUSH LOGS。这个bug在MySQL(和PHP搭配之最佳组合) 5.0.0的slave中已经解决了。

下表列出了MySQL(和PHP搭配之最佳组合) 3.23同步时会发生的问题，它们在MySQL(和PHP搭配之最佳组合) 4.0已经解决了：

* LOAD DATA INFILE 能正确处理，只要那个数据文件在更新开始时仍然存在于master上。
* LOAD DATA LOCAL INFILE 不再像以前3.23那样被略过了。
* 在3.23中，RAND() 更新同步不正常。因此在使用 RAND() 更新时采用 RAND(some_non_rand_expr) 格式。例如，可以用 UNIX_TIMESTAMP() 作为 RAND() 的参数。