Unable to establish external connection to MySQL/MariaDB database
I'm trying to connect a MariaDB client (DBeaver) to my database from my home computer. IP address/host, port, username and password connection details are correct.
I can ping the host. The host is active. I can access the database locally on the server. I've set up phpMyAdmin for this scenario. However, for a game server running on a different VPS, I need to be able to make external connections.
This is 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 is 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 browsed and edited my /etc/mysql/mariadb.conf.d/50-server.cnf file like this
Binding address = 0.0.0.0
I also created a new user and set its host to "%" using the GRANT OPTION to ensure that the user is allowed access from the external IP.
Still unable to connect to the database.
sudo iptables -L Return:
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
As far as I know, there is no firewall. I'm currently trying to connect to a database from a local environment, which has a dynamic IP. I plan to only allow setup from other VPS IPs once I reach this point.
UFW is not installed, based on the comments I tried: sudo ufw status
The problem that occurs when I try to connect from the desktop is a timeout issue. It's like the server isn't reading/accepting incoming connections from my IP.
I also used portchecker.co to check the server IP for port 3306 and it came back as Closed even though it says above it is listening.
When trying to connect to HeidiSQL, I get the error: Unable to connect to server at '1.2.3.4' (10060) Of course, 1.2.3.4 has been replaced with the IP of my server.
Check netstat -tlnp | grep 3306 - it returns:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* Listening 3900555/mariadbd
