Mysql5.x更新到Mysql8.x

2022-01-10 12:32:43

mysql5.x更新到mysql8.x

mysql在大一时,稀里糊涂安装了,到现在都不清楚具体流程,最后刚好又在学习mysql,于是想着把mysql5.x更新到mysql8.0,刚开始以为只是简单的版本迭代,没想到花了一个多小时进去,建议先阅读目录在进行操作

!数据备份!

操作:一定要记得数据备份,不管是否重要,以防万一.刚开始我只是备份了Mysql5.x文件中data文件夹,但到后面才发现Navicat有一个自带的备份功能,更好用一些

卸载当前的mysql5.x

操作:

进入计算机管理(此电脑右击-管理-服务-[Mysql服务名]-停止)
进入注册表(win+r - regedit), HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL文件夹删除
删除Mysql5的全部文件
卸载MySQL Server5.0服务
环境变量Path查看是否有Mysql5.x/bin的变量,如果有删除

官网下载mysql8.x

操作:

进入官网下载https://dev.mysql.com/downloads/mysql/,有两种安装方式, .zip压缩包与.msi(Installer),这里选用.zip安装包.下载结束之后解压

初始化mysql

操作:

操作分为4步;

? 1. 以管理员身份打开cmd.exe,切换到Mysql8\bin的路径.[错误 :如果发现cd无法切换目录,直接使用盘符例 F:,再cd具体路径中]

? 2.在bin同级目录下新建my.ini文件,文件写入初始化内容,需要注意的是目录需要设置为自己的安装目录.

[mysql]
 
# 设置mysql客户端默认字符集
 
default-character-set=UTF8MB4
 
[mysqld]
 
#设置3306端口
 
port = 3306
 
# 设置mysql的安装目录
 
basedir=F:\Mysql8\mysql-8.0.23-winx64
 
# 设置mysql数据库的数据的存放目录
 
datadir=F:\Mysql8\mysql-8.0.23-winx64\data
 
# 允许最大连接数
 
max_connections=200
 
# 服务端使用的字符集默认为8比特编码的latin1字符集
 
character-set-server=UTF8MB4
 
# 创建新表时将使用的默认存储引擎
 
default-storage-engine=INNODB

F:\Mysql8\mysql-8.0.23-winx64\bin>以下操作在此目录级操作

先删除之前存在的服务 "mysql"

F:\Mysql8\mysql-8.0.23-winx64\bin>sc delete mysql

执行初始化,这一步会根据my.ini文件进行一些初始化

F:\Mysql8\mysql-8.0.23-winx64\bin>mysqld --initialize --console

initialize执行初始化, console控制台可在dos窗口显示执行信息,成功执行成功后,会给出默认的账户信息 A temporary password is generated for root@localhost: FGeoQ5yk+i#W

  F:\Mysql8\mysql-8.0.23-winx64\bin>mysqld --initialize --console
  2021-02-08T08:04:23.222141Z 0 [System] [MY-013169] [Server] F:\Mysql8\mysql-8.0.23-winx64\bin\mysqld.exe (mysqld 8.0.23) initializing of server in progress as process 2644
  2021-02-08T08:04:23.232760Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  2021-02-08T08:04:30.132195Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  2021-02-08T08:04:39.768145Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: FGeoQ5yk+i#W

安装新服务 mysqld --install [服务名] ,默认是mysql
```
F:\Mysql8\mysql-8.0.23-winx64\bin>mysqld --install
```
启动服务 net start mysql

4.initialize操作生成的随机密码,需要先登录到mysql服务的修改密码,

mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码

mysql -h localhost -P3306 -uroot -pFGeoQ5yk+i#W

登录成功后修改密码:ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘新密码‘;

可能出现的问题:

? 一 :看到有人使用utf8作为默认字符集,在后续初始化过程中,会出现警告:

[Warning] [MY-013242] [Server] --character-set-server: ‘utf8‘ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.

UTF8MB4 与UTF-8的区别:

UTF8MB4 : most bytes 4,兼容四字节的unicode

UTF-8(utf8mb3):utf8 编码最大字符长度为 3 字节

根据官网的介绍utf8mb3字符集已经被弃用

The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead. Although utf8 is currently an alias for utf8mb3, at some point utf8 is expected to become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.

二:在执行mysqld --initialize --console出错

mysql8.0需要删除data先文件夹,再执行

2021-02-08T07:56:54.992590Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2021-02-08T07:56:54.994413Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-02-08T07:56:54.996565Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-02-08T07:56:54.998371Z 0 [System] [MY-010910] [Server] F:\Mysql8\mysql-8.0.23-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.

navicat连接

错误:Navicat 连接MySQL数据库出现错误2059 - authentication plugin ‘caching_sha2_password‘

原因:Navicat不支持MySQL的登录账户加密方式"caching_sha2_password",修改加密方式为"mysql_native_password"

操作:进入mysql服务端 ALTER USER ‘root‘@‘localhost‘ IDENTIFIED WITH mysql_native_password BY ‘用户名‘;

Mysql5.x更新到Mysql8.x

码农公寓