I have a requirement which has remote mysql clients needing to connect to a mysql server on the net.

I don't know of any interfaces or in-between software so about the only thing I can think of is to change the port so it's not as obvious and of course, give the clients read/write access and not much else for permissions.

Anyone know of some practices for doing such things?

Thanks.

- Don't do that until you know you have to (meaning MySQL-over-SSH using local UNIX socket not possible).
- Read the MySQL documentation.
- Only changing TCP/3306 is only obfuscation: use iptables rules (also see ipset) to restrict traffic.
- Grant user_name@IP or change last octet for wildcard if IP range is required.

1 members found this post helpful.

It's only in the planning stages which is why I am asking questions.

I've been looking for some sort of php application which might accept connections from clients for example instead of giving them direct access to mysql which I do not want to do.

Can't restrict traffic because users would be in the field, using any connection to send updates into the DB.
Figured it would be very basic read/write or only write but to an intermediary server accessible from the net.

Bottom line is I think I am looking for something in between. Something which would accept the data, close the connection, then maybe send it to the actually mysql server.

Basically, it is users on the road, sending updates back home but wondered if there was a way of allow direct access to mysql in some safe way that I might not be aware of.

Thanks for the input.

There might be a way or two that users can access a secure web page to modify or access data.

That sounds positive. I don't need a full web site for this but could put something up if it's so that it could act as a receiver for the connections.

Something simple like name/password, send update, close connection. Guessing something on the server side would later take the data and send it to mysql?

Any chance you might have some leads on this? I've been searching but searching for 'secure web php access modify mysql' is leading to tons and tons of useless stuff

One thing for sure is you want your clients sending data, not interacting with mysql in any way, shape or form. I don't know of any off-the-shelf applications, but if you could set up a secure way for clients to drop the data (sftp maybe?) and then have a program/cron job look for new data and process it, that might do the trick. The data would have to be in a standard format of some kind (json, xml, tab delimited, maybe even a spreadsheet) and the program could probably be a simple perl or python script that parses the file and adds the data to the database.

Right, I don't want anyone to have direct access to mysql and need an intermediary, php based perhaps.
Does anyone know of a simple php app I could use as a starting point?
I would run a very basic web server and the php app.

I'm not a programmer but if I have something to work with, I can often figure things out from there.

You may want to check out hotscripts.com, as they often have good templates to start from. But you don't say what kind of data these users are going to be handling, how large the records are, or what kind of access they need. Are they going to mostly be inserting records into a database? Making live queries? What do the people in the field have to work with?

There are some other ways that are more secure that may work, depending on your details. If people only have to shove records in, it's fairly trivial to put a small web-form locally on their computers, and have that form simply email the data back to your server. A program on the server could read the email and insert the record, and it could easily be written to accept mail only from certain user ID's. And that's just ONE thing you could do..there are many ways to approach this, that will depend on your situation.

I didn't mention the type of data because I don't know yet but I can tell you it's just simple logging stuff from road side monitoring devices. It would be small amounts of data being sent either automatically or by technicians stopping at each location and connecting via satellite to the internet.

Yes, just shoving data in, no output needed.

You still don't say what the techs in the field will be using, so we don't know what kind of resources they'll have. A full-blown Linux laptop with 4G data card has different possibilities than a tablet. That will determine what kind of solution you can put ON that device.

A simple PHP page that has a tech fill in the blanks is trivial. Having that page shovel out an email with that information is also easy. From there, it'll be received by your server, and processed/input. That would seem like the easiest way to go. But again...without details, there's not much we can offer.

There aren't any details to give otherwise I certainly would. My original question was only about safely getting clients reports into mysql.

Php was suggested, which seems like a pretty given way of doing things and that's how it is going to end up being I think. I can't imagine putting mysql directly on the net so the question was pretty much 'are there any other ways of doing this' and the answer seems to be 'nope'

Thanks for the input.

Sorry, but the answer is "yes", which I've said previously. And while you don't have details about this, what you have to work with will determine which solutions fit.

A PHP form would be easy for an end-user to work with, but it's certainly NOT the only thing they could use. They could fill out a spreadsheet, save it as a CSV file, and email it. They could even just bang out the text into the body of an email manually, to keep it VERY rudimentary. The email solution will depend on a server-side program to read and process those emails, which isn't too difficult to write. Then again, it could be that these roadside-devices already have some sort of bluetooth/wifi/wireless connectivity, and burp back data when queried. Could be a simple script to ping the device is all that's needed.

>Sorry, but the answer is "yes", which I've said previously.

Guess that's the problem when one asks questions not necessarily knowing all the correct terms to use.

The only in-between solutions, as I called it, seem to be standard practices, LAMP, POST methods, perhaps curl and it's file upload function, etc etc.

Knowing that putting mysql directly on the net is a bad idea, I wondered if some had done that, perhaps there was a known practice, a way of doing so which was pretty safe. Everyone seems to agree, no, look for an intermediary.

There are lots of ways to get this done and all of them require programming so wondering if there might be something already out there before I start calling on the programmers. Or, once I have all more details and a plan, then I can call on someone for the programming bits.

So the answer to putting mysql on the net is no and using something in-between is yes but nothing specific in terms of a solution specifically for that, only the methods I already know about, as mentioned above.

Not trying to argue, just saying I'm still ending up looking for that solution now.

And thanks for the hotscripts lead, I had forgotten about that place but have been looking on sourceforge and others. Can't seem to give you the Helpful yes, the site keeps telling me I need to post first.

"You must have atleast 1 posts before being able to rate posts. "

I had been looking to replace filemaker in linux. I set up one of the examples but forgot which one it was. I can't find it even now.


It took a lot but part of it was the ability to integrate database with web page.