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!