Mysql升级后如何导入之前低版本数据——mysql5数据导入到mysql8

Mysql升级后如何导入之前低版本数据——mysql5数据导入到mysql8

V+变量
2025-03-24 / 0 评论 / 22 阅读 / 正在检测是否收录...

Mysql8是很多服务器使用的mysql版本,但一些运行的老网站由于内存或者之前版本问题,使用的都是老旧的5.x版本。

理论上的两种升级方式:

1)inplace就地升级

大致思路:在一台服务器上,进行原版本升级到新版本,风险较大。如果是主从环境可以先就地升级从库,然后再升级主库,滚动方式升级。

1)逻辑迁移升级

大致思路:准备新的服务器,然后将数据从源库中逻辑导出,然后再导入到新的数据库中,数据量大的时候,会很慢。例如:
如果是一主一从(主->从1),在有条件的情况下,可以新准备一台服务器,采用物理备份的方式将数据备份恢复到新的服务中,然后构建从库的从库(主->从1->从2),最后将从2进行inplace方式升级,然后进行业务验证,验证完成后构建主->从2。升级从1,将(主->从1)的从1断开,从1升级完成后,构建(主->从1,主->从2),此时可以升级主库,主库停止写业务,主从关系变更为(从1->从2)原从1承担写库,待主库完成升级后重新加入主从即可。
本次采用就地升级的方式,即直接使用原来的data目录

1. 流程概述

在将MySQL 5数据库导入到MySQL 8之前,我们需要完成以下几个步骤:

步骤描述
1.备份MySQL 5数据库
2.安装MySQL 8
3.迁移MySQL 5数据库
4.测试迁移结果
5.错误排查

下面我们将逐步介绍每个步骤需要做什么,并提供相应的代码示例。

2. 备份MySQL 5数据库

在进行数据库迁移之前,我们需要先备份MySQL 5数据库以防止数据丢失。可以使用以下命令来备份数据库:

$ mysqldump -u <username> -p<password> <database_name> > backup.sql

其中,是MySQL 5数据库的用户名,是该用户的密码,<database_name>是要备份的数据库名。上述命令将会将数据库备份文件保存为backup.sql。

3. 安装MySQL 8

在迁移数据库之前,您需要先安装MySQL 8。根据操作系统和个人喜好选择合适的安装方式。安装完成后,请确保MySQL 8服务已经启动。

4. 迁移MySQL 5数据库

在将数据库从MySQL 5迁移到MySQL 8之前,我们需要创建一个新的数据库来存储迁移后的数据。可以使用以下命令在MySQL 8中创建一个新的数据库:

CREATE DATABASE <new_database_name>;

其中,<new_database_name>是您想要创建的新数据库的名称。

接下来,我们可以使用以下命令将MySQL 5备份文件导入到MySQL 8中:

$ mysql -u <username> -p<password> <new_database_name> < backup.sql

这将会将备份文件中的数据导入到新的MySQL 8数据库中。

5. 测试迁移结果

完成数据库迁移后,我们需要测试迁移结果以确保数据正确导入。可以使用以下命令登录到MySQL 8并查看数据:

$ mysql -u <username> -p<password> <new_database_name>

之后,您可以执行SQL查询来验证数据是否正确导入。

6. 错误排查

1)sql_require_primary_key未定义

打开要导入的.sql文件,找到下面代码,在后面加入set sql_require_primary_key = 0;即可

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

最终代码为

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
set sql_require_primary_key = 0;

2)错误号为1418的错误

导入sql文件时会报错,函数无法创建
调用存储过程或者函数以及触发器时,会出现错误号为1418的错误:
解决方法:在my.ini中的[mysqld]下添加配置:log-bin-trust-function-creators=1,即可关闭。

3)mysqldump还原utf8mb4数据库的时候遇到报错

mysqldump: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

查看

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.73, for debian-linux-gnu (x86_64)

还是MySQL 5.5.73的很老版本,在网上下载了MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar,解压后得到MySQL-client-5.6.17-1.el6.x86_64.rpm(只安装Client也可以只获取这一个文件)。
因为权限和版本兼容性的原因,需要在.sql文件中去掉

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='16283487-45bd-11e7-9ef2-7cd30ac3f3fe:1-299493847,
43c1dc25-dbbe-11e5-a66f-a0d3c1f93abc:1-543829194,
500866a4-e70d-11e8-802d-7cd30adb113a:1-416157186,
5fd4db5e-fc00-11e6-be1d-a0d3c1f43dd0:1-93905767,
6567f578-fecb-11e7-95aa-7cd30adb15da:1-18921416,
72617ba5-eca2-11e9-a9e9-7cd30ae00d0e:1-704516432,
83fdc286-bbba-11e7-a056-a0d3c1f8397c:1-12603194,
8d21b36e-b6e5-11e6-bb7e-a0d3c1f8397c:1-41968137,
a0bcb219-2379-11e5-b4d9-a0d3c1f43dd0:1-3390044,
a7f7b8d7-da4f-11ea-b7c2-506b4b4197cc:1-828737650,
b667cab5-1e31-11e5-926a-a0d3c1f93abc:1-1814845499,
c0e956af-d2bd-11eb-8bbb-7cd30adb159e:1-116084394,
c37acaf4-1e31-11e5-926b-a0d3c1f43dd0:1-2289807,
e73f79ba-2a90-11e5-a317-a0d3c1f43dd0:1-1425172325';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

这些内容,可以手工编辑,也可以用Linux命令sed来批量删除:

sed -i '17,37d' /sql-backup/$i-2021-7-22.sql
sed -i '/SQL_LOG_BIN/d' /sql-backup/$i-2021-7-22.sql

4)备份的时候遇到表过大报错的问题:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `kor_postcode` at row: 4646629

这个表有2G多数据,1G多索引。
采取的办法:RDS的MySQL参数net_read_timeout从30改为120还是不行,mysqldump添加--quick 参数还是不行,添加参数--max_allowed_packet=2000M也还是不行,又按照这篇中改了--compress --skip-lock-tables --single-transaction --skip-extended-insert等都还是不行,最后是修改了ECS上/etc/my.cnf,添加两行:

[client]
max_allowed_packet=1024M

再次运行mysqldump就可以了。

5)还原的时候遇到主键等重复的报错:

Warning: Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 4410: Duplicate entry 'pass' for key 'PRIMARY'

这个问题在search_total和其它表都有可能出现,特别是一些英文以外的字符,有可能是与字符集有关,但也没有找到具体解决办法。
尝试手工去打开.sql文件,找到对应的行、对应的字符去修改或者删除,但又是太多了不好找,而且vim打开、查找都非常慢。
后来找了一些资料,干脆把这种重复的覆盖或者忽略,办法是在mysqldump命令中添加--replace或者--insert-ignore参数。实测这样导出的.sql文件是可以用mysql命令正常导入的。
最后的语句是这样的:

mysqldump -h hostname -u username -ppassword --opt --default-character-set=utf8mb4 --set-gtid-purged=OFF --replace database > database.sql
mysql -h hostname -u username -ppassword -f -D database < database.sql

7.结论

通过按照上述步骤,您将能够成功将MySQL 5数据库导入到MySQL 8中。请确保在执行每个步骤时使用正确的命令和参数。

尽管本文提供了一些基本的代码示例,但您可能还需要根据具体情况进行一些调整和修改。如果在迁移过程中遇到任何问题,请查阅MySQL官方文档或寻求专业开发者的帮助。

0

评论 (0)

取消