mysql5.6升级mysql8

extra

与此文档无关,暂记到这里

ALTER TABLE his_patient_info DISCARD TABLESPACE;
 DROP TABLE his_patient_info; 
 CREATE TABLE `his_patient_info`。。。

下载解压

windows安装mysql8(zip免安装版): mysql-8.0.34-winx64.zip

解压到

D:\vivachekcloud\mysql-8.0.34-winx64

此目录下新建data目录，用于存放数据库数据

D:\vivachekcloud\mysql-8.0.34-winx64\data

备份旧数据库

备份旧数据库sql

移除旧数据库服务

移除就数据库服务

# 进入旧数据库bin目录 D:\vivachekcloud\mysql\bin
./mysqld --remove

# 或者
sc delete MySQL

修改环境变量

修改环境变量path 和 MYSQL_HOME

path中替换mysql相关部分为： D:\vivachekcloud\mysql-8.0.34-winx64\bin
如果path中使用的是%MYSQL_HOME%\bin, 还需要改下MYSQL_HOME=D:\vivachekcloud\mysql-8.0.34-winx64

修改my.ini或my.cnf

添加my.ini配置文件.

D:\vivachekcloud\mysql-8.0.34-winx64\my.ini

内容改为如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql]

socket=D:/vivachekcloud/mysql-8.0.34-winx64/data/mysql.sock

[mysqld]
lower_case_table_names=1
#Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
user= root
basedir = D:/vivachekcloud/mysql-8.0.34-winx64
datadir = D:/vivachekcloud/mysql-8.0.34-winx64/data
#log-error=C:/vivachekcloud/mysql-8.0.34-winx64/data/mysql.err
pid-file=D:/vivachekcloud/mysql-8.0.34-winx64/data/mysql.pid
socket=D:/vivachekcloud/mysql-8.0.34-winx64/data/mysql.sock
port = 3306
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

#mysql5.6的sqlmode
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#mysql8默认的sql_mode，这里只是备份记录一下。
#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default-time-zone="+08:00"

# https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded
default_authentication_plugin=mysql_native_password

需要设定下各个目录位置。

注意：这里修改了mysql8的sql_mode,去掉了“ONLY_FULL_GROUP_BY,”用于兼容5.6下写的sql

初始化数据库实例

建数据文件夹

D:\vivachekcloud\mysql-8.0.34-winx64\data

mysql8初始化数据库实例（会在数据文件夹下，建立必要的权限表等。如果有mysql8要安装多实例，重复本节的操作即可）

mysqld --defaults-file=D:\vivachekcloud\mysql-8.0.34-winx64\my.ini --initialize  --console

windows环境下–defaults-file选项必须放在最前面（可能是个bug）， –initialize-insecure指明要初始化实例（但是没有设置密码），–console将输出和错误显示到前台。–initialize指明要初始化实例，初始密码会输出在控制台或日志中。

官网mysql5.6的zip包，默认已经初始化mysql数据文件夹。mysql8的zip包，安装时需要先 myqld --initialize初始化数据文件夹。或者也可以选择安装exe。

控制台运行或安装为服务

不安装为服务， 直接在控制台运行：

.\mysqld --defaults-file=D:\vivachekcloud\mysql-8.0.34-winx64\my.ini --console

若要安装为服务， 用mysqld --install <SERVICE_NAME> --defaults-file=/XXX/my.ini.

这里是：

.\mysqld --install MySQL --defaults-file=D:\vivachekcloud\mysql-8.0.34-winx64\my.ini

重设密码

允许root远程登陆(新开控制台窗口，或新建一个navicat mysql数据库连接， 密码填初始化mysql实例时在控制台或mysql日志中打印的临时密码)。

mysql -h 127.0.0.1 -P 3306 -uroot
use mysql;



alter user 'root'@'localhost' identified by 'Nov2014';
create user 'root'@'%' identified by 'Nov2014';

grant all privileges on *.* to 'root'@'%' with grant option;

flush privileges;


-- show variables like '%sql_mode%';
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
-- 去掉 ONLY_FULL_GROUP_BY， 写入到my.ini/my.cnf中

-- 如果 还是出现 caching_sha2_password 插件不受支持的提示, 参考: https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Nov2014';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Nov2014';

导入医院数据

重新建医院生产库，导入医院sql数据

重新打包后台服务

需要重新打包vivachekcloud.jar：

idea中 ctrl+shift+f全局搜索 rank ，限定 *.xml
所有sql中的 rank 都改为 `rank`转义掉 （这是个新关键字）

application-prod.properties中 
spring.datasource.url值中增加一个选项 &allowPublicKeyRetrieval=true