系统Ubuntu16.04 LTS
1、开放3306端口
查看端口状态:
netstat -an|grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
目前只有本机可以访问
输入指令:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
如果之前配置过全局配置文件,也可输入指令:
sudo vim /etc/mysql/my.cnf
修改文件,找到下面那行,在bind前面加一个#
bind-address = 127.0.0.1
:wq退出
重启MySQL:
sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
查看端口状态:
netstat -an|grep 3306
tcp6 0 0 :::3306 :::* LISTEN
至此端口放开了
2、下面MySQL新建远程连接用户
新建用户名密码开放远程连接（对所有局域网内主机）：
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
新建用户名密码开放远程连接（对局域网内指定IP）
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
删除用户：
use mysql;
delete from user where user='admin' and host='%';
新建无权限用户:
grant usage on *.* to 'foreigner'@'%' identified by 'foreigner';
增加其权限:
grant all privileges on db.* to 'foreigner'@'%';
撤回权限:
revoke all privileges on db.* from 'foreigner'@'%';
查看权限:
mysql> show grants for 'foreigner'@'%';
+-----------------------------------------------------+
| Grants for foreigner@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'foreigner'@'%' |
| GRANT ALL PRIVILEGES ON `soso`.* TO 'foreigner'@'%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
centOs系统:以root权限进入MySQL
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit;
重启MySQL.
service mysqld stop
service mysqld start