Allow a website on one server have write access to a database on another server?

AARRRGH

Hello

I hope someone can help me with this.

I have two servers.

I user server_1 as my main server.

I have a script on server_2 which needs write access to a database on server_1.

Am I right in thinking I need to update a file (hosts?) on server_1 to allow server_2 access the database?

If so, which exact file do I need to update, and with what?

Any help gratefully appreciated.

Thanks.

p

You'll need to be way more specific.

MySQL & other database software are designed to be able to access remotely. It may just work, but you'll need to talk to you hosting company to see what the story is.

seamus

Shouldn't need any special files. What type of database is it?

aidan_walsh

Server 2 db should have own login details. Server 1 script should log into db using said credentials. IMO.

AARRRGH

Sorry guys, it's MySQL.

Yeah, my initial thoughts were I should just be able to give the server name, username and password in the db connect function, but someone has told me this won't work...?

i.e. in the script on server_2:

// pseudocode
db_connect(server_1, database, username, password)

aidan_walsh

AARRRGH wrote: »

Sorry guys, it's MySQL.

Yeah, my initial thoughts were I should just be able to give the server name, username and password in the db connect function, but someone has told me this won't work...?

Unless there are specific reasons it won't, it should.

Liam Byrne

AARRRGH wrote: »

Sorry guys, it's MySQL.

Yeah, my initial thoughts were I should just be able to give the server name, username and password in the db connect function, but someone has told me this won't work...?

i.e. in the script on server_2:

// pseudocode
db_connect(server_1, database, username, password)

SOME hosts will have remote calls disabled for security purposes, or a specific list of allowed hosts, but for the most part the above is perfectly acceptable.

In fact, where hosting is properly optimised (database server optimised one way, PHP server optimised another) the above is probably done on most sites.

I know the specifics of particular "hosts" aren't discussed on this forum, so I need to mask this example, but I know of at least one setup where the above is the norm, where the databases are on sql.hostname.ie, and any www site that uses a database as part of its hosting does precisely what you've outlined above.

I've also got a work-in-progress site on a testing URL that's using a "live" database from another server, so that I can get the front-end working based on what's already in the database.....this setup has


WIP SITE <== DB SERVER ==> LIVE SITE

Eoin

I can access MySQL on my shared hosting account from websites that I have running locally on my machine.

p

You should just test it & see if it works on your setup. If not, contact your hosting company to allow it from certain servers.

AARRRGH

Right, I tested it and it didn't work.

It turned out I needed to add "%" to the MySQL allowed hosts list. This allows any server access my databases. I will probably change this to the IP of server_2, but for now, it works.

Thanks everyone for your help.

Evil Phil

You could have always outsourced this to get it solved :pac:

AARRRGH

Evil Phil wrote: »

You could have always outsourced this to get it solved :pac:

HAha... would you believe someone who is doing some work for me (yes, outsourced work!) told me the solution. :pac:

jmcc

AARRRGH wrote: »

Right, I tested it and it didn't work.

It turned out I needed to add "%" to the MySQL allowed hosts list. This allows any server access my databases. I will probably change this to the IP of server_2, but for now, it works.

Thanks everyone for your help.

This is a bloody dangerous thing to do on a live, internet facing server. You should have specified individual hosts or IPs rather than using % as this is the global wildcard * in SQL. Find out the hostname or IP from which you need to use the database and add that. And remove that % wildcard.

Regards...jmcc

AARRRGH

They still need my username, password and database name though, right?

jmcc

AARRRGH wrote: »

They still need my username, password and database name though, right?

Yes and that's all that stands between the data and them. If it is a standard role type username or an easily guessed username all they have to do is do a brute force attack on the password.

Regards...jmcc

AARRRGH

Sure they could do that with the root account as well.

I understand it makes sense to define the allowed IPs (I did say I will probably do that) but I think you're being a bit paranoid.

ntlbell

As jmcc said that wildcard is pretty loose.

are they your own servers?

I would look at restricting to the host of the remote box

if they're your own boxes you can also tunnel mysql over SSH for remote connections.

obviously on a busy server this will add some overhead.

ntlbell

AARRRGH wrote: »

Sure they could do that with the root account as well.

I understand it makes sense to define the allowed IPs (I did say I will probably do that) but I think you're being a bit paranoid.

well remote connections for the root account should be disabled

AARRRGH

Yep, they're my own servers (well, they're dedicated servers only I access.)

I'll take your advice - better safe than sorry. Cheers.