一次数据库迁移遇到的一些问题
简单数据库迁移操作
迁移方案
迁移方案很简单, 首先将旧的库dump下来, 然后在新库中导入旧的库 dump 下来的文件.
# 旧库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 使用下面的命令新建了一个视图,
-- 创建视图, 该视图View的DEFINER 为user1
CREATE VIEW temp_view AS SELECT * FROM some_table;
然后我们将这个数据库A使用 mysqldump 迁移到数据库B, 这时, mysqldump会生成一个在数据库B上新建一个View的命令, 但是这个命令是这个样子的:
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.
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为视图所属的数据库名.
对上面命令进行解释:
- CONCAT 是拼接组成SQL 语句
view_definition是视图View的定义, 也就是创建视图时的SELECT语句.information_schema是 MySQL 内部的一个虚拟数据库, 它包含关于数据库元数据的信息, 如表、列、权限等.information_schema.views是其中的一个视图,用来存储有关数据库中所有视图的信息.
所以上面的命令会生成一系列修改视图View的DEFINER的语句, 这些语句的格式如下:
ALTER DEFINER=`new_user`@`%` VIEW my_view AS SELECT * FROM my_table;
我们再次在数据库中执行生成的一系列的语句, 就会替换视图的 DEFINER. 替换之后, 再次使用mysqldump语句即可.
mysqldump -h host -P post -u user -p database > database_backup.sql
GDIT 重复复制错误
我在使用上面所示的语句将dump的sql文件导入到新库的过程中遇到下面的错误:
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty
这个错误是因为我们的mysqldump生成了下面这条语句:
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 认为这些事务在新的数据库中也算作事务, 我们不认就可以了, 对功能无影响.
