The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Quick Mysql question

I am writing a script that needs to connect to a mysql database on the Internet. I understand that I need to connect using the a defined account, is there any thing else that I am missing ?
Tuesday, June 20, 2006
Yes, a lot of information from your question
Tuesday, June 20, 2006
Ok, I will explain in a little more detail for morons like J.

I want to connect to a mysql server that runs on a machine different from where I plan to run my script. I am trying to connect to the server as root from my script but it does not worlk. Am I missing something ?
Tuesday, June 20, 2006
It depends on what kind of script you are running, but in PHP certainly t doesn't matter if the db is on the same domain or not, all you need are the domain of the MySQL server, account name and password and the name of the database. From there a simple mysqli_connect will do the job. See for full details. Of course, if you're not using PHP then all the above information is completely useless to you, hence why a little more detail will help us to help you.
Paul Brown Send private email
Tuesday, June 20, 2006
> a mysql database on the Internet

Holes in the firewalls, or a VPN connection...
Christopher Wells Send private email
Tuesday, June 20, 2006
Some DBs have an option to prevent root access from anyhwhere but the local computer.  Want to be sure its set to allow remote access
Honu Send private email
Tuesday, June 20, 2006
MySQL certainly allows you to connect as root, but it's a stupid thing to do, unless root isn't really your super user account (possible but unlikely).

Create a dedicated user, make sure the server is listening for network connections, check for firewalls, and away you go.

You should do your testting with the mysql shell of course. Until that works, you don't have a working setup.
PHDude Send private email
Tuesday, June 20, 2006
Oh, and not being an @$$hole helps to. No need to insult people who are trying to help.
PHDude Send private email
Tuesday, June 20, 2006
PHPDude is right about the need for an account.  You shouldn't be connecting with the root account from any scripts.  A different account with remote access to the requested database is a good idea.

If the connection occurs over the open internet, you'll probably need a security arrangement with the server's owner to your machine.  There may be no way to connect though; the recommended security arrangement for mysql is that the server is not exposed to public networks.
Clay Dowling Send private email
Tuesday, June 20, 2006
What do you mean by "it does not worlk"? What goes wrong? Are you getting an error message? If so, what?

We're often happy to help people with problems like this, free of charge even, but it's difficult to do so without a description of the problem that's detailed enough for us to know what's going on.
Tuesday, June 20, 2006
You need a user defined in the remote MySQL system, that is allowed access from the hostname you're trying to connect from (alternatively you can specify all hosts with the '%' wildcard, but that will leave you open to more mischief).  The user should be added to the 'users' table in the 'mysql' database. Make sure to assign the user all 'N' for permissions.  Then create an entry in the 'db' table for the user and the database you want to grant access to (the remote host you're connecting from will go in there as well), and put in all your 'Y' settings for permissions you want to allow the user to have in that table.

Remember to do a "FLUSH PRIVILEGES;" command to reload the new permissions info (stop and restart MySQL will also do that, but why shut it down if you don't need to).

Make sure that "skip-networking" is not enabled in the my.cnf configuration file; that tells MySQL not to listen on port 3306 (the default, or whatever other port you tell it to).

If the remote system is behind a firewall, ensure that it is pinholed to allow your traffic on port 3306 (or whatever you decide to use).
Tuesday, June 20, 2006
take a look at the following:

it's a great mysql class library (called ez-sql) that I have been using for awhile now.  It also has support for a bunch of other database types (postgres,access,ms-sql,etc).

The most likely problem is the fact that you are using the root account.  By default it does not allow access from a remote machine.
Justin Silverton Send private email
Monday, July 03, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz