环境：
1.本机：Windows10，sqlyog
2.服务器： Ubuntu16.04，MySQL5.7.33
MySQL我用的是apt安装的，有很多问题，建议大家用二进制程序安装。
问题描述
在服务器上登录MySQL只能用本地的127.0.0.1地址，用服务器本身地址会提示拒绝访问
可以连接
拒绝连接
尝试修改MySQL远程访问权限
# 在第一次开启MySQL服务的时候，会自动生成一个随机的密码
# 到mysqld.log文件中查找password
root@sfpql:~# grep password /var/log/mysqld.log
2020-12-16T07:47:14.117739Z 1 [Note] A temporary password is generated for
root@localhost: pVLJs6&o(QQe
# 使用这个随机密码登录到MySQL
root@sfpql:~# mysql -u root -p
pVLJs6&o(QQe # 这里用找到的密码登录
# 修改密码
alter user root@localhost identified by '123456';
# 远程授权
# 这里修改的是所有ip都可以远程访问MySQL
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
修改后发现没有用，MySQL依然拒绝远程访问
查看3306端口
root@sfpql:~# netstat -an|grep 3306
查看端口
发现3306端口前面地址是127.0.0.1，怪不得只能用127.0.0.1的地址登录
修改MySQL服务地址：
-
查找MySQL
查找MySQL
-
查看MySQL配置文件
配置文件
-
修改配置文件
vi /etc/mysql/my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
# 添加如下代码
[mysqld]
bind-address = 0.0.0.0
~
~
~
"/etc/mysql/my.cnf" 23L, 714C
- 重启MySQL服务
systemctl stop mysql不是apt安装的MySQL用systemctl stop mysqld
systemctl start mysql不是apt安装的MySQL用systemctl start mysqld - 查看3306端口
netstat -an|grep 3306
端口状态
尝试远程连接
telnet sfpql 3306
能够连接
结语
MySQL远程连接不上是常见问题，一般设置好MySQL的远程访问权限就可以了。
apt安装的MySQL会自己将启动地址设置在本地，同时apt安装的MySQL的服务查看命令是systemctl status mysql其他安装方式的常看命令是systemctl status mysqld
网友评论