Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Can't connect to MySQL server on 'DOMAIN' (111)

  1. #1
    Join Date
    Jul 2006
    Location
    Calgary, Alberta
    Beans
    1,116
    Distro
    Ubuntu 13.04 Raring Ringtail

    Can't connect to MySQL server on 'DOMAIN' (111)

    I have a default installation of MySQL on my default Ubuntu server, I haven't really changed any settings after install. The default configuration is to disallow external connections to MySQL and I'm trying to allow external connections so I can connect to it from anywhere, mainly so I can use MySQL Workbench instead of PHPMyAdmin. I've made the necessary changes but it still doesn't seem to work.

    Here is part of my my.cnf file:

    Code:
    [mysqld]
    #
    # * Basic Settings
    #
    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
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address          = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    And I logged into my mysql (from localhost) as root to run this:

    Code:
    grant all privileges on *.* to 'development'@'%' identified by '**********';
    Of course I put the correct password in there. Then I ran:

    Code:
    flush privileges;
    I even restarted the MySQL server:

    Code:
    sudo service mysql restart
    However I still cannot connect from my other machine with any user. I can't connect as this user, I can't connect as root, it keeps giving me this 111 error in the title:

    Code:
    deemar@Chugger:/etc/mysql$ mysql -u development -p -h domain
    Enter password: 
    ERROR 2003 (HY000): Can't connect to MySQL server on 'domain' (111)
    What am I missing to allow external access to MySQL?
    Quote Originally Posted by Tristam Green View Post
    I can tell you something about a turntable.
    I have two of them.
    And a microphone.
    Where it's at.

  2. #2
    Join Date
    Nov 2008
    Location
    S.H.I.E.L.D. 6-1-6
    Beans
    Hidden!
    Distro
    Ubuntu Development Release

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    MySQL by default listens only on the localhost (127.0.0.1) interface.

    From configuration file above:
    Code:
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address          = 127.0.0.1
    Uncomment the bind-address line, and bind to 0.0.0.0 to bind to all addresses on the system.
    Don't waste your energy trying to change opinions ... Do your thing, and don't care if they like it.

  3. #3
    Join Date
    Jul 2006
    Location
    Calgary, Alberta
    Beans
    1,116
    Distro
    Ubuntu 13.04 Raring Ringtail

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Quote Originally Posted by sandyd View Post
    MySQL by default listens only on the localhost (127.0.0.1) interface.

    From configuration file above:
    Code:
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address          = 127.0.0.1
    Uncomment the bind-address line, and bind to 0.0.0.0 to bind to all addresses on the system.
    Code:
    [mysqld]
    #
    # * Basic Settings
    #
    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
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 0.0.0.0
    #
    # * Fine Tuning
    #
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    Code:
    name@computer:/sbin$ sudo service mysql restart
    mysql stop/waiting
    mysql start/running, process 6078
    Code:
    deemar@Chugger:/etc/mysql$ mysql -u development -p -h domain
    Enter password: 
    ERROR 2003 (HY000): Can't connect to MySQL server on 'domain' (111)
    Didn't work.
    Quote Originally Posted by Tristam Green View Post
    I can tell you something about a turntable.
    I have two of them.
    And a microphone.
    Where it's at.

  4. #4
    Join Date
    Nov 2008
    Location
    S.H.I.E.L.D. 6-1-6
    Beans
    Hidden!
    Distro
    Ubuntu Development Release

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Whats the output of
    Code:
    lsof -Pni :3306
    Don't waste your energy trying to change opinions ... Do your thing, and don't care if they like it.

  5. #5
    Join Date
    Jul 2006
    Location
    Calgary, Alberta
    Beans
    1,116
    Distro
    Ubuntu 13.04 Raring Ringtail

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Quote Originally Posted by sandyd View Post
    Whats the output of
    Code:
    lsof -Pni :3306
    Code:
    name@computer:~$ lsof -Pni :3306
    name@computer:~$
    Quote Originally Posted by Tristam Green View Post
    I can tell you something about a turntable.
    I have two of them.
    And a microphone.
    Where it's at.

  6. #6
    Join Date
    Nov 2008
    Location
    S.H.I.E.L.D. 6-1-6
    Beans
    Hidden!
    Distro
    Ubuntu Development Release

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Odd, mysql is refusing to bind to the addresses.

    Does /var/log/mysql/error.log have anything that might indicate why it is not binding?
    Don't waste your energy trying to change opinions ... Do your thing, and don't care if they like it.

  7. #7
    Join Date
    Jul 2006
    Location
    Calgary, Alberta
    Beans
    1,116
    Distro
    Ubuntu 13.04 Raring Ringtail

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Quote Originally Posted by sandyd View Post
    Odd, mysql is refusing to bind to the addresses.

    Does /var/log/mysql/error.log have anything that might indicate why it is not binding?
    Code:
    140901 15:40:15 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated an$
    140901 15:40:15 [Note] Plugin 'FEDERATED' is disabled.
    140901 15:40:15 InnoDB: The InnoDB memory heap is disabled
    140901 15:40:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140901 15:40:15 InnoDB: Compressed tables use zlib 1.2.8
    140901 15:40:15 InnoDB: Using Linux native AIO
    140901 15:40:15 InnoDB: Initializing buffer pool, size = 128.0M
    140901 15:40:15 InnoDB: Completed initialization of buffer pool
    140901 15:40:15 InnoDB: highest supported file format is Barracuda.
    140901 15:40:15  InnoDB: Waiting for the background threads to start
    140901 15:40:16 InnoDB: 5.5.38 started; log sequence number 364633718
    140901 15:40:16 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    140901 15:40:16 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
    140901 15:40:16 [Note] Server socket created on IP: '0.0.0.0'.
    140901 15:40:16 [Note] Event Scheduler: Loaded 0 events
    140901 15:40:16 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
    140901 17:45:02 [Note] /usr/sbin/mysqld: Normal shutdown
    
    140901 17:45:02 [Note] Event Scheduler: Purging the queue. 0 events
    140901 17:45:02  InnoDB: Starting shutdown...
    140901 17:45:04  InnoDB: Shutdown completed; log sequence number 364633728
    140901 17:45:04 [Note] /usr/sbin/mysqld: Shutdown complete
    
    140901 17:45:04 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated an$
    140901 17:45:04 [Note] Plugin 'FEDERATED' is disabled.
    140901 17:45:04 InnoDB: The InnoDB memory heap is disabled
    140901 17:45:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    140901 17:45:04 InnoDB: Compressed tables use zlib 1.2.8
    140901 17:45:04 InnoDB: Using Linux native AIO
    140901 17:45:04 InnoDB: Initializing buffer pool, size = 128.0M
    140901 17:45:04 InnoDB: Completed initialization of buffer pool
    140901 17:45:04 InnoDB: highest supported file format is Barracuda.
    140901 17:45:04  InnoDB: Waiting for the background threads to start
    140901 17:45:05 InnoDB: 5.5.38 started; log sequence number 364633728
    140901 17:45:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
    140901 17:45:05 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
    140901 17:45:05 [Note] Server socket created on IP: '0.0.0.0'.
    140901 17:45:05 [Note] Event Scheduler: Loaded 0 events
    140901 17:45:05 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '5.5.38-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
    Quote Originally Posted by Tristam Green View Post
    I can tell you something about a turntable.
    I have two of them.
    And a microphone.
    Where it's at.

  8. #8
    Join Date
    Apr 2014
    Beans
    48
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Error 111 means connection refused, is port 3306 open for the address ranges you wish to allow? Check your firewall (IpTables, ufw, etc).

    P.S Don't (try) to open ports for 0.0.0.0 that is special, just allow the address ranges you want. e.g Allow 192.168.0.0/24 port 3306 for all computers using that local subnet, but leave my.cnf as 0.0.0.0.
    Last edited by richard51; September 2nd, 2014 at 08:53 AM. Reason: Added P.S
    errare humanum est.

  9. #9
    Join Date
    Jul 2006
    Location
    Calgary, Alberta
    Beans
    1,116
    Distro
    Ubuntu 13.04 Raring Ringtail

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    Would those things be blocked by default in Ubuntu Server 14.04? I don't even know where to start checking for those things.
    Quote Originally Posted by Tristam Green View Post
    I can tell you something about a turntable.
    I have two of them.
    And a microphone.
    Where it's at.

  10. #10
    Join Date
    Apr 2014
    Beans
    48
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: Can't connect to MySQL server on 'DOMAIN' (111)

    On a fresh install of Ubuntu Server 14.04, Iptables will be set to Allow for Input, Output and Forward. UFW will be disabled, so no, it shouldn't be blocked!

    On a side note, I know that you have granted all privileges, but if you have still got phpMyAdmin installed, log-in as root on the MySQL machine with phpMyAdmin, then check user & database privileges from there.

    You can check Iptables and check the status of UFW using the following command:
    Code:
    $ iptables -L -v --line-numbers
    $ ip6tables -L -v --line-numbers
    $ ufw status verbose
    Last edited by richard51; September 2nd, 2014 at 05:11 PM. Reason: Added commands to check firewall, etc.
    errare humanum est.

Page 1 of 2 12 LastLast

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
  •