mysql(5.7 ) 外网访问

一、设置MySQL服务允许外网访问

修改mysql的配置文件,有的是my.ini(windows),有的是my.cnf(linux),

在配置文件中增加

[mysqld]

port=3306

bind-address=0.0.0.0

然后重新启动mysql服务,执行service mysql restart。

二、设置mysql用户支持外网访问

需要使用root权限登录mysql,更新mysql.user表,设置指定用户的Host字段为%,默认一般为127.0.0.1或者localhost。

登录数据库

mysql -u root -p

2.查询

select user,host from user;

select host, user, authentication_string, plugin from user;

创建host

如果没有"%"这个host值,就执行下面这两句:

mysql> update user set host='%' where user='root';

mysql> flush privileges;

4.授权用户

任意主机以用户root和密码mypwd连接到mysql服务器

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;

mysql> flush privileges;

IP为192.168.133.128的主机以用户myuser和密码mypwd连接到mysql服务器

mysql> GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.133.128' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;

mysql> flush privileges;

## 授权不改密码

mysql> grant all privileges on *.* to 'username'@'%' with grant option;

mysql> flush privileges;

创建用户

mysql> create USER 'username'@'host' IDENTIFIED BY 'password';

## 密码的加密方式

mysql > create user 'username'@'localhost' identified with mysql_native_password BY 'password';

其中username为自定义的用户名;host为登录域名,host为'%'时表示为 任意IP,为localhost时表示本机,或者填写指定的IP地址;paasword为密码

mysql> CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';

mysql> CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';

mysql> CREATE USER 'pig'@'%' IDENTIFIED BY '123456';

mysql> CREATE USER 'pig'@'%' IDENTIFIED BY '';

mysql> CREATE USER 'pig'@'%';

6 撤销授权

#收回权限(不包含赋权权限)

mysql> REVOKE ALL PRIVILEGES ON *.* FROM user_name;

mysql> REVOKE ALL PRIVILEGES ON user_name.* FROM user_name;

#收回赋权权限

mysql> REVOKE GRANT OPTION ON *.* FROM user_name;

#操作完后重新刷新权限

mysql> flush privileges;

7 删除用户

mysql> DROP USER 'username'@'host';

8 授权

授权 select,insert,update,delete 权限给某一个表

mysql> grant select,insert,update,delete on database.table to 'opt_crm'@'localhost' with grant option;

授权 select,insert,update,delete 给 crm 库,注意如果是全库的用户 * 号代替

mysql> grant select,insert,update,delete on crm.* to 'opt_crm'@'localhost' with grant option;

转自于:https://www.cnblogs.com/costa92/p/17632254.html

参考文章

评论可见,请评论后查看内容,谢谢!!!
 您阅读本篇文章共花了: