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.

Using localhost for remote MySQL server

Discussion in 'Database Discussions' started by arren, May 13, 2010.

  1. arren

    arren Member
    PartnerNOC

    Joined:
    Jul 24, 2008
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Hi guys,


    I have a question in mind that I would need the help on my setup for using localhost for my remote MySQL server.

    I have setup 2 cPanel server and one server will be the master server for the web+DNS+email server and etc except MySQL. The slave server will be hosting for my mySQL server.

    Then I would like to know on how to configure that we remain using the hostname localhost when connecting to our MySQL server without using the IP address of the slave MySQL server?

    I have checked on several iptables rules that port forward all the 3306 connection to my slave server but it does not work.

    Any ideas?
     
  2. hostingtech

    hostingtech Member

    Joined:
    Feb 4, 2010
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    Hi Arren,

    Check/Use below link in WHM , Cpanel has automated it already and has made it easy for us :

    WHM >> SQL Services >> Setup Remote MySQL server

    The description on the page is mostly self explanatory, check it out and let me know if you have any questions.
     
  3. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    18
    using "localhost" as remote mysql server hostname

    hostingtech didn't really answer your question.

    What I think you want is to be able to have the SQL server on another host, but not make everyone who stuck "localhost" in as the hostname for all their applications.

    You have a couple options here. you *could* change the IP address for 'localhost' to the remote machine IP in your /etc/hosts file.
    This is a very bad idea as many linux services use localhost presuming it's the actual localhost.

    what you probably need is to install MySQL-Proxy on localhost and have this direct communication to the other host.
    https://launchpad.net/mysql-proxy

    You then have 2 options:
    1) have new customers use the remote mysql settings (following hostingtech's advice) and use this to give your existing customers time to update their DB hostname in their applications;
    OR
    2) use the MySQL Proxy configuration and eventually take advantage of it's many features (load-balancing, failover, redirecting different db's to different servers, etc...)
     
  4. rejected

    rejected Well-Known Member

    Joined:
    Sep 19, 2006
    Messages:
    48
    Likes Received:
    0
    Trophy Points:
    0
    Send me a private message I could possibly do what I done for my old employer. We had 2 cPanel boxes 1 ran the SQL and we modified PHP core to add a feature called mysql.map_hosts which was a comma seperated list of hosts to map eg localhost, server1, server2 then the value would be replaced with the value of mysql.default_host.

    eg mysql.map_hosts = server1
    mysql.default_host = localhost
    <?php
    mysql_connect("server1", "root", "");
    ?>
    would then connect to localhost :)
     
  5. arren

    arren Member
    PartnerNOC

    Joined:
    Jul 24, 2008
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Thanks for your advise but I have done this part and it is working fine. But what I want is using localhost as hostname instead of the remote MySQL server IP.

    Hi, freedman. The idea on changing localhost ip address does not work. I have tried that. I will look into the MySQL proxy you suggested and shall update the result here. :) Thanks.

    Hi rejected, will this cause all the connection of database only coming from root only? I am actually running a shared hosting cPanel server here.
     
  6. hostvn

    hostvn Member
    PartnerNOC

    Joined:
    Oct 1, 2007
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ha Noi, Viet Nam
    I tried many times to config mySQL server extenal for cPanel. and i had many issue with it. So know, i stopped extenal mySQL server. I'm running all services of cPanel on 1 server. I'm testing cPanel + CloudLinux and cPanel + LiteSpeed WS. But i'm still have issue with mod_fcgid. It's not competiable with CloudLinux. For shared hosting server, we have many issue with overload, ddos, local attack..etc.. so difficult for perfect configure.
     
  7. factor

    factor Member

    Joined:
    Dec 14, 2002
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    very stupid question, but what you are saying is that if we don't change settings of every user app to connect to remote mysql server, even setup via whm option, remote mysql server will only be used with internal cpanel & whm mysql (webmail, settings, etc) but NOT for end user apps?

    i thought they had setup some kind of tunneling or something so we didn't have to update hundreds of config files from every php app out there.

    Thanks in advance for your help.

    Regards,
     
  8. iseletsk

    iseletsk Well-Known Member

    Joined:
    Mar 3, 2010
    Messages:
    163
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Princeton, New Jersey, United States
    What kind of issues are you having with CloudLinux / mod_fcgid? It should be working well -- we have many people using it right now.
     
  9. markhard

    markhard Well-Known Member

    Joined:
    Apr 22, 2004
    Messages:
    250
    Likes Received:
    0
    Trophy Points:
    16
    do note that mysql treat 'localhost' as UNIX socket and not translate it into 127.0.0.1 (TCP/IP)

    so even if you use mysql-proxy and set it to run as 'localhost', mysql will still use the UNIX socket instead of connecting through TCP/IP

    this is the design of mysql server and there is no way to change it via config.

    in case you want to use remote mysql server but still want the application to 'think' that the mysql server is in the same server, then you have to use mysql-proxy and run it on 127.0.0.1 (this ensure that mysql client connect to the server via TCP/IP and not UNIX socket)
     
  10. rshafer

    rshafer Registered
    PartnerNOC

    Joined:
    Aug 19, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Using localhost as the hostname for your database in your code is a bad practice. If you use a domain name like mysql.<yourdomain>.com then you can control the IP address that your apps connect to by simply editing the entry in your domains dns zone. I work for a hosting company and using practices like this will greatly decrease your headaches when it is time to perform a migration or to move your databases to a remote server. Remember DNS is your friend.
     
  11. JerrySmith

    JerrySmith Active Member

    Joined:
    Apr 21, 2011
    Messages:
    35
    Likes Received:
    0
    Trophy Points:
    6
    Hello,

    I don't know if you have considered it, but I believe stunnel would be a good option here.

    This would allow you to connect to localhost on port 3306 as usual and have communication transferred using SSL to the remote host.

    Something similar to:

    Random Things » stunnel for mysql – server and client

    You would have the client accept connections on 3306 and connect on port 3309 then have the server accept on 3309 and connect on 3306.

    Not sure if this will work but it seems it would.
     
  12. MarcoH64

    MarcoH64 Member

    Joined:
    Oct 4, 2005
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Thailand
    cPanel Access Level:
    Root Administrator
    I really do not agree with you on this. This might be true in an environment where you have many databases on many different remote hosts.

    It is for sure not true for accessing a database on the same server. And i think that many (most?) setups only use a single server hosting both the application and the database(s). Using 'localhost' will make MySQL (at least on linux) use a socket instead of a TCP connection and that is a lot faster.
     
Loading...

Share This Page