2

I have two Raspbery PI 3 which I have installed (almost) identical. The configuration of /etc/mysql/mariadb.conf.d/50-server.cnf is identical for both Raspberrys.

Problem: I can only connect to the mysql database of one of these Raspberrys (using MySQL Workbench). So I looked for the problem and found a more "low level" problem: even telnet is not working on one of these Raspberrys.

telnet raspberry1 3306

works perfect, while

telnet raspberry2 3306

tells me that a connection cannot be established.

A ping works from my windows pc to both raspberrys.
A local connection to the MySQL server also works on both Raspberrys.

I even tried to disable any firewall on raspberry2 using sudo /sbin/iptables --flush

When using a port scanner like sudo nmap -sS -O raspberry1 raspberry2 I see that port 3306 is open on raspberry1 but not on raspberry2.

sudo netstat -ln gives me a slightly different output on both RPi's:

raspberry1:
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
raspberry2:
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

Edit:
As mentioned above the file 50-server.cnf is identical on both raspberries - especially the line bind-address = 0.0.0.0 is set in both files.
(I also restarted raspberry2 using sudo /etc/init.d/mysql restart. I even restarted Raspbian.)
I still have no clue where the Local Address 127.0.0.1 comes from.

Where could be differences in the configuration of the second Raspberry Pi?

  • how are you running the telnet commands? – jsotola May 19 '18 at 22:34
  • @jsotola: On a windows computer I entered the commandline (cmd) and sent the commands ("telnet raspberry2 3306") – Michael Hutter May 20 '18 at 10:58
  • @Fabian: I try to connect to the database using the program "MySQL Workbench". But this can't work as long as telnet isn't able to get a connection. With my question I ask for ideas, which differences between both Raspberrys could cause this problem. – Michael Hutter May 20 '18 at 11:25
  • Any firewalls in the mix, either on the network or on the server? Might they be doing an IP whitelist or similar? – Paul May 20 '18 at 13:51
  • 1
    please run on both RPis sudo netstat -ln | grep 3306 and post the result – Fabian May 20 '18 at 21:34
2

By default, the MariaDB server listens for TCP/IP connections on a network socket bound to a single address [...] In Debian [...] the default bind_address is 127.0.0.1, which binds the server to listen on localhost only.

source: Server System Variables - MariaDB Knowledge Base

sudo netstat -ln shows in the column "Local Address" for your raspberry1 "0.0.0.0:3306" (all IPv4s are allowed to access this device on port 3306) and for your raspberry2 "127.0.0.1:3306" (only localhost is allowed):

raspberry1:
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
raspberry2:
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

This is the reason why you can connect to raspberry1 on port 3306, but not to raspberry2 on the same port. To make your raspberry2 accessible just set the correct bind_address in mycnf.

Security advice: If your RPis are not behind a well managed firewall, please read why it is a bad idea to allow direct connections to your databases and how you can configure MariaDB in a safe way for remote client access.

|improve this answer|||||
  • Both files 50-server.cfg already contain bind-address = 0.0.0.0 and I restarted the mysql server (and even the whole operating system). I still get the Local Address 127.0.0.1. Do you know if there is any way how the setting in the config file may be overwritten (e. g. by command line param)? My problem still exists :-( – Michael Hutter May 21 '18 at 20:32
  • @MichaelHutter for testing purposes set the bind_address at 50-server.cfg to a different IPv4 from you network, i.e. 192.168.178.1 and restart. Then run again netstat and let me know the result (maybe you've edited the wrong config file). – Fabian May 21 '18 at 21:13
  • I found my mistake. I had a second (backup) file with extension .cnf in the folder /etc/mysql/mariadb.conf.d. This file was additionally executed after my real .cnf file. Now with your answer I am able to connect to the database. Thank you very much! – Michael Hutter May 22 '18 at 20:43
  • @MichaelHutter Glad to hear it's solved! Cheers! – Fabian May 22 '18 at 21:49

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.