ubuntu系统中mysql8设置用户远程连接流程步骤
数据库  /  管理员 发布于 3年前   965
环境架构:
ubuntu 20.04
lnmp 1.18一键安装包
mysql 8.0.28
进入步骤:
用xshell登录ubuntu系统-进入mysql
root@iZwz984fp76m12whtx8tpgZ:~# 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端口这条规则)
root@iZwz984fp76m12whtx8tpgZ:~# 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
root@iZwz984fp76m12whtx8tpgZ:~# iptables -D INPUT 6
root@iZwz984fp76m12whtx8tpgZ:~# 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连接一下
122 在
学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..123 在
Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号