记一次数据库迁移的踩坑过程-编程学习网

短信预约信息系统项目管理师报名、考试、查分时间动态提醒

记一次数据库迁移的踩坑过程

迁移步骤

迁移数据库是一项需要很谨慎的任务。整个迁移过程大概分成以下几步：

备份原数据库数据

//备份数据库，并指定日期
mysqldump -uadmin -p****** databaseName | gzip > /databak/databaseName_$(date +%Y%m%d).sql.gz

云数据库上初始化数据库、编码、用户名、数据库等基础信息先通过腾讯云平台创建用户，以及相关权限

//连接数据库
mysql -h172.16.0.1 -uUserName -p******
//创建数据库，并指定编码
CREATE DATABASE databaseName DEFAULT CHARACTER SET  utf8mb4 COLLATE utf8mb4_general_ci;

执行还原操作

//解压备份好的.sql文件
gunzip -v /databak/databaseName_20200517.sql.gz
//还原数据库
source /databak/databaseName_20200517.sql

产生的问题

正常情况下，按照以上迁移数据的步骤，应该等还原操作完成即可，但是事情往往不会那么顺利，如果很顺利可能我们对数据库迁移的认知就到这里就可以了。

实际上在执行还原操作时出现了错误。主要出现两次问题

没有主键

ERROR 1173 (42000): This table type requires a primary key

表的存储引擎不对

Can not create tables in myisam storage engine in user databases, controled by reject_create_table_myisam variable.

看到这两个问题，感觉比较奇怪，因为最开始在测试数据库自动备份时，已经对备份的sql文件还原过，没有发现有什么错误。为什么这一次迁移就出现这两个问题呢？

排查方法

建表缺少主键

针对问题1，看日志比较容易明白意思，就是表需要主键，这个时候就想先看看数据库中有哪些没有主键的表，看看能否直接指定。

查询没有主键的表的sql如下：

SELECT table_schema, table_name,TABLE_ROWS
FROM information_schema.tables
WHERE (table_schema, table_name) NOT IN (
SELECT DISTINCT table_schema, table_name
FROM information_schema.columns
WHERE COLUMN_KEY = "PRI"
)
AND table_schema NOT IN ("sys", "mysql", "information_schema", "performance_schema");

查询结果分析发现这些表和错误日志中的表匹配。然后查看了两张表发现的确都是没有指定主键的。正常 mysql 的设计中，如果在创建表时没有显式地定义主键,则 InnoDB 存储引擎会按如下方式选择或创建主键:

首先判断表中是否有非空的唯一索引,如果有,则该列即为主键。
如果不符合上述条件,InnoDB存储引擎自动创建一个6字节大小的指针。

但是还是报那个错。这时在想是否和数据库的版本有关系。经过 google 搜索大部分的结果都是定位到数据库的参数设置了表一定要指定主键。

解决办法如下：

//查询变量查看是否开启了强制主键，也就是建表必须有主键约束，
show global variables like "innodb_force_primary_key";
//如果是ON则设置成OFF即可
set global  innodb_force_primary_key=off;

当然我也找找这个方法去尝试了，但我执行第一句时，发现没有找到结果。然后也经过了解这个参数是mysql8.0以及MariaDB中才有这个参数。强制执行

set global  innodb_force_primary_key=off;

出现如下错误：

ERROR 1193 (HY000): Unknown system variable "innodb_force_primary_key"

所以这种方法行不通。但通过这个解决方法，我猜测问题可能就是和变量设置有关系，于是我查看了所有的 mysql 全局变量，最后找到了问题所在。

问题定位：原来腾讯云上的分布式数据库tdsql中，设置建表需要主键的参数为 reject_table_no_pk 这个时候就能定位到问题所在了。

表的存储引擎不对

通过如下sql可以查询一个库中所有使用MyISAM存储引擎创建的表

SELECT * FROM information_schema.tables where engine="MyISAM" and TABLE_SCHEMA="databaseName"

查出来的表和还原错误日志报错的表也匹配了。

通过分析问题1时，在查找全局变量时存在如下变量。

reject_create_table_myisam 意思就是拒绝使用 myisam 存储引擎建表。所以问题2也定位到了问题源头。

解决方案

通过上面一步一步分析问题，已经找到了问题的源头，找到了问题的产生原因。对于问题的解决就比较好处理了。

方法1

登录超级管理员，对这两个参数进行设置

set global reject_table_no_pk 0;
set global reject_create_table_myisam OFF;

设置完成之后，重新执行还原操作，发现问题解决。但是分布式数据库新增了的这两个参数其实是有他的用处的，这种默认值最好不要轻易调整，因为云数据库还有一个优势就是大部分的参数都调成了最佳。

通过资料搜索发现原来这两个参数是有重要作用的。 TDSQL 内核使用 row 格式的 binlog 复制。根据目前 MariaDB/MySQL 的实现方式，如果一个 update/delete 语句更新或者删除了很多行，那么到了备机上面，更新或者删除每个行时候，需要使用索引扫描或者全表扫描来找到这个行，导致备机复制变得非常慢，这是非常严重的问题。在 TDSQL 的告警平台上面就有用户出现过主备延迟因此变得非常大的告警。为了避免这些致命问题的出现，所以才有“自动增加主键”和“禁止 create table/alter table 语句产生无主键的表”

方法2

方法1能够解决还原问题，也能够解决一般数据量不大的应用。但是如果后面业务增长，可能还是需要将参数调整回来。此时方法1的解决方案就行不通了。

方法2实际就是针对没有主键的表设置主键，没有主键的表新增主键。以符合分布式数据库要求。

而对于数据库存储引擎为myisam的表通过sql语句直接调整。

//修改表的存储引擎
alter table table_name engine=innodb;

总结

数据库内容很多，很深，我们在处理工作中实际问题时，需要多多思考。从解决实际问题的过程中去深入知识点，扩展知识点。这样才能提高。

文章详情