[数据库迁移]-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’
问题原因:
- 插入的数据,违反主键唯一性
- 插入的数据,违反了唯一索引的唯一性
问题解决: 最简单的方法就是删除已经存在的值,重新插入。
三、总结
以上就是数据库迁移过程中遇到的一些问题的整理,后续碰见其他问题会继续补充。