mysql 8.0 降级为 5.7 的数据处理及优化

前段时间不知为何，脑子一热把数据库升级成了8.0版本，但无奈服务器内存太小承受不来，于是打算再降回5.7。

本以为会花费很久时间，尝试之后发现仅用了一两个小时就搞定了，时间主要用在对数据的处理环节。对于数据库的安装、卸载本身不做详述，主要详细说明一下数据的处理、转移过程。

第一步：数据备份

有宝塔面板的，可以直接使用面板的批量备份功能：

Chevereto

没有宝塔面板，用命令行备份成SQL格式的文件即可。

mysqldump -u 用户名 -p 数据库名 > 文件名.sql

第二步：数据处理

首次检索

首先检索了一下网上给出的解决方案，大概是说：需要将8.0中专属的内容替换为5.7中的内容，包括：

utf8mb4_0900_ai_ci 替换为 utf8_general_ci
utf8_croatian_ci替换为 utf8_general_ci
utf8mb4_general_ci替换为utf8_general_ci
utf8mb4 替换为 utf8

甚至有人为了方便，用下面的语句直接删掉了 AUTO_INCREMENT= 后面的内容：

sed 's/AUTO_INCREMENT=[0-9]*\b//g' data.sql > converted_data.sql

经过再次检索核实，其实mysql 5.7已经支持了utf8mb4，所以个人认为上面直接替换为utf8有些过了，并没有这个必要。只需把 utf8mb4_0900_ai_ci 这一种类型替换掉即可，否则在导入sql文件时，会出现Unknown collation: 'utf8mb4_0900_ai_ci'的错误。

chatgpt

又问了一下chatgpt，它给出了一个执行的ssh脚本：

#!/bin/bash

# 替换引擎名称
sed -i 's/ENGINE=InnoDB/ENGINE=MyISAM/g' your_backup_file.sql

# 替换新的数据类型为旧的数据类型
sed -i 's/JSON/TEXT/g' your_backup_file.sql

# 替换WITH选项
sed -i 's/WITH SYSTEM VERSIONING/ /g' your_backup_file.sql

# 替换带有UTF8MB4字符集的COLLATE选项
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' your_backup_file.sql

# 替换新的SQL语法为旧的SQL语法
# 例如：RENAME TABLE tbl_name TO new_tbl_name
# MySQL 8.0: RENAME TABLE tbl_name TO new_tbl_name
# MySQL 5.7: RENAME TABLE tbl_name TO new_tbl_name
sed -i 's/RENAME TABLE/RENAME TABLE/g' your_backup_file.sql

# 替换新的配置设置为旧的配置设置
# 例如：SET PERSIST connect_timeout = 10
# MySQL 8.0: SET PERSIST connect_timeout = 10
# MySQL 5.7: SET GLOBAL connect_timeout = 10
sed -i 's/SET PERSIST/SET GLOBAL/g' your_backup_file.sql

# 在这里添加其他必要的替换操作

echo "Conversion completed successfully."

这个脚本虽然比较全面，但仔细分析，还有些不妥之处。比如：mysql 5.7版本也支持 ENGINE=InnoDB 这个引擎、替换数据类型JSON很容易造成误伤（正文中很可能含有这个单词），甚至后面替换语法 RENAME TABLE 那里，我都一度怀疑是眼神出了问题。

最终替换方案

根据检索的内容以及chatgpt给出的代码，我考虑尽可能减少对原始数据的改动，因此我决定只把utf8mb4_0900_ai_ci 替换掉，也就是利用chatgpt给出的这个代码：

# 替换带有UTF8MB4字符集的COLLATE选项
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' backup_file.sql

考虑到在原来的内容文本中，可能会存在“utf8mb4_0900_ai_ci”被错误替换掉，因此将这个语句拓展了一下，将替换的内容额外输出，替换完后再核对一下。同时，在替换的时候尽量不要破坏原有的备份文件，防止替换过程中出现问题，因此需要将文件另存。

优化后的语句如下，只有这一行：

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/gw modified.txt' backup_file.sql > new_backup_file.sql

此外，建议在服务器使用命令行直接运行替换（

因为sed语句是一行一行的检索，对于服务器来说可以轻松完成

）。如果担心敲代码不够直观，想下载到本地电脑使用文本编辑器进行查找替换，那么这个备份文件最好不要太大。对于上百兆的文件这样操作的话，对电脑来说会非常吃力，使得执行效率大打折扣。

第三步：mysql卸载8.0，安装5.7

第四步：将修改后的备份文件导入数据表

使用宝塔面板可以轻松完成，具体过程不再详述。

祝君一次成功！