我正在嘗試從我的家用電腦將 MariaDB 用戶端（DBeaver）連接到我的資料庫。 IP 位址/主機、連接埠、使用者名稱和密碼連線詳細資訊均正確。
我能夠 ping 通主機。樓主活躍。我可以在伺服器本地存取資料庫。我已經為這種情況設定了 phpMyAdmin。但是，為了在不同的 VPS 上運行的遊戲伺服器，我需要能夠進行外部連線。
這是我的 /etc/mysql/my.cnf 檔案：
[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
此外，這是我的 50-server.cnf 檔案：
# # 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]
我瀏覽了一下並編輯了我的 /etc/mysql/mariadb.conf.d/50-server.cnf 文件，這樣
綁定位址 = 0.0.0.0
我還建立了一個新用戶，並使用 GRANT OPTION 將其主機設為“%”，以確保允許該用戶從外部 IP 存取。
仍然無法連接到資料庫。
sudo iptables -L 回傳：
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
據我所知，沒有防火牆。我目前正在嘗試從本地環境連接到資料庫，該環境具有動態 IP。我計劃僅在達到這一點後才允許從其他 VPS IP 進行設定。
UFW 未安裝，根據我嘗試過的評論：sudo ufw status
#當我嘗試從桌面連線時，出現的問題是逾時問題。就好像伺服器沒有讀取/接受來自我的 IP 的傳入連線。
我還使用 portchecker.co 檢查連接埠 3306 的伺服器 IP，它會傳回為 Closed，儘管上面顯示它正在監聽。
當嘗試連接 HeidiSQL 時，發生錯誤：無法連接到「1.2.3.4」上的伺服器 (10060) 當然，1.2.3.4 已替換為我伺服器的 IP。
檢查 netstat -tlnp | grep 3306 - 它傳回：
tcp 0 0 0.0.0.0:3306 0.0.0.0:* 監聽 3900555/mariadbd
