MySQL学习与遇到的坑

MySQL数据推送表操作

最近之前的项目交维，需要对之前开发的系统进行权限整理以及权限控制，项目之前的需求有一个利用MySQL的federated引擎实现数据库表映射，原理是在本地创建视图，在通过在远程的数据库连接这个视图。备注：设想是新建用户用来远程访问我们的视图（新用户只给查询权限），root账号创建视图，当时root用户是允许所有机器连接的，对应的ip访问权限是root@’%’（此处将是一个坑）
1先在本地创建视图

CREATE VIEW test_TT AS 
SELECT
	luyou.ID AS ROUTEID,
	luyou.`name` AS ROUTENAME,
	dmp_luyoufz.ID AS ROUTEGROUPID,
	dmp_luyoufz.`name` AS ROUTEGROUPNAME
FROM luyou
LEFT JOIN dmp_luyou_luyoufz ON luyou.ID = dmp_luyou_luyoufz.luyouid
LEFT JOIN dmp_luyoufz ON dmp_luyou_luyoufz.luyoufzid = dmp_luyoufz.ID
WHERE luyou.ext0 = '1' AND luyou.ext4 = '1';

2在远程数据库上（需要支持federated）创建表连接此视图

drop table if EXISTS `test_route`;
CREATE TABLE `test_route` (
  `routeid` int(11) DEFAULT NULL,
  `routename` varchar(255) DEFAULT NULL,
  `routegroupid` int(11) DEFAULT NULL,
  `routegroupname` varchar(255) DEFAULT NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://csmsyc:csmsyc321@172.21.7.186:3306/tests/test_TT'

mysql用户的权限发生变化

随着项目交维，客户要求对数据库的权限控制，于是去mysql库中的user表一顿操作，各种用户都严格限制，只允许特定的ip访问。第二天GG，电话被打炸了，所有的远程推送表均失效，访问下来都是这个错

The user specified as a definer ('root'@'%') 

去网上度一下给的解释几乎一样，操作也是一样

grant all privileges on *.* to root@"%" identified by "......";

但是这个是不可能的我要控制root用户的权限，一开始还以为root用户的权限控制会影响非root用户的权限访问，这个是不影响的。整个下午各种尝试，终于在下班的想到本机的视图是不是有问题，做了个测试，发现只要创建的该视图的权限发生改变，视图就会无法访问。于是研究看视图的权限控制可以参照这个。解决方法：可以统一创建mysql视图用户的权限，或者是将已经创建好的视图的安全性定义由definer设置为invoker。完美的解决了问题，get了新的知识。

几天后tomcat界面服务也出现了一个错误与上面的视图相似错误，

The user specified as a definer ('root'@'%') 

原来界面服务在插入数据时对应的表上绑定了触发器，该触发器也是对应这mysql的权限（当时是root@’%'权限，所以一样GG，服务不能使用），解决方法与视图解决方法相似，在对应的MySQL要求的权限下确立下，删除触发器重新创建触发器。