[数据库迁移]-MySQL常见问题

[数据库迁移]-MySQL常见问题

森格 | 2023年2月

介绍：记录在MySQL数据库迁移过程中遇到的问题，以及解决方案。

---

---

一、背景

对现有环境的实例，克隆出一套新的环境，新环境实例规格与源实例一致。

迁移过程：生成新实例 => 部署MySQL => dump源MySQL实例 => 导入数据到新实例。

二、常见问题

该章节为实际过程中遇到的问题。

2.1 ERROR 2003

问题： mysqldump: Got error: 2003: Can’t connect to MySQL server on ‘xxx.xxx.xxx.xxx’ (111) when trying to connect

问题原因： 由于网络抖动，或mysql服务安装失败导致。

问题解决： 检查mysql服务是否可用，telnet xxx.xxx.xxx.xxx 3306，服务正常则检查网络或磁盘是否满了。

2.2 ERROR 1273

问题： ERROR 1273 (HY000) at line 30: Unknown collation: ‘utf8mb4_0900_ai_ci’

问题原因： 源和新实例版本不一致，utf8mb4_0900_ai_ci是MySQL 8.0的默认排序规则，MySQL 5.7版本中，默认排序规则为utf8mb4_general_ci。

问题解决：

• 将新实例版本更换为 8.0
• 将源实例排序规则更改为 utf8mb4_0900_ai_ci

2.3 ERROR 1071

问题： ERROR 1071 (42000) at line 167: Specified key was too long; max key length is 767 bytes

问题原因：

对于Innodb而言，对表字段索引长度有限制，MySQL 5.6版本后引入参数 innodb_large_prefix。有ON 和 OFF 两个取值：

ON：innodb的行记录格式为Dynamic或Compressed的前提下，单列索引长度上限为3071字节。

OFF：innodb的单列索引长度上限为767字节，超出后，主键索引创建失败，辅助索引会被截断为前缀索引。

MySQL 5.6 默认OFF：

MySQL 5.7 默认ON：

问题处理：

在 my.cnf 中修改以下两个参数（MySQL 5.7 版本之后可以动态调整参数，可以不修改配置文件）

innodb_large_prefix = on

innodb_file_format = Barracuda（MySQL 5.7版本默认，见下图）

注：Antelope是原始的Innodb文件格式，支持REDUNDANT和COMPACT行格式。Barracuda是较新的文件格式，支持COMPRESSED和DYNAMIC行格式。

2.4 视图权限

问题：

mysqldump: Couldn’t execute ‘SHOW FIELDS FROM xxxxx’: View ‘xxxxx’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

视图的创建：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

视图的权限：

• DEFINER：DEFINER指定的用户，才能执行成功。
• INVOKER：只要执行者拥有执行群贤就可执行成功。

问题解决：

打开客户端，找到对应视图 => 修改视图，将 SQL SECURITY DEFINER => SQL SECURITY INVOKER，重新执行即可。

2.5 ERROR 1062

问题： ERROR 1062 (23000) at line 214: Duplicate entry ’ xxxxx’ for key ‘xxxxx’

问题原因：

1. 插入的数据，违反主键唯一性
2. 插入的数据，违反了唯一索引的唯一性

问题解决： 最简单的方法就是删除已经存在的值，重新插入。

三、总结

以上就是数据库迁移过程中遇到的一些问题的整理，后续碰见其他问题会继续补充。