无法外部连接到MySQL / MariaDB数据库。

huangapple go评论68阅读模式
英文:

Can't Connect to MySQL / MariaDB Database Externally

问题

我理解你遇到了连接MariaDB的问题,尽管你已经做了一些配置。以下是你提供的信息的翻译:

  • 你正在尝试从家用电脑连接MariaDB客户端(DBeaver)到你的数据库。IP地址/主机、端口、用户名和密码连接信息都是正确的。
  • 你可以ping通主机,主机也是活动的。你可以在服务器上本地访问数据库,为此你已经设置了phpMyAdmin。但是,出于游戏服务器的需要,你需要能够从外部连接。
  • 你提供了 /etc/mysql/my.cnf 文件的内容以及 /etc/mysql/mariadb.conf.d/50-server.cnf 文件的内容,其中包含了一些配置信息。
  • 你已经将 bind-address 设置为 0.0.0.0 以允许来自外部IP的连接。
  • 你创建了一个新用户,并将其主机设置为 '%',并赋予了 GRANT OPTION 以确保该用户允许从外部IP访问。
  • 尽管做了上述配置,但你仍然无法连接到数据库。
  • 通过运行 sudo iptables -L,你显示了防火墙规则,其中包含了允许连接到端口 3306 的规则。
  • 你指出没有防火墙规则来阻止连接。
  • 当你尝试从桌面连接时,出现超时错误,好像服务器没有读取/接受来自你的IP的连接。
  • 使用 HeidiSQL 连接时,出现错误 Can't connect to server on '1.2.3.4' (10060)

请注意,尽管端口 3306 正在监听(LISTENING),但 portchecker.co 检查显示该端口为关闭(Closed)。这可能需要进一步的调查,以确保端口正确开放并且防火墙规则正确配置。

你可能需要确保服务器上没有其他防火墙或网络访问限制,以及确保MariaDB配置正确。你还可以检查网络连接是否正常,确保能够从外部IP到达服务器。如果问题仍然存在,可能需要深入检查MariaDB的日志以获取更多信息。

英文:

I'm trying to connect a MariaDB client(DBeaver) to my database from my home PC. The IP Address/Host, port, the username, and password connection details are all correct.

I'm able to ping the host. The host is active. I can access the database locally on the server. I have phpMyAdmin set up for that case. However, I need to be able to externally connect for the sake of a game server I'm running on a different VPS as well.

Here's my /etc/mysql/my.cnf file:

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
[mysqld]
log_warnings=1
innodb_file_per_table = ON

Also, here's my 50-server.cnf file:

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#

user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

#
# * Fine Tuning
#

#key_buffer_size        = 128M
#max_allowed_packet     = 1G
#thread_stack           = 192K
#thread_cache_size      = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections        = 100
#table_cache            = 64

#
# * Logging and Replication
#

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 10
#log_slow_verbosity     = query_plan,explain
#log-queries-not-using-indexes
#min_examined_row_limit = 1000

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size        = 100M

#
# * SSL/TLS
#

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.5]

I've browsed around and I've edited my /etc/mysql/mariadb.conf.d/50-server.cnf file so that
bind-address = 0.0.0.0

I've also created a new user and set his host to '%' with GRANT OPTION to ensure that the user's allowed access from an external IP.

Still, to no avail, I'm not able to connect to the database.

sudo iptables -L returns:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ufw-before-logging-input  all  --  anywhere             anywhere
ufw-before-input  all  --  anywhere             anywhere
ufw-after-input  all  --  anywhere             anywhere
ufw-after-logging-input  all  --  anywhere             anywhere
ufw-reject-input  all  --  anywhere             anywhere
ufw-track-input  all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:3306

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
ufw-before-logging-forward  all  --  anywhere             anywhere
ufw-before-forward  all  --  anywhere             anywhere
ufw-after-forward  all  --  anywhere             anywhere
ufw-after-logging-forward  all  --  anywhere             anywhere
ufw-reject-forward  all  --  anywhere             anywhere
ufw-track-forward  all  --  anywhere             anywhere

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
ufw-before-logging-output  all  --  anywhere             anywhere
ufw-before-output  all  --  anywhere             anywhere
ufw-after-output  all  --  anywhere             anywhere
ufw-after-logging-output  all  --  anywhere             anywhere
ufw-reject-output  all  --  anywhere             anywhere
ufw-track-output  all  --  anywhere             anywhere

Chain ufw-after-forward (1 references)
target     prot opt source               destination

Chain ufw-after-input (1 references)
target     prot opt source               destination

Chain ufw-after-logging-forward (1 references)
target     prot opt source               destination

Chain ufw-after-logging-input (1 references)
target     prot opt source               destination

Chain ufw-after-logging-output (1 references)
target     prot opt source               destination

Chain ufw-after-output (1 references)
target     prot opt source               destination

Chain ufw-before-forward (1 references)
target     prot opt source               destination

Chain ufw-before-input (1 references)
target     prot opt source               destination

Chain ufw-before-logging-forward (1 references)
target     prot opt source               destination

Chain ufw-before-logging-input (1 references)
target     prot opt source               destination

Chain ufw-before-logging-output (1 references)
target     prot opt source               destination

Chain ufw-before-output (1 references)
target     prot opt source               destination

Chain ufw-reject-forward (1 references)
target     prot opt source               destination

Chain ufw-reject-input (1 references)
target     prot opt source               destination

Chain ufw-reject-output (1 references)
target     prot opt source               destination

Chain ufw-track-forward (1 references)
target     prot opt source               destination

Chain ufw-track-input (1 references)
target     prot opt source               destination

Chain ufw-track-output (1 references)
target     prot opt source               destination

There's no firewall in place as far as I'm aware. I'm trying to currently connect to the database from my local environment, which has a dynamic IP. I'm planning on setting to be allowed from the other VPS IP solely once I get to that point.

UFW is not installed, as per a comment I tried: sudo ufw status

When I try connecting from my desktop, the issue that's presented is a timeout issue. As if the server's not reading/accepting an incoming connection from my IP.

I've also utilized portchecker.co to check port 3306 for the server's IP and it is coming back as Closed, even though above it shows it's LISTENING.

When trying to connect with HeidiSQL I get the error: Can't connect to server on '1.2.3.4' (10060) Of course, 1.2.3.4, is replaced with my server's IP.

Upon checking netstat -tlnp | grep 3306 - it returns:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3900555/mariadbd

答案1

得分: 0

与托管公司来回沟通,让他们在他们的一端修复它。他们从未给我提供确切的问题原因,但通过工单,他们成功解决了端口的问题。

英文:

Had to go back and forth with the hosting company to get them to fix it on their end. They never gave me an exact reasoning for the problem, however through ticketing they were able to resolve the issue with the port.

huangapple
  • 本文由 发表于 2023年6月1日 01:46:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76376096.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定