MySQL 升级方法指南大全

原文:MySQL 升级方法指南大全

通常,从一个发布版本升级到另一个版本时,我们建议按照顺序来升级版本。例如,想要升级 MySQL 3.23 时,先升级到
MySQL 4.0,而不是直接升级到 MySQL 4.1 或 MySQL 5.0。

如果都是比较新的版本的升级可以参考下面的文章,

MySQL数据库的版本更新很快,新的特性也随之不断的更新,更主要的是解决了很多影响我们应用的BUG,为了让我们的MySQL变得更美好,我们有必要去给它升级,尽管你会说它现在已经跑得很好很稳定完全够用了。下面我们来看看几种常用的升级方法。

介绍之前,我们先做一些声明,MySQL采用二进制包来安装,升级都是在同一台DB Server上操作。

第一种,很简单,适用于任何存储引擎。

1. 下载并安装好新版本的MySQL数据库,并将其端口改为3307(避免和旧版本的3306冲突),启动服务。

2. 在新版本下创建同名数据库。

# mysqldump -p3307 -uroot create mysqlsystems_com

3. 在旧版本下备份该数据库。

# mysqldump -p3306 -uroot mysqlsystems_com >
mysqlsystems_com.bk

Note: 你也可以加上–opt选项,这样可以使用优化方式将你的数据库导出,减少未知的问题。

4. 将导出的数据库备份导入到新版本的MySQL数据库中。

# mysql -p3307 -uroot mysqlsystems_com <
mysqlsystems_com.bk

5. 再将旧版本数据库中的data目录下的mysql数据库全部覆盖到新版本中。

# cp -R /opt/mysql-5.1/data/mysql /opt/mysql-5.4/data

Note: 大家也都知道这个默认数据库的重要性。

6. 在新版下执行mysql_upgrade命令,其实这个命令包含一下三个命令:

# mysqlcheck –check-upgrade –all-databases –auto-repair

# mysql_fix_privilege_tables

# mysqlcheck –all-databases –check-upgrade –fix-db-names
–fix-table-names

Note:
在每一次的升级过程中,mysql_upgrade这个命令我们都应该去执行,它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时作出修复,还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。

7.
关闭旧版本,将新版的数据库的使用端口改为3306,重新启动新版本MySQL数据库。到此,一个简单环境下的数据库升级就结束了。

第二种,同样适用任何存储引擎。

1. 同样先安装好新版本的MySQL。

2. 在旧版本中,备份数据库。

# mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot
–tab=/opt/mysqlsystems_bk mysqlsystems_com

Note:
–tab选项可以在备份目录mysqlsystems_bk下生成后缀为*.sql和*.txt的两类文件;其中,.sql保存了创建表的SQL语句而.txt保存着原始数据。

3. 接下来在新版本的数据库下更新数据。

# mysqladmin -p3307 -uroot create mysqlsystems_com

# cat /opt/mysqlsystems_bk*.TRG

启动服务器,倒入触发器:

mysql> delimiter // ;

mysql> source /tmp/triggers.sql //

不兼容的变化:MySQL 5.1.6引进了触发器权限机制。以前,创建触发器需要有 SUPER 权限,现在,这个操作只需要有
TRIGGER 权限。这改善了权限安全状况

一些MySQL 5.1中作为保留关键字在MySQL 5.0中并没有作为保留关键字

新引入了 "INSTALL PLUGIN" 和 "UNINSTALL PLUGIN" 语句用于操作API插件。同样,创建
FULLTEXT 索引时,可以用 "WITH PARSER" 子句关联解析器插件

3、从 MySQL 4.1 升级到 MySQL 5.0

服务器部分:

不兼容的变化:InnoDB 和 MyISAM 表中空格结尾的 TEXT 字段索引顺序改变了。因此需要运行 "CHECK
TABLE" 语句修复数据表,如果出现错误,就运行 "OPTIMIZE TABLE" 或 "REPAIR TABLE"
语句修复,甚至重新转储(用mysqldump)

不兼容的变化:从MySQL 5.0.15开始,如何处理 BINARY 字段中填充的值已经改变了。填充的值现在是 0x00
而非空格了,并且在取值的时候不会去除末尾的空格

不兼容的变化:从MySQL 5.0.3开始,DECIMAL 的实现方式已经改变了,5.0对 DECIMAL
的格式限制严格多了

不兼容的变化:在MySQL 5.0.3到5.0.5之间版本的 MyISAM 和 InnoDB 表中创建的 DECIMAL
字段升级到5.0.6之后会发生崩溃

不兼容的变化:从5.0.3开始,除非和主函数之间有辅助的符号链接,否则服务器将不再默认地加载用户自定义函数(UDFs),也可以通过
--allow-suspicious-udfs 选项来启用

不兼容的变化:5.0中禁用了更新日志(update log) ,不过可以用二进制日志(binary log)来代替它

不兼容的变化:5.0中不再支持 ISAM 类型存储引擎(作者:可以通过重新编译源代码支持,不过非常不建议这么做)

不兼容的变化:5.0中不再支持 MyISAM 的 RAID 选项,可以用 mysqldump
导出旧表然后重新导回去实现升级

在5.0.6中,记录存储过程和触发器的二进制日志发生了一些变化,详见手册的 "17.4 Binary Logging of
Stored Routines and Triggers"

SQL部分:

不兼容的变化:从5.0.12开始,自然连接和使用 USING
的连接,包括外部连接的衍生形式,都按照SQL:2003标准来处理了;这个变化导致减少了自然连接和使用 USING
的连接产生的结果字段数,并且还将按照更合理的顺序显示这些字段,逗号比较符的优先顺序和 JOIN, LEFT JOIN
中的一样了

不兼容的变化:在以前,等待超时的锁会导致 InnoDB 回滚当前全部事务,从5.0.13开始,就只回滚最近的SQL语句了

不兼容的变化:触发器的变化,跟前面讲到的一样

不兼容的变化:从5.0.15开始,CHAR() 函数返回二进制字符串,而不是按照连接字符集格式的字符串。子句 USING
charset_name 可以自定义返回结果的字符集

不兼容的变化:在5.0.13以前,NOW() 和 SYSDATE() 返回的结果一样。但从5.0.13开始,SYSDATE()
返回的是语句执行点的时间,这就可能和 NOW() 返回的结果不一样了,不过可以用 --sysdate-is-now 选项让
SYSDATE() 作为 NOW() 的同名函数

不兼容的变化:在5.0.13以前,GREATEST(x,NULL) 和 LEAST(x,NULL) 如果 x 不是 NULL
值,则返回 x 。从5.0.13开始,只要任何参数是 NULL ,就返回 NULL,跟Oracle一样

不兼容的变化:在4.1.13/5.0.8以前,DATETIME 的加0后就转换成 YYYYMMDDHHMMSS 格式,现在变成
YYYYMMDDHHMMSS.000000 格式了

不兼容的变化:在4.1.12/5.0.6中,语句 LOAD DATA INFILE 和 SELECT ... INTO OUTFILE
中,当 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY
的值都是空的时候,结果就被改变了。以前,字段都按照它显示的宽度来读写的。现在变成按照足够保存字段值的宽度来读写它。然而,对MySQL
4.0.12/5.0.6来说,那些在它们之前导出来的文件可能无法正确用 LOAD DATA INFILE 语句导入

一些MySQL 5.0中作为保留关键字在MySQL 4.1中并没有作为保留关键字

从5.0.3开始,DECIMAL 用更有效的格式来存储

5.0.3开始,在计算 DECIMAL 值和舍入精确值的时候采用精确数学

4.1中,FLOAT 或 DOUBLE 之间的比较碰巧没问题,但在5.0中可能就不行了

从5.0.3开始,VARCHAR 和 VARBINARY 字段中末尾的空格不再删除

从5.0.3开始,BIT 是一个独立的数据类型了,不再是 TINYINT(1) 的同名词了

MySQL 5.0.2增加了一些SQL模式以使对排除包含非法或者缺失值得记录有着更严格的控制

从5.0.2开始,关键字 SCHEMA 和 SCHEMAS 被认为分别是 DATABASE 和 DATABASES
的同名词

5.0中用户变量对大小写不敏感,而4.1中则不然

增加了一个新的启动选项 innodb_table_locks,它导致 LOCK TABLE 时也可以请求 InnoDB
表锁。这个选项默认打开,不过可能在 AUTOCOMMIT=1 和 LOCK TABLES 应用中会导致死锁

C API部分:

不兼容的变化:由于5.0中 DECIMAL
数据类型的实现方式发生了变化,因此如果使用就版本的库文件需要注意这个问题

不兼容的变化:在5.0.3中,ER_WARN_DATA_TRUNCATED 警告符号改名为 WARN_DATA_TRUNCATED


MYSQL 结构体中的 reconnect 标志被 mysql_real_connect() 设为 0。

4、从 MySQL 4.0 升级到 MySQL 4.1

服务器部分:

不兼容的变化:以下好几个都是需要重建数据表的,可以使用 mysqldump 导出表后重新导回去

如果在4.1.0到4.1.3版本的MySQL中创建了包含 TIMESTAMP 字段的 InnoDB
表。则在升级到4.1.4及更高时需要重建表,因为存储格式发生变化了

从4.1.3开始,InnoDB 表采用同一种字符集比较函数来比较那些 非latin1_swedish_ci 字符集且不是 BINARY
的字符串

如果在4.1.0到4.1.5版本的MySQl中对 UTF8
字段或者其他多字节字段作了前缀索引,则在升级到4.1.6及更高时必须重建表

如果在4.1之前,数据库、表、字段、约束名中使用了重音字符(字节值是128到255的字符),那么不能直接升级到4.1。因为4.1使用
UTF8 来存储元数据名。

字符串根据标准SQL来比较:比较之前不删除末尾的空格,以前用末尾空格扩展了比较短的字符串。现在的结果是 'a'
> 'a\t',以前则不这样。可以用 mysqlcheck 来检查一下数据表

MyISAM 现在使用更好的校验和算法了

不兼容的变化:MySQL把字符串类型字段的长度定义理解为字符长度而不是字节长度。

重要提示:MySQL 4.1用 UTF8 字符集存储数据表名和字段名。如果有用标准 7字节 US-ASCII
范围之外的字符作为表名/字段名的话,需要重建表

重要提示:升级到4.1.1或更高后,就很难降级回到4.0或4.1了,因为 InnoDB 使用了多个表空间的缘故

不兼容的变化:MySQL 4.1.13支持让每个连接设定时区,因此系统变量 timezone 改成
system_time_zone

所有的数据表和非二进制字符串(CHAR, VARCHAR, 和 TEXT)的字段都有字符集,二进制字符串字段包括 BINARY,
VARBINARY, 和 BLOB

MySQL4.0中,如果有字段类型为 CHAR BINARY 或 VARCHAR
BINARY,则它们会被当作二进制字符串类型

如果数据表的字段中存储着MySQL 4.1直接就能支持的字符集字符数据时,则可以将这个字段的值转换成由合适的字符集存储

MySQL 4.1中对数据结构描述文件 .frm 的格式稍作改进,新版本能兼容这个新格式,但是旧版本则不能

windows下的服务器启动时增加 --shared-memory 选项即可支持从本地客户端连接时使用共享内存

不兼容的变化:从MySQL 4.1.1开始,对用户自定义函数集合接口发生了很大改进

不兼容的变化:从4.1.10a开始,除非和主函数之间有辅助的链接,否则服务器将不再默认地加载用户自定义函数(UDFs),也可以通过
--allow-suspicious-udfs 选项来启用

客户端部分:

mysqldump 默认启用 --opt 和 --quote-names 选项

SQL部分:

不兼容的变化:字符串根据标准SQL来比较,如上面的"服务器变化"部分中提到的

不兼容的变化:TIMESTAMP 返回 'YYYY-MM-DD HH:MM:SS' 格式的字符串。在MySQL 4.0中,可以增加选项
--new 来获得MySQL 4.1中这方面的特性

不兼容的变化:二进制数据例如 0xFFDF 被当成字符串而非数字

不兼容的变化:在MySQL
4.1.1前,语句解析器不是那么严格,它在处理字符串转时间转换时会忽略第一个数字前的其他字符。在4.1.1之后,就比较严格了

不兼容的变化:在MySQL 4.1.2,SHOW TABLE STATUS 结果的 Type 字段改名为 Engine 了

当执行多表删除语句时,要删除的表只能使用它的别名,而不能用真实表名

返回结果是 DATE, DATETIME, 或 TIME 类型的函数的结果会被转换成时间型

AUTO_INCREMENT 字段不能设定 默认(DEFAULT) 值了

LIMIT 不再接受负数参数了

SERIALIZE 不再是 sql_mode 变量的有效值了,它的取代值是 SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE

C API部分:

MySQL 4.1中的密码哈希算法做了改进以提升安全性,不过会导致兼容性问题。使用MySQL
4.0及更早版本的客户端库文件会发生问题。

不兼容的变化:mysql_shutdown() 函数增加一个参数:SHUTDOWN-level

某些函数例如 mysql_real_query() 发生错误时返回 1 而非 -1

密码处理部分:

MySQL 4.1中的密码哈希算法做了改进以提升安全性,不过会导致兼容性问题。使用MySQL
4.0及更早版本的客户端库文件会发生问题。解决办法有:

升级客户端库文件到4.1(不用升级服务器端库文件)

运行 mysql_fix_privilege_tables 脚本来加宽 user 表中的 Password
字段值,以适应新的哈希算法。如果想要允许4.1以下的客户端还能连接到服务器,那么服务器运行时要增加参数
--old-passwords

5、附录

1、) 在Windows平台上升级MySQL步骤:

备份旧数据

停止旧服务器

从windows的系统服务中删掉mysql服务,用如下命令:

C:\> C:\mysql\bin\mysqld
--remove用可执行安装文件方式安装mysql,或者解压可直接执行的二进制压缩包来安装

重新注册mysql服务,用如下命令:

C:\> C:\mysql\bin\mysqld --install 重启服务器

其他的问题详见上面提到的各种升级中会碰到的情况

2、) 升级授权表

升级授权表之前一定要备份好 mysql 数据库,以备升级失败时使用旧的授权表。

在unix或类unix系统中,运行 mysql_fix_privilege_tables 脚本来升级授权表:

shell> mysql_fix_privilege_tables 必须在 mysqld
运行着的时候执行这个脚本,它尝试使用 root 帐号来连接服务器;因此,当 root 需要密码时,用如下方式来指定密码:

shell> mysql_fix_privilege_tables
--password=root_password 在 MySQL 4.1之前,则是用如下形式来指定密码:

shell> mysql_fix_privilege_tables
root_password

接下来 mysql_fix_privilege_tables 脚本会升级授权表,在这个过程中可能会有一些 Duplicate
column name 警告信息,无需理会它们。待它运行完之后,重启一下服务器即可。

在windows平台上,授权表想要升级到4.0.15并不容易。从4.0.15开始,发行版中包含一个sql脚本:mysql_fix_privilege_tables.sql,用
mysql 客户端运行它来升级授权表,运行类似如下命令:

C:\> C:\mysql\bin\mysql -u root -p mysql

mysql> SOURCE
C:/mysql/scripts/mysql_fix_privilege_tables.sql

把上面提到的目录改成真实的目录。

3、) 升级同步

请查看我翻译的文档"6.6 升级同步"

4、) mysql_update MySQL升级时检查数据表

每次升级的时候都必须运行 mysql_upgrade
脚本。它检查了当前版本的MySQL下的所有数据库表的不兼容性,就会检查这些表;并且发现有问题时,也会修复这些表。mysql_update
同时升级了系统表,因此可以兼容新的权限机制并且使用新增的权限。

由于 mysql_update
会把检查过和修复过的表都标记上当前的MySQL版本号,因而保证了下一次在同一个MySQL版本下运行这个脚本时,都会再次报告哪些表需要修复或检查。

它还会把MySQL的版本号记录在数据文件目录下的一个文件中:mysql_upgrade.info。这个文件用于标识当前发布版本检查表时哪些表可以略过,检查时想要忽略这个文件,只需附加上
--force 选项。

为了能检查和修复数据表,并且升级系统表,mysql_update 执行了一下命令:

mysqlcheck --check-upgrade --all-databases --auto-repair

mysql_fix_privilege_tables

mysql_update 目前只支持类unix平台;在windows下,需要手工执行 mysqlcheck
命令,升级授权表请看附录"升级授权表"。

执行 mysql_update 时,MySQL服务器必须运行着,它有以下几个参数:

--help

显示帮助信息并且退出

--basedir=path

设定MySQL的安装路径

--datadir=path

设定MySQL的数据文件路径

--force

告诉 mysql_update,在检查时忽略是否存在 mysql_upgrade.info
文件,强行检查该版本的MySQL数据表,不管是否已经检查过了

--user=user_name, -u user_name

连接到MySQL的用户名,默认是 root

--verbose

冗余模式。发生问题时打印出更多的信息

其他的选项诸如 --password[=password] 是要传递给 mysqlcheck 和
mysql_fix_privilege_tables 脚本的,并不是必须的。

上一篇:mysql升级php找不到pdo


下一篇:Codeforces Round #479 (Div. 3) F. Consecutive Subsequence (简单dp)