英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论