Using localhost for remote MySQL server

arren

Member
PartnerNOC
Jul 24, 2008
15
0
51
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?
 

hostingtech

Member
Feb 4, 2010
21
0
51
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.
 

freedman

Well-Known Member
Feb 13, 2005
314
5
168
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...)
 

rejected

Well-Known Member
Sep 19, 2006
48
0
156
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 :)
 

arren

Member
PartnerNOC
Jul 24, 2008
15
0
51
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.
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.

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...)
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.

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 :)
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.
 

hostvn

Member
PartnerNOC
Oct 1, 2007
15
0
51
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.
 

factor

Member
Dec 14, 2002
16
0
151
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,
 

iseletsk

Well-Known Member
Verifed Vendor
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.
What kind of issues are you having with CloudLinux / mod_fcgid? It should be working well -- we have many people using it right now.
 

markhard

Well-Known Member
Apr 22, 2004
252
0
166
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)
 

rshafer

Registered
PartnerNOC
Aug 19, 2011
2
0
51
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.
 

JerrySmith

Active Member
Apr 21, 2011
35
0
56
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.
 

MarcoH64

Member
Oct 4, 2005
16
0
151
Thailand
cPanel Access Level
Root Administrator
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.
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.