Using localhost for remote MySQL server

[arren]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.

[cPanelJerrySmith]

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]

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.