mysql导入数据权限_mysql5.7导入数据的权限问题

mysql5.7导入数据的权限问题

mysql5.7中创建用户时虽然使用

grant all on xxx.* to test@”localhost”

但是使用mysql命令导入时，还是会出错

mysql –default-character-set=utf8 -utest -p testdb < testdb20180103.sql

会出现下面错误：

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

解决办法是：

用root登录mysql

查询super权限

select user,Super_priv from mysql.user;

赋予super权限

update mysql.user set Super_priv='Y' where user='test';

刷新权限

flush privileges;

这时就可以导入sql文件了

导入完成后收回super权限

update mysql.user set Super_priv='N' where user='test';

flush privileges;