Change config for use database on second server

[ASTRAPI]

Hello

How can i set my config to use a second serverfor mysql?

$INFO['sql_host']            =    'localhost';

Do i have to add just the server ip here?

Also do i have to edit the my.cnf and add some settings there also?

Both servers are on the same Datacenter and both are using 1.000Mbit connection.

Thank you

[Rhett]

You can set the mysql server to use yes, either "localhost" or the "IP" of the server you wish to use, however there are no provisions for IPB to use "TWO" mysql servers.

[ASTRAPI]

Yes i mean to use one http server and one second server just for the database and not two db servers :smile:

So something like this it wil be ok?

$INFO['sql_host']            =    '123.456.789.000';

Port? Or it will look on default 3306 port?

Do i have to configure anything else on my.cnf or in the second server?

Thank you

[Rhett]

Hello, that is all you need, just set the IP in that location, and then ensure you have the database on that server with the proper user info as well.

Thank you

[ASTRAPI]

Does mysql by default will listen on a network pc?

Do i have to open the port on firewall ?

Also maybe enable networking on my.cnf ?

[Rhett]

By default mysql listens on port 3306, depending on your firewall you may or may not need to adjust it to allow access from another server however.

[Makoto]

You'll also need to ensure the MySQL server and MySQL user on your remote database server are configured to allow remote connections from your web servers IP, of course.

For my.cnf, make sure you don't have skip-networking set in your configuration. If it is set, just comment it out. Also make sure bind-address is set to your servers IP and not just 127.0.0.1 (which means the MySQL server will only accept connections from localhost).

[Dmacleo]

if in same datacenter couldn't you also use the internal network ip to avoid losses?

[Makoto]

Possibly, yeah.

That's the best thing to do if you have the option.

[ASTRAPI]

Great info thanks :)

But how i allow on mysql server to accept connections only from my http server and not allow any connections from outside?

[Murmel]

That's done by your ISP. I use also since many years two different server without any problems. One for the files and one for the DB.

[Dmacleo]

Great info thanks :smile:

But how i allow on mysql server to accept connections only from my http server and not allow any connections from outside?

I forget the phrasing now but in my.cmf file you can restrict connections to the one ip of other server

That's done by your ISP. I use also since many years two different server without any problems. One for the files and one for the DB.

your ISP has nothing to do with it. if on shared or managed hosting your HOST may do it. if on vps/dedicated (unmanaged) you do it yourself.

[Murmel]

sorry wrong translation :sad:

not the ISP - the hosting company is right :smile:

[Dmacleo]

I generally don't worry about that term, but over last week I have dealt with people making the mistake and contacting wrong people for support, so figured may as well mention it here.

thanks for being cool about it :)

[Makoto]

When you configure permissions with MySQL, you generally use a query such as this,

GRANT ALL ON ipboard.* TO 'ipboard'@'localhost';

When you create a MySQL user, you want to replace "localhost" here with your web servers IP.

If you only want your web server to be able to make connection attempts to the MySQL server, you should probably configure iptable rules for port 3306.

[ASTRAPI]

Do you mean to use the ip of the http server instead of localhost?

[Dmacleo]

are you using a control panel pf any type or all done through shell?

iirc remote access is enabled by default on mysql (check me on that in case I am wrong) so what you are looking to do is prevent everything BUT your http server from accessing.

a lot depends on if using any panels, if so they usually allow setting the parameters through the gui.

I;ve never tested to see what works better, restricting though firewall (eg only allowing 1 ip on that port) or doing it through mycnf. I would think firewall (iptables) would be better as then the sql server processes would not have to deal with it.

last time I did it was with a control panel so I just set to allow ONLY the https server ip, remember the internal ips are not the same as the external ones.

[ASTRAPI]

No i am not using any panel at all :)

[Dmacleo]

I would think using the firewall (csf or whatever) to block all access on that one port except from your internal ip would work.

not in a position I can test it though.

so on remote server you'd set database up like normal, allow user like normal, then on http server in the conf_global you just point to the sql server internal ip.

I THINK that would work.

[Makoto]

MySQL's default configuration is to only accept connections from localhost on Debian, but the default configuration varies depending on your distribution. I don't remember what the default on CentOS is.

But regardless, the MySQL user in question has to be configured to allow remote connections.

Do you mean to use the ip of the http server instead of localhost?

Yes. The IP that will be making connections to the remote MySQL server, so your web servers IP.

[Murmel]

Now lets try ;-).

Sometimes the mysql server listen to outside sometimes not. But you can try to connect with your system from the outside (win/linux) to your server and try if its working (hopefully not).

Regardless of that your mysql server has 2 IPs probably. One internal (192.168.x.x) and maybe one external.

Now you can connect your http server with the internal IP from the SQL Server which can not reached from the outside!

And if you have an external IP of the SQL Server from your host it works maybe also in the config but that doesn't mean that the external is also reachable from the outside.

Maybe a even better idea is: Just ask the company where the SQL Server stands ;-).

Murmel

PS: Hi DMAC. Better look at my text ;-).

[GreenLinks]

If you are using a separate database server make sure you have a direct connection to your database server .

[Dmacleo]

Murmel

PS: Hi DMAC. Better look at my text ;-).

they are in same datacenter which is why I said use the internal ip's for server <--> server. not sure what you mean by that.

[GreenLinks]

they are in same datacenter which is why I said use the internal ip's for server <--> server. not sure what you mean by that.

If you want to have best performance being on the same server doesn't help much , you basically need to have two network cards on your web server , one should be connected to web the other should have a direct connection.

[Dmacleo]

the way its presented these are 2 distinct servers in same building.

no way for me to know if they are close together or not or how many nics.

yeah if they were set like that its best but if only 1 nic using internal ip still better than using external.

edit: it very well may be worth having datacenter add second nic if needed. I have limited experience with that (windows server 2008r2 machines and MS SQL Server 2008r2) but my limited use (mail archiving for 20 users, not actual mail system itself) made it worth it for me. with the loads I had on that machine though I was easily able to use just a consumer grade nic card, probably not good idea though.