MySQL-Remote access

Discussion in 'Installation/Configuration' started by OnTheRun, May 29, 2006.

  1. OnTheRun

    OnTheRun New Member

    Hello all,
    I'm trying to get the remote-access for mysql working.
    I created a new database and set "Remote Access" to "Yes". But when I try to connect to that database with i.e. MySQL Administrator, I get the following error:

    I then configured the port 3306 in the firewall of ispconfig, as well on our firewall. I then tried to connect from the server itself, on itself. That gave me the following result:

    Code:
    mysql -h www.mydomain.ch -p -P 3306
    ERROR 2003: Can't connect to MySQL server on 'www.mydomain.ch' (111)
    Has anyone a clue, what could be the problem?

    Thank you and with kind regards
     
  2. falko

    falko Super Moderator Howtoforge Staff

    What's the output of
    Code:
    netstat -tap
    ?
     
  3. OnTheRun

    OnTheRun New Member

    Hello falko,
    thank you for your answer.
    The output of netstat -tap is:
    Code:
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 *:8000                  *:*                     LISTEN     1137/icecast
    tcp        0      0 *:imaps                 *:*                     LISTEN     835/couriertcpd
    tcp        0      0 *:pop3s                 *:*                     LISTEN     858/couriertcpd
    tcp        0      0 localhost:mysql         *:*                     LISTEN     21556/mysqld
    tcp        0      0 *:pop3                  *:*                     LISTEN     844/couriertcpd
    tcp        0      0 *:imap2                 *:*                     LISTEN     821/couriertcpd
    tcp        0      0 *:www                   *:*                     LISTEN     22707/apache2
    tcp        0      0 *:81                    *:*                     LISTEN     11887/ispconfig_htt
    tcp        0      0 *:ftp                   *:*                     LISTEN     10578/proftpd: (acc
    tcp        0      0 pptout-chsrv4.pa:domain *:*                     LISTEN     12380/named
    tcp        0      0 localhost:domain        *:*                     LISTEN     12380/named
    tcp        0      0 *:ssh                   *:*                     LISTEN     1078/sshd
    tcp        0      0 *:smtp                  *:*                     LISTEN     10908/master
    tcp        0      0 localhost:953           *:*                     LISTEN     12380/named
    tcp        0      0 *:https                 *:*                     LISTEN     22707/apache2
    tcp        0      0 server.domai:www router:10861         ESTABLISHED21046/apache2
    tcp        0      0 server.domai:www router:10860         TIME_WAIT  -
    tcp        0      0 server.domai:www router:10862         ESTABLISHED21043/apache2
    tcp        0      0 server.domai:53873 klecker.debian.org:www  TIME_WAIT  -
    tcp        0    148 server.domai:ssh 10.0.0.158:1255         ESTABLISHED21149/0
    tcp        0      0 server.domai:8000 server.domai:48105 ESTABLISHED1137/icecast
    tcp        0      1 server.domai:www 77.net116.adsl.ev:58519 LAST_ACK   -
    tcp        0      0 server.domai:48105 server.domai:8000 ESTABLISHED29429/ices
    With kind regards
     
  4. falko

    falko Super Moderator Howtoforge Staff

    MySQL is listening only on 127.0.0.1 and not on all interfaces:

    Change this in your my.cnf file and restart MySQL.
     
  5. OnTheRun

    OnTheRun New Member

    Hello,
    thank you for your answer :)
    I commented bind-address now in my.cnf and netstat -tap looks like this:

    Code:
    tcp        0      0 *:mysql                 *:*                     LISTEN     32403/mysqld
    The strange thing is, when I connect with:

    Code:
    mysql -h server.domain.local -P 3306 -p
    I always get acces denied. This happens too, if I use the tld (i.e. www.domain.ch or domain.ch).

    But when I use:

    Code:
    mysql -h 127.0.0.1 -P 3306 -p
    it works like a charm. I used the same user and password (root) in both attempts.
    Do you have any idea, what could be the problem?

    Thank you and with kind regards
     
  6. falko

    falko Super Moderator Howtoforge Staff

    If you have a phpMyAdmin, go to the mysql database and there to the table users. Have a look at the Host column and see which hostnames are listed there. These are the hostnames you can use to connect.
     
  7. tristanlee85

    tristanlee85 New Member

    I'm in the same situation. I've looked at the tables and see where all the hostnames are set to localhost. I've got a domain plastikhosting.net that is hosted on a friend's server that is going to need to connect to my MySQL server on my domain plastikracing.net located on my ISPConfig server. Is there anyway to set plastikracing.net to accept both localhost and plastikhosting.net MySQL connections?
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    You can add a new user in the mysql user table for the host plastikhosting.net or you chech the "Remote Access" Checkbox in the mysql settings in ISPConfig.
     
  9. falko

    falko Super Moderator Howtoforge Staff

    And make sure your firewall doesn't block port 3306 and that MySQL is listening on all interfaces.
     

Share This Page