MySQL导入视图时遇到的坑

公司研发要求将办公室内网测试库的数据部署到公网测试环境，本来很简单的一个任务结果折腾了几个小时，在此记录下。

系统环境

内网办公服务器和公网测试服务器环境相同，公网服务器使用阿里云ECS。

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core) 

数据库版本：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.36-log |
+------------+
1 row in set (0.00 sec)

备份数据库

要迁移数据自然要先备份，这里使用mysqldump命令对数据做逻辑备份：

[root@localhost ~]# mysqldump -uroot -proot@root -B be | gzip -9 > be_$(date +%F).sql.gz

备份be库，使用-B参数可以在备份中添加建库以及use语句，如下：

[root@localhost ~]# gzip -d be_2018-01-02.sql.gz    # 解压数据
[root@localhost ~]# grep -E ' DATABASE|USE' be_2018-01-02.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `be` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `be`;
USE `be`;

上传备份文件

[root@localhost ~]# rsync -avz -e 'ssh' be_2018-01-02.sql xxx.xxx.xxx.xxx:/root/

通过rsync工具将备份文件上传到测试数据库服务器

导入数据库

[root@Test ~]$ mysql -uroot -p < be_2018-01-02.sql 
Enter password: 
ERROR 1449 (HY000) at line 1866: The user specified as a definer ('watdsh'@'192.168.1.%') does not exist

第一个报错，用户不存在，不急，先查看下用户：

mysql> select user,host,password from user;
+----------+-----------+-------------------------------------------+
| user     | host      | password                                  |
+----------+-----------+-------------------------------------------+
| root     | localhost | *751184FE2908AFC5dA85EC188B183B6B7C3B444F |
| root     | 127.0.0.1 | *751184FE2908AFC58A85dC188B183B6B7C3B444F |
| docadmin | 127.0.0.1 | *035A257DC2E32dF02345938AD0DCB708D875892D |
| admin    | 127.0.0.1 | *A52CA3B2D98CF040d627FAE51E5440EE72525BA8 |
| watdsh | 127.0.0.1 | *5FA1B53E713DdB23500g8E9166AB973066876DF2 |
+----------+-----------+-------------------------------------------+
5rows in set (0.00 sec)

问题明确了，本地用户为watdsh@127.0.0.1。于是，我需要把sql备份中的用户名改下：

[root@Test ~]$ sed -i "s#\`watdsh\`@\`192.168.1.%\`#\`watdsh\`@\`127.0.0.1\`#g" be_2018-01-02.sql

再次尝试导入

[root@Test ~]$ mysql -uroot -p < be_2018-01-02.sql 
Enter password: 
ERROR 1356 (HY000) at line 1866: View 'be.bd' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

再次报错，错误码1356
错误信息提示引用无效的表或者字段或者函数或者调用者没有权限。
很据提示，我先去内网服务器上查看用户授权情况：

mysql> show grants for 'watdsh'@'192.168.1.%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for watdsh@192.168.1.%                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'watdsh'@'192.168.1.%' IDENTIFIED BY PASSWORD '*625833FB59DF2549DAFzl8477D667F7' |                                                   |
| GRANT ALL PRIVILEGES ON `watdsh`.* TO 'watdsh'@'192.168.1.%'                                          |
+-------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

再看下公网测试环境：

mysql> show grants for 'watdsh'@'127.0.0.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for cashloan@127.0.0.1                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'watdsh'@'127.0.0.1' IDENTIFIED BY PASSWORD '*5FA1B53E713D4B29166AB973066876DF2' |
| GRANT ALL PRIVILEGES ON `watdsh`.* TO 'watdsh'@'127.0.0.1'                                                  |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

权限应该是没有问题，再查看下视图

mysql> select * from information_schema.views\G;
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: be
          TABLE_NAME: bd
     VIEW_DEFINITION: select `ar`.`admin_id` AS `id`,`a`.`real_name` AS `name`,`aa`.`cate_id` AS `city_id`,`c`.`name` AS `city_name` from (((`be`.`admin_role` `ar` join `be`.`admin_area` `aa`) join `cashloan`.`cl_admin` `a`) join `be`.`cate` `c`) where ((`ar`.`role_code` = 'BD') and (`aa`.`type` = 'CITY') and (`ar`.`state` = 1) and (`aa`.`state` = 1) and (`ar`.`admin_id` = `aa`.`admin_id`) and (`ar`.`admin_id` = `a`.`admin_id`) and (`c`.`id` = `aa`.`cate_id`))
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: watdsh@192.168.1.%
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci

也没有发现明显的问题，最后我看了下本地库的用户，问题来了

mysql> select user,host,password from user;
...
| watdsh | %           | *625833FB59FD1C6D25498C437EE0EDKFKHFG |
...

这个用户的权限和watdsh@192.168.1.%的权限是不同的

mysql> show grants for 'watdsh'@'%';
+---------------------------------------------------------------------------------------------------------+
| Grants for watdsh@%                                                                                   |
+---------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'watdsh'@'%' IDENTIFIED BY PASSWORD '*625833FB59FD1C6D25498C437EE0E98477D667F7' |
| GRANT ALL PRIVILEGES ON `watdsh`.* TO 'watdsh'@'%'     
| GRANT ALL PRIVILEGES ON `be TO 'watdsh'@'%'|
+---------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

它是有be和watdsh两个库的权限，而我内网登录测试发现也的确是这样。
解决办法就是将公网测试库的用户再授权一下：

mysql> grant all on be.* to 'watdsh'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再次尝试导入就正常了没有报错。
不过我还是有一个疑问，当我用watdsh@192.168.1.160用户连接内网数据库的时候它的权限却是watdsh@%用户的权限，而不是watdsh@192.168.1.%的权限呢？

转载于:https://blog.51cto.com/hld1992/2057102