ubuntu系统中mysql8设置用户远程连接流程步骤
环境架构：
ubuntu 20.04
lnmp 1.18一键安装包
mysql 8.0.28进入步骤：
用xshell登录ubuntu系统-进入mysql
[email protected]:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 Source distribution
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Database changed
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| 127.0.0.1 | root | mysql_native_password |
| ::1 | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)
mysql> update user set host='%' where user ='root' and host = 'localhost';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| 127.0.0.1 | root | mysql_native_password |
| ::1 | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
6 rows in set (0.00 sec)
注意：我这里用了lnmp一键安装包默认已改好了
caching_sha2_password加密方式在远程访问时候不支持，需要改成mysql_native_password
修改方式1：
在/etc/my.conf文件修改一下参数
[mysqld]
default_authentication_plugin = mysql_native_password
修改方式2：
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
修改防火墙iptables规则
（我这为了方便直接就删掉阻止3306端口这条规则）
[email protected]:~# iptables -L -n --line-numbers
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
2 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80
5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:443
6 DROP tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306
7 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmptype 8
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
[email protected]:~# iptables -D INPUT 6
[email protected]:~# iptables -L -n --line-numbers
Chain INPUT (policy ACCEPT)
num target prot opt source destination
1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
2 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
3 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22
4 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:80
5 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:443
6 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmptype 8
Chain FORWARD (policy ACCEPT)
num target prot opt source destination
Chain OUTPUT (policy ACCEPT)
num target prot opt source destination
到此远程连接权限就配置好了，对了如果是云环境就需要开放安全组了
比如 阿里云，腾讯云等
用Navicat连接一下
相关文章