逐浪吧｜搏浪号博客 - sed
type: channel
link: https://t.imsail.com/tag/sed/
items: mysql 8.0 降级为 5.7 的数据处理及优化

mysql 8.0 降级为 5.7 的数据处理及优化
type: item
date: 2024-03-05T17:37:00+08:00
description: 前段时间不知为何，脑子一热把数据库升级成了8.0版本，但无奈服务器内存太小承受不来，于是打算再降回5.7。本以为会花费很久时间，尝试之后发现仅用了一两个小时就搞定了，时间主要用在对数据的处理环节。对于数据库的安装、卸载本身不做详述，主要详细说明一下数据的处理、转移过程。第一步：数据备份有宝塔面板的，可以直接使用面板的批量备份功能：没有宝塔面板，用命令行备份成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_…