新建用户>use mysql;create user XXX@‘%’ identified by ‘password’;flush privileges;
删除用户>use mysql;drop user "XXX"@"XXX"|delete from user where User="XXX" and Host="XXX";flush privileges;
修改用户密码>use mysql;alter user 'XXX'@'XXX' identified by 'newPassword';flush privileges;
或者 mysqladmin -u 用户名 -p原来的密码 password 新的密码,会出现如下警告信息:mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
用户赋权:
所有权限为>grant all privileges on db.* to user@"XXX" identified by "userPwd";flush privileges;
部分权限>(SELECT, INSERT, UPDATE, CREATE, DELETE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE)
>grant SELECT|INSERT on db.* to user@"XXX" identified by "userPwd";flush privileges;
忘记root密码>service mysqld stop;
>mysql -uroot -p;Enter键;
> ALTER USER 'root'@'%' IDENTIFIED BY 'newPwd';执行出现错误提示ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords;
详见:
https://bugs.mysql.com/bug.php?id=79027
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
>update mysql.user set authentication_string=password('MyNewPass') where user='root';flush privileges;
>exit;
>pkill mysql && service mysqld start;
收回权限:
收回所有权限> revoke all priviliges on *.* from user@"XXXX;flush privileges;
收回部分权限>revoke delete|update|... on *.* from user@"XXXX;flush privileges;
查看用户有哪些权限>show grants for userName;
查看所有的用户>select User,Host from mysql.user;
flush privileges>重新加载权限表,不执行此操作,新建的用户以及赋予的权限都不会生效;执行之后,对已经存在的session不会有影响;