1.恢复root权限
在测试mysql 脚本时,为了测试授权操作,为了恢复环境,执行了revoke操作将root的权限全都全都屏蔽了(revoke all privileges on *.* from root)
恢复办法:
- 先停止mysql服务:service mysql stop
- 修改mysql配置文件/etc/mysql/my.cnf,在[mysqld]配置段下增加配置:skip-grant-tables,然后重启mysql服务
my.cnf 配置范例:
[mysqld_safe]
nice =0
[mysqld]
user = mysql
skip-grant-tables
....
- 通过mysql 命令进入mysql shell(此时,不需要认证了),执行sql命令:
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;
- 然后,修改mysql配置,去掉“skip-grant-tables”属性,重启mysql。此时mysql就正常了。然后通过授权语句进行赋权:
grant all privileges on *.* to 'root'@'%' identified by 'rootpass' with grant option;
flush privileges;
2. 关于补充Myql 用户及权限的操作
创建用户:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
授权:GRANT privileges ON databasename.tablename TO 'username'@'host'
回收权限:REVOKE privilege ON databasename.tablename FROM 'username'@'host';
删除用户: DROP USER 'username'@'host';
查看用户的授权:show grants for dog@localhost;