mysql5.7.9用户操作

新建用户>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不会有影响；