一次数据库迁移遇到的一些问题

简单数据库迁移操作

迁移方案#

迁移方案很简单, 首先将旧的库dump下来, 然后在新库中导入旧的库 dump 下来的文件.

Copy
# 旧库dump的指令 mysqldump -h host -P post -u user -p database > database_backup.sql # 新库导入的命令 mysql -h host -P post -u user -p database < database_backup.sql

在这个简单的方案中, 我需要一些问题如下:

mysqldump 1449 错误#

在使用mysqldump命令的时候, 出现报错如下:
Got error: 1449: The user specified as a definer ('some_user'@'%') does not exist when using LOCK TABLES
上面的some_user是我自己替换的.

错误的原因#

在进行一次数据库迁移的操作中, mysqldump默认操作会将视图View也复制迁移到新的数据库中, 这个复制实际上是在新的数据库中重新创造一个视图, 但是重新创造的过程中, 这个View的DEFINER 仍然是旧库中建立这个视图的那个用户.

举例就是数据库A中, 我们用户 user1 使用下面的命令新建了一个视图,

Copy
-- 创建视图, 该视图View的DEFINER 为user1 CREATE VIEW temp_view AS SELECT * FROM some_table;

然后我们将这个数据库A使用 mysqldump 迁移到数据库B, 这时, mysqldump会生成一个在数据库B上新建一个View的命令, 但是这个命令是这个样子的:

Copy
CREATE ALGORITHM=UNDEFINED DEFINER=`user1`@`%` SQL SECURITY DEFINER VIEW `temp_view` AS SELECT * FROM some_table;

可以看到, 数据库B中新建的视图的DEFINER仍然是 user1. 但是, 数据库B可能压根不存在这个用户.
这样导致一个问题就是, 当我们再次使用 mysqldump dump数据库B的时候, 由于数据库B的用户是 user2, 并且不存在用户 user1, 因此在dump该数据库B的时候, 复制这个视图的时候, 使用 LOCK TABLES的时候会找不到用户user1, 报错. 也就是上面的错误 Got error: 1449: The user specified as a definer ('some_user'@'%') does not exist when using LOCK TABLES.

如何解决错误#

创建缺失用户#

既然当前数据库B中没有这个用户, 那么新建这个用户不就解决了吗, 实际上这是最暴力的方式了.

修改视图View的DEFINER#

这种方法实际是将我们需要拷贝的视图的DEFINER修改为我们存在的用户, 或者拷贝的用户.
通常我们使用下面的语句修改一个视图View的DEFINER.

Copy
SELECT CONCAT("ALTER DEFINER=`user2`@`host` VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='your-database-name';

通常我们会登录到数据库的主机上, 或者使用DBVisualize这一类的工具, 执行上面的命令, 执行过程中需要修改user2为你在数据库B中存在的用户, 还需要设置 table_schema为视图所属的数据库名.
对上面命令进行解释:

  1. CONCAT 是拼接组成SQL 语句
  2. view_definition 是视图View的定义, 也就是创建视图时的SELECT语句.
  3. information_schema 是 MySQL 内部的一个虚拟数据库, 它包含关于数据库元数据的信息, 如表、列、权限等. information_schema.views 是其中的一个视图,用来存储有关数据库中所有视图的信息.

所以上面的命令会生成一系列修改视图View的DEFINER的语句, 这些语句的格式如下:

Copy
ALTER DEFINER=`new_user`@`%` VIEW my_view AS SELECT * FROM my_table;

我们再次在数据库中执行生成的一系列的语句, 就会替换视图的 DEFINER. 替换之后, 再次使用mysqldump语句即可.

Copy
mysqldump -h host -P post -u user -p database > database_backup.sql

GDIT 重复复制错误#

我在使用上面所示的语句将dump的sql文件导入到新库的过程中遇到下面的错误:

Copy
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty

这个错误是因为我们的mysqldump生成了下面这条语句:

Copy
SET @@GLOBAL.GTID_PURGED='fa548a0a-c727-11ea-9d81-fa163eab711d:1-467584';

错误原因#

这里的GTID_PURGED 是一种GDIT表, 关于GDIT, 可以参考我的另一篇文章: 传送门.
这个 GTID_PURGED 存储的是事务的GDIT表, 这些事务已经在该数据库的主服务器执行成功, 并且Commit, 但是这些事务没有写进binlog中. 这是因为这些事务实际上是在旧的数据库中执行的, 在新的数据库中当然没有binlog, 但是MYSQL还是认为这些事务执行了, 所以这些 GDIT 实际上就是在旧的数据库的服务器中的 gtid_executed表, 表示在旧的数据库服务器中执行的所有事务.

例如上面的例子, fa548a0a-c727-11ea-9d81-fa163eab711d 就是旧的数据库的服务器的UUID, 1-467584 表示执行的事务的ID.

解决方法#

其实解决方法很简单, 将这一行注释即可, 也就是说 MYSQL 认为这些事务在新的数据库中也算作事务, 我们不认就可以了, 对功能无影响.

posted @   虾野百鹤  阅读(15)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 我干了两个月的大项目,开源了!
· 推荐一款非常好用的在线 SSH 管理工具
· 千万级的大表,如何做性能调优?
· 盘点!HelloGitHub 年度热门开源项目
· Phi小模型开发教程:用C#开发本地部署AI聊天工具,只需CPU,不需要GPU,3G内存就可以运行,
点击右上角即可分享
微信分享提示
CONTENTS