MySQL5.6逻辑升级到MySQL8.4的踩坑记录

MySQL5.6到MySQL8.4区别过大由于我升级的数据库结构比较简单只选择了MySQL5.7作为跳板按照官方的推荐流程应该是MySQL5.6-MySQL5.7-MySQL8.0-MySQL8.4MySQL的逻辑升级通俗的说就是把数据库里的数据导出后导入版本高的数据库中相对的本地升级是直接在原有的数据库上做版本升级一、安装注意事项MySQL 官网提供两种安装包- 标准安装包.msi 或 .dmg- 免安装压缩包解压即用如果使用免安装包需要手动添加 my.ini 配置文件否则服务无法启动。二、导出命令1.MySQL5.6-MySQL5.7导出命令mysqldump --all-databases --single-transaction --routines --triggers --events --default-character-setutf8mb4 --hex-blob -u root -p --result-filexxx.sql--all-databases导出整体数据库在MySQL5.6升级到MySQL5.7的过程中可以使用MySQL8.4不允许直接导入系统库也就无法使用了--single-transaction保证innoDB表的数据一致性导出的即执行命令这个时点的数据在导出过程中他人修改数据库产生的增量是不会导出的使用该命令最好选择业务低峰期或者将数据库修改为只读--default-character-setutf8mb4 和--result-filexxx.sql确定生成的sql文件的编码格式使用powershell默认导出的文件编码会变成utf16无法正常导入--result-file别忘客户没用result-file跑来问为什么导出来的编码还是utf16的,导入高版本时建议也把--default-character-set加上--hex-blob保护二进制数据用的将二进制数据变成十六进制存放保证数据一致2.MySQL5.7-MySQL8.4导出命令由于MySQL8.4不允许直接修改系统库所以只能拆分成数据和用户及权限两个部分的sql文分别导入了数据的导出和5.6到5.7的命令差不多就把--all-databases改成--databases 数据库名 就行了导出用户的命令mysqlpump -u root -p --exclude-databases% --users --exclude-usersmysql.sys,mysql.session,mysql.infoschema,root --result-fileusers.sqlmysqlpump,别写成mysqldump了--exclude-databases%所有数据库不导出--users导出所有用户--exclude-users...排除这些用户三、关于用户的密码先导入MySQL5.6的数据再去执行mysql_upgrade命令MySQL5.7将原来存放在Password列的密码换到了authentication_string列而MySQL5.7自带的mysql_upgrade命令能自动将密码换到authentication_string列而MySQL8.4默认使用caching_sha2_password,所以导入8.4前在my.ini文件中[mysqld]后添加上mysql_native_passwordON重启MySQL服务(其实这步多余了升级后还是要明文重新设置密码旧密码的哈希值没用了)四、关于字符集和排序规则升级完执行sql文会发现提示要把utf8mb3升到utf8mb4根据原来的数据库选择utf8mb4_general_ci还是utf8mb4_0900_ai_ci我这边原先使用的就是utf8_general_ci就直接选择utf8mb4_general_ci做升级了升级之后记得检查不直接写collate utf8mb4_general_ci很容易被误认成utf8mb4_0900_ai_ci等其他规则collation_connection默认设置是utf8mb4_0900_ai_ci在执行 WHERE 列名 中文 这类查询时会提示utf8mb4_0900_ai_ci和utf8mb4_general_ci的冲突这点实在没啥办法只能在连接设置里写个预执行语句SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;保证打开workbench就将collation_connection设置成utf8mb4_general_ci五、关于安全模式我使用的是workbench8.0.36在Perferences里的SQL Editor可以设置关闭Safe Updates刚升级完没必要强求使用安全模式先保证业务能正常执行六、关于零日期刚升级完先别急着修改可以先把sql_mode改回去保证业务正常后再考虑将零日期修改成合法日期