MySQL导入视图时遇到的坑
公司研发要求将办公室内网测试库的数据部署到公网测试环境，本来很简单的一个任务结果折腾了几个小时，在此记录下。系统环境
内网办公服务器和公网测试服务器环境相同，公网服务器使用阿里云ECS。
# cat /etc/redhat-release
CentOS Linux>
数据库版本：
mysql> select version();
+------------+
| version()|
+------------+
| 5.6.36-log |
+------------+
1 row in set (0.00 sec)
备份数据库
要迁移数据自然要先备份，这里使用mysqldump命令对数据做逻辑备份：
# mysqldump -uroot -proot@root -B be | gzip -9 > be_$(date +%F).sql.gz
备份be库，使用-B参数可以在备份中添加建库以及use语句，如下：
# gzip -d be_2018-01-02.sql.gz # 解压数据
# 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`;
上传备份文件
# rsync -avz -e 'ssh' be_2018-01-02.sql xxx.xxx.xxx.xxx:/root/
通过rsync工具将备份文件上传到测试数据库服务器
导入数据库
$ 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备份中的用户名改下：
$ sed -i "s#\`watdsh\`@\`192.168.1.%\`#\`watdsh\`@\`127.0.0.1\`#g" be_2018-01-02.sql
再次尝试导入
$ 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.%的权限呢？
页:
[1]