Linux上MySQL8+需要更改的一些配置项

前段时间公司项目从windows服务器迁移到Linux服务器，自己负责数据的迁移和Mysql配置，以下整理了一下遇到的报错：

一、导入sql文件时的报错

1.导入的sql中含有函数的创建时，会报错：

ERROR 1418 (HY000) at line 18248: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
解决：mysql> SET GLOBAL log_bin_trust_function_creators = 1;  

2.导入的sql中行数据太大，会报错：

ERROR 1118 (42000) at line 2187: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

解决：关闭严格模式

打开MySQL安装目录下的my.cnf（如果找不到可以搜索）

在[mysqld]下面加上innodb_strict_mode=0

保存并重启mysql

二、项目启动后tomcat报错

1.javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify

解决：在数据库连接配置文件中，配置连接数据库的url时，加上useSSL=false
jdbc:mysql://127.0.0.1:3306/daservicedb?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false

2.sql_mode的ONLY_FULL_GROUP_BY模式默认设置为打开状态，会报错：

Expression #1 of ORDER BY clause is not in SELECT list, references column 'xxxx' which is not in SELECT list; this is incompatible with DISTINCT

select version(); 查询版本
select @@global.sql_mode; 查询sql_mode
去除ONLY_FULL_GROUP_BY

vim/etc/my.cnf
文件底部追加:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
保存并重启mysql