Tidak dapat mewujudkan sambungan luaran ke pangkalan data MySQL/MariaDB
Saya cuba menyambungkan klien MariaDB (DBeaver) ke pangkalan data saya dari komputer rumah saya. Alamat IP/hos, port, nama pengguna dan butiran sambungan kata laluan adalah betul.
Saya boleh ping hos. Tuan rumah aktif. Saya boleh mengakses pangkalan data secara setempat pada pelayan. Saya telah menyediakan phpMyAdmin untuk senario ini. Walau bagaimanapun, untuk pelayan permainan yang berjalan pada VPS yang berbeza, saya perlu dapat membuat sambungan luaran.
Ini ialah fail /etc/mysql/my.cnf saya:
[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
Selain itu, berikut ialah fail 50-server.cnf saya:
# # 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]
Saya melihat dan mengedit fail /etc/mysql/mariadb.conf.d/50-server.cnf saya supaya
绑定地址 = 0.0.0.0
Saya juga mencipta pengguna baharu dan menetapkan hosnya kepada "%" menggunakan GRANT OPTION untuk memastikan pengguna dibenarkan akses daripada IP luaran.
Masih tidak dapat menyambung ke pangkalan data.
sudo iptables -L Kembali ke:
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
Setahu saya, tiada firewall. Saya sedang cuba menyambung ke pangkalan data daripada persekitaran tempatan, yang mempunyai IP dinamik. Saya bercadang untuk hanya membenarkan persediaan daripada IP VPS lain sebaik sahaja saya mencapai tahap ini.
UFW tidak dipasang, berdasarkan komen yang saya cuba: sudo ufw status
Masalah yang berlaku apabila saya cuba menyambung dari desktop ialah isu tamat masa. Ia seperti pelayan tidak membaca/menerima sambungan masuk daripada IP saya.
Saya juga menggunakan portchecker.co 检查端口 3306 的服务器 IP，它返回为 Closed walaupun ia mengatakan di atas ia mendengar.
Apabila cuba menyambung ke HeidiSQL, saya mendapat ralat: 无法连接到“1.2.3.4”上的服务器 (10060) Sudah tentu, 1.2.3.4 telah digantikan dengan IP pelayan saya.
Semak netstat -tlnp | grep 3306 - ia kembali:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* 监听 3900555/mariadbd
