项目里一直用的是mysql5.7，这次立了个新项目，我果断换上mysql8.0，心想肯定新版的性能更好更强大啊，其实无形间也踩了不少坑，mysql8.0和mysql5.0的小改动（升级）引发的兼容性问题。。
一、mysql8.0搭建
首先是搭建mysql8.0环境，这里当然使用时下最方便的docker-compose方式了
my.cnf如下
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
#gtid:
server_id = 1 #服务器id
gtid_mode = on #开启gtid模式
enforce_gtid_consistency = on #强制gtid一致性，开启后对于特定create table不被支持
#binlog
log_bin = mysql-binlog
log_slave_updates = on
binlog_format = row #强烈建议，其他格式可能造成数据不一致
#relay log
skip_slave_start = 1
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
init_connect = 'SET collation_connection=utf8mb4_general_ci'
# Custom config should go here
!includedir /etc/mysql/conf.d/这里sql_mode必须要设置
原因是什么呢？
mysql 5.7+中 默认启用了 ONLY_FULL_GROUP_BY
这个表示什么呢，从字面上看，大概就是一种严谨的SQL模式，类似于Oracel那些Group by语句，就是你查询那些字段，group by的时候也要写上哪些字段。知道原因了，那把这个模式去掉就可以了。
如果是mysql8.0，则需要去掉NO_AUTO_CREATE_USER，因为在mysql8.0中已经没有这个模式了。
报错方式： Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY
参考文档：https://www.jianshu.com/p/9534a6878f65
#查看mysql是否启用ONLY_FULL_GROUP_BY
select @@global.sql_mode
docker-compose.yml文件如下
# 使用说明 V3.2.0
# docker-compose up
version: '3.1'
services:
mysql8-01:
container_name: mysql8-01
hostname: mysql8-01
image: mysql:latest
restart: always
ports:
- 13306:3306
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_HOST: '%'
MYSQL_ROOT_PASSWORD: root密码
MYSQL_USER: 普通账号
MYSQL_PASSWORD: 普通账号密码
command:
--default-authentication-plugin=mysql_native_password
--max_connections=1000
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--default-time-zone='+8:00'
--explicit_defaults_for_timestamp=true
--lower_case_table_names=1
--expire-logs-days=7
# privileged: true
logging:
driver: "json-file"
options:
max-size: "100m"
volumes:
- ./data:/var/lib/mysql
- ./logs:/var/log/mysql
- ./conf/my.cnf:/etc/mysql/my.cnf
- ./init:/docker-entrypoint-initdb.d参数详解：
参数1：
--default-authentication-plugin=mysql_native_password
这就是mysql8.0和mysql5.0的兼容问题：mysql5.0 加密方式默认是mysql_native_password，mysql8.0默认是 caching_sha2_password
如果想让mysql8.0用的数据可以兼容5.0，只能妥协，用mysql_native_password的方式
报错方式：2059
参数2：
--collation-server=utf8mb4_general_ci
编码也是mysql8.0升级的地方之一，mysql8.0默认用 utf8mb4_0900_ai_ci 编码
如果想让mysql8.0用的数据可以兼容5.0，只能妥协，用 utf8mb4_general_ci
二、查看并设置数据库编码
由于mysql8.0默认的编码为 utf8mb4_0900_ai_ci，而我们如果想兼容mysql5.0就得确保用的都是 utf8mb4_general_ci，如果编码没设置对，会造成里面表编码不一致导致的报错
#查看当前编码mysql> SHOW VARIABLES WHERE variable_name LIKE '%character%' OR variable_name LIKE '%collation%' ;
+-------------------------------+--------------------------------------+
| Variable_name | Value |
+-------------------------------+--------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /opt/idc/mysql8.0.23/share/charsets/ |
| collation_connection | utf8mb4_bin |
| collation_database | utf8mb4_bin |
| collation_server | utf8mb4_bin |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------------------------+
#从上面client, connection,database，results层层环节扣着，任何一个环节的字符集不兼容都会出现乱码问题。
#查看全局编码
show global variables like '%coll%'
show global variables like '%cha%'
#设置全局编码
#设置自定义列变量编码
set global default_collation_for_utf8mb4 = 'utf8mb4_general_ci'
set global collation_connection = utf8mb4_general_ci
后来发现用上面语句，重启数据库后会还原，得用以下语句设置
SET PERSIST default_collation_for_utf8mb4='utf8mb4_general_ci';
设置成如图这样即可
关于 default_collation_for_utf8mb4参数的用途：
System Variable |
|
Scope | Global, Session |
Dynamic | Yes |
SET_VAR Hint Applies | No |
Type | Enumeration |
Valid Values |
|
mysql 8.0字符集uft8mb4默认的collate 是utf8mb4_0900_ai_ci ，
这个值是参数default_collation_for_utf8mb4控制，如果创建表时只给了字符集utf8mb4,没指定collate话，就会是默认的 utf8mb4_0900_ai_ci
也就是这个参数控制新建表的默认编码，如果未设置，新建表又没指定编码，默认赋值 utf8mb4_0900_ai_ci
这个时候如果表里的其他表编码为utf8mb4_general_ci，当进行查询视图等操作时会报错
报错为：java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='；
解决方法
#给没有设置编码的表重新设置一下：
mysql> alter table table_name default character set utf8mb4 collate=utf8mb4_general_ci;
#这样设置只针对表的，但是表中字段未修改：
mysql> ALTER TABLE table_name convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 使用show table status语句查看某个库下面全部表的字符集编码
show table status from 库名;
# 但只想获取指定表的编码信息，就可利用like进行限制：
show table status from 库名 like "表名";所以提前设置好全局变量就能避免这个坑
字符集知识扩展：
数据库服务端的字符集具体要看存储什么字符
以上这些参数如何起作用：
1.库、表、列字符集的由来
①建库时，若未明确指定字符集，则采用character_set_server指定的字符集。
②建表时，若未明确指定字符集，则采用当前库所采用的字符集。
③新增时，修改表字段时，若未明确指定字符集，则采用当前表所采用的字符集。
2.更新、查询涉及到得字符集变量
更新流程字符集转换过程：character_set_client-->character_set_connection-->表字符集。
查询流程字符集转换过程：表字符集-->character_set_result
3.character_set_database
当前默认数据库的字符集，比如执行use xxx后，当前数据库变为xxx，若xxx的字符集为utf8，那么此变量值就变为utf8(供系统设置，无需人工设置)。
mysql8.0参数官方参考文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_collation_connection
关于编码问题的参考文档：
https://www.modb.pro/db/377587
https://blog.51cto.com/lhrbest/2698445
https://www.bilibili.com/read/cv10964527/
三、mysql数据迁移，初始化
1.转储下载并压缩数据库sql文件
#转储并下载数据库文件
cd /data/bak/mysqldump 原始库名 -h 原始库ip -P端口（如果是3306可省略） -uroot -p密码 --add-drop-table >/data/bak/123.sql#压缩数据库文件
tar -zcvf 123.tar.gz *.sql2.把压缩文件上传新数据库docker容器 init目录，并解压缩
3.在新数据库上新建数据库
按照原数据库编码格式新建一个数据库
#进入mysql容器
docker exec -it mysql8-01 /bin/bash
#登陆数据库
mysql -uroot -p密码
#新建数据库并设置编码
CREATE DATABASE 数据库名 CHARACTER SET utf8mb4 collate utf8mb4_general_ci;也可以在navicat等工具上新建
4.导入数据库
方法一：
（1）进入容器，登陆数据库
#进入mysql容器
docker exec -it mysql8-01 /bin/bash
#登陆数据库
mysql -uroot -P密码（2）选择数据库
mysql>use abc;（3）导入数据（注意sql文件的路径）
mysql>source /docker-entrypoint-initdb.d/abc/abc.sql;
方法二：
mysql -u用户名 -p 参数(可选) 数据库名 < 数据库名.sql
mysql -uroot -p密码 数据库名 < abc.sql
四、数据库账号权限设置
1.限制账号登陆ip
#1.登陆mysql
mysql -u root -p
#2. 切换数据库到mysql
show databases;
use mysql;
#3.查询现有账号的host权限
select host,user from user where user='root';
#4.修改现有账号的host权限，%代表无限制，可以设置成IP地址，或者IP+%的方式：192.168.1.% ，user可以设置已有的任何账号，localhost表示只能本机登陆，不能远程登陆
update user set host = '%' where user='root' and host='localhost';
#4.确认登陆IP已修改
select host, user from user where user='root';
#5.使修改生效
flush privileges;2.给普通账号赋权
这里要说一个概念性问题
（1）在mysql中 'root'@'%'被认为是一个完整的账号
（2）在高版本mysql（mysql8）中已不支持通过grand命令新建账号
这样就会导致一个问题，就是mysql8中想通过grand 修改账号的登陆IP是行不通的，mysql会报错
报错为：MySQL8.0 ERROR 1410 (42000): You are not allowed to create a user with GRANT
实质问题原因是：授权语法不严谨导致所授权的用户及其所在主机名，要与实际存在的用户及其主机名一致。
如果已建用户是:testuser@localhost,你要授权给testuser@%,那肯定不行，必须一样才行。
所以主机名不一致的需要先修改主机名或者修改授权SQL语句。
也就是说此时在mysql的逻辑里，修改账号的登陆IP等于新建账号，而grand语法已不支持新建账号
这个时候要么就按第一步修改登陆IP再赋权，要么就修改赋权语句给现有账号和IP
以下是赋权操作
#1.登陆mysql，并切换到mysql数据库
mysql -u root -p
use mysql;
#2.创建账号（有普通账号这步可以省略）
create user 'aaa'@'192.168.1.1' identified by 'password';
#3.给账号赋权
#给账号aaa赋指定库里所有表的所有操作权限,并带有赋权权限
grant all privileges on 数据库名.* to 'aaa'@'192.168.1.1' with grant option;
#给账号aaa赋指定库里所有表的增删改查权限
grant select,insert,update,delete on 数据库名.* to 'aaa'@'192.168.1.1';
#给账号root赋予所有库里所有表的所有权限
grant all privileges on *.* to 'root'@'%' with grant option;
#4.使修改生效
flush privileges;
#5.查看账号权限
show grants for 'aaa'@'192.168.1.1'
创建只读账号示例：
#查看现有用户
SELECT User,Host FROM mysql.user;
#创建只读用户，10.122.201.%是ip段
create user 'rouser'@'10.122.201.%' identified by '123456';
#赋权相关库只读权限
GRANT SELECT ON 库名.* TO 'rouser'@'10.122.201.%';
#刷新缓存，使用户生效
flush privileges;参考：
https://www.yzktw.com.cn/post/982778.html
https://www.yzktw.com.cn/post/1010441.html
扩展：
撤销已经赋予给 MySQL 用户权限的权限。
revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
```sql
grant all on *.* to dba@localhost;
```
```sql
revoke all on *.* from dba@localhost;
```
更多参考：https://zhuanlan.zhihu.com/p/148525190
另外，mysql5升到mysql8还会遇到的报错为
java.sql.SQLException: Unknown system variable 'query_cache_size'
这个报错是驱动问题，原因是mysql-connector-java的版本过低，需要升级版本到8.0以上
