命令客户端
/opt/mysql-8.0.32/bin/mysql -h127.0.0.1 -P3306 -uroot -pMyPassword21
一、创建用户
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
例子:CREATE USER 'user'@'%' IDENTIFIED BY 'password';
二、身份验证插件
mysql 5.7 默认是mysql_native_password
mysql 8.0 默认是caching_sha2_password
指定身份验证插件
CREATE USER 'replicator'@'%' IDENTIFIED WITH caching_sha2_password BY 'myPassword21';
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'myPassword21';
修改身份验证插件
ALTER USER 'replicator'@'%' IDENTIFIED WITH caching_sha2_password BY 'myPassword21';
ALTER USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'myPassword21';
mysql8.0 主从复制 Authentication plugin ‘caching_sha2_password‘ Error_code: MY-002061解决办法
三、修改密码允许远程登录
use mysql
select user,authentication_string,host from user;
alter user 'root'@'localhost' IDENTIFIED BY 'root';
update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;
四、删除用户
命令:DROP USER 'username'@'host';
例子:DROP USER 'user'@'%';
五、授予权限
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
例子:GRANT ALL ON test.* TO 'user'@'%';
刷新权限:FLUSH PRIVILEGES;
# 用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
六、收回权限
命令:REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例子:REVOKE ALL ON test.* FROM 'user'@'%';
刷新权限:FLUSH PRIVILEGES;
七、查看用户权限
命令:SHOW GRANTS FOR 'username'@'hostname';
例子:SHOW GRANTS FOR 'root'@'%';
八、文档
13.7.1 Account Management Statements
13.7.1.2 CREATE ROLE Statement
13.7.1.3 CREATE USER Statement
13.7.1.7 RENAME USER Statement
13.7.1.9 SET DEFAULT ROLE Statement