mysql jdbc5.1.7垃圾,Windows下 MySQL 5.1升级5.7实战（不太成功）

Windows下 MySQL 5.1升级5.7实战(不太成功)

需求描述

想在windows下想跑一个开源工程，这个web工程因为涉及到对emoji表情符的存储，所以数据库要支持UTF8-MB4字符集，当前的MySQL 5.1 版本是不支持这个字符集的，根据提示需要将MySQL升级到5.6或者5.7，但是目前数据库中有一些数据，说重要也不重要，抱着尝试的目的试一试看看本地升级能否成功。

做了哪些动作

简单翻了下官方升级文档以及网上的参考，升级主要有两种方式，一种是使用安装包程序installer 点开dashboard按提示安装就行，还有一种是压缩包解压，我选的是第二种。

看到网上有一种说法，有原地升级以及逻辑升级两种方式，

所谓原地升级就是将本地的文件替换，简单粗暴。

逻辑升级是使用mysqldump工具将数据库导出，然后再5.7中重新导入。

为了省事，我当然选择第一个方法。

官网下载MySQL5.7压缩包，解压到待安装路径

CMD中执行 net stop mysql停止mysql服务，使用sc stop mysql也可

执行mysqld --remove mysql移除mysql服务

找到MySQL5.1的安装路径，找到data文件夹以及my.ini文件，复制到5.7的存放路径,入下图所示：

修改 my.ini文件，basedir参数后面跟的是5.7的存放路径

[client]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

# 设置3306端口

port = 3306

# 设置mysql的安装目录

basedir=D:/Program Files/mysql-5.7.31-winx64/

# 设置 mysql数据库的数据的存放目录

# datadir=C:\\web\\sqldata

# 允许最大连接数

max_connections=20

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

CMD中执行mysqld --install mysql5.7 执行安装

CMD中执行net start mysql5.7很遗憾，报错“net helpmsg 3534”

看网上评论，要进行初始化，执行mysqld --initialize --console进行初始化，然而初始也报错，报错大致意思是data文件夹中有数据无法执行初始化。

到这一步其实就很明显了，MySQL是不支持5.1到5.7使用原地升级的模式进行升级的。本来5.1版本中的数据也是可有可无的，于是把data文件夹全部删除，再次执行mysqld --initialize --console进行初始化成功

CMD中执行net start mysql5.7 服务成功拉起来

到这一步，MySQL5.7其实已经安装完成,root用户登录设置密码

mysql -u root

set password for root@localhost = password('123');

综上，5.1并没有升级到5.7，后来我又把5.1data文件夹中的数据库文件复制到了5.7的 data目录下，执行show databases;是能看到数据库的，但是如果执行查询语句就会报数据库不存在…

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| data_basefrom51 |

| jpress |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

7 rows in set (0.00 sec)

mysql> select * from sr_map;

ERROR 1146 (42S02): Table 'data_basefrom51.sr_map' doesn't exist

查阅MySQL5.7的官方文档后的一些思考

关于升级的可行方案，文档里是这么描述的：

Upgrade Paths

• Upgrade is only supported between General Availability (GA) releases.

• Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is recommended before

upgrading to the next version. For example, upgrade to the latest MySQL 5.6 release before upgrading

to MySQL 5.7.

• Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.5 to 5.7 is

not supported.

• Upgrade within a release series is supported. For example, upgrading from MySQL 5.7.x to 5.7.y is

supported. Skipping a release is also supported. For example, upgrading from MySQL 5.7.x to 5.7.z is

supported.

也就是只支持5.6–>5.7的版本升级，5.5->5.7是不支持的，遑论5.1…

升级有两种途径：使用安装程序、压缩包解压

There are two approaches for upgrading MySQL on Windows:

• Using MySQL Installer

• Using the Windows ZIP archive distribution

安装程序就是一步一步按提示点击即可，再看一下压缩包解压这种方式：

To perform an upgrade using the Windows ZIP archive distribution:

Download the latest Windows ZIP Archive distribution of MySQL from https://dev.mysql.com/ downloads/.

If the server is running, stop it. If the server is installed as a service, stop the service with the following command from the command

prompt: C:\> SC STOP mysqld_service_name

Alternatively, use NET STOP mysqld_service_name.

If you are not running the MySQL server as a service, use mysqladmin to stop it. For example, before upgrading from MySQL 5.6 to 5.7, use mysqladmin from MySQL 5.6 as follows:

C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqladmin" -u root -p password shutdown

Extract the ZIP archive. You may either overwrite your existing MySQL installation (usually located at C:\mysql), or install it into a different directory, such as C:\mysql5. Overwriting the existing installation is recommended.

Restart the server. For example, use the SC START mysqld_service_name or NET START mysqld_service_name command if you run MySQL as a service, or invoke mysqld directly otherwise.

As Administrator, run mysql_upgrade to check your tables, attempt to repair them if necessary, and update your grant tables if they have changed so that you can take advantage of any new capabilities.

看到这里也就明白了，如果是官方支持的版本升级方案，比如从5.6升级到5.7，只要把压缩包解压，停服务，覆盖原路径，启服务，执行mysql_upgrade即可，还是很方便的。非常简单。但是官方文档还是建议在升级之前要备份数据以防万一。