MySQL 5.x数据迁移到MySQL 8.0

由 James Qi, 2021年7月23日

很多年前一直在使用MySQL 5.x，前几年开始用阿里云RDS的时候，也是使用的MySQL 5.5、5.6、5.7，最近有Aliyun RDS陆续到期，看到新购RDS有MySQL 8.0的选项，再查阅一些资料，说MySQL 8.0性能上比5.x有大幅提高，而且5.x逐步也不再被MySQL官方和阿里云支持，想到如果直接续费3年的话，老版本还有几年都无法升级，就干脆这次新购MySQL 8.0版本的RDS，把老数据进行迁移，下面是迁移中遇到一些问题的记录。

我们采取的是使用mysqldump命令备份老RDS上的库到.sql文件，然后使用mysql命令逐个还原.sql文件到新RDS上。

先是备份、还原了几个utf8的MediaWiki网站数据库都没有问题，但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.1.73, for redhat-linux-gnu (x86_64)

还是MySQL 5.1.73的很老版本，在网上下载了MySQL-5.6.17-1.el6.x86_64.rpm-bundle.tar，解压后得到MySQL-client-5.6.17-1.el6.x86_64.rpm（只安装Client也可以只获取这一个文件）。

使用rpm -qa | grep -i mysql查看安装了哪些mysql相关内容：

mysql-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64

再用yum -y remove mysql-libs*卸载老版本，然后用rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm 安装新版本，就可以看到新的版本号了：

mysql --version
mysql  Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using  EditLine wrapper

再用mysqldump备份可以成功，带有警告信息：

Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

这时用mysql还原的时候又会遇到报错：

Warning: Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 3554: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

在网上搜索，因为权限和版本兼容性的原因，需要在.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

后面还遇到一些问题，等会继续记录。

备份的时候遇到表过大报错的问题：

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就可以了。

还原的时候遇到主键等重复的报错：

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

2021年9月24日补充：

Drupal 7网站的MySQL从5.x升级到8.0以后，drupal日志可能出现大量报错，例如：

访问：https://example.com/admin/structure/schema 这样管理数据库结构的页面，出现报错：

Notice: Undefined property: stdClass::$index_name in SchemaDatabaseSchema_mysql->inspect() (line 191 of /path/sites/all/modules/schema/engines/mysql.inc).

可以用drush这样设置：

drush vset schema_status_report 0
drush vset schema_suppress_type_warnings 1
drush cc all

也就是设置

 Include schema comparison reports in site status report ❌
When checked, schema comparison reports are run on the Administer page, and included in the site status report.
 Suppress schema warnings. ☑️
When checked, missing schema type warnings will be suppressed.

然后暂时就不会报错了，但要根本上解决还有待进一步研究。

自由标签
MysSQL

评论1

关于文本格式

Plain text

  • 不允许使用HTML标签。
  • 网页和电子邮件地址自动转换为链接。
  • 自动断行和分段。
验证码
此问题用于测试您是否是人类访问者并防止自动提交垃圾信息。

2002-2023 v11.7 a-j-e-0