The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Remote MYSQL access

Discussion in 'Database Discussions' started by shortfork, Apr 28, 2008.

  1. shortfork

    shortfork Well-Known Member

    Joined:
    Sep 4, 2006
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    With my previous server (Ensim) I was able to remotely connect to databases on a user by user basis with Ems Mysql Managar. With what looks like the same setup on the new cPanel box, no joy.

    Ports are open... in fact, firewall down or up makes no difference.

    Still get a can't connect... is there something we need to do within the system to get it to bind to the remote port?

    Wayne
     
  2. shortfork

    shortfork Well-Known Member

    Joined:
    Sep 4, 2006
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Remote Mysql Connection Issues Resolved

    OK... I'm going to try to contribute to this community whenever I can (those few times I actually find the right question to ask and my research pays off)

    Here's the problem, and the solution..

    Problem: You have been using a version of MYSQL that is an older version compaired to what your new cPanel has. You need to connect to the databases from a remote host via port 3306. You wrack your brain, set up the hosts in phpmyadin.. set the privledged users corectly and NOTHING you do works.. you can't connect!

    I finally telnetted in on a closed port, watched tail -f /var/log/messages/ saw it blocking me from that closed port.. telnetted into box on 3306, which, by the way, is open.. saw NOTHING.. just like when I tried to connect to it remotely using EMS Mysql manager lite..

    Did some MORE googing and came up with this..

    "As of MySQL v.4.1, the mysqld server program no longer accepts outside requests to connect to port 3306. Only connections coming from the local machine (represented as 127.0.0.1) are allowed to connect to port 3306. This default is set in the /etc/mysql/my.cnf configuration file.

    bind-address = 127.0.0.1"

    So the solution is.. open up my.cnf (location in this quote is wrong, it's actually /etc/my.cnf

    comment out as so..

    #bind-address = 127.0.0.1

    service mysql restart...

    Bingo..

    You can now remotely connect to your server and mysql via port 3306. mind you.. you should open up your firewall ONLY to those IP addresses you need to use it and no others.. but..

    Damn, wish I'd have known this two weeks ago when I was trying to use a remote database!

    cPanel guys.. You just *MIGHT* want to add something like this to your WHM panel, at least a note, since "remote sql host" stuff is ALL over the panel and there is no way in hell you're going to get it working until you edit this file and bind sql to port 3306...

    Seems later versions of mysql have caught a lot of us with our pants down!

    Wayne
     
  3. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,460
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    cPanel does not set that in your /etc/my.cnf file. That was set by whoever performed your server install.
     
  4. shortfork

    shortfork Well-Known Member

    Joined:
    Sep 4, 2006
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    According to what I read (researched) it's SOP for default mysql install past the version mentioned above (4.1x or something)... so it'd be default setup wouldn't it?

    The Planet is host, I'm sure they just have a standard config they load on all new server installs..

    Anyway, some sort of docs on the box would be good here, hope this thread helps someone along the line.

    Wayne
     
  5. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,460
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    That knowledge is only relevant on systems that use RPMs provided by the OS Vendor or MySQL directly, which use the configuration file MySQL provides. cPanel does not use that configuration file. The only values cPanel places in /etc/my.cnf during install are the following:

    Code:
    [mysqld]
    set-variable = max_connections=500
    safe-show-database
    
    Thus, as I said earlier, bind-address was set by whoever performed your server install.
     
  6. shortfork

    shortfork Well-Known Member

    Joined:
    Sep 4, 2006
    Messages:
    63
    Likes Received:
    0
    Trophy Points:
    6
    Question then, when we do an sql update via the cPanel, (which I did) and it recompiles mysql, does *that* then put in the default?

    Where do the update files come from when we allow the cp to install them?

    Wayne
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,460
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    If you are using RPMs provided by cPanel, then the RPMs come from our update servers. Those do not use the my.cnf provided by MySQL, they use what we provide (it's a little deeper than that. We only setup /etc/my.cnf if it doesn't exist).

    The update process will only compile MySQL from source if:

    1. Your OS is FreeBSD and a Package does not exist, hence it pulls it from Source

    2. Your OS is an unsupported Linux distribution for which RPMs don't exist and a usable package for that distro does not exist.

    3. You are using the Gentoo Linux distribution, which we don't support (this is like #2, except Gentoo typically builds everything from source).
     
Loading...

Share This Page