Windows下MySQL5.6升级到MySQL5.7

1.升级前查看MySQL版本：

mysql> select @@basedir;
+-------------------------+
| @@basedir               |
+-------------------------+
| D:\mysql-5.6.51-winx64\ |
+-------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select @@datadir;
+------------------------------+
| @@datadir                    |
+------------------------------+
| D:\mysql-5.6.51-winx64\data\ |
+------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.01 sec)

mysql>
D:\mysql-5.6.51-winx64\bin>mysql -V
mysql  Ver 14.14 Distrib 5.6.51, for Win64 (x86_64)
D:\mysql-5.6.51-winx64\bin>

 

 

 

 2.将现有的mysql服务关闭。在cmd窗口，进入到mysql目录下面，将mysql服务移除。

 

D:\mysql-5.6.51-winx64\bin>net stop MySQL5.6
MySQL5.6 服务正在停止.
MySQL5.6 服务已成功停止。

D:\mysql-5.6.51-winx64\bin>mysqld.exe --remove MySQL5.6
Service successfully removed.

 3、下载最新版的MySQL5.7

下载地址：https://dev.mysql.com/downloads/mysql/
最新的mysql5.7的压缩包解压开你会发现，没有data目录和my.ini文件，跟之前的版本不一样。

 

 4、将之前mysql5.6的data目录和my.ini文件拷贝至mysql5.7下。
注意：my.ini中，版本5.6的配置，有一些在版本5.7下面已经不再使用了。
将mysql.ini文件配置做以下修改。

[mysqld]
character_set_server=utf8                                #服务端使用的字符集

# basedir = .....
basedir=D:\mysql-5.6.51-winx64                       #设置mysql的安装目录[根据本地情况进行修改]
# datadir = .....
datadir=D:\mysql-5.6.51-winx64\data                #设置mysql数据库的数据的存放目录[根据本地情况进行修改]
# port = .....
server_id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
port=3306
default-character-set=utf8
default-storage-engine=INNODB  # 创建新表时将使用的默认存储引擎

max_connections=200                 # 允许最大连接数

[mysql]
default-character-set=utf8   # 设置mysql客户端默认字符集

 

5、将mysql5.7的服务添加到windows系统的服务中，并且启动mysql服务。

 

D:\mysql-5.6.51-winx64\bin> cd /d D:\mysql-5.7.37-winx64\bin
D:\mysql-5.7.37-winx64\bin> mysqld.exe --install MySQL5.7
Service successfully installed.
D:\mysql-5.7.37-winx64\bin>

6.启动mysql服务

 

D:\mysql-5.7.37-winx64\bin>net start MySQL5.7
MySQL5.7 服务正在启动 ..
MySQL5.7 服务已经启动成功。
D:\mysql-5.7.37-winx64\bin>

7.升级mysql，升级速度具体看data目录的大小情况而定。

D:\mysql-5.7.37-winx64\bin>mysql_upgrade.exe -uroot -p
Enter password: *********
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                                 OK
mysql.engine_cost                                  OK
mysql.event                                            OK
mysql.func                                              OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                               OK
mysql.help_keyword                                OK
mysql.help_relation                                 OK
mysql.help_topic                                     OK
mysql.innodb_index_stats                       OK
mysql.innodb_table_stats                       OK
mysql.ndb_binlog_index                         OK
mysql.plugin                                           OK
mysql.proc                                             OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                        OK
mysql.slave_master_info                        OK
mysql.slave_relay_log_info                     OK
mysql.slave_worker_info                            OK
mysql.slow_log                                          OK
mysql.tables_priv                                      OK
mysql.time_zone                                        OK
mysql.time_zone_leap_second                  OK
mysql.time_zone_name                             OK
mysql.time_zone_transition                       OK
mysql.time_zone_transition_type              OK
mysql.user                                                OK
Upgrading the sys schema.
Checking databases.
aaa.course                                               OK
aaa.sc                                                      OK
aaa.student                                             OK
aaa.teacher                                             OK
sys.sys_config                                         OK
Upgrade process completed successfully.
Checking if update is needed.

D:\mysql-5.7.37-winx64\bin>。

8.升级成功后再次停止、启动服务

 

验证升级后的MySQL信息和数据。

D:\mysql-5.7.37-winx64\bin>mysql -uroot -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();

+-----------+
| version() |
+-----------+
| 5.7.37    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+-------------------------+
| @@basedir               |
+-------------------------+
| D:\mysql-5.6.51-winx64\ |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@datadir;
+------------------------------+
| @@datadir                    |
+------------------------------+
| D:\mysql-5.6.51-winx64\data\ |
+------------------------------+
1 row in set (0.00 sec)

mysql>

 到此升级完成。

====================================================

注意：当升级成功之后，执行sql脚本，可能会报：Table 'performance_schema.session_status' doesn't exist，这是因为以前的data是mysql5.6中的，与新装的mysql是5.7的之间版本不一致导致，解决办法是：

第一步：在管理员命令中输入：

mysql_upgrade -u root -p --force

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

net stop MySQL5.7

net start MySQL5.7

再次运行mysql，就解决了。

 

Linux下的示例：

[root@dbserver ~]# /usr/local/mysql/bin/mysql_upgrade -u root -p  -s --force
Enter password:
The --upgrade-system-tables option was used, databases won't be touched.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
[root@dbserver ~]#
或者

[root@dbserver ~]# /usr/local/mysql/bin/mysql_upgrade -u root -p   --force
Enter password:
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@dbserver ~]#