connect to single db from two sites

[frustrated]

Here's the situation.

 

I have two websites that are basically mirrors of each other. One is a .com and the other .net and each site is hosted through a different hosting company. My plan was to set up a mysql database on the .net site and then connect to this database through both the .com and .net site.

 

So I setup the database on the .net site and then uploaded files to the .com site. I went to the files I uploaded, where a connection to this database was required and it was unable to make the connection even though I know all the connection info was correct. Can hosting companies make it where you can't connect to the database from other hosting companies...is there anyways to override this.

 

Any feedback would be appreciated.

 

Thanks.

[fenway]

It is possible -- it all depends on how they have their MySQL server set up, and how they let you define new users. They can block outside connections on port 3306, and can also only create user accounts that connect via localhost. In either case, you're stuck, and you can't get around it. To be fair, this is a good security policy to have in place from a hosting company, to prevent malicious attacks. The only real solution is to set up your DB elsewhere, where you can have external connections, and have both sites connect to there. Good luck.

[frustrated]

Thanks, you answered my question...though it wasn't what I wanted to hear.

 

Let me ask you this...are there services out there that offer a mysql databases in this sort of situation...so that you can connect to them from numerous places?

[fenway]

I'm sure that there are services that provide just MySQL DB hosting, but I've never used such a company. Good luck.

[siberyak]

In place of hostname you have to use IP address of the net host

 

$db_link = mysql_connect($hostname, $dbuser, $dbpassword)

 

but it is not enough. In MySQL there is a user table. In the table there is host field.

This field usually set to localhost for security sake.

You have to ask hosting provider to change host field

to your site.com IP or to '%' then user from any IP could connect to MySQL database.

 

Please see site www.configure-all.com and select Database design from main menu

and then select MySQL manual. It is about security in MySQL

 

Good luck

[shoz]

While "previewing" I see that siberyak has mentioned some of what I've written. I've left them just the same however.

 

Keep in mind that users are identified by username AND host when connecting to the MYSQL server. You may be able to grant access to your user using something such as the following

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]GRANT ALL ON dbname.* TO username@otherhost.com [!--sql2--][/div][!--sql3--]

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/adding-users.html\" target=\"_blank\"]Adding Users[/a]

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html\" target=\"_blank\"]Mysql Privilege System[/a]

 

You should also be aware that you'll most likely suffer medium to severe performance problems depending on what you're doing because of the need for one server to go over the net to talk to the other server.

 

Doing something as simple as updating one database from the other periodically could probably help. Again, depending on what you're doing you may need to do it once a day, once an hour or more frequently. Of course, at some point it won't be feasible.

 

If you have the necessary access, [a href=\"http://dev.mysql.com/doc/refman/4.1/en/replication.html\" target=\"_blank\"]Replication[/a] may be one way to go, as it should allow for more efficient updating.

 

I don't have any personal experience with "Replication" at the moment, so I can't really offer any help on that.

 

You should consider hosting both the .net and .com from the same server/webspace.

[ryanlwh]

it is very difficult to do what you want with shared hosting. I stumbled upon a free external mysql hosting provider. www.freesql.org. whether or not you want the risk of having an external mysql host is up to you.

 

an alternative is to get your own server or buy a dedicated hosting plan so that all the sites are on one server/intranet.

[frustrated]

Yea ideally it would be nice if both the sites were hosted on the same server, but these aren't my sites and the owner has each site through a totally different hosting company...why...I don't know. I created a simple shopping cart for this person where they go into an admin area to add products. My hope was that they could go into the admin area through the .com site, add products to a database (either set up in the .com or .net), but then I could pull the products out through both the .com and .net.

 

But...it doesn't look like that is going to be possible with the hosting plans she has.

 

Thanks for all your help...have given me some good reading material.

[fenway]

Just a few more comments. First, www.freesql.org is NOT a provider -- it's just a testbed for fooling around with SQL, not for anything that even resembles a production environment. Second, regardless of what the GRANT tables say, if the host has blocked external access to port 3306, you can set up everything with '%' and it still won't matter.

[shoz]

I have two websites that are basically mirrors of each other.

If they're suppose to be the same site, then you can host them from the same docroot on the same server. If any scripts on the site aren't written well, you may need to redirect from one to the other.

 

Talk to the .com or .net host about doing it.

 

You can tell the owner that they'd save money by using one host to do this. If the sites are suppose to differ somehow you should still be able to host them from the same server assuming you're using a decent host. Again, you can talk to them about it.

 

Redirecting from one host to the other even in your current situation could solve your problem (If the sites should be the same).

 

I'll leave you to talk to the owner about changing the way she's doing things. Talking to the hosts in advance may help give you some thoughts on what can be done.