MYSQL管理常用命令

#查看所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

#创建用户

CREATE USER 'wefanbot'@'%' IDENTIFIED BY 'WEfanbot@147852'; 

设置与更改用户密码 

SET PASSWORD FOR 'root'@'%' = PASSWORD('xxx');

##撤销用户权限 

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

revoke all privileges on *.* from  'username'@'host';

##删除用户 

DROP USER 'dev'@'%'; 

DROP USER 'root'@'%';

#查看用户权限

show grants for 'wefanbot'@'%';

##授权 

GRANT ALL ON cms.* TO 'cms_prod'@'%'; 

GRANT ALL ON demo.* TO 'demo'@'%'; 

GRANT ALL ON *.* TO 'wefanbot'@'%'; 

--用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令: 

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

--  授权某用户某数据库只读权限 

GRANT SELECT ON 数据库名.* to 用户名@'%';

授权用户具有所有权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

#查看所有数据库

show databases:

FLUSH PRIVILEGES;

mysql -u root -p