1.创建用户

1.1 允许本地localhost访问

create user 'test'@'localhost' identified by '123456';  

1.2 允许外网ip访问

create user 'test'@'%' identified by '123456';  

2.分配权限

2.1 分配所有权限

grant all privileges on *.* to test@localhost identified by '123456';

2.2 分配指定databases上所有表所有操作

grant select,insert,update,delete on user.* to test@localhost identified by '123456';

3.刷新授权

flush privileges;

4.修改密码

4.1 sql修改

4.1.1直接修改

当前root用户登录

set password=password('123123');

4.1.2 修改user表

user mysql;

uodpate user set authentication_string = password('123123') WHERE user = 'test';

MySQL5.7密码字段为authentication_string,不是password ,可通过desc user查看具体字段 

4.2 忘记root密码,修改配置文件

mysql配置文件 mysql.ini 添加 skip-grant-tables 

$ mysql 直接登录

mysl > 

5.查看用户权限

show grants for test;

+----------------------------------+

| Grants for test@%                

+----------------------------------+

| GRANT USAGE ON *.* TO 'test'@'%' 

+----------------------------------+

6.限制访问ip限制

6.1.用户test只能本地访问

grant all privileges on *.* to 'test'@'localhost' identified by '123456';

6.2.用户test可以全网访问

grant all privileges on *.* to 'test'@'%' identified by '123456';

6.3.修改配置文件mysql.conf

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

bind-address            = 0.0.0.0

bind-address            =127.0.0.1 //本地访问