协助开发导入库时，他提醒我，库里有存储过程的定义。我使用了--routines 的选项，可是导入时，出了问题：
1.提示无法创建该存储过程，原来是忘了打开 log_bin_trust_function_creators 变量，
set global log_bin_trust_function_creators=on; 后，成功创建，从服务器也需要此操作
才行，如果二进制日志是 ROW 的话，即使从上有此存储过程，也是不会调用的。
2.等到导入成功后，却又出现了无法调用的问题，搜索了 这篇文章，记录下此问题。
创建一个 definer 为 user_admin 的存储过程
mysql> DELIMITER //mysql> CREATE DEFINER='user_admin' PROCEDURE num1() -> BEGIN -> SELECT COUNT(id) FROM itopserver; -> END //Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> DELIMITER ;
2. 创建一个用户，并赋于其 相关权限
mysql> create user user_itop identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> grant select,insert,delete,update on itop.* to 'user_test'@'%';Query OK, 0 rows affected (0.00 sec)mysql> show grants for user_test@'%';+---------------------------------------------------------------------+| Grants for user_test@% |+---------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user_test'@'%' || GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO 'user_test'@'%' |+---------------------------------------------------------------------+2 rows in set (0.00 sec)
3. 使用 user_test 连接 mysql 并执行刚才创建的 procedure
mysql> call num1;ERROR 1370 (42000): execute command denied to user 'user_test'@'%' for routine 'itop.num1'
4. 这样看是权限的问题，因为只给了他操作表的方法。 增加其 itop 的 execute 权限
mysql> grant execute on itop.* to user_test@'%';Query OK, 0 rows affected (0.00 sec)mysql> show grants for user_test@'%';+----------------------------------------------------------------------------------------------------------+| Grants for user_test@% |+----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user_test'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO 'user_test'@'%' |+----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
5. 重新使用此用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;mysql> call num1();ERROR 1449 (HY000): The user specified as a definer ('user_admin'@'%') does not exist 现在可以调用该存储过程了，但是提示存储过程定义中的definer不存在，
原来仅仅是连接到MySQL服务器的用户具有执行存储过程的权限是远远不够的，
最终要通过存储过程定义中指定的definer来执行存储过程。
6. 说 user_admin 不存在，那就创建 user_admin 这个用户
mysql> grant select,insert,delete,update on itop.* to 'user_admin'@'%' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> show grants for user_admin@'%';+-----------------------------------------------------------------------------------------------------------+| Grants for user_admin@% |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT, INSERT, UPDATE, DELETE ON `itop`.* TO 'user_admin'@'%' |+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
7. 重新使用 "user_test" 用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;mysql> call num1();ERROR 1370 (42000): execute command denied to user 'user_admin'@'%' for routine 'itop.num1'
8. 看来不仅仅是连接到MySQL服务器的用户需要具有存储过程上的执行权限，存储过程定义者同样需要该权限。
mysql> grant execute on itop.* to 'user_admin'@'%';Query OK, 0 rows affected (0.00 sec)mysql> show grants for user_admin;+-----------------------------------------------------------------------------------------------------------+| Grants for user_admin@% |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'user_admin'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `itop`.* TO 'user_admin'@'%' |+-----------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
9. 重新使用 "user_test" 用户连接 Mysql 执行刚才定义的存储过程
mysql> use itop;mysql> call num1();+-----------+| COUNT(id) |+-----------+| 372 |+-----------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)
可以执行了