MySQL拒绝访问问题

运行Sqoop时出现：

java.sql.SQLException: Access denied for user 'root'@'hadoop-master' (using password: YES)

使用对应的hadoop-master对应的IP登录，失败。直接赋予%权限，问题依旧

grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;

原因：

MySQL会先判断授权的IP是否配置映射，有多条权限记录时，如果其中某些记录配置了 IP host 映射，可能会产生不能访问

 

解决办法：

1. 给对应host授权

# 检查数据库中root是否绑定host为hadoop-master
select user,host from mysql.user where user = 'root';

# 如果没有的话赋予对应权限
grant all privileges on *.* to root@'hadoop-master' identified by '123456' with grant option;
flush privileges;

2. 只保留host为%

#修改user表，把Host表内容修改为%
update user set host='%' where host='localhost';

#删除root用户的其他host
delete from user where Host='hadoop-master';
delete from user where Host='127.0.0.1';
delete from user where Host='::1';

grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;

另外 

# 查看当前授权
show grants;

# 查看指定host授权
show grants for 'root'@'localhost';

# 取消授权
revoke all on *.* from 'root'@'hadoop-master';

# 添加授权
grant 权限 on '数据库名.表名' to '用户'@'登录主机' identified by '用户密码'; 
grant select,update,insert,delete on *.* to 'root'@'hadoop-master' identified by '123456';

# 权限：all、all privileges(所有权限)、select、update、delete、insert(表数据)、create、alert、drop(表结构)、
#     references(外键)、create temporary tables(创建临时表)、index(操作索引)、create view,show view(视图)、
#     create routine、alert routine、execute(存储过程)
# 数据库：数据库名或者*(所有数据库)
# 表：表名或者*(某数据库下所有表)
# 主机：主机名或者%(任何其他主机)