当前位置

MySQL 5.x数据迁移到MySQL 8.0

James Qi 在 2021年7月23日 - 09:26 提交

很多年前一直在使用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.

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

自由标签:

评论

添加新评论

Plain text

  • 不允许使用HTML标签。
  • 自动将网址与电子邮件地址转变为链接。
  • 自动断行和分段。