MySQL的复制(5.0.x版)part 2-2-sdccf-ChinaUnix博客

6.6. 升级复制设置

当在复制设置中升级服务器时，升级过程取决于当前的服务器版本和要升级的服务器版本。

6.6.1. 将复制升级到5.0版

该节适用于将复制从MySQL 3.23、4.0或者4.1升级到5.0。4.0服务器应为4.0.3或更新版。

当将早期MySQL版本系列主服务器升级到5.0时，应先确保该主服务器的所有从服务器使用了相同的5.0.x版本。如果不是这样，你应先升级从服务器。升级从服务器时，应先关闭从服务器，升级到相应5.0.x版本，然后重启从服务器并重新开始复制。5.0版本的从服务器能够读取升级前写入的旧的中继日志并执行日志中包含的语句。升级后从服务器创建的中继日志为5.0格式。

从服务器升级后，关闭主服务器，将它升级到与从服务器相同的5.0.x版本并重启它。5.0主服务器能够读取升级前写入的旧的二进制日志并将它们发送到5.0从服务器。从服务器可以识别旧的格式并正确处理它。升级后主服务器创建的二进制日志采用5.0格式。这样也可以由5.0从服务器识别。

换句话说，当升级到5.0时没有什么措施，只是将主服务器升级到5.0之前先将从服务器升级到5.0。请注意从5.0降级到旧版本不会如此简单：必须确保已经完全处理所有5.0版本的二进制日志或中继日志，以便在降级前可以移除它们。

6.7. 复制特性和已知问题

一般原则，SQL级复制兼容性要求主服务器和从服务器均支持使用的特性。如果你在主服务器上运用了一个特定版本的特性,但你的从服务器比那个版本低,那样就不能完成复制.类似的不兼容极有可能在不同系列的版本间发生,例如,你不能从5.0复制到4.1.但是,这项不兼容也有可能在一个系列的版本复制时发生.例如SLEEP()函数只在MySQL5.0.12及以后的版本中才支持.如果你在主服务器里面运用了这个函数,那么你就不能复制到一个版本低于5.0.12的从服务器上.

如果你计划在5.1和以前版本的MySQL之间进行复制，你应查阅对应以前版本系列的MySQL参考手册，查询该系列复制特征相关信息。

下面列出了关于支持什么和不支持什么的详细信息。关于复制的其它InnoDB具体信息参见15.2.6.5节，“InnoDB和MySQL复制”。

关于存储的程序和触发器的复制问题在17.4节，“存储子程序和触发程序的二进制日志功能”中讨论。

已知问题:在5.0.17中,CREATE TRIGGER的语法更改为包含一个DEFINER子句用来指定触发器启动时的访问权限.(查看18.1 "CREATE TRIGGER语法")尽管如此,如果你向从一个低于5.0.17的主向一个运行5.0.17或者5.0.19的从复制时,CREATE TRIGGER这个语句的复制将会失败,从服务器的错误为Definer not fully qualified.

A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ...

这样写的CREATE TRIGGER语句将被复制到新版本的从服务器,从服务器从注释中提取DEFINER子句,然后正确执行.

5.0.20中这个问题得以修复.

· AUTO_INCREMENT, LAST_INSERT_ID(),TIMESTAMP的值可以被正确复制.下面是特殊情况(to the following exceptions.

INSERT DELAYED ... VALUES(LAST_INSERT_ID())会在主与从上插入不同值().这个在5.1中修复运用基于行或者混合格式的二进制日志.

在5.0.26前,运用了LAST_INSERT_ID()的存储过程不能被正确复制.

当一个语句用了向一个AUTO_INCREMENT列插入的存储功能时,产生的AUTO_INCREMENT值不会被写入二进制日志,所以一些时候从服务器上就会写入不同值.

用ALTER TABLE向一个表增加一个AUTO_INCREMENT可能不后会在主和从上产生一样的顺序.这样问题发生的原因是:行编号的顺序取决于这个表用的特定存储引擎和这些列插入的顺序.如果主和从的顺序必须一样,那么这些行必须在分配AUTO_INCREMENT值之前就已经存储了.假定你想要向表t1增加一个AUTO_INCREMENT列,以下的几个语句会产生一个新表t2与t1一样,但是有一个identical列.

· CREATE TABLE t2 LIKE t1;

· ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;

· INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

这是架设t1表有col1和col2列

重要:为了保证主从一样的顺序,t1里面的所有列必须用ORDER BY排序

以上给出的语句主要受CREATE TABLE ... LIKE的限制.外键的定义还有DATA DIRECTORY , INDEX DIRECTORY 的表选项将被忽略.如果一个表包含了上述特性,那么就用CREATE TABLE语句来创建t2,就和创建t1的语句一样,但是加上一个AUTO_INCREMENT的列

不管用来创建和增加AUTO_INCREMENT给副本用的是什么方法,最后一步都是删除原始表,然后将副本重命名.

· DROP t1;

· ALTER TABLE t2 RENAME t1;

查看 Section B.1.7.1, “Problems with ALTER TABLE”.

· 特定的函数在以下的一些情况下不能被恰当复制:

· USER(), CURRENT_USER(), UUID(), VERSION(), and LOAD_FILE()函数没有更改的被复制,这样在从服务器上不会可靠的执行.

· 在5.0.13中, SYSDATE()不再和NOW()等同.意思是SYSDATE()不是安全复制的,因为二进制日志中的SET TIMESTAMP语句完全不影响它,而且不起决定作用.为了避免这样,你可以启动服务器的时候带--sysdate-is-now这个选项来将SYSDATE()作为NOW()的别名

· GET_LOCK(), RELEASE_LOCK(), IS_FREE_LOCK(), and IS_USED_LOCK()这些控制用户级的锁的函数被复制时从并不知道主的并发连接情况.因此这些函数不应该被用来插到主的表里面,因为从服务器上的内容将会不同.(例如,不要发出这样的语句INSERT INTO mytable VALUES(GET_LOCK(...)).)

为了避开之前的限制,你可以用这样的策略:将有问题的函数的结果保存在一个用户变量中,然后在后面的语句中引用它.例如,下面的INSERT语句由于UUID()函数导致有问题

· INSERT INTO t VALUES(UUID());

为了避免问题,用下面的语句替代:

· SET @my_uuid = UUID();

· INSERT INTO t VALUES(@my_uuid);

这些语句被复制因为@my_uuid的值作为一个用户变量保存于二进制日志中,它优先于INSERT语句保存,然后能够被INSERT所使用.

同样的思想也适用于多行插入,但是用起来更麻烦些.对于一个两行的插入,你可以这样做:

· SET @my_uuid1 = UUID(); @my_uuid2 = UUID();

· INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);

尽管如此,如果行数很多或者未知,这个方法就十分困难甚至不可行.例如,你不能将下面的语句转换,在它里面每个独立的用户变量与每一个行关联.

· INSERT INTO t2 SELECT UUID(), * FROM t1;

· 用户的权限仅当mysql数据库被复制才会被复制.这就是说GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER语句要在从服务器上起作用仅仅当复制建立时包括了mysql数据库.

如果你复制所有的数据库,但是不希望那些影响用户权限的语句被复制,那么建立从服务器不要复制mysql数据库,用--replicate-wild-ignore-table=mysql.%选项.从服务器就会识别出与权限相关的SQL语句不会起作用,这样就不会执行这些语句了.

· FOREIGN_KEY_CHECKS, SQL_MODE, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL这些变量在MySQL5.0里面都会被复制.系统变量storage_engine(also known as table_type)不会被复制,这对于不同存储引擎间的复制是件好事.

· 从5.0.3开始(主和从),即使主和从有不同的全局字符设置变量global character set variables,复制也能进行.从5.0.4开始(主和从),即使主和从有不同的全局时区变量global time zone variables,也能进行.

The following applies to replication between MySQL servers that use different character sets:
1. If the master uses MySQL 4.1, you must always use the same global character set and collation on the master and the slave, regardless of the MySQL version running on the slave. (These are controlled by the --character-set-server and --collation-server options.) Otherwise, you may get duplicate-key errors on the slave, because a key that is unique in the master character set might not be unique in the slave character set. Note that this is not a cause for concern when master and slave are both MySQL 5.0 or later.
2. If the master is older than MySQL 4.1.3, the character set of any client should never be made different from its global value because this character set change is not known to the slave. In other words, clients should not use SET NAMES, SET CHARACTER SET, and so forth. If both the master and the slave are 4.1.3 or newer, clients can freely set session values for character set variables because these settings are written to the binary log and so are known to the slave. That is, clients can use SET NAMES or SET CHARACTER SET or can set variables such as collation_client or collation_server. However, clients are prevented from changing the global value of these variables; as stated previously, the master and slave must always have identical global character set values.
3. If you have databases on the master with character sets that differ from the global character_set_server value, you should design your CREATE TABLE statements so that tables in those databases do not implicitly rely on the database default character set (see ). A good workaround is to state the character set and collation explicitly in CREATE TABLE statements.
If the master uses MySQL 4.1, the same system time zone should be set for both master and slave. Otherwise some statements will not be replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. You can set the time zone in which MySQL server runs by using the --timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. Both master and slave should also have the same default connection time zone setting; that is, the --default-time-zone parameter should have the same value for both master and slave. Note that this is not necessary when the master is MySQL 5.0 or later.

· CONVERT_TZ(...,...,@@global.time_zone)不能被正常复制,仅仅当主和从是5.0.4及之后的版本CONVERT_TZ(...,...,@@session.time_zone)才能被正确复制.

· 会话变量(session)当用在更新表的语句中时不能被正常复制.例如, SET MAX_JOIN_SIZE=1000 ,后面接上 INSERT INTO mytable VALUES(@@MAX_JOIN_SIZE)将不会在主和从上插入相同数据.这不适用于以下情况: SET TIME_ZONE=... 后面接 INSERT INTO mytable VALUES(CONVERT_TZ(...,...,@@time_zone)),,这句话在5.0.4上能正确复制.

· 将主上的事务性(transactional)表而从上面是非事务性的表(non-taransactional)这样的复制可能是可以的.例如,你可以复制一个InnoDB主的表作为一个MyISAM的从的表.尽管如此,如果你这么做了,当从服务器在BEGIN/COMMIT之间停止时会出现问题,因为从服务器会在BEGIN块的起始处重启.

· 在5.0里面涉及有用户定义变量(形如@var_name)的更新语句可以被正确复制.但是4.1之前的版本不支持.注意5.0开始用户变量名称是不敏感的case insensitive.在配置5.0和老版本间的复制时应该考虑这个情况.

· 带有RAND()或者用户定义变量的非延迟(non-delay) INSERT语句可以被正确复制.尽管如此,如果用INSERT DELAYED这样的语句可能会导致主从的结果不一致.

· 从可以通过SSL连接到主

· 视图总是被复制到从.视图由它自己的名字过滤,而不是由它们相关的表.这表示即使一个视图里面含有一个被replication-ignore-table规则所过滤掉的表,这个视图也会被复制.因此应该注意保证视图没有复制表里面的数据,一般会因为安全原因而过滤掉.

· 在5.0里面(从5.0.3开始),有一个全局系统变量slave_transaction_retries.如果复制过程中从上的SQL线程执行一个事务时失败(由于an InnoDB deadlock,或者它超过了下列值InnoDB innodb_lock_wait_timeout,或者 NDBCluster的 TransactionDeadlockDetectionTimeout 或者TransactionInactiveTimeout ),事务会自动重试slave_transaction_retries定义的次数然后停止产生一个错误.默认的值是10.从5.0.4开始,这个重试的总数可以在SHOW STATUS的输出中看见.查看Section 5.2.5, “Status Variables”.

· 如果主上的CREATE TABLE 语句中使用了DATA DIRECTORY or INDEX DIRECTORY的表选项,这个选项也用在从上.但是如果从的上面没有相应的目录或者存在但是不能访问的话会产生问题.MySQL支持一个sql_mode的选项称为NO_DIR_IN_CREATE.如果从服务器运行没有启用这个SQL模式的话,它在复制CREATE TABLE时就会忽略DATA DIRECTORY 和INDEX DIRECTORY.这样的结果是MyISAM数据和索引文件都会在这个表的数据库文件夹下面创建.

It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. (This is in general not a good practice, even outside of replication.) For a detailed explanation of this issue, see Section B.1.8.1, “Open Issues in MySQL”.

· 主运行4.1,从运行5.0时,用LOAD TABLE FROM MASTER可能会损坏表数据,并且不被支持. ()

· 以下仅仅适用于或者主或者从正运行5.0.3及之前版本的.如果主上的一个LOAD DATA INFILE被阻断(完整性约束破坏,中断连接,等等),从直接跳过整个LOAD DATA INFILE文件.这意味着这个命令在被中断之前永久性的插入或更新了纪录的话,这些更改将不会复制到从.

· 一些FLUSH语句将不会被记录到日志因为他们复制到从的时候可能引起问题: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK.举语法例子,查看Section 13.5.5.2, “FLUSH Syntax”. FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE这些语句被写入二进制日志,然后复制给从.这一般不存在问题因为这些语句并不修改表数据.但是,在一定情况下会引起麻烦.如果你复制了mysql数据库里面的权限表,没有用GRANT直接更新了它们,你必须在从上面发出FLUSH PRIVILEGES让新权限生效.当你重命名一个MyISAM表(并且这个表是一个MERGE表的部分)时,如果你用FLUSH TABLES,你必须在从上手动的发出FLUSH TABLES.这些语句不会被写入二进制日志,除非你指定了NO_WRITE_TO_BINLOG或者它的别名LOCAL

· 当一个服务器关闭或者重启时,他的内存MEMORY (HEAP)表变为空的.主将这个影响复制给从像以下这样:主启动后运用每个MEMORY 表的第一次,它日志记录一个时间提醒从表需要被清空(通过在二进制日志中为这个表写入一个DELETE语句).. See Section 14.4, “The MEMORY (HEAP) Storage Engine”, for more information about MEMORY tables.

· 临时表被复制,除了以下这种情况:你关闭了服务器(不仅是从线程),并且你已经复制了用于更新但是还没有在从上执行的临时表.如果你关闭了从服务器,当从重启的时候,那些更新所需要的临时表救不再可用了.为了避免这个问题,不要在从有临时表打开的时候关闭它.代替方法,用下面的过程:

Issue a STOP SLAVE statement. 发出一个STOP SLAVE语句
Use SHOW STATUS to check the value of the Slave_open_temp_tables variable. 用SHOW STATUS查看Slave_open_temp_tables变量的值
If the value is 0, issue a mysqladmin shutdown command to stop the slave. 如果这个值是0,发出mysqladmin shutdown命令来停止从
If the value is not 0, restart the slave threads with START SLAVE. 如果这个值不是0,用START SLAVE重启从线程
Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave. 隔一会重复这个过程直到Slave_open_temp_tables的值为0然后你救可以停止从了.

· 用表的别名来进行多个表DELETE删除的语法在4.0和4.1里面发生了改变.在4.0里面,你要用要删除的行相关的表的真实的表名

· DELETE test FROM test AS t1, test2 WHERE ...

在 4.1里面,你必须用别名:

· DELETE t1 FROM test AS t1, test2 WHERE ...

如果你用了这样的DELETE语句,语法的改变意味着4.0的主不能复制到4.1(或者更高)的从

· 如果你用了--log-slave-updates选项那么以一种环形的主/从关系进行连接是安全的.你可以创建如下的设置:

· A -> B -> C -> A

但是,许多语句不能这样工作在这样的设置下.除非你的客户端代码编写已经考虑到了不同服务器不同顺序下更新可能产生的问题.

server ID是用二进制日志事件编码的,因此服务器A知道它读的某个事件最初是由它自己产生的而不会执行这个事件(除非A启动时有--replicate-same-server-id选项,这个只在很少的情况下有意义).这样就不会有无穷的循环.这种环形的设置仅仅当你的表之间没有冲突的更新时才会工作.还句话说,如果你在A和C里面都插入了数据,你决不能在A里面插入行,而这个行又跟插入C里面的一个行有个关键字冲突.你也不应该更新相同的行在两个服务器上,如果这些更新应用的次序十分重要.

· 如果从的一个语句产生错误,从的SQL线程中止,然后从写入一个消息到它的错误日志中.你这个时候应该手动连接到从,然后找出问题的原因(这个时候SHOW SLAVE STATUS十分有用),然后修复这个问题(例如,你可能需要创建一个不存在的表),然后运行START SLAVE.

· 关闭主然稍后再重启是十分安全的.当从失去了跟主的连接后,从会直接就发起重新连接,如果失败会周期性重连.默认是每60s重连一次.可以用--master-connect-retry来更改.从也能处理网络连接损耗.尽管如此仅在slave_net_timeout秒数的时间内从没有从主那里收到数据,从才会提示网络损耗.如果你的网络损耗很短,你可能想要减小slave_net_timeout的值. See Section 5.2.3, “System Variables”.

· 关闭从服务器（净关闭）也很安全，因为它可以跟踪它离开的地点。不纯净的关闭操作会产生问题，特别是系统关闭前硬盘缓存未刷新到硬盘上时。如果有不间断电源，可以大大提高系统容错能力。不纯净的关闭主服务器会造成主服务器上的表和二进制日志内容之间的不一致性；在主服务器上使用InnoDB表和--innodb-safe-binlog选项可以避免该问题。参见5.11.3节，“二进制日志”。

。(注释：MySQL 5.1中不需要--innodb-safe-binlog，由于引入了XA事务支持已经作废了）。

· 主端crash可能导致主的二进制日志最后的位置比从读的最近的位置要少,因为主的二进制文件没有被flush刷新.这可能导致主起来后从不能读.在主的my.cnf中设置sync_binlog=1帮助最小化这个问题因为它使得主刷新它的二进制日志更加频繁.

· 由于MyISAM表的非事务属性，可以有一个语句只是更新一个表并返回错误代码。例如，多行插入时有一个行超过键值约束，或者如果长的更新语句更新部分行后被杀掉了。如果发生在主服务器上，除非错误代码合法并且语句执行产生相同的错误代码，从服务器线程将退出并等待数据库管理员决定如何做。如果该错误代码验证行为不理想，可以用--slave-skip-errors选项掩盖(忽视)部分或全部错误。

· 如果从BEGIN/COMMIT系列的非事务表更新事务表，如果提交事务前更新非事务表，对二进制日志的更新可能会不同步。这是因为事务提交后才被写入二进制日志。

· 事务混合更新事务表和非事务表时，二进制日志中语句的顺序是正确的，即使在ROLLBACK时，所有需要的语句也会写入二进制日志。但是如果在第1个连接的事务完成前，第2个连接更新非事务表，语句记入日志时会出现顺序错误，因为第2个连接的更新执行完后立即写入日志，而不管第1个连接执行的事务的状态如何。

注意:在复制环境里,你不应该用事务同时更新事务性和非事务性的表格.