Professional Web Applications Themes

MySQL won't listen - MySQL

I set up a small LAN with 2 machines (acer.loc IP:192.168.1.33 and hppav.loc IP: 192.168.1.34) for test purposes. Each client can access its own MySQL db w/o problems either via the cmd prompt or MyODBC: acer: mysql -h192.168.1.33 -u<usr> -p<pwd> or hppav: mysql -h192.168.1.34 -u<usr> -p<pwd> The problem arises when trying to access the MySQL db from the other client. For example, if I want to connect to the hppav db from 'acer.loc' via: mysqL -h192.168.1.34 -u<usr> -p<pwd> I get the following error message: 'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL server'. The listening port ...

  1. #1

    Default MySQL won't listen

    I set up a small LAN with 2 machines (acer.loc IP:192.168.1.33 and
    hppav.loc IP: 192.168.1.34) for test purposes. Each client can access
    its own MySQL db w/o problems either via the cmd prompt or MyODBC:
    acer: mysql -h192.168.1.33 -u<usr> -p<pwd>
    or
    hppav: mysql -h192.168.1.34 -u<usr> -p<pwd>

    The problem arises when trying to access the MySQL db from the other
    client. For example, if I want to connect to the hppav db from
    'acer.loc' via:
    mysqL -h192.168.1.34 -u<usr> -p<pwd>
    I get the following error message:
    'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
    server'.

    The listening port is default 3306.

    Running 'netstat -a' reveals that MySQL is not listed as a listener.

    Question: How can I force MySQL to listen to access calls originating
    from clients on the network?

    Thanks for any hints.

    Mark

    msch-prv@bluewin.ch Guest

  2. #2

    Default Re: MySQL won't listen

    [email]msch-prvbluewin.ch[/email] wrote:
    > mysqL -h192.168.1.34 -u<usr> -p<pwd>
    > I get the following error message:
    > 'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
    > server'.
    Well, error messages 1000-1999 are server-generated error messages. So
    you _are_ connecting to the MySQL server, but this is its response.

    This may be due to a "bind-address=127.0.0.1" setting in the my.cnf (or
    my.ini) file on the server. This option is typically enabled during
    installation. It means that only clients from 127.0.0.1 (localhost) can
    connect. Edit your my.cnf and comment out this statement, if it is there.

    Also make sure you've use GRANT to enable user <usr> to connect from the
    remote client host.
    See [url]http://dev.mysql.com/doc/refman/5.0/en/adding-users.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: MySQL won't listen

    [email]msch-prvbluewin.ch[/email] wrote:
    > I set up a small LAN with 2 machines (acer.loc IP:192.168.1.33 and
    > hppav.loc IP: 192.168.1.34) for test purposes. Each client can access
    > its own MySQL db w/o problems either via the cmd prompt or MyODBC:
    > acer: mysql -h192.168.1.33 -u<usr> -p<pwd>
    > or
    > hppav: mysql -h192.168.1.34 -u<usr> -p<pwd>
    >
    > The problem arises when trying to access the MySQL db from the other
    > client. For example, if I want to connect to the hppav db from
    > 'acer.loc' via:
    > mysqL -h192.168.1.34 -u<usr> -p<pwd>
    > I get the following error message:
    > 'ERROR 1130: Host 'acer23555' is not allowed to connect to this MySQL
    > server'.
    >
    > The listening port is default 3306.
    >
    > Running 'netstat -a' reveals that MySQL is not listed as a listener.
    >
    > Question: How can I force MySQL to listen to access calls originating
    > from clients on the network?
    >
    > Thanks for any hints.
    >
    > Mark
    >
    Mark,

    Can you telnet to port 3306 on the other machine? If so, you'll get garbage
    back but at least know MySQL is listening. In this case, you need to check your
    authentication - chances are your id is only allowed to connect from
    'localhost'. I suspect this is the problem.

    If you can't telnet to port 3306, you have a configuration problem. You didn't
    say which version of MySQL you're using, but a couple of things to check in the
    my.cnf file:

    port=3306 (both in [client] and [safe-mysql] sections - 2 entries
    skip-networking ([safe-mysqld] section is NOT set.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: MySQL won't listen

    Bill Karwin wrote:
    > This may be due to a "bind-address=127.0.0.1" setting in the my.cnf
    > Edit your my.cnf and comment out this statement, if it is there.
    I should also mention that you need to restart the MySQL service for
    this change to take effect.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: MySQL won't listen

    Mark

    Many thanks for your comments.

    First off, the MySQL specs are:
    acer client: 5.0.0-alpha-nt
    hppav client: 5.0.18-nt

    I have been experimenting with the different suggestions with no
    results so far:
    1. Commented out bind address 127.0.0.1' in 'my.ini'
    2. Ran telnet on port 3306 ('o hppav3310.loc 3306'). Returns a 'Cannot
    open connexion' msg.
    3. Suppressed the password for user mark to simplify testing
    4. Granted all permissions to user 'mark' in hppav.loc's user table
    with the following entries (all other entries set to yes):
    Host User Password ...
    localhost root <none> ...
    acer2355.loc mark <none> ...
    hppav3310.loc mark <none> ...

    5. Amended 'my.ini' and added Jerry's suggestions:
    ------------
    #This File was made using the WinMySQLAdmin 1.4 Tool
    ...
    [mysqld]
    basedir=C:/Program Files/MySQL
    #bind-address=127.0.0.1
    datadir=C:/Program Files/MySQL/data
    #language=C:/Program Files/MySQL/share/your language directory
    #slow query log#=
    #tmpdir#=
    port=3306
    #set-variable=key_buffer=16M

    [safe-mysql]
    port=3306
    skip-networking=OFF

    [WinMySQLadmin]
    Server=C:/Program Files/MySQL/bin/mysqld-nt.exe
    user=mark
    password=mark
    --------------

    6. End Result. Entering on acer3350.loc the command:
    mysql -h192.168.1.34 -umark
    returns now the following error message:
    'ERROR 2003: Can't connect to MySQL server on '192.168.1.34' (10060).

    I am puzzled. Thanks for any further insights,

    Mark

    msch-prv@bluewin.ch Guest

  6. #6

    Default Re: MySQL won't listen

    [email]msch-prvbluewin.ch[/email] wrote:
    > 1. Commented out bind address 127.0.0.1' in 'my.ini'
    Did you restart the MySQL service after making this change?
    > 2. Ran telnet on port 3306 ('o hppav3310.loc 3306'). Returns a 'Cannot
    > open connexion' msg.
    Do you have a firewall or antivirus software that is blocking the
    connection from the client to the server on port 3306?

    Regards,
    Bill K.
    Bill Karwin Guest

  7. #7

    Default Re: MySQL won't listen

    Bill,

    I did restart the MySQL service after every change. The problem may be
    related to the firewall (there is a firewall on both machines). Earlier
    on, I disabled it on both clients but noticed no change when I
    rebooted. I'll give it another try.

    What is puzzling me is the fact that I can access files on the remote
    Apache server. For instance, the command
    'http://hppav3310.loc/apps/index.php' works on remote hppav from client
    acer. So, there must be a configuration issue somewhere.

    Thanks,

    Mark

    msch-prv@bluewin.ch Guest

  8. #8

    Default Re: MySQL won't listen

    [email]msch-prvbluewin.ch[/email] wrote:
    > Bill,
    >
    > I did restart the MySQL service after every change. The problem may be
    > related to the firewall (there is a firewall on both machines). Earlier
    > on, I disabled it on both clients but noticed no change when I
    > rebooted. I'll give it another try.
    >
    > What is puzzling me is the fact that I can access files on the remote
    > Apache server. For instance, the command
    > 'http://hppav3310.loc/apps/index.php' works on remote hppav from client
    > acer. So, there must be a configuration issue somewhere.
    >
    > Thanks,
    >
    > Mark
    >
    Mark,

    OK, so you can access port 80 on the Apache server. But a firewall can still be
    blocking port 3306.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  9. #9

    Default Re: MySQL won't listen

    After many hours of troubleshooting, I finally was able to solve the
    problem. It turned out that a series of unrelated factors were clouding
    the issue.

    1. Connection hick-ups. Clients are linked to a wireless gateway out of
    line of sight. By accident, I disconvered that erratic perturbations on
    the 'acer.loc' server side caused the network to temporarily freeze. By
    moving clients within range, I was able to improve channel link quality
    and issue commands when the network was 'on'.

    2. Firewall settings. I wrongly assumed that updating WAMP would also
    update firewall definitions. Manually adding MySQL to the firewall's
    exception list cured the problem.

    3. Third-party tool. For testing purposes, I relied on MySQLFront v
    2.4. Version 2.4 does not recognize the user passwords set up via
    phpMyAdmin, causing connection attempts to fail. Checking the
    connection in the command window got rid of this problem.

    Morality: When troubleshooting, 1. Be wary of erratic connection
    glitches. 2. Double check all firewall settings. 3. Stick to trusted
    tools: make sure your tools are compatible with the db version at hand.

    Thanks to all for your insightful comments.

    Mark

    msch-prv@bluewin.ch Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139