Page 1 of 1
Accessing MySQL Database on another server
Posted: Fri Jun 13, 2003 12:47 pm
by SBukoski
I haven't investigated this fully, so I'm putting a general feeler out there for it.
What I want to know is if it's possible to access data in a MySQL database that is located on a diferent server. A possible example could be if two sites were working on similar projects where the information was stored in a database, and you wanted to be able to do data sharing.
If I'm running a PHP script on
http://www.myserver.com, can I access a database located at
http://www.yourserver.com?
Thanks...
Yes
Posted: Fri Jun 13, 2003 12:50 pm
by Trill
Yea you can.... just make sure u put in the actual server address and not localhost in msyql_connect
Posted: Fri Jun 13, 2003 1:00 pm
by steedvlx
What trill said...
and make sure that your user/pass is enabled for your specific HOSTNAME or IP address in the other server's MySQL "hosts" table.
If your computer's HOST/IP isn't explicitly granted the same permisions you enjoy at your //localhost then you will probably have privilege problems.
You can set yourself up as username@%,userpass
(the % means connecting from any host)
Then you can access from any host using the same user/pass combo.
(I only say this because I spent three days trying to figure out how to do the same thing)
Hope this helps
----------------------
SteedVLX
Posted: Fri Jun 13, 2003 2:25 pm
by SBukoski
Thanks guys, that cleared things up. I was just reading the documention on mysql.com and could you confirm the following:
1) By default, you are granted the privileges assigned to you in the user table for all operations against all databases.
2) It looks in the appropriate table (eg. host, db, etc.) when you try to perform an action and if it finds a match, will allow operation based on the privileges outlined in that table.
3) It always looks at the highest level of privileges. (eg. It will take the privileges assigned in HOST instead of privileges assigned in DB)
For example, I am trying to update a table and I have my host and username specified in the DB table as having no privileges against this database. Therefore I will get an error, regardless of if I have privileges specified in TABLES_PRIV.
Posted: Fri Jun 13, 2003 9:31 pm
by steedvlx
3) It always looks at the highest level of privileges. (eg. It will take the privileges assigned in HOST instead of privileges assigned in DB)
I believe the priority for privileges to be... HOSTS, DB, TABLES, USER. But, (and anybody correct me if I'm wrong) I have always worked under the assumption that it adopts the "lowest" set of privileges for the connection.
For example, I could be user1 with global god privileges on localhost, but if I connect via a host that has a lower set of rights or is not allowed to connect to that DB at all, then I'm out of luck.
Is that what you were asking about or did I totally miss your point?
--------------------
SteedVLX
Posted: Sat Jun 14, 2003 8:33 am
by SBukoski
That's what I was looking for. I think the best thing for me to do would be to set some priveleges locally and see what happens. If I remember reading correctly, I believe it stated somewhere that the privileges are read in when MySQL starts? So if I were to make a change I would have to stop and start the service.
Posted: Sat Jun 14, 2003 10:20 am
by SBukoski
Actually, I just did a little testing, and the USER table overwrites the privileges of the others. If I defined full privileges in the USER table, it didn't matter what I defined elsewhere. If I defined no privileges in the USER table but partial privileges on the DB table then I would get the privileges defined in the DB table.
So, thanks for the guidance.

Posted: Sat Jun 14, 2003 11:16 am
by steedvlx
...USER table overwrites the privileges of the others.
Thanks for the feedback. I didn't realize that was how it worked.
------------------
SteedVLX