RDS还原数据库时报错：ERROR 1227 (42000)
最近公司准备公司的整套架构从IDC机房向云上迁移，在RDS上做数据库恢复的时候遇到如下的问题：
[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并没有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 */;
网上搜索了好久都没有解决，最后在阿里云常见问题中找到了答案：http://help.aliyun.com/knowledge_detail/5989721.html?spm=5176.788314909.3.8.xC9pCQ
RDS MySQL报错完整的信息：
[Err] 1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation （常见于MySQL 5.6）
ERROR 1725 (HY000) at line 1936: OPERATION need to be executed set by ADMIN （常见于MySQL 5.5）
通常以下操作会引起出现这个错误：
1. 在创建 存储过程、函数、触发器、事件、视图的时候出现这个错误。
2. 从本地数据库导出SQL，在RDS上应用该SQL的时候出现该错误。
3. 从RDS MySQL 5.6实例下载逻辑备份，导入到RDS或本地数据库中
原因是SQL语句中含有对root或其他用户的引用，而实际SQL执行过程中无相应用户的权限，因此需要去除这类语句。
具体操作如下：
1. 检查SQL文件，去除下面类似的子句：
DEFINER=`root`@`%`
在Linux平台下，可以尝试使用下面的语句去除：
sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' user.sql > user_revised.sql
2. 检查SQL文件，去除下面类似的语句：
SET @@GLOBAL.GTID_PURGED='d0502171-3e23-11e4-9d65-d89d672af420:1-373,
d5deee4e-3e23-11e4-9d65-d89d672a9530:1-616234';
在Linux平台，可以使用下面的语句去除
sed '/GTID_PURGED/N;s/,\n/,/' user_db.sql | grep -iv 'set @@global' > user_db_revised.sql
#有时候下面有三行呢sed -e '/GTID_PURGED/{:a;N;N;d;ba}' yx_backup.sql >yx_backup_new.sql
修改后再进行测试使用。
由于文件较大，服务器配置略低，这里我使用sed命令来删除这几行
sed -i 's/SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;/ /g' backup.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN= 0;/ /g' backup.sql
grep -A 3 GLOBAL.GTID_PURGED yx_backup.sql
sed -i 's/SET @@GLOBAL.GTID_PURGED='55a52aexxxxxxxxxxx-xxxx';/ /g' backup.sql
#有时候下面有三行呢sed -e '/GTID_PURGED/{:a;N;N;d;ba}' yx_backup.sql >yx_backup_new.sql
sed -i 's/SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;/ /g' backup.sql
根据上文的提示：我的具体操作如下：
1.[root@vm10-136-58-79 ~]# cat -n mysql_dump.sql |grep DEFINER
78629/*!50013 DEFINER=`boco_guest`@`%` SQL SECURITY DEFINER */
78647/*!50013 DEFINER=`boco_guest`@`%` SQL SECURITY DEFINER */
78665/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
78683/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
78701/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
78719/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
78737/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
2.编辑mysql_dump.sql文件，手动去除以上7行内容
3.再次执行如下语句：
[root@vm10-136-58-79 ~]# mysql -uadmin -h 10.137.212.36 -p </root/mysql_dump.sql
Enter password:
[root@vm10-136-58-79 ~]# [root@vm10-136-58-79 ~]# mysql -uadmin -h 10.137.212.36 -p -e "show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| csip_pline_e000001 |
| csip_store |
| db_pms |
| mysql |
| performance_schema |
| ysc0001 |
+--------------------+
至此，问题解决。
评论区