MySQL导入视图时遇到的坑

vivion34

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

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

[root@localhost ~]# cat /etc/redhat-release
　　
CentOS Linux>　　

　　数据库版本：
　　

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.%'>　　
| 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'>　　
| 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'@'%'>　　
| 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.%的权限呢？