时间：2021-07-01 10:21:17 帮助过：5人阅读
[root@vm10-136-58-79 ~]# mysql -uadmin -h 10.137.212.36 -p </root/mysql_dump.sql
Enter password:
ERROR 1227 (42000) at line 78664: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
备注：金山云没有给mysql的管理员root的密码，而是给了一个管理用户admin
排查思路：
重视报错信息
登录RDS，查看mysql相关的用户和权限信息
mysql> select user,host,password from mysql.user; +--------------+-----------+-------------------------------------------+ | user | host | password | +--------------+-----------+-------------------------------------------+ | root | localhost | *9F9F6951C3EA2E9CEA830C3A3102949B51548ECE | | rdsrepladmin | % | *EAA4FB76B6D0776B5CAA760B6A754C5D61CCFCED | | rdsadmin | localhost | *843E8882BD0547FD0B7109D70C4A1CF82038D1F3 | | admin | % | *9BEFFE73220FACA3F2AC73B58059B1894609877B | +--------------+-----------+-------------------------------------------+
admin的权限如下所示：
mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: root Password: *9F9F6951C3EA2E9CEA830C3A3102949B51548ECE Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: *************************** 2. row *************************** Host: % User: rdsrepladmin Password: *EAA4FB76B6D0776B5CAA760B6A754C5D61CCFCED Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: NULL *************************** 3. row *************************** Host: localhost User: rdsadmin Password: *843E8882BD0547FD0B7109D70C4A1CF82038D1F3 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: NULL *************************** 4. row *************************** Host: % User: admin Password: *9BEFFE73220FACA3F2AC73B58059B1894609877B Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: N Process_priv: Y File_priv: N Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: N Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: authentication_string: NULL
从上述内容可以看出：管理用户admin并没有Super_priv权限，如何给admin用户增加super权限？
3.查看mysql_dump.sql文件：
[root@vm10-136-58-79 ~]# vim mysql_dump.sql.bak（重点看如下几行）
78628 /*!50001 CREATE ALGORITHM=UNDEFINED */
78629 /*!50013 DEFINER=`boco_guest`@`%` SQL SECURITY DEFINER */
78630 /*!50001 VIEW `temp_district` AS select `district`.`id` AS `id`,`district`.`cityId` AS `cityId`,`district`.`Name` AS `Name`,`district`.`po stCode` AS `postCode` from `district` group by `district`.`cityId` */;
78631 /*!50001 SET character_set_client = @saved_cs_client */;
78632 /*!50001 SET character_set_results = @saved_cs_results */;
78633 /*!50001 SET collation_connection = @saved_col_connection */;
原本认为 78628 /*!50001 CREATE ALGORITHM=UNDEFINED */这个是注释语句不会执行，可是为什么执行到这附近报错了呢？
mysql /*!50001 注释解释
在MySQL里，多行解释 是 /* */，这个是SQL的标准
但是MySQL扩张了解释 的功能
假如 在起头的/*后头加了惊叹 号，那么此解释 里的语句将被推行
比如
mysql> /*! select * from test */;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
再看如下语句
/*!50001 select * from test */;
这里的50001表示假如 数据库是5.00.01以上版本，该语句才会被推行
因此固然 你的mysqldump出来的成立 view的语句在解释 里，实际 上它是会被推行 的
假如 是一下内容，就不会推行 了，那是真正的解释 了
/* select * from test */;