关于Window mysql5.5升级至5.7.36的详细步骤

Mysql升级步骤操作如下：
1.首先备份原文件，并卸载原Mysql
2.进入官网下载预安装版本
3.写入并修改my.ini文件
4.cmd命令下安装及修改密码
5.尝试客户端登录mysql
5.将原data文件覆盖到安装路径下
6.cmd下初始化操作，升级数据字典

1.备份原文件，并卸载原Mysql

1.1 备份data及安装文件

1.1.1 先停止mysql服务

1.1.2 找到mysql安装路径

因为我自己创建的数据库并不在安装路径下的data内存储，所以要先通过my.ini配置文件找到自己数据库data存放地址，然后将这里的data数据和mysql安装路径下所有文件备份

1.2 完全卸载mysql

以管理员身份打开cmd ，执行命令mysqld -remove，移除现有mysql服务。(注意，最好在执行cmd命令时，都以管理员身份运行，以避免权限不足带来的麻烦)

1. 命令删除服务 sc delete mysql

2. 删除文件所在位置的全部文件夹，即原安装路径下的文件，以及data数据

3. 删除注册表。这三个目录下的 services–>eventlog–>system–>mysql文件
   
   （这个注册表内的mysql,我并没有找到，可能在移除服务时，就自动删除了）

2.下载预安装版本，并解压

2.1 下载新版本

进入官网下载5.7最新版本
https://downloads.mysql.com/archives/community/

2.2 解压安装文件

3. 配置my.ini文件

新建一个my.ini文件，并配置该文件

# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
 
port=3306
 
[mysql]
 
default-character-set=utf8
 
 
# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]
 
# The TCP/IP Port the MySQL Server will listen on
port=3306
 
 
#Path to installation directory. All paths are usually resolved relative to this.
basedir="F:/java1/mysql-5.7.36-winx64"
 
#Path to the database root
datadir="F:/java1/mysql-5.7.36-winx64/data"
 
# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8
 
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB
 
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
 
# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100
 
# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0
 
# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache=256
 
# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=35M
 
 
# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8
#*** MyISAM Specific options
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=69M
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=55M
 
# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K
 
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K

注意修改路径basedir是指mysql安装路径，datadir是存放数据库文件路径
basedir=“F:/java1/mysql-5.7.36-winx64”
datadir=“F:/java1/mysql-5.7.36-winx64/data”

3.1 修改环境变量

4. 导入data文件

将原data数据库文件复制到安装路径下

5. cmd命令下安装及修改密码

以管理员身份打开cmd,执行以下操作
1. 移除mysql

mysqld -remove

2. 安装mysql

mysqld -install

3. 初始化mysql。

初始化成功会在my.ini所在的文件目录下自动生成date文件夹，此时文件夹内什么都没有。

mysqld --initialize --console

此时，生成了一个临时密码，可以记一下，不过接下来也可以直接修改密码。

4. 启动mysql服务

net start mysql

如果出现以下错误：

把date文件夹内的所有错误文件、log文件都删除后。错误没有了。
再启动服务，就启动成功了！

4.2修改密码

复制别人的，按着操作就行了

1、开始 → 搜索栏里面输入cmd → 右键cmd.exe选择以管理员的身份运行（亦可以在C:\Windows\System32目录下找到这个cmd.exe，右键，以管理员身份运行）

2、输入net stop mysql停止MySQL服务

3、输入命令行来到mysql的bin目录下，输入下列粗体命令（文件位置根据自己实际情况更换）

C:\MySQL\bin>mysqld --defaults-file=“C:\MySQL\my.ini” --console --skip-grant-tables

等一下，显示出以下结果说明MySQL启动:

170215 22:26:09 [Warning] The syntax ‘–log’ is deprecated and will be removed inMySQL 7.0. Please use ‘–general_log’/’–general_log_file’ instead.
　　170215 22:26:09 [Warning] The syntax ‘–log_slow_queries’ is deprecated and will be removed in MySQL 7.0. Please use ‘–slow_query_log’/’--　 slow_query_log_file’ instead.
　　170215 22:26:09 [Warning] The syntax ‘–log’ is deprecated and will be removed in MySQL 7.0. Please use ‘–general_log’/’–general_log_file’ instead.
　　170215 22:26:09 [Warning] The syntax ‘–log_slow_queries’ is deprecated and will be removed in MySQL 7.0. Please use ‘–slow_query_log’/’–slow_query_log_file’ instead.
　　170215 22:26:09 [ERROR] The update log is no longer supported by MySQL in version 5.0 and above. It is replaced by the binary log. Now starting MySQL with --log-bin=’’ instead.
　　170215 22:26:09 InnoDB: Started; log sequence number 0 324221
　　170215 22:26:09 [Note] mysqld: ready for connections.Version: ‘5.1.33-community-log’ socket: ‘’ port: 3306 MySQL Community Server (GPL)

4、再以管理员的身份打开一个cmd.exe，输入命令行来到mysql的bin目录下，输入：mysql -uroot mysql

5、进入mysql之后，输入命令行修改密码：

mysql>update user set authentication_string=password(‘123456’) where user=‘root’；

6、刷新权限：mysql>flush privileges;

7、退出mysql：mysql> quit;

8、关闭MySQL：D:\MySQL\bin>mysqladmin shutdown(出现错误，则：mysqladmin -uroot -p shutdown 然后输入新密码)

9、至此修改密码完成，可以输入命令行：net start mysql 启动MySQL服务，mysql -uroot -p ，输入密码就可以进入mysql了。

6. 解决版本冲突问题

在管理员命令中输入，以升级数据字典：

mysql_upgrade -u root -p --force

第二步：重新启动mysql的服务：

net stop mysql

net start mysql

再次运行mysql

7. 客户端登录mysql

一般到这就能登录成功了，下面是一些可能出现的问题，及解决方案

管理员身份下运行mysqld --console 出现以下错误，

The innodb_system data file ‘ibdata1‘ must be writable

是相关文件权限不足导致的，可以将
这三份文件通过修改文件权限解决：右键属性->安全->设置最高级权限

修改后重启mysql服务，再尝试命令，就不会有错误了。

通过cmd可以用密码进入mysql,但在用sqlyog登录mysql时出现了以下错误：
解决方案如下链接：
链接
注意这里再紧接着一行修改密码

update mysql.user set authentication_string=password('这里是你的新密码') where user='root' ; 

不然就会出现下面的错误。。

若出现了密码错误的问题

这里再次通过cmd窗口进入mysql,
并修改密码
mysql -uroot -p ，输入密码就可以进入mysql了。
然后修改密码

update mysql.user set authentication_string=password('这里是你的新密码') where user='root' ; 

之后打开服务，找到mysql重启服务后就能进入了。
如果密码修改不了，就详细按照这个操作：
链接

参考文档：
https://blog.csdn.net/weixin_42132325/article/details/114475122
https://blog.csdn.net/weixin_39717865/article/details/110870157