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

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

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:

  1. [client-server]
  2. # Port or socket location where to connect
  3. # port = 3306
  4. socket = /run/mysqld/mysqld.sock
  6. # Import all .cnf files from configuration directory
  7. !includedir /etc/mysql/conf.d/
  8. !includedir /etc/mysql/mariadb.conf.d/
  9. [mysqld]
  10. log_warnings=1
  11. innodb_file_per_table = ON

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

  1. #
  2. # These groups are read by MariaDB server.
  3. # Use it for options that only the server (but not clients) should see
  5. # this is read by the standalone daemon and embedded servers
  6. [server]
  8. # this is only for the mysqld standalone daemon
  9. [mysqld]
  11. #
  12. # * Basic Settings
  13. #
  15. user                    = mysql
  16. pid-file                = /run/mysqld/mysqld.pid
  17. basedir                 = /usr
  18. datadir                 = /var/lib/mysql
  19. tmpdir                  = /tmp
  20. lc-messages-dir         = /usr/share/mysql
  21. lc-messages             = en_US
  22. skip-external-locking
  24. # Broken reverse DNS slows down connections considerably and name resolve is
  25. # safe to skip if there are no "host by domain name" access grants
  26. #skip-name-resolve
  28. # Instead of skip-networking the default is now to listen only on
  29. # localhost which is more compatible and is not less secure.
  30. bind-address            = 0.0.0.0
  32. #
  33. # * Fine Tuning
  34. #
  36. #key_buffer_size        = 128M
  37. #max_allowed_packet     = 1G
  38. #thread_stack           = 192K
  39. #thread_cache_size      = 8
  40. # This replaces the startup script and checks MyISAM tables if needed
  41. # the first time they are touched
  42. #myisam_recover_options = BACKUP
  43. #max_connections        = 100
  44. #table_cache            = 64
  46. #
  47. # * Logging and Replication
  48. #
  50. # Both location gets rotated by the cronjob.
  51. # Be aware that this log type is a performance killer.
  52. # Recommend only changing this at runtime for short testing periods if needed!
  53. #general_log_file       = /var/log/mysql/mysql.log
  54. #general_log            = 1
  56. # When running under systemd, error logging goes via stdout/stderr to journald
  57. # and when running legacy init error logging goes to syslog due to
  58. # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
  59. # Enable this if you want to have error logging into a separate file
  60. #log_error = /var/log/mysql/error.log
  61. # Enable the slow query log to see queries with especially long duration
  62. #slow_query_log_file    = /var/log/mysql/mariadb-slow.log
  63. #long_query_time        = 10
  64. #log_slow_verbosity     = query_plan,explain
  65. #log-queries-not-using-indexes
  66. #min_examined_row_limit = 1000
  68. # The following can be used as easy to replay backup logs or for replication.
  69. # note: if you are setting up a replication slave, see README.Debian about
  70. #       other settings you may need to change.
  71. #server-id              = 1
  72. #log_bin                = /var/log/mysql/mysql-bin.log
  73. expire_logs_days        = 10
  74. #max_binlog_size        = 100M
  76. #
  77. # * SSL/TLS
  78. #
  80. # For documentation, please read
  81. # https://mariadb.com/kb/en/securing-connections-for-client-and-server/
  82. #ssl-ca = /etc/mysql/cacert.pem
  83. #ssl-cert = /etc/mysql/server-cert.pem
  84. #ssl-key = /etc/mysql/server-key.pem
  85. #require-secure-transport = on
  87. #
  88. # * Character sets
  89. #
  91. # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
  92. # utf8 4-byte character set. See also client.cnf
  93. character-set-server  = utf8mb4
  94. collation-server      = utf8mb4_general_ci
  96. #
  97. # * InnoDB
  98. #
  100. # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  101. # Read the manual for more InnoDB related options. There are many!
  102. # Most important is to give InnoDB 80 % of the system RAM for buffer use:
  103. # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
  104. #innodb_buffer_pool_size = 8G
  106. # this is only for embedded server
  107. [embedded]
  109. # This group is only read by MariaDB servers, not by MySQL.
  110. # If you use the same .cnf file for MySQL and MariaDB,
  111. # you can put MariaDB-only options here
  112. [mariadb]
  114. # This group is only read by MariaDB-10.5 servers.
  115. # If you use the same .cnf file for MariaDB of different versions,
  116. # use this group for options that older servers don't understand
  117. [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:

  1. Chain INPUT (policy ACCEPT)
  2. target     prot opt source               destination
  3. ufw-before-logging-input  all  --  anywhere             anywhere
  4. ufw-before-input  all  --  anywhere             anywhere
  5. ufw-after-input  all  --  anywhere             anywhere
  6. ufw-after-logging-input  all  --  anywhere             anywhere
  7. ufw-reject-input  all  --  anywhere             anywhere
  8. ufw-track-input  all  --  anywhere             anywhere
  9. ACCEPT     tcp  --  anywhere             anywhere             tcp dpt:3306
  11. Chain FORWARD (policy ACCEPT)
  12. target     prot opt source               destination
  13. ufw-before-logging-forward  all  --  anywhere             anywhere
  14. ufw-before-forward  all  --  anywhere             anywhere
  15. ufw-after-forward  all  --  anywhere             anywhere
  16. ufw-after-logging-forward  all  --  anywhere             anywhere
  17. ufw-reject-forward  all  --  anywhere             anywhere
  18. ufw-track-forward  all  --  anywhere             anywhere
  20. Chain OUTPUT (policy ACCEPT)
  21. target     prot opt source               destination
  22. ufw-before-logging-output  all  --  anywhere             anywhere
  23. ufw-before-output  all  --  anywhere             anywhere
  24. ufw-after-output  all  --  anywhere             anywhere
  25. ufw-after-logging-output  all  --  anywhere             anywhere
  26. ufw-reject-output  all  --  anywhere             anywhere
  27. ufw-track-output  all  --  anywhere             anywhere
  29. Chain ufw-after-forward (1 references)
  30. target     prot opt source               destination
  32. Chain ufw-after-input (1 references)
  33. target     prot opt source               destination
  35. Chain ufw-after-logging-forward (1 references)
  36. target     prot opt source               destination
  38. Chain ufw-after-logging-input (1 references)
  39. target     prot opt source               destination
  41. Chain ufw-after-logging-output (1 references)
  42. target     prot opt source               destination
  44. Chain ufw-after-output (1 references)
  45. target     prot opt source               destination
  47. Chain ufw-before-forward (1 references)
  48. target     prot opt source               destination
  50. Chain ufw-before-input (1 references)
  51. target     prot opt source               destination
  53. Chain ufw-before-logging-forward (1 references)
  54. target     prot opt source               destination
  56. Chain ufw-before-logging-input (1 references)
  57. target     prot opt source               destination
  59. Chain ufw-before-logging-output (1 references)
  60. target     prot opt source               destination
  62. Chain ufw-before-output (1 references)
  63. target     prot opt source               destination
  65. Chain ufw-reject-forward (1 references)
  66. target     prot opt source               destination
  68. Chain ufw-reject-input (1 references)
  69. target     prot opt source               destination
  71. Chain ufw-reject-output (1 references)
  72. target     prot opt source               destination
  74. Chain ufw-track-forward (1 references)
  75. target     prot opt source               destination
  77. Chain ufw-track-input (1 references)
  78. target     prot opt source               destination
  80. Chain ufw-track-output (1 references)
  81. 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
  • connection
  • debian
  • external
  • mariadb
  • mysql
匿名

发表评论

匿名网友

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

确定