A-A+

MySQL 5.5升级MySQL 5.6

2015年06月10日 DB, upgrade 评论 1 条

 

目录

配置参数

保留字

内置函数

SQL 不兼容性

配置参数

从MySQL 5.6.6开始,几个MySQL Server配置参数的默认值会与以前的版本默认值不同。这些参数变化的目的是为了提供直接就可以用的更好的性能,以减少管理员来手动配置这些参数的工作。

未来版本中这些参数默认值也会随着应用的反馈而进行调整。

在一些场景下,配置参数会有不同的静态缺省值。比如,有些参数的缺省值是依照一些其它参数的变化而变化的。例如,参数back_log旧的缺省值现在是50,现在却是随max_connections的值变化而调整。这样做的好处是,利用服务器可用的信息来做出参数的设定可能会比一定固定值效果更好。

下面的表格总结了参数默认值的变化。任何下面的值都可以在服务器启动时被显示的覆盖。

参数 旧的默认值 新的默认值
Back_log 50 据max_connections大小 自动调整
binlog_checksum NONE CRC32
--binlog-row-event-max-size 1024 8192
Flush_time 1800( on Windows) 0
innodb_autoextend_increment 8 64
innodb_buffer_pool_instances 1 8 (platform dependent)
innodb_checksum_algorithm INNODB CRC32 (changed back to INNODB in MySQL 5.6.7)
innodb_concurrency_tickets 500 5000
innodb_file_per_table 0 1
innodb_old_blocks_time 0 1000
innodb_open_files 300 据innodb_file_per_table, table_open_cache大小 自动调整
innodb_stats_on_metadata ON OFF
join_buffer_size 128KB 256KB
max_allowed_packet 1MB 4MB
max_connect_errors 10 100
sync_master_info 0 10000
sync_relay_log 0 10000
sync_relay_log_info 0 10000

关于与以前版本的兼容性问题,最重要的变化是:

innodb_file_per_table为新增参数,以前版本是没有的

innodb_checksum_algorithm 缺省为CRC32,以前版本缺省值由INNODB决定

binlog_checksum 缺省为CRC32,以前版本没有

因此,如果对一个尚未显示指定这些参数默认值的MySQL版本进行升级,考虑向后兼容的问题,你需要在MySQL的配置文件中明确的指定这些参数的值为以前版本的默认值。

例如,在配置文件的mysqld选项中增加如下行:

[mysqld]

innodb_file_per_table=0

innodb_checksum_algorithm=INNODB

binlog_checksum=NONE

让这些参数维护兼容性的方案如下:

随着innodb_file_per_table启用新的缺省值,升级后的ALTER TABLE操作会将系统表空间中的Innodb表变为各个独立的.ibd文件。 使用innodb_file_per_table = 0可以避免这种情况发生。

设置innodb_checksum_algorithm=INNODB允许升级到MySQL 5.6后的二制版降级。而设置值为CRC32, 旧的版本会不能使用checksum校验。

设置binlog_checksum=NONE时,MySQL 5.6版本作为主机,将不会导致旧版本的备机无法识别binlog的校验。

保留字

在MySQL 5.5的基础上, MySQL 5.6 新增了一些保留字,如果数据库对象名(如模式、表、列等)名字使用了以下保留字,那么升级后可能原 SQL 语句无法使用,报语法出错。

5.6 增加以下保留字

GET

IO_AFTER_GTIDS

IO_BEFORE_GTIDS

MASTER_AUTO_POSITION MASTER_BIND

保留字检查方法

在升级前,可通过以下基于 information_schema 的查询,检查是否存在对象名使用了 5.6的新保留字。

# 检查表名、模式名合法性select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from information_schema.tables where table_name in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS','MASTER_AUTO_POSITIONMASTER_BIND') or table_schema in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS', 'MASTER_AUTO_POSITIONMASTER_BIND');

# 检查表和视图的列名合法性

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns where column_name in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS','MASTER_AUTO_POSITIONMASTER_BIND');

# 检查存储函数、存储过程名字合法性

select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE from information_schema.routines where routine_name in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS','MASTER_AUTO_POSITIONMASTER_BIND');

# 检查触发器和事件

select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.triggers where TRIGGER_NAME in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS', 'MASTER_AUTO_POSITIONMASTER_BIND');

select EVENT_SCHEMA,EVENT_NAME from information_schema.events where EVENT_NAME in ('GET', 'IO_AFTER_GTIDS','IO_BEFORE_GTIDS', 'MASTER_AUTO_POSITIONMASTER_BIND');

内置函数

高版本包含所有低版本的内置函数,因此,原 SQL 中使用这些内置函数不需任何改变。

然而,如果在低版本建立了 UDF 或存储函数,其名字与高版本的新内置函数冲突的话,那么,升级后这些 UDF 或存储函数就无法使用。解决方法为:

1. 如果是 UDF 与内置函数冲突,只能是使用 DROP FUNCTION 删除 UDF ,然后以非冲突名字通过 CREATE FUNCTION 重建这些函数,同时需要修改相应的 SQL 语句。

2. 如果是存储函数与内置函数冲突,可以使用方法 1 。或者所有存储函数的调用加上 DB 名前缀,如 dbname.func(...) ,这样语法解析器能正确识别其为存储函数而不是内置函数。

MySQL 5.6新增很内置函数包括如下

ASYMMETRIC_DECRYPT()

ASYMMETRIC_DERIVE()

ASYMMETRIC_ENCRYPT()

ASYMMETRIC_SIGN()

ASYMMETRIC_VERIFY()

Buffer()

CREATE_ASYMMETRIC_PRIV_KEY()

CREATE_ASYMMETRIC_PUB_KEY()

CREATE_DH_PARAMETERS()

CREATE_DIGEST()

FROM_BASE64()

GTID_SUBSET()

GTID_SUBTRACT()

IS_IPV4_COMPAT()

IS_IPV4_MAPPED()

IS_IPV4()

IS_IPV6()

RANDOM_BYTES()

SHA2()

SQL_THREAD_WAIT_AFTER_GTIDS()

ST_Area()

ST_AsBinary(), ST_AsWKB()

ST_AsText(), ST_AsWKT()

ST_Buffer()

ST_Centroid()

ST_Contains()

ST_Crosses()

ST_Difference()

ST_Dimension()

ST_Disjoint()

ST_Distance()

ST_EndPoint()

ST_Envelope()

ST_Equals()

ST_ExteriorRing()

ST_GeomCollFromText(), ST_GeometryCollectionFromText(), ST_GeomCollFromTxt()

ST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB()

ST_GeometryN()

ST_GeometryType()

ST_GeomFromText(), ST_GeometryFromText()

ST_GeomFromWKB()

ST_InteriorRingN()

ST_Intersection()

ST_Intersects()

ST_IsClosed()

ST_IsEmpty()

ST_IsSimple()

ST_LineFromText()

ST_LineFromWKB(), ST_LineStringFromWKB()

ST_NumGeometries()

ST_NumInteriorRings()

ST_NumPoints()

ST_Overlaps()

ST_PointFromText()

ST_PointFromWKB()

ST_PointN()

ST_PolyFromText(), ST_PolygonFromText()

ST_PolyFromWKB(), ST_PolygonFromWKB()

ST_SRID()

ST_StartPoint()

ST_SymDifference()

ST_Touches()

ST_Union()

ST_Within()

ST_X()

ST_Y()

TO_BASE64()

VALIDATE_PASSWORD_STRENGTH()

WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()

WEIGHT_STRING()

如果 UDF 或存储函数使用了这些名字,需要通过上述的方法来解决。另外,由于 MySQL 新版本会不断新增新内置函数,为了避免不必要的冲突,建议 UDF 和存储函数名字有特殊标识,如使用统一前缀, udf_ 或 func_ 等,这样能大大减少与内置函数冲突的概率。

内置函数检查方法

升级前,可通过以下语句判断是否存储函数名字是否与内置函数冲突。

select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE from information_schema.routines where routine_name in ('ASYMMETRIC_DECRYPT','ASYMMETRIC_DERIVE','ASYMMETRIC_ENCRYPT','ASYMMETRIC_SIGN','ASYMMETRIC_VERIFY','Buffer','CREATE_ASYMMETRIC_PRIV_KEY','CREATE_ASYMMETRIC_PUB_KEY','CREATE_DH_PARAMETERS','CREATE_DIGEST','FROM_BASE64','GTID_SUBSET','GTID_SUBTRACT','IS_IPV4_COMPAT','IS_IPV4_MAPPED','IS_IPV4','IS_IPV6','RANDOM_BYTES','SHA2','SQL_THREAD_WAIT_AFTER_GTIDS','ST_Area','ST_AsBinary', 'ST_AsWKB','ST_AsText', 'ST_AsWKT','ST_Buffer','ST_Centroid','ST_Contains','ST_Crosses','ST_Difference','ST_Dimension','ST_Disjoint','ST_Distance','ST_EndPoint','ST_Envelope','ST_Equals','ST_ExteriorRing','ST_GeomCollFromText', 'ST_GeometryCollectionFromText, 'ST_GeomCollFromTxt','ST_GeomCollFromWKB', 'ST_GeometryCollectionFromWKB','ST_GeometryN','ST_GeometryType','ST_GeomFromText', 'ST_GeometryFromText','ST_GeomFromWKB','ST_InteriorRingN','ST_Intersection','ST_Intersects','ST_IsClosed','ST_IsEmpty','ST_IsSimple','ST_LineFromText','ST_LineFromWKB', 'ST_LineStringFromWKB','ST_NumGeometries','ST_NumInteriorRings','ST_NumPoints','ST_Overlaps','ST_PointFromText','ST_PointFromWKB','ST_PointN','ST_PolyFromText', 'ST_PolygonFromText','ST_PolyFromWKB', 'ST_PolygonFromWKB','ST_SRID','ST_StartPoint','ST_SymDifference','ST_Touches','ST_Union','ST_Within','ST_X','ST_Y','TO_BASE64','VALIDATE_PASSWORD_STRENGTH','WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS','WEIGHT_STRING');

SQL 不兼容性

Server变化

不兼容变化:在创建表时指定的defalut值可能随sql_mode的变化而变得无效。例如:

SET sql_mode = '';

CREATE TABLE t (d DATE DEFAULT 0);

SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';

INSERT INTO t (d) VALUES(DEFAULT);

在这种情况下,在create table时可以显示默认值0,但insert时却不能这么用。针对当前的sql_mode,server并不能获取默认值来进行插入或者更新。在上述例子中, date列的默认值"0000-00-00"会被插入进去。

在MySQL 5.6.13中,server会在insert或者update时适当检查sql_mode的值产生的告警或者错误。

对slave升级了新版本而master未升级新版本的情况下,使用语句级binlog(binlog_format=STATEMENT)产生的不兼容,会出现insert时在slave上卡住的错误。

对于这个问题,先停掉master上的所有新的请求且等到slave 跟上,然后依次升级master与slave。另外,如不能停掉所有的master上的请求,临时将语句级binlog改为行级binlog(binlog_format=ROW),直到所有的slave执行binlog到改为行级的位置。然后升级slave并将master的行格式改为语句级。

不兼容变化:在MySQL 5.6.11及往后的版本中支持CREATE TABLE ... [SUB]PARTITION BY ALGORITHM=n [LINEAR] KEY (...)这种语法,这种语句与MySQL 5.1是兼容的。这种语法在MySQL 5.6.10及MySQL 5.6的更高版本是不支持的,尽管是从MySQL 5.3.31开始支持这个功能。MySQL 5.5.31的mysqldump及往后MySQL 5.5的版本在备份表的时候都能备份出ALGORITHM 这个选项,将之放在条件注释里,如下:

CREATE TABLE t1 (a INT)

/*!50100 PARTITION BY KEY */ /*!50531 ALGORITHM = 1 */ /*!50100 ()

PARTITIONS 3 */

当MySQL 5.6.10或者更早的MySQL 5.6的server中导入上面的CREATE TABLE语句时,这些可以产生语法错误的关于版本的hint不会被忽略。因此,这些MySQL版本导入这样的CREATE TABLE语句,需要将上面的hint去掉或者将50531换成50611。

不兼容变化:对TIME, DATETIME, 和TIMESTAMP类型的列,在MySQL 5.6.4之前的版本与MySQL 5.6.4及之后的版本中的格式是不同的。这是由于在MySQL 5.6.4中允许这些时间类型有小数部分。在将MySQL 5.5升级到MySQL 5.6.4或者之后的版本时,建议同时将TIME,DATETIME和TIMESTAMP也升级到MySQL 5.6.4。在MySQL 5.6及MySQL 5.6.4及往后版本中允许ALTER TABLE创建表包含时间类型的字段变成二进制格式,但在.frm文件损坏时却难以重建表。另外,MySQL 5.6.4中前述的时间类型会更有效的利用空间。更多MySQL 5.6.4中关于时间类型的变化,请参阅Storage Requirements for Date and Time Types.

在MySQL 5.6.16中,ALTER TABLE通过ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX及FORCE 选项来将旧的时间类型升级为5.6的时间类型。因此,下面的SQL语句可以升级旧的时间类型:

ALTER TABLE tbl_name FORCE;

这种转换不能使用 INPLACE 算法,因为表结构必须重组,而ALGORITHM=INPLACE会导致错误。如果需要指定ALGORITHM=COPY。

当通过ALTER TABLE将一个时间类型进行转换,会产生一个wangings,通过show warnings: TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format。

由于上面描述的关于时间类型的不兼容变化,通过ALTER TABLE ... IMPORT TABLESPACE的方式来导入MySQL 5.6.4之前版本中的表包含有DATETIME与 TIMESTAMP类型会失败。导入MySQL 5.5中包含有这些时间类型的表到MySQL 5.6.4或者更新版本最有可能出现这类问题。

以下方案给出了一个通过MySQL 5.6.4版本前的.frm重新创建一个与MySQL 5.6.4及更新版本兼容的表行结构。这个过程涉及通过使用Memory存储引擎替换Innodb来改变MySQL 5.6.4版本前的表的.frm文件, 复制.frm文件到一个目标实例中data目录下,并通过使用ALTER TABLE的方式来改变表存储引擎到InnoDB。如果你的表不包含外键,那和可以使用这个过程;如果你的表包含有主键,那么使用第二种方案。

如果你的表不存在外键:

如果你的表存在外键:

不兼容变化:在MySQL 5.6中,如果指定的字符集为character_set_server is ucs2, utf16, utf16le, 或者utf32,全文索引加载或者查询是用的latin1。如果任何表使用全文索引且字符集类型为ucs2, utf16, utf16le, or utf32,可以通过下面的语句进行修复:

REPAIR TABLE tbl_name QUICK;

不兼容变化:在MySQL 5.6.20中,针对Bug #69477的patch限制了redo log中BLOB字段的大小必须小于redo log文件大小的10%。因此导致一种新的限制,innodb_log_file_size应该至少要设置为比表中的行里面的最长的BLOB字段大10倍。如果你的innodb_log_file_size已这样设置或者你的表不包含有blob字段,那么可以不用变化。

在MySQL 5.6.22,In MySQL 5.6.22,redo log中的BLOB字段的大小限制被放宽到总的innodb_log_file_size大小,即(innodb_log_file_size * innodb_log_files_in_group). (Bug #19498877)。

SQL变化:

相对于MySQL 5.5, MySQL 5.6新增加了一些保留字。具体看http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html。

使用YEAR(2)这样的数据类型前需要考虑会产生的问题。在MySQL 5.6.6,YEAR(2)被废弃。 表中YEAR(2)列被识为当作为和以前一样,但是新版本或者alter table后会将 YEAR(2)转换为YEAR(4)。更多信息参阅Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”。

在MySQL 5.6.6,明确规定不允许赋一个默认值给存储过程或者函数或者存储程序中的局部变量(如SET var_name = DEFAULT这类语句)。这种用法在以前的版本或者文档中都没有支持的,被标记为不兼容变化是为了注意不要在代码里不经意的用到这种用法。和以前版本一样,新版本同样保留允许给系统变量赋默认值,但是将DEFAULT值给参数或者局部变量不有语法错误的。

在升级到MySQL 5.6.6及更新版本,已有的存储过程中若包含有这种用法会产生语法错误。如果从MySQL 5.6.5或者更高版本mysqldump出来的文件导入到MySQL 5.6.6或更新版本,这种被影响的存储过程或者函数需要进行修改。

在MySQL中,TIMESTAMP数据类型与其它数据类型非标准的方式有所不同:

a. TIMESTAMP列如果没有明确声明NULL属性,默认为NOT NULL。(而其他数据类型,如果没有显示声明为NOT NULL,则允许NULL值。)设置TIMESTAMP的列值为NULL,会自动存储为当前timestamp。

b. 表中的第一个TIMESTAMP列,如果没有声明NULL属性、DEFAULT或者 ON UPDATE,会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性。

c. 表中第二个TIMESTAMP列,如果没有声明为NULL或者DEFAULT子句,默认自动分配’0000-00-00 00:00:00′。插入行时没有指明改列的值,该列默认分配’0000-00-00 00:00:00′,且没有警告。

从MySQL5.6.6开始这种默认设置的方法被废弃了。在MySQL启动时会出现以下警告:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use --explicit_defaults_for_timestamp server option (see

documentation for more details).

关闭警告,在my.cnf中加入explicit_defaults_for_timestamp=ture。重启MySQL后错误消失,这时TIMESTAMP的行为如下:

a. TIMESTAMP如果没有显示声明NOT NULL,是允许NULL值的,可以直接设置改列为NULL,而没有默认填充行为。

b. TIMESTAMP不会默认分配DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP属性。

c. 声明为NOT NULL且没有默认子句的TIMESTAMP列是没有默认值的。往数据表中插入列,又没有给TIMESTAMP列赋值时,如果是严格SQL模式,会抛出一个错误,如果严格SQL模式没有启用,该列会赋值为’0000-00-00 00:00:00′,同时出现一个警告。(这和MySQL处理其他时间类型数据一样,如DATETIME)

通过做先升级slave后升级master的方式来升级。主从间的同步需要explicit_defaults_for_timestamp这两个值都相同。

先关闭slave,然后进行升级,并按期望值配置explicit_defaults_for_timestamp,然后重启拉起slave;

slave会识别旧的master的binlog格式,TIMESTAMP列会采取旧的TIMESTAMP行为。

然后关闭mater,对master进行升级,并将explicit_defaults_for_timestamp的值配置为与slave相同,再拉起master。

原创文章,转载请注明: 转载自腾讯游戏DBA团队

本文链接地址: MySQL 5.5升级MySQL 5.6

文章的脚注信息由WordPress的wp-posturl插件自动生成

Copyright © 腾讯游戏DBA团队 保留所有权利.  

用户登录

分享到: