mysql 开启远程登陆权限 liunx篇
mysql安装完成后默认是不允许远程登陆的
今天记录一下开启远程登陆的全部过程
我的服务器是 ubuntu 18 环境是 lnmp
首先登陆mysql
mysql -u root -p
use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456'; // 这里的123456为你给新增权限用户设置的密码,%代表所有主机,也可以具体到你的主机ip地址
flush privileges; //这句表示从mysql数据库的grant表中重新加载权限数据
mysql> select user,host from user; //查询是否设置成功
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
然后我在本地登陆还是登陆不上!!!
错误提示 2003
查看一下端口通了没:
本地机器cmd输入
telnet ip 3306
不通~
查看服务器防火墙:
iptables --list
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all -- anywhere anywhere
ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED
ACCEPT tcp -- anywhere anywhere tcp dpt:ssh
ACCEPT tcp -- anywhere anywhere tcp dpt:http
ACCEPT tcp -- anywhere anywhere tcp dpt:https
DROP tcp -- anywhere anywhere tcp dpt:mysql
ACCEPT icmp -- anywhere anywhere icmp echo-request
发现mysql是DROP
尝试修改
vim /etc/iptables/rules.v4
修改DROP为ACCEPT
# Generated by iptables-save v1.6.1 on Thu Jun 24 14:57:24 2021
*filter
:INPUT ACCEPT [17:680]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [112:144896]
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 443 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT #修改DROP为ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
COMMIT
# Completed on Thu Jun 24 14:57:24 2021
~
保存成功后重启服务器。
我用的是腾讯云
要在安全组中添加3306端口为开放。