1

Hello I am trying to connect to an external MariaDB server. The configurations files on this installations are in a different place but I managed to find my way around. The installation im using is:

Server version: 10.1.41-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

I have added the external user:

MariaDB [(none)]> select user, host from mysql.user;
+---------+---------------+
| user    | host          |
+---------+---------------+
| username| my-ip         |
| username| localhost     |
| root    | localhost     |
+---------+---------------+

And I have found the bind-address in a very strange directory configuration file in /etc/mysql/mariadb.conf.d/50-server.cnf along with other strangely named configuration files. But it was the only file in the /etc/mysql/ directory with a [mysqld] section. In previous mysql installations I found it in the /etc/init.d/ directory. There is no my.cnf in the etc or home directory.

50-client.cnf  50-mysql-clients.cnf  50-mysqld_safe.cnf  50-server.cnf
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
bind-address = my-ip
port = 3306

Here are my defaults:

$ mysqld --print-defaults
mysqld would have been started with the following arguments:
--user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --lc-messages-dir=/usr/share/mysql --skip-external-locking --bind-address=my-ip --port=3306 --key_buffer_size=16M --max_allowed_packet=16M --thread_stack=192K --thread_cache_size=8 --myisam_recover_options=BACKUP --query_cache_limit=1M --query_cache_size=16M --log_error=/var/log/mysql/error.log --expire_logs_days=10 --max_binlog_size=100M --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci 

I enabled port using ufw command:

sudo netstat -plutn | grep 3306
[sudo] password for username: 
tcp        0      0 myip:3306      0.0.0.0:*               LISTEN      15696/mysqld  

And port is visible from outside though the server seems to block ping based port scanning so I hade to use a specific nmap option:

$ nmap -p 3306 -Pn server-ip

Starting Nmap 7.60 ( https://nmap.org ) at 2019-11-11 13:04 CET
Nmap scan report for no-reverse-yet.local (server-ip)
Host is up.

PORT     STATE    SERVICE
3306/tcp filtered mysql

Nmap done: 1 IP address (1 host up) scanned in 2.14 seconds
mycomputer:~$ nmap -p 3306 server-ip

Starting Nmap 7.60 ( https://nmap.org ) at 2019-11-11 13:06 CET
Note: Host seems down. If it is really up, but blocking our ping probes, try -Pn
Nmap done: 1 IP address (0 hosts up) scanned in 3.14 seconds

So I dont see why when I try to connect to it, it still does not work?

pi@raspberrypi:~ $ mysql -h server-ip -P 3306 -u username -p
Enter password: 
ERROR 2002 (HY000): Can't connect to MySQL server on 'server-ip' (115)

Using tcpdump during the attempt to connect gives me this:

$ sudo tcpdump -i eth0 port 3306            
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes
13:29:42.998728 IP myipaddress > no-reverse-yet.local.mysql: Flags [S], seq 2240116053, win 29200, options [mss 1460,sackOK,TS val 785971424 ecr 0,nop,wscale 7], length 0

It shouldnt by UFW filtering my ports:

$ sudo ufw status
Status: active

To                         Action      From
--                         ------      ----
3306/tcp                   ALLOW       Anywhere                  
3306                       ALLOW       my-ip            
3306/tcp (v6)              ALLOW       Anywhere (v6)   

solution: just like @oxfn suggested i put host as % and I also put bind address to 0.0.0.0. This allowed me to connect to the server. What I believe the problem was is that the previous version of mysql verified my by IP and this version verified me by any given URL which you can see below the IP on this page https://myip.is/ . In my case, my ISP gave my router for some reason a URL.

Thank you guys for your suggestions I would not have done it otherwise!

  • netstat -plutn | grep 3306 doesn't prove the port is open on the firewall. Use network utilities (e.g. nc) to verify. – mustaccio Nov 10 at 15:46
  • @mustaccio yes thankyou. I have added the external nmap scan to the problem. though i still cannot connect :/ – maxsieg Nov 11 at 12:20
  • @a_horse_with_no_name sorry but doesnt the "mysqld --print-defaults" show the SQL configuration? I would like to by able to access and edit the SQL server remotely without using ssh, so that I can have a program running on my server that reads and edits entries. – maxsieg Nov 11 at 12:55
  • Check that your user is allowed to connect on this host. It is determined in mysql.users table, column host. You can set host value to % to allow to connect on any host, applicable to your server. The problem is that Mysql/Maria says "can't connect ..." on any login/connection error – oxfn Nov 11 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.