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

Thread: Mysql, can only connect from local host?

  1. #1
    Join Date
    Aug 2012
    Beans
    119

    Mysql, can only connect from local host?

    So, I've got a VM running ubuntu server, then I've got a laptop running ubuntu desktop, that is being a server/desktop for a week or so. (Gunna run testing game servers on it)
    The server has phpmyadmin on in and I can use that fine, but I can't seem to get me laptop's game server to use Mysql.
    I tried to telnet from both my server and laptop. The server will connect to itself though local host, but this.
    Code:
    admin-ben@LangweilerG:/etc/mysql$ telnet localhost 3306
    Trying ::1...
    Trying 127.0.0.1...
    Connected to localhost.
    Escape character is '^]'.
    [
    5.6.28-0ubuntu0.15.04.1
                           'jb.YY▒,3YKImhBQOy6mysql_native_passwordConnection closed by foreign host.
    admin-ben@LangweilerG:/etc/mysql$
    The laptop won't connect to it at all.
    Code:
    admin-ben@Dellio:~$ sudo telnet 192.168.0.102 3306
    Trying 192.168.0.102...
    telnet: Unable to connect to remote host: Connection refused
    admin-ben@Dellio:~$
    How can I setup my Mysql server to allow connections from my lan? Do note I allowed 3306 though ufw on laptop and the VM don't seem to have a firewall?
    My router is not port forwarded on this port as I don't want anyone connecting to it, from outside my local network.
    I went to my /etc/mysql/ and I checked every config file in there and nothing looked like I could put a IP in it, as I found a forum here, but it was from 2010.
    HTML Code:
    http://ubuntuforums.org/showthread.php?t=1625679
    I tried the first line of code, with the grep. There was about 5 lines and 1 was very long.

    Thanks,
    Higgins909

  2. #2
    Join Date
    Nov 2009
    Location
    Mataro, Spain
    Beans
    14,279
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: Mysql, can only connect from local host?

    If I'm not mistaken, in the main mysql config file my.cnf you have a bind-address parameter. By default it is 127.0.0.1 (localhost) to protect from "outside" entry. You can change that bind address to the server private IP but note in this case that you can't use localhost any more. And there can be only one bind address.

    If you set it to 0.0.0.0 it will listen on all IPs it has (if there are more) and localhost too.

    Don't forget to restart the service after doing the modifications.

    And if the laptop is the client you don't need to allow 3306 on it unless you are filtering outgoing traffic too. Only on the server but you said it has no firewall.
    Darko.
    -----------------------------------------------------------------------
    Ubuntu 14.04 LTS 64bit & Windows 10 Pro 64bit

  3. #3
    Join Date
    Aug 2012
    Beans
    119

    Re: Mysql, can only connect from local host?

    That's that thread I didn't exactly link in said. But I looked in it and this is all it has.
    Code:
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    
    !includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mysql.conf.d/
    I once tried to mod this file because I thought I needed to turn of strict mode... which I guess doesn't exist or maybe just for linux.
    It would cause Mysql to not start.
    Through phpmyadmin, I setup a user with everything, to be connectable from anywhere (any host %)

    Edit, here is a grep thing.
    Code:
    admin-ben@LangweilerG:/etc/mysql$ netstat -l --numeric-ports | grep 3306
    tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
    admin-ben@LangweilerG:/etc/mysql$
    Last edited by Higgins909; April 2nd, 2016 at 02:30 AM.

  4. #4
    Join Date
    Nov 2009
    Location
    Mataro, Spain
    Beans
    14,279
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: Mysql, can only connect from local host?

    Is that your whole /etc/mysql/my.cnf or you didn't copy the full content? Because it should have much more options in it. This one is basically empty.

    The netstat clearly shows that mysql is binded only on 127.0.0.1 which is the default behavior. You should change that with bind-address in /etc/mysql/my.cnf.
    Darko.
    -----------------------------------------------------------------------
    Ubuntu 14.04 LTS 64bit & Windows 10 Pro 64bit

  5. #5
    Join Date
    Aug 2012
    Beans
    119

    Re: Mysql, can only connect from local host?

    Quote Originally Posted by darkod View Post
    Is that your whole /etc/mysql/my.cnf or you didn't copy the full content? Because it should have much more options in it. This one is basically empty.

    The netstat clearly shows that mysql is binded only on 127.0.0.1 which is the default behavior. You should change that with bind-address in /etc/mysql/my.cnf.
    Yep, that's the full my.cnf in that file path.
    I've tried to sudo mysql -u root -p
    then enter bind-address = 0.0.0.0; but it says there is something wrong with my syntax on line one.
    Code:
    admin-ben@LangweilerG:~$ mysql -V
    mysql  Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using  EditLine wrapper
    admin-ben@LangweilerG:~$
    Maybe I installed the wrong version or something?

    Edit: forgot to mention that every time that I try to modify it, it won't let mysql restart.
    Last edited by Higgins909; April 3rd, 2016 at 01:46 AM.

  6. #6
    Join Date
    Nov 2009
    Location
    Mataro, Spain
    Beans
    14,279
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: Mysql, can only connect from local host?

    I don't think you can issue bind-address on the mysql prompt. It should be in the config file. You are saying that modifying the my.cnf is not letting mysql restart restart correctly?

    If you don't have important data in the DBs yet, I think it might be a good time to consider reinstalling mysql.

    Did you install it using standard ssh session and command line? Or maybe using some GUI tools? I am asking because often various GUI tools do not correctly edit some config files and can create issues that way.

    Your situation is very weird because the default my.cnf after installation has much more data in it.
    Darko.
    -----------------------------------------------------------------------
    Ubuntu 14.04 LTS 64bit & Windows 10 Pro 64bit

  7. #7
    Join Date
    Aug 2009
    Location
    Makati City, Philippines
    Beans
    2,269
    Distro
    Xubuntu 16.04 Xenial Xerus

    Re: Mysql, can only connect from local host?

    Did you use sudo to edit the /etc/mysql/my.cnf file?


    This is a system file and cannot be edited by the normal user. You need the sudo or root access to modify this file. Same goes for restarting mysql service. Can you post your screenshot on how you edit the file and the error you receives when you restart mysql.

  8. #8
    Join Date
    Aug 2012
    Beans
    119

    Re: Mysql, can only connect from local host?

    Yes I've used sudo nano /etc/mysql/my.cnf to edit the my.cnf. I've scrolled down to check and even used vi.
    I want to say I used the ssh/command line to install mysql, but then after that I installed phpmyadmin and that added apache2 which was done though a semi graphic menu.
    The error logs seems to be empty. Yes, mysql hangs on startup/restart of mysql when I modify my.cnf
    How would I go about, reinstalling mysql? apt-get remove, purge? other? Also since I've got phpmyadmin and apache2 working with it?

  9. #9
    Join Date
    Mar 2006
    Location
    Williams Lake
    Beans
    Hidden!
    Distro
    Ubuntu Development Release

    Re: Mysql, can only connect from local host?

    I guess someone should mention here that if you allow access to mysql from systems other than the one it is installed on, you may be leaving yourself open to others accessing it. I'd suggest if you want to use mysql from the command line, you do it via ssh without a password.

  10. #10
    Join Date
    Aug 2012
    Beans
    119

    Re: Mysql, can only connect from local host?

    Its only going to be on a LAN, the modem/router thing will not be port forwarded for mysql. But what do you mean "without a password" Like do I log into server by ssh, then sudo mysql -u root -p or ?
    I installed mysql-server on my debian machine but did not install phpmyadmin or apache2 as I was just testing and the my.cnf was way more filled out.
    On a security note, I noticed in phpmyadmin, you could setup so users could only connect to the mysql server from a certain ip. (have yet to test for sure)

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
  •