Gary's picture

Hello,

I am attempting to, using MySQL Workbench, connect to MySQL, using username "garyroot".

I executed the following:

1. I have MySQL user "garyroot" set up with "Any" under "Hosts" (see attached screenshot, "MySQL_Users.png")

2. I made sure that the "MySQL server listening address" is set to "Any" (see attached screenshot, "MySQL_Server.png")

3. I then rebooted the entire appliance.

However, the error returned is "Can't connect to MySQL server (10061)".

Two other points -

a. I ran "netstat -tunlp" at the appliance's terminal, and the output has only one entry for port 3306 (see attached screenshot, "Netstat.png").  Am I correct that "netstat" is telling me that MySQL is not listening for connections on port 3306 other than from localhost?  If so, it would appear that perhaps #2 did not have any effect?

b. Is it a question of also setting up a firewall rule of some sort in the "Linux Firewall" section of the appliance?

Thanks in advance for any suggestions!

Forum: 
Jeremy Davis's picture

I can't see your screenshots unfortunately. But it sounds like you are on the right track, but I'm not sure why it isn't working for you?!

To clarify and confirm, only the standalone "MySQL" appliance itself is pre-configured to allow remote connections. All other appliances that include MySQL (technically MariaDB these days) are bound to localhost.

Personally, I'm a commandline user and I assume from your post you are using Webmin. In essence there are 3 steps required:

  1. Enable listening for remote connections (in /etc/mysql/my.cnf IIRC).
  2. Allowing the specific user you wish to connect with to connect from a remote address (or create a new user that is allowed to connect remotely).
  3. Restart MySQL (to apply config change #1).

There is actually a doc page that explicitly notes how to do this, although it needs checking/updating to ensure that it's still relevant to v15.x. As something of an aside (as I note you are using a separate user) in v15.x appliances, without further config changes, root can ONLY connect from localhost (uses unix socket so no password required). FWIW that's why we create an additional (root like) "Adminer" MySQL user. I can't be sure, but I suspect that if you use those instructions and swap out your "garyroot" user where that says "root", it should still work.

Assuming that you are running your server locally, the firewall should not be giving you any grief. TurnKey ships with firewall rules configured, but the firewall itself is not enabled by default (which we will likely change at some point in the future).

TurnKey appliances on AWS (either via the Hub or AWSMP) provide default security group settings that match the default firewall rules.

Gary's picture

Hi, Jeremy,

Many thanks for your help!

My three screenshots were uploaded in the "Attached" section of the form used to start a new Forum topic.  Sorry they didn't get to you, but it looks like you did not need them anyway.

I am using v.15 of the LAMP Stack appliance (Version 1.881 of the Webmin).  I am attempting to connect to MySQL using MySQL Workbench 6.1 which is running on my personal workstation.  In regard to the three steps you outlined:

1. /etc/mysql/my.cnf appears to be a link to /etc/alternatives/my.cnf, which appears to be a link to /etc/mysql/mariadb.cnf, which does not contain a "bind-address" line.

2. User "garyroot" is a MySQL user I created with the "User Permissions" page of the Webmin, and it has "Any" underneath "Hosts", just like "adminer".  In the MySQL "user" table, I checked the table column entries for "garyroot" against the table column entries for "adminer", and they match, except for the "Password" column.  MySQL Workbench does not let me connect using "adminer" either.

3. Whenever I make any changes, I simply reboot the entire appliance, just to be on the safe side.

What's baffling me is that, on the "MySQL Server Configuration" page of the Webmin, I have "MySQL server listening address" set to "Any".

The output from "netstat -tunlp" is reproduced below.  It seems like there is only one line that has anything to do with MySQL, and that line allows connections on port 3306 only from 127.0.0.1

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      843/mysqld          
tcp        0      0 127.0.0.1:10000         0.0.0.0:*               LISTEN      1055/perl           
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      548/sshd            
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1027/master         
tcp        0      0 127.0.0.1:12319         0.0.0.0:*               LISTEN      711/shellinaboxd    
tcp        0      0 0.0.0.0:12320           0.0.0.0:*               LISTEN      681/stunnel4        
tcp        0      0 0.0.0.0:12321           0.0.0.0:*               LISTEN      681/stunnel4        
tcp6       0      0 127.0.0.1:7983          :::*                    LISTEN      948/java            
tcp6       0      0 :::80                   :::*                    LISTEN      799/apache2         
tcp6       0      0 :::22                   :::*                    LISTEN      548/sshd            
tcp6       0      0 :::8983                 :::*                    LISTEN      948/java            
tcp6       0      0 :::443                  :::*                    LISTEN      799/apache2         
tcp6       0      0 :::12322                :::*                    LISTEN      799/apache2         
udp        0      0 0.0.0.0:10000           0.0.0.0:*                           1055/perl           
udp        0      0 10.1.100.36:123         0.0.0.0:*                           1160/ntpd           
udp        0      0 127.0.0.1:123           0.0.0.0:*                           1160/ntpd           
udp        0      0 0.0.0.0:123             0.0.0.0:*                           1160/ntpd           
udp6       0      0 fe80::250:56ff:fe83:123 :::*                                1160/ntpd           
udp6       0      0 ::1:123                 :::*                                1160/ntpd           
udp6       0      0 :::123                  :::*                                1160/ntpd

 

 

 

 

Jeremy Davis's picture

Your netstat output confirms that it's definitely only listening to localhost for connections.

I've just had a quick look at the standalone MySQL applaince build code and can tell you that the config file which needs adjustment is /etc/mysql/mariadb.conf.d/50-server.cnf (that's the one that has the bind address line). Hopefully commenting that line out and restarting MySQL should do the trick (it sounds like your custom MySQL user account should be good to go). So try this:

sed -i "s|^bind-address|#bind-address|" /etc/mysql/mariadb.conf.d/50-server.cnf
service mysql restart

Hopefully that will do the trick.

Gary's picture

Hi, Jeremy,

Yes, that did the trick.  Thank you so much!

Add new comment