A vm virtualbox 网络方式改为桥接(虚拟机与宿主机可相互访问 虚拟机与虚拟机之间可相互访问)
B linux(ubuntu) 设置防火墙允许3306端口被外界访问
1.防火墙的打开
sudo ufw enable
2.防火墙的重启
sudo ufw reload
3.打开想要的端口(以9000为例)
ufw allow 9000
4.查看本机端口使用情况
ufw status
C mysql设置授权允许远程访问
1 登录mysql
mysql -u root -p
2、创建数据库
create database test;//以创建test为例
3、创建用户
创建user01,只能本地访问
create user user01@'localhost' identified by 'password';
创建user02,可以远程访问
create user user02@'%' identified by 'password'
4、修改用户密码
以user01为例:
set password for 'user01'@'localhost'=password('anotherpassword')
5、授权
授予user01管理test的全部权限 某库某表
grant all privileges on test.* to user01;
授予user02查看权限,并修改密码
grant select on *.* to 'user02'@'%' identified by 'anotherpassword';
授予所有权限
GRANT ALL PRIVILEGES ON *.* TO zhangsan@"%" IDENTIFIED BY "lisi";
允许root用户在任何地方进行远程登录 并具有所有库任何操作权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;
允许root用户在一个特定的IP进行远程登录 并具有所有库任何操作权限
GRANT ALL PRIVILEGES ON *.* TO root@"172.16.16.152" IDENTIFIED BY "youpassword" WITH GRANT OPTION; 重载授权表: FLUSH PRIVILEGES; 退出mysql数据库: exit
GRANT select,insert,update,delete ON *.* TO root@"172.16.16.152" IDENTIFIED BY "youpassword";
GRANT select,insert,update,delete ON TEST-DB TO test-user@"172.16.16.152" IDENTIFIED BY "youpassword";
REVOKE privileges ON 数据库[.表名] FROM user-name;
清除了用户对于TEST-DB的相关授权权限,但是这个“test-user”这个用户还是存在
REVOKE all on TEST-DB from test-user;
用户表内清除用户: DELETE FROM user WHERE user="test-user";
6 重载授权表:
FLUSH PRIVILEGES;
7 退出mysql数据库:
exit